function getBackgroundColor(rangeSpecification) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); return sheet.getRange(rangeSpecification).getBackgroundColor(); } function sumWhereBackgroundColorIs(color, rangeSpecification) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(rangeSpecification); var x = 0; for (var i = 1; i <= range.getNumRows(); i++) { for (var j = 1; j <= range.getNumColumns(); j++) { var cell = range.getCell(i, j); if(cell.getBackgroundColor() == color) x += parseFloat(cell.getValue()); } } return x; } function onOpen(e) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{ name : "Refresh", functionName : "refreshLastUpdate" }]; sheet.addMenu("Refresh", entries); }; function refreshLastUpdate() { SpreadsheetApp.getActiveSpreadsheet().getRange('A70').setValue(new Date().toTimeString()); } function onChange(e){ var sheet = SpreadsheetApp.getActiveSpreadsheet(); sheet.duplicateActiveSheet(); } function countCellsWithBackgroundColor(color, rangeSpecification) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(rangeSpecification); var x = 0; for (var i = 1; i <= range.getNumRows(); i++) { for (var j = 1; j <= range.getNumColumns(); j++) { var cell = range.getCell(i, j).getBackground(); if (cell == color) { x++; } } } return x; }

enter image description here enter image description here wrote a script that counts the number of cells of a certain color in a given range. If I change the number of cells of a given color, the function does not automatically recalculate the number of cells. How to make the function recalculate automatically?

enter image description here

enter image description here

  • Good day. Please provide the code of your script - and then we may be able to help. - Sergey Pryanichkin
  • Look please - Love Lyulin
  • Tell me, how do you use this function in the table? Enter as a formula? Or run from the menu? Where is the result displayed? - Sergey Pryanichkin
  • I enter it as a formula into a specific cell = countCellsWithBackgroundColor ("# ffff00"; "OU4: OU100"; ""; "OU4: OU100") - Lyubov Lyulin
  • So. Immediately a huge request - do not upload the code with a screenshot - and the text - so that we can copy it ourselves and try. And so - I have to manually fill it. And I do not want to spend time on this. - Sergey Pryanichkin

1 answer 1

It is necessary to hang up the call of your function on the trigger "change data in the table" onEdit (e).

https://developers.google.com/apps-script/guides/triggers

G-Apps-Script.COM