Google sheets appscript to copy tabs to new sheets

Question

I have a google sheet with around 190 tabs on that i need to split into 190 different files The files need to be named the same as the tab, the contents of the tab need to be copied as values but i also need to bring the formatting accross (just not the formulas).

I have looked around, and through a combination of previous questions and answers plus using the function list help have formed the following code. It actually works for the first few tabs but then throws up an error about being unable to delete the only sheet.

    function copySheetsToSS() {
  var ss = SpreadsheetApp.getActive();
  for(var n in ss.getSheets()){
    var sheet = ss.getSheets()[n];// look at every sheet in spreadsheet
    var name = sheet.getName();//get name
    if(name != 'master' && name != 'test'){ // exclude some names
      var alreadyExist = DriveApp.getFilesByName(name);// check if already there
      while(alreadyExist.hasNext()){
        alreadyExist.next().setTrashed(true);// delete all files with this name
      }
      var copy = SpreadsheetApp.create(name);// create the copy
sheet.copyTo(copy);     
copy.deleteSheet(copy.getSheets()[0]);// remove original "Sheet1"
copy.getSheets()[0].setName(name);// rename first sheet to same name as SS
      
      
var target_sheet = copy.getSheetByName(name);
var source_range = sheet.getRange("A1:M50");
var target_range = target_sheet.getRange("A1:M50");
      
var values = source_range.getValues();

target_range.setValues(values);

    }
  }
}

I am hoping someone can tell me what i have done wrong as I cannot figure it out at this point. I am also open to better solutions though please be aware I am very much a beginner on google appscript, nothing too complex please.

thankyou

I guess the error is obvious. You can't delete the last sheet. In other words, a spreadsheet file needs to have at least one sheet. In fact, you can't even hide all sheets. At least one needs to be visible. — soMario
the 'sheet.copyTo(copy); ' line is copying over a sheet, so at the point i am deleting the 1st sheet there should be 2 sheets meaning 1 will be left over, but yes that is where i am going wrong somehow — LCousins

ziganotschka ziganotschka · Accepted Answer · 2020-12-08T12:49:43

In principle your script correctly adds a new sheet to the new spreadsheet before removing the preexisting one

However, mind that calls to service such as SpreadsheetApp are asynchronous.

And this becomes the more noticeable, the longer your script runs.

In your case it apparently leads to behavior that the only sheet is being deleted before the new sheet is being created.

To avoid this, you can force the execution to be synchronous by implementing calls to SpreadsheetApp.flush().

This will ensure that the old sheet won't be deleted before the new one gets inserted.

Sample:

copy.deleteSheet(copy.getSheets()[0]);// remove original "Sheet1"
SpreadsheetApp.flush();
copy.getSheets()[0].setName(name);

You might want to introduce call toflush()` also at other positions where it is important for the code to run synchronously.

Google sheets appscript to copy tabs to new sheets

1 Answers

In principle your script correctly adds a new sheet to the new spreadsheet before removing the preexisting one