CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-api
user12736831
user12736831

Reputation:

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

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");
}

Upvotes: 0

Views: 1317

Answers (2)

user12736831
user12736831

Reputation:

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.

Upvotes: 2

CMB
CMB

Reputation: 5163

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)

Upvotes: 2

Related Questions