I'm trying to write a simple script to clear the values from a specific Google Sheets range [H29:H] if the background color of the cell = #ffff00. While the script doesn't return any errors, it isn't making any changes to cells that meet those criteria (or anywhere else in the sheet). Any guidance as to where I am going wrong?
function resetCells() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Test Sheet');
var rangeData = sheet.getDataRange();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange('H29:H');
var rangeColors = searchRange.getBackgrounds();
for ( i = 29 ; i < lastRow - 1; i++){
if(rangeColors[i][8] === '#ffff00'){
sheet.getRange(i,8).clearContent();
};
};
}
When I saw your script, I thought that the index of rangeColors might not be corresponding to i in the for a loop. I thought that this might be the reason for your issue. In this case, how about the following modification?
for ( i = 29 ; i < lastRow - 1; i++){
if(rangeColors[i][8] === '#ffff00'){
sheet.getRange(i,8).clearContent();
};
};
for (i = 0; i < rangeColors.length; i++) {
if (rangeColors[i][0] === '#ffff00') {
sheet.getRange(i + 29, 8).clearContent();
}
}
or, in your situation, when sheet.getRange(i + 29, 8).clearContent() is used at outside of the loop, the process cost might be able to be reduced a little. In this case, please modify as follows.
var rangeList = rangeColors.reduce((ar, [h], i) => {
if (h === '#ffff00') ar.push("H" + (i + 29));
return ar;
}, []);
sheet.getRangeList(rangeList).clearContent();
#ffff00, the cell content is clear.What you did wrong
In rangeColors[i][8] you were assuming rangeColors related to the cells in the spreadsheet. But they actually relate to the searchRange you made. So rangeColors[i][8]should be rangeColors[i][0]. Where i starts at 0.
To make it "extra fun" for you rangeColors is an array which is zero-based indexed. And when you are working with a range you can retreive the first cell with range.getCell(1, 1), not range.getCell(0, 0).
Because I got a little carried away I wrote this solution ( may be an interesting extra to read )
I don't think mapping an array of arrays (rangeColors) to a range is a great way to do this; It's hard to keep track on what your code is doing.
What I did was:
range.getCell(1, 1) is the top left cell in the range. This is the (main) mistake you made in your code.code:
resetCells( '#ffff00' );
function resetCells( color ) {
const app= SpreadsheetApp.getActiveSpreadsheet();
const sheet = app.getSheetByName('Sheet1');
const range = sheet.getRange('F4:F50');
for( let row = 1; row < range.getNumRows() + 1; row++ ) {
for( let column = 1; column < range.getNumColumns() + 1; column++ ) {
const cell = range.getCell( row, column );
if( cell.getBackground() === color )
cell.clearContent();
}
}
}
Disclaimer: I never used this google-app-script before. So I may have done something wrong performance-wise; iterating a large amount of cells takes a little long;