Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Use OnChange function only on the cell that has changed

I’m using the following script to provide the hex color code of column A:

function onChange(e) {
  if (e.changeType == "FORMAT") {
    var formula = "=GetCellColorCode";
    var tempFormula = "=sample";
    var sheet = e.source.getActiveSheet();
    sheet.createTextFinder(`^\\${formula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
    sheet.createTextFinder(`^\\${tempFormula}`).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
  }
}

function GetCellColorCode(input) 
{ 
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var cell = ss.getRange(input); 
var result = cell.getBackground(); 
return result 
}

I’m using =GetCellColorCode("A"&ROW()) in column G. However, it spans hundreds of rows, and takes too long to update, as every instance of the formula updates when one cell colour background is changed. Is there a way to change this script so only the formula on the row where the cell background colour has changed is updated? I’ve tried using replaceWith rather than replaceAllWith, but it doesn’t update at all. Thanks in advance!

EDIT: For further context, I need this speeding up as I also have a script in there that updates my filter (I want to filter out all green and red rows), but it seems to time out frequently so the filter isn’t often updated:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

function update_filter() {
  var col = 7;
  var filter = SpreadsheetApp.getActiveSheet().getFilter();
  var criteria = filter.getColumnFilterCriteria(col);
  filter.setColumnFilterCriteria(col, criteria);
}

>Solution :

active range usually provides the range where change took place. Try

var sheet1/*renamed from sheet*/ = e.source.getActiveSheet();
var sheet = sheet1.getRange(sheet1.getActiveRange().getRow(),7);

The variable sheet is now of range type, which points to the active row’s G column.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading