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

}

Answer 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: