Still having trouble with some webhook workfl...

AppSheet AppSheet Q&A
1

Still having trouble with some webhook workflow using Zapier. My rent received table columns are populated by Zapier from my table Property. I basically copied the attendance app. When I use action Rent Received I get a single row created as it should (with exception of ID and Amount Received column, which I will refer to soon), however if I then use action Rent Not Received, which sets it the column in Property table to false, it first repeats the “Y” followed by the “N” rather than just “N” as in the screenshot. Any ideas??

Regarding the ID and Amount Received columns, much like the attendance app my ID column uses UNIQUEID() in initial value, however it does not populate - is this because entrys are direct to spreadsheet and not via app? how does attendance app get around this or does it copy the uniqueid from the parent table? And my amount received expression is =IF([Rent Received]=“Y”,[_THISROW].[Rent Amount],“0”)

in the Appformula field… However this does not work, again is this because the data is not created through the app but rather directly in the sheet and if so, how does the attendance app achieve it?

*refer to unit 3 & 2 in pciture. This was “Rent Received” x 1 and “Rent Not Received” x 1 for each unit, as you can see 1 x extra “Y” created when “Rent Not Received” action performed

Thank you.

2

@William_Stewart

I am not sure I follow, but I wonder if you could have the webhook call the API directly as described here help.appsheet.com - Example: Copying Newly Added Records

Having “one less moving part” might make things easier.

Example: Copying Newly Added Records help.appsheet.com

3

Thanks @Philip_Garrett_Appsh, I will give this a go. I didnt realise it was an option, but yes agree, the less moving / external parts the better!

4

Would the whitespace in my webhook URL be causing the error “URL not well formed”? This seems to be the only error now.

5

@William_Stewart

i.e Rent Received instead of RentReceived? My table name does have the space but I can change if need be, obviously I would rather avoid this as I would need to change all table references too.

6

@William_Stewart

We should probably handle a white space but that will take time. Can you remove the white space from the table name for now? Sorry for the hassle.

7

@Philip_Garrett_Appsh Removing the whitespace did result in success, however it did not result in the row being added in the table. Is this now because I am on a Free plan?

8

@William_Stewart

What does the Audit History say as described here help.appsheet.com - Troubleshooting API Calls

Troubleshooting API Calls help.appsheet.com

9

{

“Operation”: “REST API invoke”,

“AppId”: “f30c709c-224b-43f2-90ff-2fcd917618f2”,

“TableName”: “RentReceived”,

“Result”: “Failure”,

“ResultError”: “REST API REST API invoke request failed: API data is missing.”,

“RecordType”: “Stop” }

Unsure which API data it is referring to.

10

@William_Stewart

I just published the article yesterday. Hope it helps!

11

@Philip_Garrett_Appsh actually - looking at the article this is only to copy newly added rows? I am using Zapier to copy certain columns when an already existing row is updated (i.e. when Date changes) Can the method you describe be trigged on updates? thanks

12

@William_Stewart

Yes, it is essentially the same.

  1. Trigger the webhook on any Update to the table you want to monitor for changes. 2. Change the “Action” in the Post body to “Edit”. 3. You only need to include the key fields and those fields you wish to update in the Post body. Fields you omit from the Post body are unchanged.
13

@Philip_Garrett_Appsh Thanks. I can’t seem to get this working. Using template: {

“Action”: “Edit”,

“Properties”: {

“Locale”: “en-Australia”,

“Timezone”: “New Zealand Standard Time”

},

“Rows”: [

{

“Property Name”: “<<[Property Name]>>”,

“Date”: “<<[Date]>>”,

“Rent Received”: “<<[Rent Received]>>”

“Rent Amount”: “<<[Rent Amount]>>”

}

] }

I did made the template in Textedit and set to plain text to save as .JSON. Other details are correct from what I can tell. Nothing showing errors. Any where I should pay attention to?

14

@William_Stewart

Can you provide: 1. Account id 2. App name 3. Workflow rule name

I am seeing errors in Audit History for rule “Rent RCVD Webhook”. I assume that is the rule in question.

Go to Manage > Monitor > Audit History. Then look for entries for that rule.

15

@Philip_Garrett_Appsh

  1. 708906 2.Property Management App 3. Rent RCVD Webhook

Doesn’t seem to be triggering the webhook from what I can tell. Also, I have just seen that the time is not reflective of current time zone, seems to be 13 hours behind (daylight saving here). But the time zone is definitely set on NZ standard time. Should I be using UTC+12 instead?

16

@William_Stewart

