Can I use Google Apps Script to replace text across multiple tabs in a Google Sheet?

Question

I am building on a previous project in which I have a Google Form which takes responses in a Google Sheet and uses a template Sheet to populate the Form responses and have that generate a new Sheet document. This is a very dumbed-down version of what I'm trying to execute in reality, but the goals remain the same: I am trying to replace text across multiple tabs in the template Sheet when generating a new one.

Currently, in my Apps Script, I have code which is successfully able to make a copy of the template file and name it accordingly:

//Enter collected info into Requirements Template
      const googleSheetTemplate = DriveApp.getFileById('1wqCwMhpuDLReU1hE1CbcDL-Vdw_4zge1xM6oOl34Ohg');
      const destinationFolder = DriveApp.getFolderById('1GxNZQmP8mxHBhVl5AMoqBFs8sAIYzcm3');

      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 2');

      const copy = googleSheetTemplate.makeCopy(`${row[3]}, ${row[0]} Vehicle Order` , destinationFolder);
      const newSheet = SpreadsheetApp.openById(copy.getId());
      const A1 = newSheet.getDataRange();

And the next few lines which are meant to be able to find and replace certain strings within the newly copied Sheet are as follows:

  A1.createTextFinder("{{Customer}}").replaceAllWith(row[3]);
  A1.createTextFinder("{{Car}}").replaceAllWith(row[1]);
  A1.createTextFinder("{{Color}}").replaceAllWith(row[2]);
  A1.createTextFinder("{{Delivery}}").replaceAllWith(row[5]);

The issue I am experiencing is that the first tab of the Sheet gets populated, but the second tab does not.

Is there more I must add somewhere in order to get the second tab filled out? Is this even possible in Google Apps Script?

---

Entire code below:

function myFunction() {
    // get the spreadsheet information
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //const responseSheet = ss.getSheetByName('Form Responses 2');
  const data = ss.getDataRange().getValues();
  //console.log(data);

  // Loop over the rows
  data.forEach((row,i) => {

    // Identify whether notification has been sent
    if (row[4] === '') {

      // Get the Form info
      var emailTo = "jeffreyabr@gmail.com"
      var subject = 'Car Request';
      const Timestamp = row[0];
      var Car = row[1];
      var Color = row[2];
      var requestor = row[3]
      var delivery = row[5];

      //Form variable declarations
      formTime = Timestamp;
      formCar = Car;
      formColor = Color;
      formName = requestor;
      formDelivery = delivery;

      //Enter collected info into Requirements Template
      const googleSheetTemplate = DriveApp.getFileById('1wqCwMhpuDLReU1hE1CbcDL-Vdw_4zge1xM6oOl34Ohg');
      const destinationFolder = DriveApp.getFolderById('1GxNZQmP8mxHBhVl5AMoqBFs8sAIYzcm3');

      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 2');

      const copy = googleSheetTemplate.makeCopy(`${row[3]}, ${row[0]} Vehicle Order` , destinationFolder);
      const newSheet = SpreadsheetApp.openById(copy.getId());
      const A1 = newSheet.getDataRange();

      A1.createTextFinder("{{Customer}}").replaceAllWith(row[3]);
      A1.createTextFinder("{{Car}}").replaceAllWith(row[1]);
      A1.createTextFinder("{{Color}}").replaceAllWith(row[2]);
      A1.createTextFinder("{{Delivery}}").replaceAllWith(row[5]);


      const orderLink = newSheet.getUrl();
      //Add URL to Sheet
      sheet.getRange(i + 1, 7).setValue(orderLink)

      orderBlob = [];

      //Get the blob of order attachment
      if(row[6]){
        var order1 = row[6].split(', ');
      
        order1.forEach(url => {
          var orderFileId = url.replace('https://drive.google.com/open?id=','');
          var orderFile = DriveApp.getFileById(orderFileId);
          orderBlob.push(orderFile.getBlob());
        });
      }


      let body = '';

      // Generate email
      var html = HtmlService.createTemplateFromFile("email.html");
      var htmlText = html.evaluate().getContent();
      
      // Send email
      GmailApp.sendEmail(emailTo, subject, body, {htmlBody: htmlText, attachments: orderBlob})

  // Mark as Notified
  const g = 'Notification sent';
  ss.getRange(i + 1,5).setValue(g);
      }
  })
}

Answer 1

Answer

• You can try a looping method to access all of the sheet tabs inside of your newSheet Spreadsheet file using the getSheets() method.

Just replace part of your script from the creation of newSheet to the last line of A1.createTextFinder with the script below:

[UPDATED] Sample Script

  const newSheet = SpreadsheetApp.openById(copy.getId());
    
  for (currentSheet = 0; currentSheet < newSheet.getSheets().length; currentSheet++) {
    const a1 = newSheet.getSheets()[currentSheet].getDataRange();
    a1.createTextFinder("{{Customer}}").replaceAllWith(row[3]);
    a1.createTextFinder("{{Car}}").replaceAllWith(row[1]);
    a1.createTextFinder("{{Color}}").replaceAllWith(row[2]);
    a1.createTextFinder("{{Delivery}}").replaceAllWith(row[5]);
  }

Sample Test:

Sample copy of the newSheet file w/ 2 sheet tabs

Script test demonstration

The newSheet file that contains 2 sheet tabs:

Sheet 1

Sheet 2