Google Sheets – Run Script Through Multiple Tabs

google sheetsgoogle-apps-script

I have a script which deletes columns of information that is now rendered obsolete.

However, I have multiple tabs which means going through them all manually, how can I edit my script to do this automatically for me, I have been trying for an hour or so now on Google trying other peoples solutions but because my coding skills is below basic I have no clue what I am doing.

function deleteColumns() {
  var required = ["Owned",  "Set",  "Card number",  "Edition",  "Rarity",   "Name", "Card type",];

  var sheet = SpreadsheetApp.getActiveSheet();
  var width = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
  for (var i = headers.length - 1; i >= 0; i--) {
    if (required.indexOf(headers[i]) == -1) {
      sheet.deleteColumn(i+1);
    }
  }
}

Log 1
Log 2

Best Answer

Explanation:

You need to make two modifications:

  • Use getSheets to get all the sheets of your spreadsheet file.

  • Define a list of sheet names you would like to execute your current solution to:

    var selectedSheets = ["Sheet1","Sheet5","Test2"];

  • Create a for loop (in this case forEach) and iterate over the sheets.

  • Finally (optional), for every sheet check if the sheet name is included in the selectedSheets array you defined earlier.

Solution - Run on selected sheets:

Adjust selectedSheets to include the sheet names for which you want to run the script. Make sure the names match exactly the name of your sheets. No extra spaces or different lower/upper case letters.

function deleteColumns() {
  var required = ["Owned",  "Set",  "Card number",  "Edition",  "Rarity",   "Name", "Card type",];
  var selectedSheets = ["Sheet1","Sheet5","Test2"]; // select the sheets you want to run the function for
  var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets
  // iterate over all sheets and execute code on selectedSheets
  sheets.forEach(sheet=>{
     if(selectedSheets.includes(sheet.getName())){           
        var width = sheet.getLastColumn();
        var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
        for (var i = headers.length - 1; i >= 0; i--) {
           if (required.indexOf(headers[i]) == -1) {
           sheet.deleteColumn(i+1);
        }
        }
     }
  });
}

Solution - Run on every sheet in the spreadsheet:

function deleteColumns() {
  var required = ["Owned",  "Set",  "Card number",  "Edition",  "Rarity",   "Name", "Card type",];
  var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets
  // iterate over all sheets and execute code on selectedSheets
  sheets.forEach(sheet=>{
        var width = sheet.getLastColumn();
        var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
        for (var i = headers.length - 1; i >= 0; i--) {
           if (required.indexOf(headers[i]) == -1) {
           sheet.deleteColumn(i+1);
        }
        }
  });
}
Related Topic