Re: Hello, I'm trying to use the Zapier integrati....

Daisy_Ramirez · 10-04-2018 05:00 PM

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?

Phil

@Daisy_Ramirez

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” }

Phil

@Daisy_Ramirez

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.

Daisy_Ramirez

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

Phil

@Daisy_Ramirez

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

Phil

@Daisy_Ramirez

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.

Daisy_Ramirez

@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.

Phil

@Daisy_Ramirez

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

Daisy_Ramirez

@Philip_Garrett_Appsh Thanks Philip …I’ll try it now and confirm back

Daisy_Ramirez

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?

Daisy_Ramirez

@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?

Daisy_Ramirez

@Daisy_Ramirez

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]>>” }

Daisy_Ramirez

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”

Phil

@Daisy_Ramirez

What do you see in the Audit History for the REST APIinvoke call?

Daisy_Ramirez

Thanks Philip

Daisy_Ramirez

checking it now…

Daisy_Ramirez

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” }

Daisy_Ramirez

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” }

Phil

@Daisy_Ramirez

I see a matching record in row 2 of table “Sheet54”.

Daisy_Ramirez

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

Phil

@Daisy_Ramirez

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.

Daisy_Ramirez

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

Daisy_Ramirez

@Daisy_Ramirez

Just a quick note, I cleared Sheet54 and ran it again and that’s when the record posted. Tested 3 records successfully

Phil

@Daisy_Ramirez

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.

Daisy_Ramirez

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?

Phil

@Daisy_Ramirez

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

Phil

@Daisy_Ramirez

I assume our messages crossed. See my answer just above.

Daisy_Ramirez

ok got it. is the key value you mentioned earlier the key of the table reference on the work flow?

Phil

@Daisy_Ramirez

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.

Daisy_Ramirez

@Philip_Garrett_Appsh Got it - A million thank you’s for all of your help!

Daisy_Ramirez

@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”

Phil

@Daisy_Ramirez

Thanks for finding and reporting the problem Daisy.

If testing goes well the change will be released on Wednesday afternoon Seattle time.

Daisy_Ramirez

@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

Daisy_Ramirez

@Philip_Garrett_Appsh 3rdArm-506404

Phil

@Daisy_Ramirez

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.

Daisy_Ramirez

@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?

Daisy_Ramirez

@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