onEdit event doesnt triggered when google sheet being updated from AppSheet

[Gleb Gorshkov]

Hi everyone!
I`ve built pretty app based on google Sheet via AppSheet.
Main logic is hanged on onEdit event.
When I am doing changes directly in google Sheet everything work as intented.
But onEdit trigger doesn`t called when I do changes in AppSheet.

I was wondering is there are good approach to solving this problem. Maybe some unified trigger.
Fast googling didn`t help. So Ive ended up here.

[Alan Wells]

The On Edit event only triggers from manual edits.  The only way to trigger an event from a change that was made from code is to use the "On Change" event, and the code that sets the values in the Sheet but do so with a special setting.  So, you'd need to install an On Change event, and filter out all event types that are not edits.  For any event type that is not an edit, stop the code from running.  But the On Change event will only trigger from a code change if it's done with the Sheets API.

Does that sound like it would work in your case?

[Gleb Gorshkov]

Thanks for pointing to the installable triggers but as i can see from developers dot google dot com. There is no field with actual information about what was changed (only its type). I was hoping to find something like e.value just like in onEdit.
Still I cant believe that such a simple integration require unbearable amount of knowledge. All I want is to be able to call my working function when cell being changed by any source (manual or through api or through Appsheet etc.). Maybe there are a out of the box solution that I`ve missed?

пятница, 6 августа 2021 г. в 21:51:01 UTC+5, aj.a...@gmail.com:

[Alan Wells]

I'd definitely be interested if someone has a solution.  It is a needed capability that a few people try to pursue.  It doesn't get a lot of interest, and I don't know if there is a feature request already.

[Gleb Gorshkov]

Could you please point to the entry point of feature request process? I would like to give it a try.

пятница, 6 августа 2021 г. в 22:44:20 UTC+5, aj.a...@gmail.com:

[Alan Wells]

Making feature requests is part of the Issue Tracker.

https://issuetracker.google.com/issues

[Jonathan Butler]

My ultra top secret work around for this is a custom function that watches the entire range of the sheet (ex Sheet1:A:F). That custom function updates anytime the range it looks at updates. I use that to call a deployed web app in the same file. That web app will then call the function I want with full permissions. If you want, for security, you can have a key that you send to your web app to validate requests.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0d0ab4b8-71e4-405c-adb8-31c06e062ab6n%40googlegroups.com.

[Alan Wells]

I'm not sure what is meant by "watch."

Can you point us to the docs, or explain how a custom function is made to watch?

Thank you.

[Jonathan Butler]

Sure, I can go into more detail. Everything I will talk about will be taken from the custom function documentation. Custom functions are (to me) the most powerful thing about google apps script for a few reasons. 

1. They recalculate whenever the data changes, not strictly from human interaction.

2. They have a set of things they can do without permissions being explicitly granted.

3. The data the function is watching get's passed as an argument to the function almost instantly.

I define watching as the range given to any formula or custom function that is needed to perform the calculation.

Whenever the data a custom function is "watching" changes, the function recalculates. In the example above A1:B2 is being "watched" by the custom function in C1 called double. That alone is already pretty powerful, but combined with the ability to run with limited permissions, it's a game changer.

In the list above you see one of the available resources is URL Fetch. So whenever the custom function runs, you have the ability to make fetch calls. You can then use that to call your own web app in the same or any other script file to perform some work.

To conclude, whenever data changes the custom function runs. Whenever the custom function runs you can make a fetch request, whenever you send the fetch request, you can send whatever information you want. For example you can you the SpreadsheetApp to get the active sheet and send that to a central "server" that runs a function on that sheet.

I will also include an example of the custom function running for anyone to play around with (Make a copy).

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6d6050fc-e984-4a06-a419-eefaa0e71b73n%40googlegroups.com.