CODEWITHSUNDEEP
google-apps-scripttriggersgoogle-sheets
user1917506
user1917506

Reputation: 41

How to trigger the onEdit event of a referenced cell?

I am writing Google app script of Google spreadsheet, but I encounter some problem. I try to trigger the "onEdit" function by a cell whose value is referenced by other cell. However, it doesn't seem to work well. For example, the value of A cell is referenced by B cell(the value of A is "='B'"), and if I changed the value of B cell, A cell will change accordingly, but fail to trigger the onEdit function of A cell. I am thinking if it is because the change made automatically by spreadsheet is not an event that will trigger the onEdit function.

Does anybody know other solutions to solve this problem?

Thanks in advance!

Upvotes: 3

Views: 4701

Answers (2)

RocketDonkey
RocketDonkey

Reputation: 37279

onEdit will run any time a cell is updated (as you assumed). Using your logic above, would it work to set a check in the onEdit event to see what cell was changed, and if it was B, could you make the logical assumption that A changed as well?

function onEdit(event)
{
  // 'event' contains information about the edit that took place
  // Here, we read the 'source' and get the active range (our changed cell)
  // We then pull out the string form of the notation to get the cell that changed
  var changedCell= event.source.getActiveRange().getA1Notation();
  Browser.msgBox(changedCell)
  if (changedCell == 'B1') {
    // If the changed cell is the one that affects A, do something
    Browser.msgBox('You changed B1, and this will affect A');
  }
}

Upvotes: 3

Srik
Srik

Reputation: 7965

As you've mentioned, the onEdit would be triggered for cell B not cell A. You can use this to read off cell A or you can have a function triggered every minute to read cell A.

Upvotes: 0

Related Questions