How to stop Google App Script is duplicating copies?

Question

I am trying to write a script so that when a new row is added to our google sheet, it will copy a template spreadsheet and paste the link into the corresponding column. The sheet is updated automatically when a new person signs up.

The script below can copy the template sheet, rename it based on the adjacent text in row A and then and paste the link in column 20.

Unfortunately, every time it runs it creates new sheets for all rows in the sheet, instead of just finding the latest row and creating 1 new sheet.

Also, I want to set the share permissions for the new share the spreadsheet to a certain group, rather than anyone with the link.

////function copyTo() {
// var tempsheet = SpreadsheetApp.openById("1q....");
//var mastersheet = tempsheet.getSheetByName("Setting Sheet");
//var lrow = mastersheet.getLastRow();
//for (i= 2; i<lrow; i++ ) {
//let names = mastersheet.getRange(i,1).getValues();
//sheet = tempsheet.copy(names);
//}
//}

//function createSpreadsheet(){
//var tempsheet = '1....';
//var sheetName = "MASTER"
//SpreadsheetApp.openById(tempsheet).copy(sheetName);

//for (r=2; r<lrow; r++) {
//let name = ss.getRange(r,1).getValue();


function cloneGoogleSheet(fileName) {
  var destFolder = DriveApp.getFolderById("19Q....");
  var newFile = DriveApp.getFileById("1vvgD....").makeCopy(fileName, destFolder);
  return newFile.getUrl();
}

function createSheets() {
  var workBook = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = workBook.getSheetByName("Sheet1")
  var data = sheet.getRange("A2:A" + sheet.getLastRow()).getValues()

  data.forEach((row, index) => {
    sheet.getRange(index + 2, 20).setValue(cloneGoogleSheet(row[0]))
  })

}

data.forEach() will create a new file for every row of column A containing data. So if you add a new row of data the forEach will create a new file for the old data as well as the new row data. You probable need to include a check if the url exists skip the copy process. — TheWizEd
– TheWizEd, Commented May 18, 2022 at 19:09
Welcome to Stack Overflow. The script doesn't include anything to "just finding the latest spreasdsheet entry" and to "share the spreadsheet with the same group". If you haven't done this yet, please read developers.google.com/apps-script/guides/sheets. If you need further help, I suggest you to focus your question on one specific issue, show what you have tried to solve and include a brief description of your search efforts as is suggested in How to Ask related to that issue. — Wicket
– Wicket, Commented May 18, 2022 at 21:17

halfer · Accepted Answer · 2025-01-05 16:54:36Z

1

Your main goal is to copy one unique template of the spreadsheet and paste its link in the corresponding column (column #20 from your sample script) adjacent to the newly inserted/added row in the Sheet file every time your API inserts a new user sign up info.

I suggest using a Time-driven installable trigger. This way, you will be able to run a script (e.g. every number of minutes/hours or time of the day) that will capture new row(s) of data in your sheet being updated by your API.

For more context, here's a tweaked script for this implementation & its demonstration below.

Tweaked Script:

var workBook = SpreadsheetApp.getActiveSpreadsheet();
var sheet = workBook.getSheetByName("Sheet1");

function storeLastRow() { //Run this once here in the editor to kickstart the whole script
  try {
    const scriptProperties = PropertiesService.getScriptProperties();
    scriptProperties.setProperty('LAST_ROW', sheet.getLastRow());
    const recentRow = scriptProperties.getProperty('LAST_ROW');
    console.log("Last Row recorded: "+ recentRow); //LOG FOR TRACKING
  } catch (err) {
    Logger.log('Failed with error %s', err.message);
  }
}

function captureNewRow() {
  const scriptProperties = PropertiesService.getScriptProperties();
  const recentRow = scriptProperties.getProperty('LAST_ROW');
  createSheets(parseInt(recentRow));
  storeLastRow(); //This will auto-store the new last row on your script for the next time trigger
}

function createSheets(recentRow) {
  console.log("LAST ROW RECORDED: "+recentRow+"\nCURRENT LAST ROW: "+sheet.getLastRow()); //LOG FOR TRACKING
    for(var i = recentRow; i < sheet.getLastRow(); i++){ //Loop through the new rows one-by-one and save a dedicated copy per new rows
      var filename = sheet.getRange((i+1),1).getValue();
      sheet.getRange((i+1), 20).setValue(cloneGoogleSheet(filename))
      console.log("Done processing new row #"+ (i+1)); //LOG FOR TRACKING
    }
}

function cloneGoogleSheet(fileName) {
  var destFolder = DriveApp.getFolderById("1VaI...");
  var newFile = DriveApp.getFileById("1oJ2...").makeCopy(fileName, destFolder);
  return newFile.getUrl();
}

Demonstration:

Firstly, run the storeLastRow function once to kick start the process. This will store the current last row in your sheet.

Next, create a time-driven trigger for the captureNewRow function (in my testing, I run it every minute):

Let's say there's a new row of data added on the sheet (as seen below) & after a few minutes, the captureNewRow function runs and adds the copy of the template file on the column 20 adjacent to the new row:

If multiple rows of data were added on the sheet before the next time trigger run, the captureNewRow function can also process them as seen below:

edited Jan 5 at 16:54

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered May 19, 2022 at 1:51

SputnikDrunk2

4,0881 gold badge9 silver badges20 bronze badges

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

6 Comments

SputnikDrunk2 Over a year ago

@Mr Potato Please check the info above. Feel free to comment in case you have follow up questions. I hope this helps.

Mr Potato Over a year ago

Thanks so much for your reply. I can see you solution works perfetly based on the way I phrase my question of adding a row. What I actually meant was when text is added to the row. So for example, there is an api running which updates rows 1 at a time and adds it to the next available row on the sheet. The onchange trigger works, but it should pickup the last populated row. Hope that makes sense and is something you can help with? Thanks :)

SputnikDrunk2 Over a year ago

@MrPotato I see. So the next available row is being populated automatically by the API, does this mean the sheet isn't being updated manually by any user? Also, kindly update your post and explain how your setup works for better understanding.

Mr Potato Over a year ago

Thanks Irvin. I've updated my post to try and better describe the setup.

SputnikDrunk2 Over a year ago

Hmm.. if the sheet row is being updated automatically without any user interaction, it seems the only way is via using a time-driven trigger (also called a clock trigger). I'll take a look...

|

Collectives™ on Stack Overflow

How to stop Google App Script is duplicating copies?

1 Answer 1

Tweaked Script:

Demonstration:

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Tweaked Script:

Demonstration:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related