How to get cell location whose value is changed with reference to other cell value in Google Apps Script

0
votes

I have sheet in which whenever there is an edit, for example in cell "A1", the background color of this cell will be changed to green. I am using on edit trigger. This works when I manually enter value in cell "A1".

But if this cell is connected with another cell "B2" and any change in "B2" cell will automatically change value in cell "A1" but when it happens, the background color does not change. I think it is because I did not edit value manually. Is there any way that we can change background color of cell "A1" whenever it's value is changed due to another cell? Hope you got the point. I am using following script. Any help would be appreciated.

function HighlightCell() {

  var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cell  = Sheet.getActiveCell();
 
  Logger.log(cell);
 
  SpreadsheetApp.flush();
  cell.setValue("Highlighted");
  
  SpreadsheetApp.flush();  
  cell.setBackground("green");
}
2
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-api
onEdit trigger is activated upon user edits only. Formulas or scripts can not trigger an onEdit function. I am afraid you have to redesign your logic. – soMario
Noted. Do you recommend any solution about this question? – Roomi
Carlos provided a work around. Essentially you need to find a way to link the manual edit of the source cell (B1) to the target cell (A1) . If the cells are not linked between them then you can't do anything. One idea would be for your script to check all formulas of column A and adjust the cells that their formula is related to the manually edited cell. You can use getFormulas and then check if you edit cell B1 which of the formulas are going to be affected, and then adjust the color of these cells. Workarounds exist, the question is how open you are to these workarounds. – soMario

2 Answers

2
votes

The parameter in an onEdit(e) simple trigger, the Event Object, will always point to the last manually edited cell, thus in your example, e.range will point to cell B2, regardless of whatever changes it made to other cells.

You can edit your trigger to check if there's an edit in a defined cell instead of A1 and point the cell range to the substring in the formula:

function onEdit(e) {
  if (e.range.getSheet().getName() == "Sheet1") {
    var formula = e.range.getFormula();
    if (formula != "") {
      HighlightCell(formula.substring(1));
    }
  }
}

function HighlightCell(cellNotation) {
  var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cell  = Sheet.getRange(cellNotation); 
  cell.setValue("Highlighted");
  cell.setBackground("green");
}

Sample:

enter image description here

Reference:

Simple Triggers: onEdit(e)

1
votes

I have managed to find a solution of this by following formula concept. So if you are in sheet2 and enter value in a cell that is linked with a cell of sheet1 using formula, then it will highlight cell in sheet1. Anyone who has similar requirement, can use this function. Here is a code:

function FormulaGet(e) {
  
  if (e.range.getSheet().getName() == "Sheet2") {
    
  var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var Notation  = Sheet.getActiveRange().getA1Notation();
  Notation = Notation.toString();  
  var cell = Sheet.getActiveCell();
  Logger.log(Notation);
  var row = cell.getRow()
  Logger.log(row);
  var col = cell.getColumn();
  Logger.log(col);
    var Sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var formulas = Sheet1.getRange("Sheet1!B2:Q40").getFormulas();
    checkElement(formulas,Notation);
     
    }
}

function checkElement(array, str) {
  var item;
  var item1;
  for (var i = 0; i < array.length; i++) {
    item = array[i];
    
    if (item.includes(str) === true || Array.isArray(item) && checkElement(item, str)) {
      item1=item.substring(8,10)
      Logger.log(item1);
      var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
      Sheet.getRange(item1).setBackground("#b7e1cd");
     
      return true;
    }
  }
  
  return false;
}

You can set your range as you want. I have set my range (B2:Q40) according to my need. Thank you @Carlos and @Marios for guiding me in right direction.