Sheets script - making changes to sheets other than the initial active sheet-CodePudding

I have a script running that reformats borders on a variable range on the active sheet. I would like to have it cycle through other sheets in the same workbook, without those changes being visible to the user (ie - the GUI of the current screen stays as the only thing visible while the script runs).

Currently each sheet is displayed as it loops and performs the reformat. How can I keep the initial sheet visible, and have the script run in the background? Obviously it is the use of ".setActiveSheet" and ".getActiveSheet" causing this.

Still pretty new at all this, so any suggestions to otherwise cleanup/condense/speed up greatly appreciated.

function allBorders(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SpreadsheetApp.getActiveSheet();
  var sheetname = ss.getSheetName();
  var range;
  var headrows = 3;
  var lr = ss.getLastRow() //last row with data
  var lc = ss.getLastColumn()
  var mr = ss.getMaxRows() //max possible rows
  var ns = SpreadsheetApp.getActiveSpreadsheet().getNumSheets()

Logger.log("Number of sheets: " ns)

  for (var i = 0; i < ns; i  ){
    Logger.log("i value: " i)
    spreadsheet.setActiveSheet(spreadsheet.getSheets()[i]);
    sheetname = spreadsheet.getSheetName();
    Logger.log("Sheetname: " sheetname)
    switch(sheetname){
      case "Openings":
        ss = SpreadsheetApp.getActiveSheet();
        headrows = 6;
        lr = ss.getLastRow() //last row with data
        lc = ss.getLastColumn()
        mr = ss.getMaxRows() //max possible rows
        break;
      case "My Trips":
        ss = SpreadsheetApp.getActiveSheet();
        headrows = 6;
        lr = ss.getLastRow() //last row with data
        lc = ss.getLastColumn()
        mr = ss.getMaxRows() //max possible rows
        break;
      case "All_Trips":
        ss = SpreadsheetApp.getActiveSheet();
        headrows = 6;
        lr = ss.getLastRow() //last row with data
        lc = ss.getLastColumn()
        mr = ss.getMaxRows() //max possible rows
        break;
      default:
        break;
    }
      range = ss.getRange((1 headrows),1,mr,lc) //clear all rows below header
        range.setBorder(false,false,false,false,false,false);
      range = ss.getRange((1 headrows),1,(lr-headrows),lc) //border active rows
        range.setBorder(true, true, true, true, true, true);
  }
}

CodePudding user response：

All sheets except the first

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const allshtsexceptthefirst = ss.getSheets().filter((_,i) => i > 0).map(sh => sh.getName())
  Logger.log(allshtsexceptthefirst)
}

CodePudding user response：

Use Array.forEach(), like this:

function surplusBorders() {
  const sheetsToFormatRegex = /./i;
  const ss = SpreadsheetApp.getActive();
  ss.toast('Formatting borders...');
  let numFormattedSheets = 0;
  ss.getSheets().forEach(sheet => {
    if (sheet.getName().match(sheetsToFormatRegex)) {
      sheet.getRange('A1:Z').setBorder(false, false, false, false, false, false);
      sheet.getRange(`A${sheet.getFrozenRows()   1}:Z${sheet.getLastRow()}`).setBorder(true, true, true, true, true, true);
      numFormattedSheets  = 1;
    }
  });
  ss.toast(`Done. Formatted borders in ${numFormattedSheets} sheets.`);
}

To only format some subset of the sheets, adjust the sheetsToFormatRegex regular expression.

CodePudding user response：

Found one way around the main problem. Apparently G doesn't have the ability to perform a "when selected" trigger for sheets. But the workaround posted here seems to accomplish that. And I can simplify/speed up the redraw portion a lot by eliminating the loops. Trigger on sheet change