Faster way to iterate cells and change background color of specific cells based on second sheet-CodePudding

I have 2 massive sheets (over 100 columns, 400 rows). I'm trying to highlight currently changed cells in yellow. But, also removing any previous yellow highlights first. (there are other cells with different colors that need to be left alone).

Prior to running this code I am saving a copy of sSheet each week and comparing sSheet with the copy from the previous week "changesSheet". The changesSheet is a comparison sheet full of formulas comparing last week's data with current data. It is mostly blank, but the adjusted cells have data...the rest of the cells are blank.

The cells in both changesSheet and sSheet are alligned. Meaning if there was a change in cell 'A10' of sSheet it is reflected in 'A10' of changesSheet.

for each cell,
if its yellow on sSheets, change sSheets cell to white
if it has data on changesSheet, change sSheet cell to yellow

The code below works.... But it's way too slow and will never make it through 400 rows without timing out.

var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requests Form');  
var changesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ChangesSheet');  

for (row=1;row<lastRow 1;row  ){  
  Logger.log(row);  
  for(col=1;col<lastCol 1;col  ){  
    
    var cell = sSheet.getRange(row,col);  
    var cell2 = changesSheet.getRange(row,col);  
    if(cell.getBackground()== "#ffff00"){  
      cell.setBackground('white');  
    }  
    if (cell2.getValues()!=""){  
      cell.setBackground('yellow');  
    }  
  }  
}

Any suggestions to make this go faster?

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

About if it has data on changesSheet, change sSheet cell to yellow, in your script, the cell values are not checked. So, I guess that you might be using conditional formatting rules. If my understanding is correct, how about the following modification?

Modified script:

var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requests Form');
var changesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ChangesSheet');

var backgrounds = changesSheet.getDataRange().getBackgrounds();
var values = backgrounds.map(r => r.map(c => c == "#ffff00" ? "yellow" : "white"));
sSheet.getRange(1, 1, values.length, values[0].length).setBackgrounds(values);

By this modification, I thought that the process cost might be able to be reduced a little.
In this modification, when the background color of cells in "ChangesSheet" sheet is "#ffff00", the same cell coordinate is changed to "yellow" in "Requests Form" sheet.
If you want to reverse this, please modify c == "#ffff00" ? "yellow" : "white" to c == "#ffff00" ? "white": "yellow".

References:

Added 1:

From your following reply,

if (cell2.getValues()!=""){ I only need to check that there IS some data in the same cell on the changesSheet. Currently there is no conditional formatting on the changesSheet

From your showing script, I thought that you might be using conditional formatting roles. But, if you want to check only whether the cell is empty, I thought that the following sample script can be also used.

var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requests Form');
var changesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ChangesSheet');

var backgrounds = changesSheet.getDataRange().getDisplayValues();
var values = backgrounds.map(r => r.map(c => c ? "yellow" : "white"));
sSheet.getRange(1, 1, values.length, values[0].length).setBackgrounds(values);

In this case, it checks whether the cells are empty instead of the background colors.

Added 2:

About your following additional request,

The script changes ALL cells white or yellow. There are already cells with different colors on the sSheet. If a cell is not yellow, can it use the original sSheet color?

In this case, how about the following sample script?

var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Requests Form');
var changesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ChangesSheet');

var backgrounds = changesSheet.getDataRange().getDisplayValues();
var t = sSheet.getDataRange().getBackgrounds();
var values = backgrounds.map((r, i) => r.map((c, j) => c ? "yellow" : t[i] && t[i][j] ? t[i][j] : null));
sSheet.getRange(1, 1, values.length, values[0].length).setBackgrounds(values);