IFFT Populated Google Spreadsheet Log Does not Trigger onEdit Script

2
votes

I have a Google Spreadsheet that IFTTT adds a line to when particular events occur. I would like a Google script to run each time a line is added. Currently, the script has an installed onEdit trigger but it isn't triggered unless I manually edit the spreadsheet.

This thread and this thread describe similar issues but I lack the knowledge to try out their proposed solutions. The first suggests using a webapp and the second suggests using a time based trigger and checking for changes in the spreadsheet.

Any guidance would be greatly appreciated.

Further Details

Each row that comes in via IFTTT represents a purchase of a random prize. One column contains the price of the purchase, another column contains the date of the purchase, and another contains purchase details. When the total amount spent changes, the spreadsheet fetches a random number and the prize associated with the number. I want those details emailed to me when a prize is bought (more specifically, I want them emailed to IFTTT so it can SMS me the details). The email and email address are generated by formulae in spreadsheet cells. A script grabs the email and address from the spreadsheet and sends the email. Currently, the email is successfully triggered when the spreadsheet is manually edited but not via the IFTTT log entry.

Most of this project is achieved via formulae in the spreadsheet, rather than scripts, as I am much more confident using them.

4
google-apps-scriptgoogle-sheets
It's helpful if you can provide greater context on what you're script is supposed to do, and some sample code, as it might allow us to suggest a solution. – HDCerberus

4 Answers

2
votes

The onEdit() trigger only fires on user input.

You should see if you can use onFormSubmit if IFTTT sends form-info?

1
votes

My workable but untidy and unsatisfying solution is as follows.

  1. The following functions are used to record the time and date that the Total Spent cell was updated.

function timestamp() { var d = new Date() return d.toLocaleTimeString(); }

function datestamp() { return new Date() }

  1. SPLIT() and INT() are used to manipulate the date and timestamps into usable formats that can be compared with the current date and time. The datestamp() function always gives the date in PST so I've had to add the appropriate number of hours to convert it.
  2. One IF() cell checks whether the date is today and another IF() cell checks whether the time has a value that is less than the time 5 minutes ago.
  3. If the conditions are satisfied (total spent was edited today and less than 5min ago) the email cell is given the value of IFTTT's email address. If not the cell gets a space.
  4. My sendEmails script is triggered every 5 minutes by an installable time based trigger. It will send an email to the valid address if the conditions are met and a blank email address if they are not.
1
votes

One workaround could be the following:

You could make a script in this spreadsheet, and save the getLastRow() value in scriptproperties.

Compare them every minute or so via a timed trigger, and when it had increased, do your thing ;)

0
votes

just change the trigger to be select event type to be "On Change". This will run the script whenever you edit it and also whenever IFTTT adds the row.