Possible code optimizations for a spreadsheet cell loop-CodePudding

I am attempting to iterate over multiple cells of a spreadsheet and, if they meet a set of requirements, I would like to change their background color and change their text. Problem is, if it finds multiple of these cells that have this specific setting, it takes a long time as it needs to update each one of those manually. The reason I do not have it update an entire range is because some of the cells in the middle are reserved for another purpose. Here is the code I have written so far, how could I optimize it for it to run faster?

["File1","File2","File3"].forEach(function(sheet) {
   spreadsheet.setActiveSheet(spreadsheet.getSheetByName(sheet), true);
    for(i=10;i<99;i  ) {
      let cell = spreadsheet.getRange("J" i);
      if (cell.getBackground() == "#00ff00") {
        cell.setBackground('#ea4335')
        cell.clearContent();
      } else if(cell.getBackground() == "#ea4335" && cell.getValue().toString().length > 0) {
        cell.clearContent();
      }
    }
});

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 the following modification?

Modified script:

function myFunction() {
  const sheetNames = ["File1","File2","File3"]; // Please set your sheet names.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    const range = sheet.getRange("J10:J98");
    const values = range.getDisplayValues();
    const backgrounds = range.getBackgrounds();
    const { ranges1, ranges2 } = backgrounds.reduce((o, [j], i) => {
      if (j == "#00ff00") {
        o.ranges1.push(`J${i   10}`);
        o.ranges2.push(`J${i   10}`);
      } else if (j == "#ea4335" && values[i][0]) {
        o.ranges2.push(`J${i   10}`);
      }
      return o;
    }, { ranges1: [], ranges2: [] });
    if (ranges1.length > 0) sheet.getRangeList(ranges1).setBackground("#ea4335");
    if (ranges2.length > 0) sheet.getRangeList(ranges2).clearContent();
  });
}

In this modification, as a simple modification, in order to reduce the process cost of your script, RangeList is used.

Note:

In this modified script, it supposes that your showing script works fine. Please be careful about this.

Modified script:

Note:

References: