Appshet forms filled not triggering onEdit or onChange Google Scripts?

[sorin_mihai]

Hello all,

I have a bit of a technical question about Appsheets way of editing the Google Spreadsheet that it uses for it’s Database.

I’m writing some Google Script on the Spreadsheet I’m using as Database for the App I’m making in Appsheet. It’s meant to send an email when a New Row is Added.

The Script works great, but when Adding a New Row by filling in a Form through the Appshet App it doesn’t fire the script onEdit or onChange as it would if that New Row was added via a Google Form.

Am I wrong on this, or is this true in your experience as well?

---

My workaround, in case anyone is looking for solutions here, is that I made a Mark column (“sent” or empty). Then I run the script as TimeDriven every half hour and check for the last 10 rows with data from the bottom to the top of the spreadsheet, then check for the mark and if it’s not sent then fire the “SendEmail” function and mark the column “Sent” so that on the next search, it wont send the email again.
And it works

However, the thing is that if adding rows to the spreadsheet through filling or editing Forms in Appsheet is NOT recognized as an Edit or Change in the Spreadsheets Google Script, than you have to make these TimeDriven Functions and that’s really bad for two main reasons:

• you can’t get instantaneous actions to fire on filling up the form
• you consume a looot of resources.
  (this one function takes a total of 30 minutes to run a day. and it only runs every half hour. If it where to run every minute … and it’s just one function … and it has to take all those resources, even if there was no filling of the form done that day and thus no emails sent)

Any idea on this subject?
It would be a huuuge gain to know that you could run OnEdit functions through Google Script when filling in or editing Appsheet Forms…either directly or through some workarounds if you know any.

Thank you,
Sorin

[jcadence]

Hi @sorin_mihai! Have you had a chance to review any of our documentation on OnEdit? It might be able to answer your questions. If not, ping this thread again and we’ll help you get to the bottom of this.

[sorin_mihai]

Hy,

Thanks for writing

Unfortunately, this is what it says from the first lines in the documentation:

" In particular, if you utilize Google AppScript onEdit triggers on your Google Sheet, or a third-party add on that utilizes these triggers, the triggers are not fired when AppSheet makes a change to the data in the spreadsheet. This is an unfortunate limitation imposed by Google ".

So that about answers it
It’s really too bad they imposed this on you …

I’m thinking of making a column in the sheet that has a formula that edits itself when a line is introduced via Appsheet and maybe that edit will trigger the on edit.

You have any other ideas?

[LeventK]

@sorin_mihai
Provided you are adding a new row to your gSheet via Google Form and provided that gSheet is assigned as a response sheet, than neither onChange nor onEdit works, you need to invoke the script via onSubmit(e).

[Sibonelo]

Hi, Facing the same problem.

Did you find a solution to this?

[SeiZMiK]

I'm also facing the same issue. I'm trying to pre-add entries to another sheet based on what the user submits in the app, but the submission in the app does not trigger any onEdit/onSubmit/onChange event. Help please.

[qwpaco]

I have exactly the same problem. How to solve it?

[scott192]

onEdit() can only be used when the spreadsheet is open and being used by somebody to make changes in the sheet.

It is onChange() that you need to use and you must be very specific about the changes that should then trigger your script to send an email.

I used to do all of this using onChange() before Appscript Tasks were made available.  I urge you to swap to Appscript Taks in Appsheet.  You will never look at spreadsheet triggers again...well perhaps the onFormSubmit one, but that's a very specific use case...