Google Sheet Notifications From Fetched Data

Rainwavesnow · 06-18-2023 08:41 AM

I have seen some talk around this missing option for AppSheet. Wondering if anybody has found a work around this acceptance criteria:

As a end user of the app

I need to get notified when a new row is added (programmatically via a Google app script) and then do a deep link to that new rows data.

For example. I have a Google app script that fetches an an api that populates to my Google sheet. The sheet is connected to AppSheet. But when a new row is added to the sheet there is no way to get notified through the app of the data change.

In my case I need to compare latlong between the dynamic fetch sheet and a static Google sheet with their latlong’s and notify personnel on the new row/data.

I would think Google sheets would have better asynchronous options with AppSheet.

Thank You

WillowMobileSys

@Rainwavesnow wrote:

I need to get notified when a new row is added (programmatically via a Google app script) and then do a deep link to that new rows data.

Since it seems you want a notice for each new row as it is added, I recommend changing the Google App Script to NOT add the row directly to the datasource. Instead have the script call the AppSheet API to add the row. Then you can create Bots that detect the new row and trigger additional functionality such as sending a notification.

Manage apps using the AppSheet API

@Rainwavesnow wrote:

I would think Google sheets would have better asynchronous options with AppSheet.

They are working on it! AppSheet was only recently acquired by Google. The focus over the past couple of years has been integrated into the Google technology stack and building out the more business oriented features (new Desktop Mode).

Rainwavesnow

Cool. What minimal plan do you need to access the AppSheet api. And is that adding a row to the AppSheet database in preview?

Disregard…I found it…. This feature is supported for Enterprise plans only.

WillowMobileSys

@Rainwavesnow wrote:

Disregard…I found it…. This feature is supported for Enterprise plans only.

There has been some recent conversations about this and I believe the consensus was that even the Core plans were able to create API calls from External services. I have only 1 Core app which has only internal API calls. So I have no experience to leverage and can only suggest trying it.

@Rainwavesnow wrote:

Also the bot is limited on how often you can check for a new row I believe. 1 hour minimum?

Yes, a single Bot can now be scheduled Hourly but you can set the minute on which it runs. So....you could create 4 Hourly Bots - 1st runs at 00 minutes, 2nd runs at 15 minutes, 3rd at 30 and 4th at 45.

Of course you could add more Bots to run even more frequently.

PRO TIP:  because it is likely that other App Creators run their Bots at 00, 15, 30 or 45, 
you might want to avoid the server congestion and associated delays and set them to run at 
7, 22, 37 and 52 minutes.....as an example!

I hope this helps!

Rainwavesnow

Also the bot is limited on how often you can check for a new row I believe. 1 hour minimum?

For example my Google fetch script runs every 15 minutes to look for new data from the api endpoint. Airtable even has their automations down to 15 minutes for every scheduled run. (Zapier and Make .com can go to even smaller schedules based on plan type)

dbaum

@Rainwavesnow wrote:

This feature is supported for Enterprise plans only.

While that's what's officially published, I believe it may reference only the AppSheet admin API for managing teams, billing, etc. Regardless, my understanding is that at least some non-Enterprise plans also de facto can use the AppSheet API.

@Rainwavesnow wrote:

Also the bot is limited on how often you can check for a new row I believe. 1 hour minimum?

No--at least not in practice. While that's the maximum frequency for a single automation event, you can create multiple automations that run the same process.

@Rainwavesnow wrote:

Zapier and Make

ICYMI: Both of these services have AppSheet connectors and can update data in an AppSheet app.

@Rainwavesnow wrote:

I have a Google app script that fetches an an api

Consider exploring instead the new feature to Use return values from webhooks - AppSheet Help.

Rainwavesnow

Thank you all for pointing me in the right direction.

However when trying to post to the api for my appsheet database I keep getting this:

@Rainwavesnow wrote:
I have seen some talk around this missing option for AppSheet. Wondering if anybody has found a work around this acceptance criteria:
As a end user of the app
I need to get notified when a new row is added (programmatically via a Google app script) and then do a deep link to that new rows data.

For example. I have a Google app script that fetches an an api that populates to my Google sheet. The sheet is connected to AppSheet. But when a new row is added to the sheet there is no way to get notified through the app of the data change.
In my case I need to compare latlong between the dynamic fetch sheet and a static Google sheet with their latlong’s and notify personnel on the new row/data.

I would think Google sheets would have better asynchronous options with AppSheet.

Thank You

Here is my current code:

dbaum

Based on a cursory skim (not a thorough analysis) of your script, it looks like you're explicitly setting the value of the [_RowNumber] column. That's presumably not possible--the row number comes from the data source. Try excluding that.

Also, in general, row number is not an ideal key.