Is there a better way for a script to apply banding to every sheet?-CodePudding

I've written a few scripts to help me clean/reset a worksheet that we reuse every week. The idea is that we have ~50 people that use this, and due to a combination of malice and ignorance, they often find ways around our permissions, and edit things we don't want them to. Recently, this worksheet has grown so large that my poorly-written scripts will time out before reaching the end. (We're currently at ~250 sheets.) I've been able to rewrite most of these and lowered the runtime significantly. One step continues to slow me down though: removing and reapplying banding to a specific range (A5:H27) on every page.

Is there a way that I can make this code more efficient and decrease my runtime from ~12 min for this step?

function fixBanding(){
  var spreadsheet = SpreadsheetApp.getActive();
  var allSheets = spreadsheet.getSheets();
  
  allSheets.forEach(function(sheet){
    if(sheet.getSheetName() !== "HelperSheet"){
      sheet.getRange('A5:H27').activate();
      sheet.getRange("A5:H27").getBandings().forEach(banding => banding.remove());
      sheet.getRange("A5:H27").applyRowBanding()
        .setHeaderRowColor('white')
        .setFirstRowColor('#cbcbcb') //med grey
        .setSecondRowColor('white');
    }
  })
}

CodePudding user response：

Seems like your code is optimised enough,I think you can split up the run to multiple run by:
1)At the starting of the loop initialise a date variable to store the current time
2)while looping through the sheets keep checking the total time past from starting
3)if the total time is greater than 80% of the total script available execution time (6 min) then start a new trigger schedule after say 20sec Installable trigger
4)and as well as store the sheet number or name up to which you have already done the processing in a new excel sheet (say config)
5)when the trigger starts, the code should take up the sheet name/number from the "config" sheet which is the sheet up to which the processing is already done and start processing after that sheet number.

CodePudding user response：

I believe your goal is as follows.

Your script works fine. And, you want to reduce the process cost of your script.

In this case, how about using Sheets API? When Sheets API is used, the process cost can be reduced a little. When Sheets API is used in your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google servives.

function myFunction() {
  // Retrieve sheet IDs and andedRange IDs.
  const ss = SpreadsheetApp.getActive();
  const spreadsheetId = ss.getId();
  const { sheetIds, bandedRangeIds } = Sheets.Spreadsheets.get(spreadsheetId, { fields: "sheets(properties(sheetId,title),bandedRanges(bandedRangeId))" }).sheets.reduce((o, { properties: { sheetId, title }, bandedRanges }) => {
    if (title != "HelperSheet") {
      o.sheetIds.push(sheetId);
      o.bandedRangeIds = [...o.bandedRangeIds, ...bandedRanges.map(({ bandedRangeId }) => bandedRangeId)];
    }
    return o;
  }, { sheetIds: [], bandedRangeIds: [] });
  
  // Create the request body for Sheets API.
  const firstBandColor = { red: 0.79, green: 0.79, blue: 0.79 };
  const secondBandColor = { red: 1, green: 1, blue: 1 };
  const headerColor = { red: 1, green: 1, blue: 1 };
  const requests = [...bandedRangeIds.map(id => ({ deleteBanding: { bandedRangeId: id } })), ...sheetIds.map(sheetId => ({ addBanding: { bandedRange: { rowProperties: { headerColor, firstBandColor, secondBandColor }, range: { sheetId, startRowIndex: 4, endRowIndex: 27, startColumnIndex: 0, endColumnIndex: 8 } } } }))
  ];
  
  // Request to Sheets API using the created request body.
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);

  // If you want to activate 'A5:H27' in each sheet, please use the following script.
  // SpreadsheetApp.flush();
  // ss.getSheets().forEach(sheet => {
  //   if (sheet.getSheetName() !== "HelperSheet") sheet.getRange('A5:H27').activate();
  // });
}

Modified script:

References: