I have this script working perfectly for tabs 3-15. I would like it to also run in tabs 80 until the end of the sheet.

There are just over 100 tabs and if the script runs through all of the tabs it takes way too long to run. I have tried changing line 6 to (var i=3;i<15,i<80<totalSheets;i ) but it seems to still go through all of them when I do that.

All help greatly appreciated, thanks.

function hidecolumns() {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var totalSheets = sheets.length;

  for(var i=3;i<15;i  )
  {
  var first_row = sheets[i].getRange(3,1,1,sheets[i].getMaxColumns()).getValues().flat();

  first_row.forEach((fr,j)=>{
  if(fr==0){
  sheets[i].hideColumns(j 1);
  }
  else {
  sheets[i].showColumns(j 1);
  }
  })
  }
  }

CodePudding user response：

I believe your goal is as follows.

• You want to reduce the process cost of your script.

In this case, how about using Sheets API? When Sheets API is used for your script, it becomes as follows.

Modified script:

This script uses Sheets API. So, please enable Sheets API at Advanced Google services.

function hidecolumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheets = ss.getSheets().filter((_, i) => (i >= 3 && i < 15) || i > 80); // Or when you want to use all sheets, you can also use ss.getSheets()
  var requests = sheets.flatMap(s => {
    var sheetId = s.getSheetId();
    var values = ss.getRange(`'${s.getSheetName()}'!A3:3`).getValues()[0];
    return values.map((r, i) => ({
      updateDimensionProperties: {
        properties: { hiddenByUser: r == 0 },
        range: { sheetId, startIndex: i, endIndex: i   1, dimension: "COLUMNS" },
        fields: "hiddenByUser"
      }
    }));
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

• In this case, from I have tried changing line 6 to (var i=3;i<15,i<80<totalSheets;i ), the sheet indexes of 3 - 14 (from i=3;i<15) and 81 - end (from i<80<totalSheets)are used.
  • I cannot understand your 1st index and end index you want to use. If you want to 3 - 15 and 80 - end, please modify ss.getSheets().filter((_, i) => (i >= 3 && i < 15) || i > 80) to ss.getSheets().filter((_, i) => (i >= 3 && i <= 15) || i >= 80).

References:

• Method: spreadsheets.batchUpdate
• UpdateDimensionPropertiesRequest

CodePudding user response：

Hide columns on many selected sheets

function hidecolumns() {
  const ss = SpreadsheetApp.getActiveSheet();
  ss.getSheets().filter((sh, i) => (i > 2 && i < 16) || (i > 79) ).forEach(sh => {
    let first_row = sh.getRange(3, 1, 1, sh.getMaxColumns()).getValues().flat();
    first_row.forEach((fr, j) => {
      if (fr == 0) {
        sh.hideColumns(j   1);
      }
      else {
        sh.showColumns(j   1);
      }
    });
  })
}