Trigger Action to recalculate ALL rows via API

[JPAlpano]

Scenario:
One set of users only interacts with App based on Table 1.
Another set of users only interacts via GSheet imported as Table2.

Action button created:
I have created an action button which simply changes an [Update Counter] of Table 1 column to +1.
This will trigger all app formulas to recalculate.

Question:
How do I trigger the action button to fire on ALL rows using the API?
Most docs I've seem talks about passing in Keys of specific rows to fire the action on.
But I want all rows to recalculate.

I will call the API via Apps Script but I just need to know the format of that API.

[dbaum · Accepted solution]

When you pass the list of keys from the action to the automation's webhook task to invoke the API, I assume you can simply include all rows with an expression like Table[Key Column].

[SkrOYC]

Make a bot that listens to adds on a "trigger_updates" table.
This should have an Action task.
Configure it to "Run action on rows"

Select the table you want to update as well as all of the rows that need to be updated and the Update action that adds +1

Then you can just add a row via API to the new table and it will trigger everything else

[JPAlpano]

Thanks, but Table 2 will not only have adds but edits.

And those edits are made directly on the sheet.

Will the bots be triggered by the adds or edits made on the sheet?

Wont the app just simply sync those new or edited info as if they were there before?

[SkrOYC]

I don't get it.

---

@JPAlpano wrote:

Table 2 will not only have adds but edits.

---

How does this change things?

---

@JPAlpano wrote:

And those edits are made directly on the sheet.

---

Which is a bad practice and can defeat the purpose of AppSheet in the first place, but I have talked about that many times and I guess anyone can do whatever they want 🤔

---

@JPAlpano wrote:

Will the bots be triggered by the adds or edits made on the sheet?

---

Afaik, bots won't be triggered with any changes made directly to the GSheet/MSExcel

---

---

@SkrOYC wrote:

Make a bot that listens to adds on a "trigger_updates" table.

---

This is a new table you should create

[JPAlpano]

I know it's bad practice. 😞

But my hands are tied. The app is mainly designed for the first set of users.

The other set of users editing in GSheet of Table 2 has operational processes where they work more efficient doing the changes in the spreadsheet than via the app.

Table 1 (the one updated via app) has Lookup() formulas to get some information from Table 2 (the one edited directly in the sheet).

The problem is that the Lookup() formulas in Table 1 won't recalculate unless I trigger the action or a user edited the rows. 

The API docs say specify the rows to run action on, but I want to run it on all, similar to that action in your 1st screenshot can do. 

[SkrOYC]

You want the LOOKUP() expressions to be updated on the Table 1 when there are changes directly made to the GSheet on Table 2?

---

---

@JPAlpano wrote:

they work more efficient doing the changes in the spreadsheet than via the app.

---

Don't worry, in the company I work for I have had a rough time convincing them to left the familiar MSExcel for a better AppSheet.
I take it as a challenge to make sure that an AppSheet app can do more things and on a better way that they are used to until they can leave the old way of doing things

[SkrOYC]

External eventing with Google Sheets - AppSheet Help

Seems like you can, but I think that it won't be an efficient thing since a lot of people will be interacting with the sheet

[JPAlpano]

Thanks. I've read that documentation and has already sent a request to our Google Admin to whitelist the add-on for our domain.

That would take some time as it would go through some evaluation processes.

For the meantime, I'm hoping there's another way via http request and not rely on the add on. 

[JPAlpano]

---

@SkrOYC wrote:

You want the LOOKUP() expressions to be updated on the Table 1 when there are changes directly made to the GSheet on Table 2?

---

Yes.  I am planning to give the users of Table 2's gsheet a custom menu in gsheet to call api of appsheet. 

[dbaum · Accepted solution]

When you pass the list of keys from the action to the automation's webhook task to invoke the API, I assume you can simply include all rows with an expression like Table[Key Column].

[JPAlpano]

The list gets long and I am hoping I wouldn't have to build that JSON object to include all the row keys.

But seems that's my only option for now until I get that add-on approved.

Will mark this as the solution.

[dbaum]

I haven't concretely done this and, so, am only thinking about this in the abstract. Nonetheless, I wouldn't think it has to be too tedious.

Fleshing out the technique referenced in my previous response:

• It should be straightforward within AppSheet to write an expression that returns the list of keys you need.
• Send that list to Apps Script where it's either already an array by default or can be turned into one.
• Invoke the AppSheet API's Find action to return the full rows for that array of keys. If you define a slice to run the API on, it could presumably include just the columns you need--i.e., key and [Update Counter].
• Use foreach to iterate on the API's response to increment each row's [Update Counter].
• Send that to AppSheet API's Edit action.

Thinking about this a little more.... it's probably possible to skip some of those initial steps. You could just start with the Find API action. Either run it on a slice that returns the rows of interest or use its SELECTOR property or just return all rows of the entire table ("Rows": []) if that's what you need.

[JPAlpano]

---

@dbaum wrote:

• It should be straightforward within AppSheet to write an expression that returns the list of keys you need.
• Send that list to Apps Script where it's either already an array by default or can be turned into one.

---

Did you mean Appscript on forst bullet and AppSheet on second?

The trigger must initially come from Apps Script

[dbaum · Accepted solution]

I indeed meant AppSheet in first bullet and Apps Script in second bullet. So, I guess I misunderstood your scenario. I didn't realize you weren't starting in AppSheet.

If I'm understanding now, then my "Thinking about this a little more...." comment is what's applicable in your scenario. Whatever the trigger is within your script, start by invoking the AppSheet API's Find action to return the keys of the rows you need--either via SELECTOR property, or via a filtered slice defined within AppSheet, or just return all the rows. Once you have the keys, you can then invoke the AppSheet API's Edit action to update the Change Counter or whatever other columns for those rows.