Code executing too fast for apps script...?

Question

I wrote some code to execute when this spreadsheet is opened. Mainly, check to see if there are sheets besides the sheet named "Main" and if there is, delete all but the "Main" one. (The "Main" sheet will the first sheet at all times.) Then clear anything that is in the "Main" sheet and replace every time with the things that you can read in the code in the end.

// This creates a menu when this spreadsheet is opened.
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  var numSheets = ss.getSheets();
  while (numSheets.length > 1){
    ss.setActiveSheet(numSheets[1]);
    ss.deleteActiveSheet();
    var numSheets = ss.getSheets();
  }

  ss.setActiveSheet(numSheets[0]);
  ss.getActiveSheet().getDataRange().clear();
  menuEntries.push({name: "Create Folder Structure", functionName: "folderStruc"});
  ss.addMenu("Click here when done.", menuEntries);


  ss.getRange('A1:B1').mergeAcross().setValue('TA');
  ss.getRange('A2').setValue('First Name');
  ss.getRange('B2').setValue('Last Name');
  ss.getRange('C2').setValue('Email');
  ss.getRange('D2').setValue('Classes');

}

The script works perfectly when I debug line by line. It also works good when there is only the "Main" sheet there. But when there is multiple sheets and I open the spreadsheet, it would run through the while loop and delete all the sheets except the "Main", but it would hang on the "Main" one and just display "Loading..." and not show any cells or anything. The custom menu shows up. Also it displays "Working" in a yellow box at top.

Remove "ss.getSheets()" from the loop? what if you only have 2 sheets, do you hit the problem. I suspect the problem is the network. I suspect some of the functions in your loop are slow because they make a network call.... Or you could remove them via a trigger. — eddyparkinson
The solution Serge answered below solved it. But you are right, it is a network problem. — PhysLabTsar

Serge insas Serge insas · Accepted Answer · 2013-06-26T07:50:19

Your code works if you simply add a small delay between each sheet delete, it's a known issue when deleting multiple sheets.

Here is the code:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  var numSheets = ss.getSheets();
  while (numSheets.length > 1){
    ss.setActiveSheet(numSheets[1]);
    ss.deleteActiveSheet();
    Utilities.sleep(1000);// 1 second delay is sufficient and seems optimal from my experience.
    var numSheets = ss.getSheets();
  }

  ss.setActiveSheet(numSheets[0]);
  ss.getActiveSheet().getDataRange().clear();
  menuEntries.push({name: "Create Folder Structure", functionName: "folderStruc"});
  ss.addMenu("Click here when done.", menuEntries);


  ss.getRange('A1:B1').mergeAcross().setValue('TA');
  ss.getRange('A2').setValue('First Name');
  ss.getRange('B2').setValue('Last Name');
  ss.getRange('C2').setValue('Email');
  ss.getRange('D2').setValue('Classes');
}

Note: you mention in your question that you won't delete the sheet called "main" but I don't see any condition in your script so I guess you rely on the sheets order. It would be quite simple to add a condition that checks the sheetName before deleting so you won't risk to delete the main sheet by accident if for any reason the sheet order has been modified. Something like this to replace the original while loop will put the sheets at its place if necessary.

  while (numSheets.length > 1){
    ss.setActiveSheet(numSheets[1]);
    Utilities.sleep(1000);
    if(ss.getActiveSheet().getName()=='main sheet'){
    ss.moveActiveSheet(0);
    }else{
    ss.deleteActiveSheet();
    }
    var numSheets = ss.getSheets();
  }

Code executing too fast for apps script...?

1 Answers