Apps script onedit triggered too fast causing code conflicting with itself

Question

I'm trying to make an applet in Google Spreadsheets and Apps script that scrapes view data about youtube videos searched by users. When a user types in a search query, a new sheet should be copied from a template sheet and customized to the query. The problem I'm encountering is that when a user rapidly types in multiple queries in succession, the script would dump multiple copies of the template sheet and name them 'Copy of template 1', 'Copy of template 2' and so on, whereas the name of each sheet should be "KW: " + its associated keyword. I suspect this is because the function duplicates and renames a sheet, but if two or more instances of a function try this at nearly the same time, they target the same sheet, causing errors.

This is the culmination of what I've tried:

function main(e){
  //spreadsheet=SpreadsheetApp.getActiveSpreadsheet() at the top of the function
  //keyword=the string the user typed in
  //...
  while(true){
    var random=Math.random().toString();
    try{
      //assign the template sheet a random name other processes will fail when they try to use it
      spreadsheet.getSheetByName('template').setName(random);
      //make a copy of the template
      spreadsheet.getSheetByName(random).copyTo(spreadsheet);
      //give the copy a proper name
      spreadsheet.getSheetByName('Copy of '+random).setName("KW: "+keyword);
      //reset the name of the template so other processes can use it
      spreadsheet.getSheetByName(random).setName('template');
      break;
    }
    //when a process fails, it should wait then try again
    catch(e){Utilities.sleep(Math.round(random*250));}
  //...
}

main is has a trigger set on edit. The above code prevents any 'Copy of template n' sheets from appearing, but it simply leaves out most of the sheets that should be produced. My guess is that the code encounters an error in the first line of the try block and loops until it times out. I'm very much at a loss as to what to do. I'd appreciate any help, thank you!

Diego Diego · Accepted Answer · 2018-04-09T07:08:45

Try copying the template sheet first, then changing the name of the new sheet. Your solution runs into errors because you're modifying the template sheet, but you should really avoid doing that. With this approach, you'll never modify the template sheet.

function main(e){
  //spreadsheet=SpreadsheetApp.getActiveSpreadsheet() at the top of the function
  //keyword=the string the user typed in
  //...
  while(true){ 
    try{
      // Select the template sheet
      var templateSheet = spreadsheet.getSheetByName("template");
      // Set the new sheet name
      var sheetName = "KW: "+keyword;
      // Copy the template sheet and set name
      var newSheet = templateSheet.copyTo(spreadsheet).setName(sheetName);
      break;
    }
    //when a process fails, it should wait then try again
    catch(e){Utilities.sleep(Math.round(random*250));}
    //...
  }
}

Granted that I don't have full knowledge of what you're doing with your script, I do have some major concerns with your approach. I don't think you should be creating a new sheet for every query. For one, that will make your data really challenging to aggregate. Secondly, since sheet names must be unique, any time someone searches for something that already has an existing sheet, your function will get stuck in the infinite while loop you set up.

I don't know what kind of data you're placing in the sheets, but you should consider trying to record the data in one sheet. Also, although your while loop works and will, in the case of errors, eventually be terminated by Google's script limitations, you should really try (1) implementing something more robust like appending a number to the sheet name and (2) properly logging the errors.

Apps script onedit triggered too fast causing code conflicting with itself

1 Answers