Google-sheets – Trigger when a cell value changed by formula

google sheetsgoogle-apps-script

This is my situation:

Spreadsheet is set to recalculate on change and every minute
One of my cells has a formula that pulls data from an API
The combination of the two makes my cell value change from time to time

I want to run a script once the value of this cell is changed.

I tried to use onEdit() but it seems to trigger only when a user changes a cell, not when a formula changes it value.

What would be the right trigger for that?

Best Answer

A trigger is not enough.

You have to set a way to record a base value. Then, among other alternatives, you could set a time-driven trigger that call a script that compares the current value to the base value, if they are not the same then update the base value and do the other things that you require when the formula calculated value changes, otherwise do nothing.

To record the base value, you could use an spreadsheet cell or the Google Apps Script Properties Service.

Related Solutions

Google-sheets – How to refresh script results when a cell color is changed

The only way I know is to use a 'dummy' parameter to the function, that is not really used in the custom function, but will trigger the function to recalculate. So, where you would normally use:

=sumWhereBackgroundColorIs(color, rangeSpecification)

you would now use

sumWhereBackgroundColorIs(color, rangeSpecification, F1)

In F1 (or any other cell you may want to use) you can have value (e.g. 1) that when changed (e.g: incremented with 1) will force the recalculation of the custom function. This 'incrementing act' can also be scripted.

Google-sheets – Trigger function when IMPORTRANGE() is updated

NOTE: Answer to revision 4

Google Apps Script on edit events are triggered by user made changes only, they aren't triggered on automatic changes like does that occur to automatic recalculation of import functions and functions like NOW. By the other hand, in order to make a change on an external sheet, you should use an installable trigger.

For further details see Installable triggers and search about this both on this site and on Stack Overflow.

NOTE: Answer to revision 1

Besides the use of the run command from the Google Apps Script editor, to run a script on demand you could use a custom menu. There are several threads that include examples of uses of custom menus on this site. Here are few examples:

References

Best Answer

Related Solutions

Google-sheets – How to refresh script results when a cell color is changed

Google-sheets – Trigger function when IMPORTRANGE() is updated

Related Topic