In my script I iterate through cells each of which represents a day on The calendar grid. So it goes from an earlier date (from a cell changed by the user) to a later one. Top down and left to right.
The script starts when a user changed value of a cell. And it must fill every subsequent days-cells by the same value up to certain cell. Lets say it must stop on the cell with red font. Thus every iteration the script must get the cell font color.
...Or, The iterations must stop when the script gets a cell representing a certain date. Thus every iteration the script must verify which date the cell represets. To understand what date a cell represents I get The values from the helper cells (headers) and use getValue(). Whatever.
Everything is bearable: looping through cells, changing the values in each cell, getting helper cells(ranges). But! As soon as I add getValue() to the given headers it starts to work unbelievably slowly. Or even I just get font color... Any function starting from "get" included in iteration makes the job unbelievably slowly!
A script with looping as many as you like getRange(), setValue() works out in tolerable time, but with just one getFontColor() or getValue this job runs in the same time for just one cell.
Either I do somethin illegal or google ? Is there an opportunity to accelerate this job significantly? Or job like this should be done quite differently?
function onEdit(evt) {
var aSheet = evt.source.getActiveSheet();
// veryfy which sheet
switch( aSheet.getName().toLowerCase() ) {
case "wage":
// get range - calendar grid
var wageGrid = aSheet.getParent().getRangeByName("wageGrid");
var editedCell = evt.range;
// loop exit flag
var weBreak = false;
editedCell.setFontColor("red");
// loop through rows
for(var rowIndex = editedCell.getRow(); rowIndex <= wageGrid.getLastRow(); rowIndex++) { if(weBreak) break;
// loop through columns
for(var collIndex = (rowIndex == editedCell.getRow())?editedCell.getColumn():wageGrid.getColumn(); collIndex <= wageGrid.getLastColumn(); collIndex++) {
// as many as you like
var currentLoopCell = aSheet.getRange(rowIndex, collIndex);
var dayHeaderCell = aSheet.getRange(rowIndex, 1);
var monthHeaderCell = aSheet.getRange(1, collIndex);
cell.setValue(evt.value);
// but getValue() or getSomeAttribute() will slow down the process
//var cellFontColor = cell.getFontColor();
//if(cellFontColor=="red") weBreak = true; break;
}
}
break;
case "nonexistentyet":
break;
default:
Logger.log("What was it?")
}
}