I looked at your most recent invocation of the rule “Rent RCVD Webhook”.

It occurred at 10/5/2018 8:07:46 AM UTC in the Audit History as the result of an update that occurred at 10/5/2018 8:07:32 AM UTC.

UserId: 708906, EventTimeStamp: 10/5/2018 8:07:46 AM +00:00, TraceId: 74840360-f488-4475-a0b8-cf2893c3245c, AppTemplateName: ‘PropertyManagementApp-708906’, TableName: ‘Property’ Properties

AppTemplateName: ‘PropertyManagementApp-708906’

AppId: ‘f30c709c-224b-43f2-90ff-2fcd917618f2’

AppTemplateVersion: ‘1.001352’ Using version 1.001352 rather than current version 1.001382

RuleName: ‘Rent RCVD Webhook’

EventType: ‘Change’

Operation: ‘Change workflow rule’

RuleEvalMode: ‘Production’

TableName: ‘Property’

RuleTableName: ‘Property’

OperationUpdateMode: ‘UPDATES_ONLY’

EventMatch: ‘Workflow event successfully matched’

Condition: ‘’

MatchesCondition: ‘True’

Actions: ‘Created 1 Actions’

Url: ‘https://api.appsheet.com/api/v1/apps/f30c709c-224b-43f2-90ff-2fcd917618f2/tables/Rent Received/Action’

Verb: ‘Post’

Headers: ‘applicationAccessKey:Va…’

Payload: ‘’

Errors: 'Error: The webhook url ‘https://api.appsheet.com/api/v1/apps/f30c709c-224b-43f2-90ff-2fcd917618f2/tables/Rent Received/Action is not well formed Error: Workflow rule ‘Rent RCVD Webhook’ ‘Body’ template properties could not be obtained due to Accessing deleted file ‘Rent RCVD Webhook_BodyTemplate_20181005_025112.json’… Error: Workflow rule ‘Rent RCVD Webhook’ ‘Body’ template could not be read due to FileMimeType ‘’ is unexpected…’

Warnings: ‘Warning: App version 1.001352, which is the latest version, is not marked deployable so the web hook is not being invoked. This web hook would have been posted to ‘https://api.appsheet.com/api/v1/apps/f30c709c-224b-43f2-90ff-2fcd917618f2/tables/Rent Received/Action’.’

Result: ‘Failure’

It has a number of problems listed in the audit record.

I suspect that at some point you replaced the Body Template with a new one but you have not gone back to your workflow rule and updated it to refer to the latest Body Template file. Instead, it appears to be referring to an older version of the file that you have deleted. Be aware that Google refers to files by their Doc Id, not their name. If you replace a template file it will have a new Doc Id. You must go back to the AppSheet Editor and update the JSON Body Template to ensure it refers to the latest template file.

Do this by: 1. Opening the webhook workflow rule in the editor. 2. Clicking the icon at the far right of the “JSON Body Template” and selecting your current JSON body template file.

It appears that at some point you updated rule “Rent RCVD Webhook” to be triggered on “ADD_ONLY”. You have not added any records to table “Property” recently, so the rule is not being triggered.

You app is not deployed, so the webhook will not be invoked.

I suggest you fix these problems and then try the rule again. When you do that, wait a minute or two for the rule to show up in Audit History. Then see what the Audit History tells you. Using the Audit History is the best way to diagnose problems. That is exactly what I do.

We do not adjust dates or times based on the Timezone in Properties. We simply use the Date and DateTime values you specify when we add or update the record.

In one of your JSON Bodies, you specified the expression UNIQUEID(). If you are adding a record with a key that specifies an InitialValue of UNIQUEID() , you should omit the field from JSON. We automatically initialize the key field based on its InitialValue. This is explained in this

paragraph which says:

Each individual Row value must normally include the key field values of the record to be added. However, if the key field contains an “Initial value”, you can omit the key field value. For example, you should omit the key field value when the key field has an “Initial value” of UNIQUEID() or RandBetween(). The system will initialize the key field to the “Initial value”.

See this article https://help.appsheet.com/integrations/api/adding-records-to-a-table

17

@Philip_Garrett_Appsh Thanks for the detailed response. I think you will have seen some old errors, as far as I can see it should be using a recent template. I had tried with on update workflow instead of added. Uniqueid was also removed in the new template. I assume this means that it is not working because it is not deployed. This said, how come my Zapier was working? - this invokes the same workflow, so why would it be working when my app is not deployed? Further, I hadn’t realised workflows were limited to paid plans - is this the case even if I am just using the app for myself? Thanks for all your time, I really appreciate your help.