https://www.pbainbridge.co.uk/2026/04/append-sheet-data-to-doc-with-varying.html

Append Sheet data to a Doc with varying Table sizes.gs

/*
Create menu item to run script from spreadsheet.
*/

function onOpen() {
  
  SpreadsheetApp.getUi()
  .createMenu('Create Docs')
  .addItem('Create Docs', 'getSpreadsheetData')
  .addToUi();
  
};



/*
This overall script is designed to bulk create Google Docs from data within a Google Sheet.
*/

function getSpreadsheetData() {

  // Log starting of the script
  console.log('Script has started');

  // get current spreadsheet
  var spreadsheet = SpreadsheetApp;
  var ss = spreadsheet.getActiveSpreadsheet();


  // display Toast notification to inform starting of the script
  ss.toast('Script has now started', 'Start');


  // get Config sheet
  var configSheet = ss.getSheetByName('Config');

  // get Drive Folder ID for storing created Docs
  var destinationFolderId = configSheet.getRange(1, 2).getValue();
  console.log('destinationFolderId is: ' + destinationFolderId);

  // get Template File ID
  var templateFileId = configSheet.getRange(2, 2).getValue();
  console.log('templateFileId is: ' + templateFileId);


  // get Data sheet
  var dataSheet = ss.getSheetByName('Data');

  // get all data as a 2-D array
  var data = dataSheet.getDataRange().getValues();


  // create a name:value pair array to send the data to the next Function
  var spreadsheetData = {
    spreadsheet: spreadsheet, dataSheet: dataSheet, destinationFolderId: destinationFolderId,
    templateFileId: templateFileId, data: data
  };


  // run Function to create Google Docs
  createDocs(spreadsheetData);

};



function createDocs(spreadsheetData) {

  // Log starting createDocs Function
  console.log('Starting createDocs Function');


  // separate out data from name:value pair array
  var spreadsheet = spreadsheetData['spreadsheet'];
  var ss = spreadsheet.getActiveSpreadsheet();
  var dataSheet = spreadsheetData['dataSheet'];
  var destinationFolderId = spreadsheetData['destinationFolderId'];
  var templateFileId = spreadsheetData['templateFileId'];
  var data = spreadsheetData['data'];


  try {
    // try getting the Folder and then set variable as true if successful
    var destinationFolder = DriveApp.getFolderById(destinationFolderId);
    var gotDestinationFolder = true;
  }
  catch (e) {
    // if failed set variable as false and Log
    var gotDestinationFolder = false;
    console.log('Failed to get destinationFolder with error: ' + e);
  }


  // only proceed if got destination Drive Folder
  if (gotDestinationFolder) {

    try {
      // try getting the Template File and then set variable as true if successful
      var templateFile = DriveApp.getFileById(templateFileId);
      var gotTemplateFile = true;
    }
    catch (e) {
      var gotTemplateFile = false;
      console.log('Failed to get templateFile with error: ' + e);
    }

  }
  else {
    // do nothing
  }


  // only proceed if got Template File also
  if (gotTemplateFile) {


    // loop through each row of data and create new Doc ******************************
    var dataLength = data.length;
    for (var i = 1; i < dataLength; i++) {


      // extract values from array of data
      var emailAddress = data[i][0];
      var firstName = data[i][1];
      var lastName = data[i][2];
      var module1code = data[i][3];
      var module1mark = data[i][4];
      var module2code = data[i][5];
      var module2mark = data[i][6];
      var module3code = data[i][7];
      var module3mark = data[i][8];


      // create name for new File
      var newFileName = 'Bulk create Docs - ' + lastName + ', ' + firstName;

      // make copy of Template File and place in destination Drive Folder
      var newFile = templateFile.makeCopy(newFileName, destinationFolder);

      // get ID and Url of new File
      var newFileID = newFile.getId();
      var newFileUrl = newFile.getUrl();


      try {
        // try opening the new File, get its Body and then set variable as true if successful
        var newFileBody = DocumentApp.openById(newFileID).getBody();
        var openNewFile = true;
      }
      catch (e) {
        // if failed set variable as false and Log
        var openNewFile = false;
        console.log('Failed to open newFileBody with error: ' + e);
      }



      // only proceed if got Body of new File
      if (openNewFile) {

        // replace default tags with data from sheet
        newFileBody.replaceText('<<emailAddress>>', emailAddress);
        newFileBody.replaceText('<<firstName>>', firstName);
        newFileBody.replaceText('<<lastName>>', lastName);
        newFileBody.replaceText('<<module1code>>', module1code.toString());
        newFileBody.replaceText('<<module1mark>>', module1mark.toString());



        // dynamically add table rows for Modules 2, 3, etc. This assumes the modules are going into the FIRST table in the Google Doc
        var tables = newFileBody.getTables();

        // checks there is a table before proceeding
        if (tables.length > 0) {

          // grabs the first table
          var moduleTable = tables[0];


          // check if module 2 has data. If it does, append a row and cells.
          if (module2code && module2mark !== '') {

            var row2 = moduleTable.appendTableRow();
            row2.appendTableCell(module2code.toString());
            row2.appendTableCell(module2mark.toString());

          } else {
            // do nothing as values are blank
          };


          // check if module 3 has data. If it does, append a row and cells.
          if (module3code && module3mark !== '') {

            var row3 = moduleTable.appendTableRow();
            row3.appendTableCell(module3code.toString());
            row3.appendTableCell(module3mark.toString());

          } else {
            // do nothing as values are blank
          };


        } else {
          // do nothing as no tables found
        };



        // paste a hyperlink to new File in spreadsheet
        var newFileLink = '=HYPERLINK("' + newFileUrl + '","' + newFileName + '")';
        dataSheet.getRange(i + 1, 10).setFormula(newFileLink);


        // refresh spreadsheet to links appear as soon as added
        spreadsheet.flush();


        // display Toast notification to confirm row complete
        ss.toast('Row ' + (i + 1) + ': ' + newFileName, 'Completed Doc');


        // save and close the document to ensure changes are applied immediately
        DocumentApp.openById(newFileID).saveAndClose();


      } else {
        // do nothing
      };


    };
    // loop through each row of data and create new Doc ******************************


  }
  else {
    // do nothing
  }


  // display Toast notification to inform end of the script
  ss.toast('Script has now ended', 'End');


  // Log end of the script
  console.log('Script has ended');


};