CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Vier
Vier

Reputation: 13

How can I apply this script to an entire column in google sheets?

I have this script that counts green cells on a single row (C2:E2) then sums them at a cell in the same row (F2). The cells within column C:E turns green depending on its value through conditional formatting. My problem now is how can I apply this to succeeding rows, basically apply my script entirely to column F.

I'm still a beginner at programming and my skills are basically searching google, combining snippets of what i can find try running and failing, and repeating all over till something works.

function updateOnEdit() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = sheet.getRange("C2:E2");
  var range_output = sheet.getRange("F2");
  var cell_color = range_input.getBackgrounds();
  var color = "#00ff00";
  var count = 0;

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

This works for row 2, count green cells within C2:E2 and total in F2.

Upvotes: 1

Views: 1251

Answers (1)

Amit Agarwal
Amit Agarwal

Reputation: 11278

You'll have to iterate through every row, count the number of green cells in that row and update the corresponding F cell value.

function updateOnEdit() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();
  var range_input = sheet.getRange(1, 3, sheet.getLastRow(), 3);
  var cell_color = range_input.getBackgrounds();
  var color = "#00ff00";  
  for(var r = 0; r < cell_color.length; r++) {
    var count = 0;
    for(var c = 0; c < cell_color[0].length; c++) {
      if(cell_color[r][c] === color) {
        count = count +1;
      }
    }
    sheet.getRange(r+1, 6).setValue(count);
  }
}

Upvotes: 1

Related Questions