Apps Script: How to capture oldValue of a cell after a change made by sheets api?

Question

I am running a python script that updates my google sheet (named 'Notifications') through the sheets api every 5 minutes. The python script first clears and resizes the sheet to only two rows and then adds the new data on top and the sheet's size gets automatically expanded. I've done that not to have empty rows if my new data is smaller in size than the old one.

So the sheet is updated every 5 minutes but if nothing new happened in my data source, the new values are going to be the same as the old. In particular, the value in 'A2' is going to be the same as before the update. If something new has happened, the value in 'A2' is going to be different. I want to track that and get a pop-up message that notifies me that something new has happened after the update.

So as far as I understand I have to use onChange because onEdit only works if the changes are made by a human and cannot track changes made by the api. I'm uncertain how to do that though.

With onEdit I've achieved it and get a pop-up if I modify the content of 'A2' manually but nothing happens when python updates my sheet.

Here's the code with onEdit:


function onEdit(e) {
  if (e.range.getA1Notation() === 'A2') {
    let oldValue = e.oldValue
    let newValue = e.newValue
    if (oldValue != newValue) {
      Browser.msgBox('The value in A2 changed from ' + oldValue + ' to ' + newValue);
    }
  }
}

So the end goal is to get a pop-up not every time python updates my sheet, but only if there's a change in the value of 'A2' after the update.

How can I do this?

onEdit trigger only occur on user edits – Cooper Aug 10 '21 at 05:08 — Cooper, Aug 10 '21 at 05:08

score 0 · Answer 1 · answered Aug 10 '21 at 05:05

The only way to get a pop-up when a a change is made through an API is by using polling function from client-side code. One way to do this is by using a sidebar to hold the client-side code.

You should store somewhere the old values then compare with the current values.

score 0 · Answer 2 · answered Aug 10 '21 at 14:42

0

You can use onChange trigger with a "ValueInputOption"="USER_ENTERED" to trigger a function when a script edits a sheet. But you can't show anything in the UI from the triggered context so getting a pop up will be impossible.

answered Aug 10 '21 at 14:42

Aerials

4,231
1
16
20

Apps Script: How to capture oldValue of a cell after a change made by sheets api?

2 Answers2