How do I use a GoogleScript to replace an entire row in a template googlesheet based off of a master googlesheet

Question

I currently have two google sheets.

• 1 is a master sheet in which each row contains information specific to an individual student.
• The other is a template that I hope to make a copy of and then populate with information from a specific row of the master sheet (so each student will have their own googlsheet with only their information in it.

The issue I'm having is that I can't figure out how to import an entire row all at once.

My initial attempt was based off of this idea. However, since I am trying to import roughly 1000 different cells (yes all in the same row) the script times out before completing the script for 60 students. I assume it will be faster to import an entire row instead of using "createTextFinder" to replace all 1000 references. I've also read that using a trigger may be a work around to the script timing out - but I also couldn't figure that out (yet). I'd be happy using either solution. The part of the code I believe you need to reference is below. And this is a copy of my master sheet. [update] This is a copy of the template sheet (note that I made a copy so the sheet ID won't match the code).

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

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


  //Enter collected info into Requirements Template
  const googleSheetTemplate = DriveApp.getFileById('1DZZVZ5y1r5RmP4dMYmZuBQWPzIPp6eVZsdUrrador3o');
  const destinationFolder = DriveApp.getFolderById('10px5WeNyVa9jSNQjCKV9jwcacCDvpByp');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');

  const copy = googleSheetTemplate.makeCopy(`${row[1]}, ${row[2]}, ${row[938]} Report Card` , destinationFolder);
  const newSheet = SpreadsheetApp.openById(copy.getId());
  const A1 = newSheet.getDataRange();

  for (currentsheet = 0; currentsheet < newSheet.getSheets().length; currentsheet++) {
  const a1 = newSheet.getSheets()[currentsheet].getDataRange();
  A1.createTextFinder("{{StudentEmail}}").replaceAllWith(row[0]);
  A1.createTextFinder("{{FirstName}}").replaceAllWith(row[1]);
  A1.createTextFinder("{{LastName}}").replaceAllWith(row[2]);

Answer 1

I believe your goal is as follows.

• You want to reduce the process cost of your script.

Modification points:

• When I saw your template Spreadsheet and the source Spreadsheet, I understood that you wanted to copy the values from a row of the source Spreadsheet to the template Spreadsheet. And also, I thought that the order of columns of the copied row is the same between the source and the template. In this case, SpreadsheetApp.create might be used instead of makeCopy. And also, a lot of createTextFinder is not required to be used.
• In your script, orderBlob is not used. So in this modification, I removed it.
• In order to put the URL and the value of Spreadsheet Ready, I used Sheets API.

When these points are reflected in your script, how about the following modification?

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function sample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Data");
  const [, ...data] = sheet.getDataRange().getValues();
  const destinationFolder = DriveApp.getFolderById('10px5WeNyVa9jSNQjCKV9jwcacCDvpByp');
  const values = data.flatMap((row, i) => {
    if (row[936] != 'send') return;
    const newSheet = SpreadsheetApp.create(`${row[1]}, ${row[2]}, ${row[938]} Report Card`);
    newSheet.getSheets()[0].setName("Import Data").appendRow(row);
    DriveApp.getFileById(newSheet.getId()).moveTo(destinationFolder);
    return [
      {values: [[newSheet.getUrl()]],range: `'Data'!${sheet.getRange(i + 1, 939).getA1Notation()}`},
      {values: [["Spreadsheet Ready"]],range: `'Data'!${sheet.getRange(i + 1, 950).getA1Notation()}`}
    ]
  });
  if (values.length == 0) return;
  Sheets.Spreadsheets.Values.batchUpdate({data: values, valueInputOption: "USER_ENTERED"}, ss.getId());
}

Reference:

• Method: spreadsheets.values.batchUpdate

Added:

From your following reply,

The Script works exactly as you described it would. It is nice to know that I am not going crazy. The reason I was using makeCopy is because I want to insert formulas and conditional formatting to help the students process the data (think graphs and charts). I'm currently trying to edit your script to replace the spreadsheetapp.create with the makecopy function - I'll reply again but feel free to make the edit as well.

When you are required to use makeCopy, how about the following sample script?

Modified script:

function sample2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Data");
  const [, ...data] = sheet.getDataRange().getValues();
  const googleSheetTemplate = DriveApp.getFileById('1DZZVZ5y1r5RmP4dMYmZuBQWPzIPp6eVZsdUrrador3o');
  const destinationFolder = DriveApp.getFolderById('10px5WeNyVa9jSNQjCKV9jwcacCDvpByp');
  const values = [data[0], data[1]].flatMap((row, i) => {
    if (row[936] != 'send') return;
    const copy = googleSheetTemplate.makeCopy(`${row[1]}, ${row[2]}, ${row[938]} Report Card` , destinationFolder);
    const newSheet = SpreadsheetApp.open(copy);
    newSheet.getSheets()[0].getRange(1, 1, 1, row.length).setValues([row]);
    DriveApp.getFileById(newSheet.getId()).moveTo(destinationFolder);
    return [
      {values: [[newSheet.getUrl()]],range: `'Data'!${sheet.getRange(i + 1, 939).getA1Notation()}`},
      {values: [["Spreadsheet Ready"]],range: `'Data'!${sheet.getRange(i + 1, 950).getA1Notation()}`}
    ]
  });
  Sheets.Spreadsheets.Values.batchUpdate({data: values, valueInputOption: "USER_ENTERED"}, ss.getId());
}