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:
- Add a record to table “task-favorites” 2. After the webhook workflow rule fires, look at Audit History and find rule “favorites_to_tasks”. It may take a minute or two for the record to show up. They are written in batches. 3. Look at the “Payload” value in the webhook audit record. You want to see something like:
{
“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.
- The webhook then invokes the API to:
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.
@Philip_Garrett_Appsh
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