e.range.getA1Notation() unable to track changes caused by formula update

by

onEdit(e) Trigger(Both simple and Installable) will not trigger unless a human explicitly edits the file. In your case, Your seem to be getting value from an external source (specifically, Google finance data).

Script executions and API requests do not cause triggers to run. For example, calling FormResponse.submit() to submit a new form response does not cause the form’s submit trigger to run.

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet’s onEdit trigger to run.

Also, for Google finance data,

Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.

Notes:

Having said that,

  • In cases where the change is made by a formula(like =IF(),=VLOOKUP()) other than auto-change formulas(like =GOOGLEFINANCE,=IMPORTRANGE,=IMPORTXML, etc), Usually a human would need to edit some other cell- In which case, You will be able to capture that event(Which caused a human edit) and make changes to the formula cell instead.

  • In cases where the change is done from sheets api, a installed onChange trigger may be able to capture the event.

More Related Contents:

  1. Google Sheets OnEdit() – You do not have permission to call a service
  2. onEdit simple trigger never seems to be triggered
  3. “SpreadsheetApp.getUi() cannot be called from this context”
  4. How to allow onEdit function to affect protected cell in a Google Sheet?
  5. We’re sorry, a server error occurred while reading from storage. Error code PERMISSION_DENIED
  6. google script openById : You do not have permission to perform that action
  7. Refresh data retrieved by a custom function in Google Sheet
  8. How do you create a “reverse pivot” in Google Sheets?
  9. Create Google Calendar Events from Spreadsheet but prevent duplicates
  10. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  11. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  12. Apps Script: how to get hyperlink from a cell where there is no formula
  13. Retrieving Google Sheets cell border style programmatically
  14. Determining the last row in a single column
  15. Cannot find method getRange(number,number)?
  16. How do I add formulas to Google Sheets using Google Apps Script?
  17. Deleting rows in google sheets using Google Apps Script
  18. Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank
  19. Adding Buttons To Google Sheets and Set value to Cells on clicking
  20. Custom function throws a “You do not have the permission required to setValue” error
  21. Margins parameters for Google spreadsheet export as PDF
  22. Script to convert .XLSX to Google Sheet and move converted file
  23. Hidden field in a Google Form
  24. onEdit() doesn’t catch all changes
  25. Google Sheet use Importxml error could not fetch url [duplicate]
  26. Converting .xls to google spreadsheet in google apps script
  27. Use script in all spreadsheets
  28. Custom Functions and Recalculation
  29. Failure of calling Google App Script onEdit function many times in a second
  30. Can the google spreadsheet ‘query’ function be used in google apps script?

Leave a Comment