1

I have a function (in a Google Apps Script), not called onEdit(), but something else and this has been set up to be called from any on edit events via in the Installable onEdit on a Google Spreadsheet.

The spreadsheet is around stocks and monitors a portfolio and the purpose of the function is to send an email as soon as a stop loss is hit on a stock, i.e. the price of a stock falls to a certain price. The price of the stock is retrieved and updated via the Google Finance API in a column in the spreadsheet.

Now, when running the function from the script or locally editing the spreadsheet, the function is called successfully. However, I am finding that as the stock price is updating automatically throughout the day on Google Drive and falling to the stop loss price, the on edit function is not being called. I read somewhere about it not being called by an "anonymous" user? I know it's not being called as I have an "email counter" column, which decreases every time the function is called, and once at 0 no more mails will be sent to avoid spamming. This not decreasing via the Google Finance API automatic price updates. Can someone advise what is causing this and if this there is any workaround?

Rubén
  • 34,714
  • 9
  • 70
  • 166
user2207068
  • 13
  • 3

1 Answers1

2

A list of things-that-don't-trigger-onEdit, along with their issue tracking ids, were provided in a previous answer.

Content of a spreadsheet changed by scripts does not trigger onEdit.

A work-around might be to use a time-based trigger, and in the call-back scan for and react ti changes. (Could the updater set a trigger to fire in the near future, perhaps?) Challenges for this will be around balancing responsiveness vs trigger limits.

Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Hi, Thanks for the reply! So in a time based trigger, would I call the function onEdit() or install a time based trigger and call the function any name? The time trigger has to be minutes apart, otherwise this could involve losing a lot of money, as decision to sell would be based on the email alert. This also sounds like a bug on Google's part - how does one raise a bug, assuming one hasnt already been raised for this - I couldnt see that you raised the issue of values "written by scripts"? – user2207068 Mar 25 '13 at 21:36
  • I've just created a time-based trigger (installable) and it worked, so thanks. Question is, can I set a start time for the trigger and the end time? These would be exchange open and close times. – user2207068 Mar 25 '13 at 21:58
  • Don't name any installable trigger `onEdit`, or any of the other special trigger names. Issue 338 is about changes via APIs - I've added it to that post. For start / end, you could try programmatically adding / deleting the day-time trigger via the [Script Service](https://developers.google.com/apps-script/service_script), from time-based triggers set to fire before & after the business day. – Mogsdad Mar 26 '13 at 00:38
  • Thanks for the suggestion. Just one thing, the installable time based trigger ran last night but didnt today - is there any logging I can add to see when it runs? I will try the programmatic triggers – user2207068 Mar 26 '13 at 19:51
  • Have a look at http://stackoverflow.com/a/11496321/1677912, I think that logging to a spreadsheet might be the most effective way to persistently monitor your triggers. You should also have the notifications for your trigger set up to mail you if there are problems. And the obvious question... was that time-based trigger set to repeat, or fire one time only? – Mogsdad Mar 26 '13 at 20:35
  • The time based trigger was set to repeat every 5 minutes, but again it didnt run. It only ran when i logged into google drive and opened the spreadsheet...then I got the alerts. Thanks for the link, I will check it out and try the programmatic triggers. – user2207068 Mar 27 '13 at 21:06
  • Found the error in the script, it was getting the "active" sheet and working on this. Of course I doubt there is an active sheet when the time based trigger runs and the active sheet only comes into play when a user is opening the sheet on the GUI. I am now opening the sheet by Id and it should work now. – user2207068 Mar 27 '13 at 21:50