Google-sheets – Issues with a counter

google sheetsgoogle-apps-scriptjavascript

I'm trying to create a button in Google sheets which, when pressed, imports a sheet from a separate spreadsheet into the current one and gives it a name of 'Miscellaneous' followed by the number 1 through to infinity.

For example, the first time the button is pressed it will import a sheet and call it 'Miscellaneous 1' and the second time I press the button it will import a second sheet called 'Miscellaneous 2', so on…

I have the import working and now I'm trying to use a counter to increment the number after 'Miscellaneous'. Can someone tell me what I'm doing wrong? The error is occurring on line 22 

var n = 0;

function countUp() {
    n += 1;
}

function copyFromTemplate(){
  var templateSpreadsheet = SpreadsheetApp.openById('1iXnLkMaPh73lcnkXP1yRV53H824y24FUa1a297OmAKk');
  var template = templateSpreadsheet.getSheets()[0]; //Assuming it is the first sheet

  //The default name will be "Copy of [original name]". We can use this to change it

    countUp();

  var newName = ("Miscellaneous " + n);




  var currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  template.copyTo(currentSpreadsheet).setName(newName);


}

Best Answer

I think the counter only work if all the code is executed at once. Since the function will start again every time you hit the button, it will not count the previous times.

I suggest you count the number of sheets in your Spreadsheet instead, and then subtract the number of sheets that is not a Miscellaneous-sheet.

var number = SpreadsheetApp.getActive().getSheets().length;
var misc = number-2;
Related Topic