Hi there. I have two tables in an auditing ap...

[GP_Kriel]

Hi there. I have two tables in an auditing app. One a master checklist and a related audit checklist that is populated by dereferencing the master checklist conditions, with additional audit questions regarding each condition of the master checklist.

I have set up a series of actions that triggers a workflow rule to write the master checklist items (based on a subset as specified in an audit table).

The workflow then posts the master checklist item to zapier that then populates the audit checklist sheet… However, this burns through the free or even low tier price plan allowance for amount of actions per month very quickly.

I would rather like to go the route of cutting our zapier and post directly via webhook from appsheet to the audit checklist google sheet via json and a suitable google sheet script. Can anyone assist with setting up the json template and an appropriate script?

[Phil]

@GP_Kriel

I am not sure I understand exactly what you are trying to do so my answer may not be responsive.

I just added an article explaining how to use a webhook and the REST API to do the

following:

1. I have two tables with identical schemas. One is called “People” and the other is called “PeopleEcho”. Both tables contains four fields, namely “Name”, “Age”, “DropDown”, and “Count”.

2. Every time I insert a record in “People” I use a webhook to invoke the REST API to insert an identical record in “PeopleEcho”.

See help.appsheet.com - Example: Copying Newly Added Records

I am simply copying the entire record from “People” to “PeopleEcho” but you could do other things. You could copy a subset of the fields. You could initialize the fields of the new record using constants or expressions.

You could use a similar approach to copy updates made to table “People”

to table “PeopleEcho”. To do that, the webhook should be triggered on Updates to table “People”, and the “Action” in the “JSON Body” should be changed to “Edit”.

Example: Copying Newly Added Records help.appsheet.com

[Henry_Scott]

@GP_Kriel hoekom gebruik jy Zapier en hoekom wil jy webhook gebruik? Kan jy nie alles doen deur Appsheet? Ek is nie expert nie, maar miskien kan ek en jy soundboard.

Let me understand a bit better. You have 2 x tables in the Appsheet app

1. Master Checklist 2) Audit Checklist And they are linked through a REF

You mention a 3rd table - Audit Table. Do you have access to this table through your Audit Appsheet App as a source? Translate

[GP_Kriel]

Dankie @Henry_Scott … Yes, I have access to these tables, and they are properly referenced… meaning that I can individually add a new AuditChecklist Item by selecting an individual MasterChecklist item and then completing the evaluation on that particular condition. So an Audit is registered in the Audits Table, and the related Audit checklist is populated by dereferencing the MasterChecklist Conditions.

The issue is, however, that most of our project checklists contain 200+ conditions to be audited over many licences/permits. Thus, the point of the actions/workflow/webhook is to copy all 200+ MasterChecklist Items to the AuditChecklist table and have them ready for evaluation. Similar to what an INSERT would do in SQL.

[Henry_Scott]

@GP_Kriel there is not enough examples in Sample App supplied by Appsheet how to do this. You will have to pay for some of the Appsheet Business partners to assist you

[GP_Kriel]

@Philip_Garrett_Appsh this seems to be what I need! I was trying to go through the google sheets API, but this seems way better. Much appreciated, thanks!

[GP_Kriel]

@LeventK thanks for the offer to assist. I’ll try the REST API and let you know if I need further assistance.

[Henry_Scott]

@GP_Kriel @Philip_Garrett_Appsh great thank you. Now we can use this as a base for developing more robust solutions. @LeventK said he will also write an article on using the API

[Phil]

@GP_Kriel @Henry_Scott

When you try this, please let me know if you discover things I can add to the documentation to make this easier for future users.

[GP_Kriel]

Hi @Philip_Garrett_Appsh this works really well! Many thanks! A few comments: 1. The link example in the article did not have “apps/” after v1 as in the other webhook articles. 2. If the target table has additional fields that have validation or other constraints, then the workflow rule will not fire unless these are met… I managed to get around this by updating the validation and constraints conditions, and initializing the fields.

[Henry_Scott]

@GP_Kriel will you share when done so we have more examples to use?

[Henry_Scott]

@GP_Kriel will this not be possible using the “native” Appsheet functionality by using a Composite Action (calling multiple Actions in a specific order) that calls a LINKTOFORM() pre-populated with the values and then Autosave on the form to create the looping action to do multiple inserts? Have not tested this myself so just brainstorming here

[GP_Kriel]

Mmm… Linktoform and autosave… will give this a try over the weekend! Dankie baie @Henry_Scott!

[Henry_Scott]

@GP_Kriel Could be a solution. Maybe speed could be a issue vs a native Webhook type solution. Would be interesting to see if there is a native Appsheet way.

Hoop dit werk!

[Aleksi_Alkio]

Unfortunately LINKTOFORM and Autosave “method” won’t work. It will stop the process in the 1st action. If you want to do that without Zapier, you would need to create this functionality with a combination of Webhook > Script > API call.

[GP_Kriel]

Thanks for the confirmation @Aleksi_Alkio.

[GP_Kriel]

So back to my original question… Can anyone assist with the script and json (body, http headers, etc.)? I managed to get a google sheets appscript, and set up the json based on the template provided by apsheet in the webhook workflow, and using this,

rows are appended, but all the fields in the record are entered with “undefined”. Could this be an oauth issue?

[Aleksi_Alkio]

Adding @LeventK

[LeventK]

@GP_Kriel how can I help you?