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]);

About The issue I'm having is that I can't figure out how to import an entire row all at once., can you provide the sample Spreadsheet of googleSheetTemplate as an image? And, in your situation, your script works fine. But you want to reduce the process cost of your script. Is my understanding correct? — Tanaike
– Tanaike, Commented Jan 31, 2022 at 5:42
Thanks for the help! I've added a link to a copy of the template above (because it all feeds into one row an image was only going to capture the first few columns). You are correct that I'm trying to reduce the process cost though I could also live with a workaround to the google script timing out. — Matt
– Matt, Commented Jan 31, 2022 at 7:09
Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your goal and that was not useful, I apologize. — Tanaike
– Tanaike, Commented Jan 31, 2022 at 7:44

Tanaike · Accepted Answer · 2022-02-01 07:17:18Z

0

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());
}

edited Feb 1, 2022 at 7:17

answered Jan 31, 2022 at 7:44

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Over a year ago

THANK YOU! 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. Again - I can not say thank you enough!

Tanaike Over a year ago

@Matt Thank you for replying. I apologize for the inconvenience. For your replying, I added one more sample script in my answer. Could you please confirm it? If I misunderstood your reply, I apologize again.

Matt Over a year ago

You are amazing and there is no need to apologize! This line of the code: Sheets.Spreadsheets.Values.batchUpdate({data: values, valueInputOption: "USER_ENTERED"}, ss.getId()); Gives me an error message that says that "sheets" is undefined. I also realized that if I get the URL to populate into another cell I can then use a second script to email the URL straight to the students. I managed to fix all the bugs and got a working script. I'm not sure how to post a link in a comment but I've edited the script in the sample sheet I posted above to show the final working code. THANK YOU!

Matt Over a year ago

Ahh - I believe you are correct! I got distracted and forgot to enable it! Thanks again for the help - trying to figure out where the accept button is now :)

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points:

Modified script:

Reference:

Added:

Modified script:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Reference:

Added:

Modified script:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related