Hello, Iโm trying to use the Zapier integration with AppSheet but when I search for AppSheet in Zapier it doesnโt come up.
Is there another step I should be following to get AppSheet to be available to me in the Zapier system?
In the Audit History at 10/7/2018 8:27:44 PM I see the following API record.
Properties:
{
โOperationโ: โREST API invokeโ,
โAppIdโ: โbfde3f7a-a539-48cb-b84e-362ea81270f5โ,
โTableNameโ: โSheet54โ,
โResultโ: โFailureโ,
โResultErrorโ: โREST API REST API invoke request failed: Failed to get API โRowsโ due to: โfileโ is not a valid table column nameโฆโ,
โRecordTypeโ: โStopโ }
That error message indicates that โfileโ is not one of the fields in the
In the Audit History at 10/7/2018 8:27:44 PM I see the following Webhook audit record for rule โfavorites_to_tasksโ. As the API audit records indicates, the Row data appears to specify a field called โfileโ that I do not see in the schema for table โSheet54โ. It is likely to work if you remove that field from your webhook JSON body template. ".
Properties:
{
โAppTemplateNameโ: โ3rdArm-506404โ,
โAppIdโ: โbfde3f7a-a539-48cb-b84e-362ea81270f5โ,
โAppTemplateVersionโ: โ1.003994โ,
โRuleNameโ: โfavorites_to_tasksโ,
โEventTypeโ: โChangeโ,
โOperationโ: โChange workflow ruleโ,
โRuleEvalModeโ: โProductionโ,
โTableNameโ: โtask_favoritesโ,
โRuleTableNameโ: โtask_favoritesโ,
โOperationUpdateModeโ: โUPDATES_ONLYโ,
โEventMatchโ: โWorkflow event successfully matchedโ,
โConditionโ: โ=AND(USEREMAIL()=[createdBy], [activate]=TRUE)โ,
โMatchesConditionโ: โTrueโ,
โActionsโ: โCreated 1 Actionsโ,
โUrlโ: โhttps://api.appsheet.com/api/v1/apps/bfde3f7a-a539-48cb-b84e-362ea81270f5/tables/Sheet54/Actionโ,
โVerbโ: โPostโ,
โHeadersโ: โapplicationAccessKey:Uโฆโ,
โPayloadโ: โ{\r\n"Actionโ: โAddโ,\r\n"Properties": {\r\n"Locale": โen-USโ,\r\n"Location": โ47.623098, -122.330184โ,\r\n"Timezone": โPacific Standard Timeโ\r\n},\r\n"Rows": [\r\n{\r\n"name": โbuy strawberriesโ,\r\n"ItemNo": โ21245456โ,\r\n"quantity": โ1โ,\r\n"task_supplier_id": โ59d262f510e3d13d3173b2baโ,\r\n"image": โโ,\r\n"detail": โโ,\r\n"list": โ59dcd1d410e3d13d31732018โ,\r\n"sub_list": โGroceriesโ,\r\n"file": โWeekly Groceriesโ,\r\n"activate": โYโ\r\n}\r\n",
โPerformanceโ: โ{โVersionโ:1,โTimeโ:โ00:00:00โ,โPerformanceTimingRootโ:{โMidโ:154,โParamsโ:{โParamListโ:[{โPidโ:13,โValueโ:โtask_favoritesโ}]},โTimerโ:{โTimeโ:โ00:00:00โ},โChildrenโ:[]}}โ,
โResultโ: โSuccessโ }
You can specify the JSON body in either the Body or Body Template. I tried both, and both worked for me.
The API can be triggered by: 1. The web hook workflow rule 2. Zapier 3. Doing a post from any system capable of doing an HTTP post.
Letโs use the Audit History to figure this out. Please do the following:
{
โActionโ: โAddโ,
โPropertiesโ: {
โLocaleโ: โen-USโ,
โTimezoneโ: โEastern Standard Timeโ
},
โRowsโ: [
{
โnameโ: โ<<[name]>>โ,
โitemNoโ: โ<<[itemNo]>>โ,
โquantityโ: โ<<[quantity]>>โ,
โtask_supplier_idโ: โ<<[task_supplier_id]>>โ
โimageโ: โ<<[image]>>โ
โdetailโ: โ<<[detail]>>โ
โlistโ: โ<<[list]>>โ
โsub_listโ: โ<<[sub_list]>>โ
}
] }
The Audit Record at 10/5/2018 8:03:02 PM looked like this:
โPayloadโ: "{\r\n
โnameโ: โtest anotherโ,\r\n
โitemNoโ: โbuy stuffโ,\r\n
โquantityโ: โ1โ,\r\n
โtask_supplier_idโ: โ59d7c79f10e3d13d3173b300โ,\r\n
โimageโ: โโ,\r\n
โdetailโ: โโ,\r\n
โlistโ: โ59d7d06810e3d13d3173b303โ,\r\n
โsub_listโ: โGroceriesโ,\r\n
โactivateโ: โYโ\r\n}\r\n",
(Ignore the slashes in front of the quotes and all โ\r\nโ values. โ\r\nโ is carriage return and line feed and it appears on each new line.)
This payload is missing the Action, Properties, etc. It looks like only the data fields are present.
The API correctly rejected that payload saying the Action was missing.
RESOLVED: Found notes on initial connection help.appsheet.com - Connect a Zapier โZAPโ to Your App Connect a Zapier โZAPโ to Your App help.appsheet.com
Depending on what you are trying to do, it may be easier to use a webhook to call the AppSheet API directly. See help.appsheet.com - Example: Copying Newly Added Records
You can use a similar approach for doing updates. Example: Copying Newly Added Records help.appsheet.com
I am not clear if webhooks and the API are germaine in this case.
Let me explain how webhooks and the API are used. 1. You create a webhook that is is triggered when the client sends an add, update, or delete request to the server.
a. Add one or more records.
b. Update one or more records.
c. Delete one or more records.
d. Invoke an Action on one or more records.
In order for the webhook and the API to be helpful in your case, you would need to perform an add, update, or delete that would trigger the webhook. I am not clear how the user selecting one or more values from their favorites list would trigger an add, update, or delete that would trigger the webhook.
Yes you are exactly right. In my scenario, a user would set several records to Send
(a Y/N field.) allowing the workflow to trigger.
My inexperience is with the webhook. Can you send a sample webhook for an Add to table scenario? Again, Iโm looking to create copies of the records from the tasks_favorites table and copy them to the tasks table.
Any help would be great a million thanks.
See help.appsheet.com - Adding Records to a Table
and https://help.appsheet.com/integrations/api/example-copying-newly-added-records
Adding Records to a Table help.appsheet.com
@Philip_Garrett_Appsh Thanks Philip โฆIโll try it now and confirm back
Is there a field mapping example for the JSON Body? I see where it outlines the field name and the corresponding data to the field in the JSON file for the tasks_favorites table but where do I specify which fields the data would go to in the tasks table ? Can this be done as an expression? Do you have an example of what a file like this would look like?
@Philip_Garrett_Appsh Is there a field mapping example for the JSON Body? I see where it outlines the field name and the corresponding data to the field in the JSON file for the tasks_favorites table but where do I specify which fields the data would go to in the tasks table ? Can this be done as an expression? Do you have an example of what a file like this would look like?
Hi Philip - no luck. Iโm getting a success in the audit logs but again nothing is posting to the tasks table. Hereโs the body data - I canโt find what Iโm doing wrong:
{ โActionโ: โAddโ, โPropertiesโ: { โLocaleโ: โen-USโ, โLocationโ: โ47.623098, -122.330184โ, โTimezoneโ: โPacific Standard Timeโ }, โRowsโ: [ { โnameโ: โ<<[name]>>โ, โItemNoโ: โ<<[itemNo]>>โ, โquantityโ: โ<<[quantity]>>โ, โtask_supplier_idโ: โ<<[task_supplier_id]>>โ, โimageโ: โ<<[image]>>โ, โdetailโ: โ<<[detail]>>โ, โlistโ: โ<<[list]>>โ, โsub_listโ: โ<<[sub_list]>>โ, โfileโ: โ<<[file]>>โ, โactivateโ: โ<<[activate]>>โ }
Hi Philip still no luck. I inserted the field โfileโ in Sheet54 to match test_favorites and got the success result but the additional record did not post to Sheet55. Here it is for quick review:
Properties:
{
โAppTemplateNameโ: โ3rdArm-506404โ,
โAppIdโ: โbfde3f7a-a539-48cb-b84e-362ea81270f5โ,
โAppTemplateVersionโ: โ1.003996โ,
โRuleNameโ: โfavorites_to_tasksโ,
โEventTypeโ: โChangeโ,
โOperationโ: โChange workflow ruleโ,
โRuleEvalModeโ: โProductionโ,
โTableNameโ: โtask_favoritesโ,
โRuleTableNameโ: โtask_favoritesโ,
โOperationUpdateModeโ: โUPDATES_ONLYโ,
โEventMatchโ: โWorkflow event successfully matchedโ,
โConditionโ: โ=AND(USEREMAIL()=[createdBy], [activate]=TRUE)โ,
โMatchesConditionโ: โTrueโ,
โActionsโ: โCreated 1 Actionsโ,
โUrlโ: โhttps://api.appsheet.com/api/v1/apps/bfde3f7a-a539-48cb-b84e-362ea81270f5/tables/Sheet54/Actionโ,
โVerbโ: โPostโ,
โHeadersโ: โapplicationAccessKey:UUs1Q-zxY1W-KS8WP-cKUE4-nT6ES-Ow7qh-ZYmWk-AnYQoโ,
โPayloadโ: โ{\r\n"Actionโ: โAddโ,\r\n"Properties": {\r\n"Locale": โen-USโ,\r\n"Location": โ47.623098, -122.330184โ,\r\n"Timezone": โPacific Standard Timeโ\r\n},\r\n"Rows": [\r\n{\r\n"name": โhoney crisp applesโ,\r\n"ItemNo": โ78665433โ,\r\n"quantity": โ1โ,\r\n"task_supplier_id": โ59d262f510e3d13d3173b2baโ,\r\n"image": โโ,\r\n"detail": โโ,\r\n"list": โ59dcd1d410e3d13d31732018โ,\r\n"sub_list": โGroceriesโ,\r\n"file": โWeekly Groceriesโ,\r\n"activate": โYโ\r\n}\r\n",
โPerformanceโ: โ{โVersionโ:1,โTimeโ:โ00:00:00โ,โPerformanceTimingRootโ:{โMidโ:154,โParamsโ:{โParamListโ:[{โPidโ:13,โValueโ:โtask_favoritesโ}]},โTimerโ:{โTimeโ:โ00:00:00โ},โChildrenโ:[]}}โ,
โResultโ: โSuccessโ
What do you see in the Audit History for the REST APIinvoke call?
Thanks Philip
checking it nowโฆ
ok I see that it does not populate Initial Value fields although the document says it does? See text below. Iโll add those fields here and confirm backโฆ
From Doc: When a Record is Added All fields are initialized to initial values and all app formulas are computed. The field values you specify in the request are applied. App formulas are computed and change fields are updated. Required, Required_If, and Valid_If conditions are enforced. The record or records are added to the table. Workflow rules, if any, are invoked.
Properties:
{
โOperationโ: โREST API invokeโ,
โAppIdโ: โbfde3f7a-a539-48cb-b84e-362ea81270f5โ,
โTableNameโ: โSheet54โ,
โActionโ: โAddโ,
โRestActionTypeโ: 1,
โDataActionโ: โApp: add a new row (not a row-level action)โ,
โlocaleโ: โen-USโ,
โlocationโ: โ47.623098, -122.330184โ,
โrunAsUserEmailโ: โโ,
โtimezoneโ: โPacific Standard Timeโ,
โtzOffsetโ: โ420โ,
โuserIdโ: 506404,
โRowsโ: 1,
โPerformanceโ: โ{โVersionโ:1,โTimeโ:โ00:00:00.3483254โ,โPerformanceTimingRootโ:{โMidโ:317,โTimerโ:{โTimeโ:โ00:00:00.3483254โ},โChildrenโ:[{โMidโ:62,โTimerโ:{โTimeโ:โ00:00:00.3395982โ}}]}}โ,
โResultโ: โFailureโ,
โResultErrorโ: โError: Field โcreatedByโ in table โSheet54โ having key โhoney crisp applesโ is required to have a value.โ,
โRecordTypeโ: โStopโ }
Ok added the fields to the JSON filed as they were already represented in Sheet54. Ran it again and received a success result but nothing posted to Sheet54
Properties:
{
โAppTemplateNameโ: โ3rdArm-506404โ,
โAppIdโ: โbfde3f7a-a539-48cb-b84e-362ea81270f5โ,
โAppTemplateVersionโ: โ1.003999โ,
โRuleNameโ: โfavorites_to_tasksโ,
โEventTypeโ: โChangeโ,
โOperationโ: โChange workflow ruleโ,
โRuleEvalModeโ: โProductionโ,
โTableNameโ: โtask_favoritesโ,
โRuleTableNameโ: โtask_favoritesโ,
โOperationUpdateModeโ: โUPDATES_ONLYโ,
โEventMatchโ: โWorkflow event successfully matchedโ,
โConditionโ: โ=AND(USEREMAIL()=[createdBy], [activate]=TRUE)โ,
โMatchesConditionโ: โTrueโ,
โActionsโ: โCreated 1 Actionsโ,
โUrlโ: โhttps://api.appsheet.com/api/v1/apps/bfde3f7a-a539-48cb-b84e-362ea81270f5/tables/Sheet54/Actionโ,
โVerbโ: โPostโ,
โHeadersโ: โapplicationAccessKey:UUs1Q-zxY1W-KS8WP-cKUE4-nT6ES-Ow7qh-ZYmWk-AnYQoโ,
โPayloadโ: โ{\r\n"Actionโ: โAddโ,\r\n"Properties": {\r\n"Locale": โen-USโ,\r\n"Location": โ47.623098, -122.330184โ,\r\n"Timezone": โPacific Standard Timeโ\r\n},\r\n"Rows": [\r\n{\r\n"name": โbuy strawberriesโ,\r\n"ItemNo": โ21245456โ,\r\n"quantity": โ1โ,\r\n"task_supplier_id": โ59d262f510e3d13d3173b2baโ,\r\n"image": โโ,\r\n"detail": โโ,\r\n"list": โ59dcd1d410e3d13d31732018โ,\r\n"sub_list": โGroceriesโ,\r\n"file": โWeekly Groceriesโ,\r\n"activate": โYโ,\r\n"createdBy": "3rdarmlife@gmail.com",\r\n"createdAt": โ10/5/2018 12:33:00 PMโ,\r\n"fav_id": โ100718โ,\r\n}\r\n",
โPerformanceโ: โ{โVersionโ:1,โTimeโ:โ00:00:00โ,โPerformanceTimingRootโ:{โMidโ:154,โParamsโ:{โParamListโ:[{โPidโ:13,โValueโ:โtask_favoritesโ}]},โTimerโ:{โTimeโ:โ00:00:00โ},โChildrenโ:[]}}โ,
โResultโ: โSuccessโ }
I see a matching record in row 2 of table โSheet54โ.
Yes its an old sample record. Should I clear Sheet54 and re-test? Remember Iโm looking to add a record to Sheet54 so Iโm looking for that 4th record
Yes, because if a record having the same key you are trying to Add already exists, then we turn the Add into and Update. That is probably what happened here.
Hi Philip, the record populated! What if the field names in the receiving table are different than the origninal table? How do I set the field values to other values? Can I use drefโs or expressions here in the field values ? Do you have sample JSON files where calculated values are used?
Thanks so much for your help
Just a quick note, I cleared Sheet54 and ran it again and thatโs when the record posted. Tested 3 records successfully
You can write any expression you like to populate the field values.
Remember that the webhook is being invoked on behalf of the record that triggered the webhook workflow rule, so references to simple field values take values from the field values of that triggering record.
However, you can write any expression you wish including de-refs and any other expression.
Aha! Ok please advise on the field values. I have hard values in for the fields but need to have for example: UniqueID for the fav_id, Now() for CreatedAt, USEREMAIL() for createdBy etc, etc.
Can I use Drefโs etc?
I have just added a new article describing how to do this.
See help.appsheet.com - Example: Copying Updated Records
Example: Copying Updated Records help.appsheet.com
I assume our messages crossed. See my answer just above.
ok got it. is the key value you mentioned earlier the key of the table reference on the work flow?
I think you are asking about when we turn an Add into an Update.
We do that when the key of the record you are trying to Add matches the key of an existing record in that table.
For example, letโs say the key field of the People table is โNameโ.
Imagine โuser1โ adds a record having the key value โBobโ and โuser2โ tries to add the same record at almost the same instant. Assume the sync from โuser1โ arrives first, so we insert the record for โBobโ. Now the sync for โuser2โ arrives a second later. We see that the record with key โBobโ already exists, so we turn the add into an update. It as if the second user did an Update to โBobโ rather than an Add.
In the enterprise database world (where we mostly come from) turning an Add (i.e. Insert) into an Update is called an โUpsertโ.
We are talking about letting you decide the conflict resolution policy for your app, but at the moment we always do an โUpsertโ when this condition arises.
@Philip_Garrett_Appsh Got it - A million thank youโs for all of your help!
@Philip_Garrett_Appsh Hi Philip, Iโve started work on my live tables and came across an error code for a field thatโs not required - it failed a Valid_If condition. I have quite a few fields that are not required so its odd that this one was picked up. Hereโs the error:
โResultโ: โFailureโ,
โResultErrorโ: โError: Value โโ in field โmember_user_idโ in table โtasksโ having key โ9ccead21โ failed โValid_Ifโ condition.โ,
โRecordTypeโ: โStopโ
Thanks for finding and reporting the problem Daisy.
If testing goes well the change will be released on Wednesday afternoon Seattle time.
@Philip_Garrett_Appsh Hi Philip, just sent an email reply as well.
Iโve configured as youโve outline above and received an API data is missing error. I then renamed columns so that both tables would match and got a success according to the audit logs but data is not being populated into the table. Can you assist? Iโm sure its something straight forward I just canโt see it. Thanks so much
@Philip_Garrett_Appsh 3rdArm-506404
I am confused by what I am seeing in the Audit History.
At 10/5/2018 5:39:56 PM
I see webhook rule โfavorites_to_tasksโ being invoked but the body contains HTML rather than JSON.
At 10/5/2018 5:39:56 PM
I see the API being invoked. It reported that the โAPI data is missingโ. That makes sense because the body was not JSON.
At 10/5/2018 6:17:09 PM
I see the API being invoked again. It fails with the error โAPI data is missingโ. How is the API being triggered? I donโt see the webhook being triggered at that time.
Can you do me a favor and create a simple test app with two tables and two or three records that reproduces the problem? That will allow me to copy the app and debug it.
@Philip_Garrett_Appsh ok I copied the text from step 8 of the document and replaced the field names with my field names.
8.Create a โJSON Body Templateโ that looks like this:
{
โActionโ: โAddโ,
โPropertiesโ: {
โLocaleโ: โen-USโ,
โTimezoneโ: โEastern Standard Timeโ
},
โRowsโ: [
{
โNameโ: โ<<[Name]>>โ,
โAgeโ: โ<<[Age]>>โ,
โDropDownโ: โ<<[DropDown]>>โ,
โCountโ: โ<<[Count]>>โ
}
] }
Should I leave the JSON Body blank and let the JSON Body Template do the work?
How is the API normally triggered? I thought the WorkFlow was firing off the API when a change was made in the target table?
@Philip_Garrett_Appsh Hi Philip, Iโm unfortunately still struggling with this one and want to make sure Iโm able to use a webhook for the scenario below:
*I have a favorites list of items (task_favorites)
Iโd like our users to maintain
*Once a week, theyโll pull up their favorites list and select the items they need
*All of the items selected should then be inserted into another table (tasks).
Iโm familiar with the deep links that allow this to take place for a single record but is there a way to do this in bulk?
Iโm using Google Sheets.
Thanks Philip
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |