Iterate Google Sheets function over certain number of rows

Question

I have a function that counts the different color of cells within a row and places the count totals in columns to the right. It currently counts 3 colors in row 2. I would like this function to do the same thing, but for rows 2 through 250 (so I have outputs for each count in every single row through 250). How can I write a loop to get this code to iterate that many times. OR, even better, would be to have one separate function for each color that I can call with a formula in all of the output cells so I don't have to actually run the function to get live updates of the output (ie cells L2:L250 just have =green, cells M2:M250 have =red, etc. I have worked much more with Excel so don't know if Sheets can do this. As a starting point, just iterating this over rows 2:250 would be a great start! Thank you!

function countcolor() {
 var book = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = book.getActiveSheet();
 var range_input = sheet.getRange("B2:H2");
 var range_output = sheet.getRange("L2");
 var cell_colors = range_input.getBackgroundColors();
 var color = "#00ff00";
 var count = 0;

 for(var r = 0; r < cell_colors.length; r++) {
   for(var c = 0; c < cell_colors[0].length; c++) {
     if(cell_colors[r][c] == color) {
       count = count + 1;
     }
   }
 }
    range_output.setValue(count);

 var book = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = book.getActiveSheet();
 var range_input = sheet.getRange("B2:H2");
 var range_output = sheet.getRange("M2");
 var cell_colors = range_input.getBackgroundColors();
 var color = "#ffff00";
 var count = 0;

 for(var r = 0; r < cell_colors.length; r++) {
   for(var c = 0; c < cell_colors[0].length; c++) {
     if(cell_colors[r][c] == color) {
       count = count + 1;
     }
   }
 }
    range_output.setValue(count);

 var book = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = book.getActiveSheet();
 var range_input = sheet.getRange("B2:H2");
 var range_output = sheet.getRange("N2");
 var cell_colors = range_input.getBackgroundColors();
 var color = "#ff9900";
 var count = 0;

 for(var r = 0; r < cell_colors.length; r++) {
   for(var c = 0; c < cell_colors[0].length; c++) {
     if(cell_colors[r][c] == color) {
       count = count + 1;
     }
   }
 }
    range_output.setValue(count);
 }

Diego Diego · Accepted Answer · 2019-12-11T00:37:07

If you store your color hex values and counts in an object map, you can iterate through your entire table once and print a single time using .setValues(). This is much faster than printing line-by-line, or worse, cell-by-cell. Using batch operations like this is a best practice. Thankfully, you were already starting to do it with .getBackgroundColors().

function countcolor() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = sheet.getRange("B2:H250");
  var range_output = sheet.getRange("L2:N250");
  var cell_colors = range_input.getBackgroundColors();

  // Will store all counts to be printed
  var color_counts = [];

  for (var r = 0; r < cell_colors.length; r++) {
    // These are the colors being counted. Reset to zero for each row.
    var row_color_counts = {
      "#00ff00": 0,
      "#ffff00": 0,
      "#ff9900": 0
    };

    // Check each cell color against row_color_counts map and increment
    for(var c = 0; c < cell_colors[0].length; c++) {
      for (var color in row_color_counts) {
        if (cell_colors[r][c] == color) {
          row_color_counts[color]++;
        }
      }
    }

    // Add the row totals to the final color_counts array
    var row_totals = [];
    for (var color in row_color_counts) {
      row_totals.push(row_color_counts[color]);
    }
    color_counts.push(row_totals);
  }

  // Print once using batch setValues() to increase script execution speed
  range_output.setValues(color_counts);
}

Iterate Google Sheets function over certain number of rows

1 Answers