Google Sheets – Remove Sheets Not Listed in an Array

google sheetsgoogle-apps-script

I have a Google App Script that has 6 sheets. onOpen, I would like to see if the user created any additional sheets, and if so, delete them.

So far, I have:

 function onOpen(){

  var all_sheet_names = ["Instructions", "Data", "Print", "Schedule" ,"Archive", "Projections"];
  var sheets_present = sheetnames();
  Logger.log(sheets_present);

  var diff = difference(sheets_present, all_sheet_names);

  Logger.log(diff);

}

function difference(all_sheets, all_sheet_names) {  
  var result = [];
  for (var i=0; i < all_sheets.length; i++) {
    if (all_sheet_names.indexOf(all_sheets[i]) === -1) {
      result.push(all_sheets[i]);
    }
  }
  return result;
}

function sheetnames() { 
  var all_sheets = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) all_sheets.push( [ sheets[i].getName() ] )
  return all_sheets  
}

My return from difference is just an array of all sheets, not just the ones that are different.

Any suggestions?

Best Answer

The problem is here:

all_sheets.push( [ sheets[i].getName() ] )

This should be changed to 

all_sheets.push( sheets[i].getName() );

The second version inserts a sheet name into array, which is what you want. The first version inserts a one-element array into array, resulting in a double array like [['Sheet1'], ['Sheet2'], ['Sheet3']].

For completeness, here is how I would write the whole thing:

function onOpen() {
  var all_sheet_names = ["Instructions", "Data", "Print", "Schedule" ,"Archive", "Projections"];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (all_sheet_names.indexOf(sheets[i].getSheetName()) == -1) {
      ss.deleteSheet(sheets[i]);
    }
  }
}
Related Topic