Google apps script - Why isn't my for-loop executing?-CodePudding

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();
          }; 
        };

    }

CodePudding user response：

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?

Modified script:

From:

for ( i = 29 ; i < lastRow - 1; i  ){
  if(rangeColors[i][8] === '#ffff00'){
    sheet.getRange(i,8).clearContent();
  }; 
};

To:

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();

In this modification, when the background color of the cell of column "H" is #ffff00, the cell content is clear.

Reference:

Class RangeList

CodePudding user response：

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:

Create a range to check
Generate column and row numbers for all the cells in the range. Note that these are relative to the range, not the stylesheet. So range.getCell(1, 1) is the top left cell in the range. This is the (main) mistake you made in your code.
Do whatever you want with every individual cell in the range.

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;