Re: Which version of AppSheet to use with Zapier?

Brady_Lovell · 02-28-2020 03:28 PM

I have a file on PDFfiller that people fill out and submit. Upon their submission, Zapier take the file and uploads it to gDrive.

I am trying to add a 3rd Zap, to Invoke Action in one of my apps in which the action will ‘change data’ (rename a File column) but am not sure which version to use?

TIA for any and all input!

Brady_Lovell

Also having a hard time understand what this required ID field is within the Zap. I was thinking it was the Key column in my Carriers table, but that is not an option from the drop down list. Any ideas?

Brady_Lovell

Ended up changing my workflow around a little bit. I finally got the file to rename like I wanted it to, but I’m not sure what the difference is between using gSheets or gForms to bring in the gForm submission data. I initially chose gForm but realized it didn’t have an ‘INSTANT’ badge next to it in my Zapier Dashboard. So I went with gSheets as it displays the ‘INSTANT’ badge. Problem here though, is it still takes 3 minutes (near exactly) every time for it to return the data. Which is fine for now, but far from instant. I’m just thankful it’s all working. (Any insight on this issue is greatly appreciated)

Anyways, when a new row is added to the gSheet, Zapier uses my gMail account to send a link of my PDFFiller form from the email address referenced from the gForm submission. (Zap: “carrier_invite_submission”)

When the user completes the PDFfiller form, Zapier uploads it to my gDrive and names it: (Zap: “carrier_submission_upload”)

CONCATENATE(“Documents/Carrier Files/Contract/”,[MC Number],".pdf")

All is working up to this point, except for the fact that the app user, won’t be able to see the newly concatenated value until the row itself is updated. I need this done automatically, and was thinking I could solve this problem by using Zapier to Invoke an AppSheet Action called “action_contract_submission” which would take the [Executed Contract] column and change the the value to equal “Uploaded”.

I would rather the action do some sort of FORCE SYNC, but haven’t gotten that far yet. I’ve read all the info I could find on the matter in all of the AppSheet resources, and have found where I may be able to use a Deep Link somehow and use an &at or something like that. I’ll have to keep digging. (If anyone has any insight on this it will be greatly appreciated)

This has been a fun challenge so far, but I’m going to need some help from someone with a little more experience with Zapier, as I have none. I’ve spent a great deal of time to get to this point, so if anyone can help, it will be greatly appreciated.

Thank you

hugheshilton

So if I’m understanding the problem correctly, Zapier is uploading the PDF to the gDrive and writing its location to the sheet. But if an AppSheet user is currently using the app, they won’t see the updated value until they sync the app. Is that correct?

I don’t know of any way to force the user’s app to sync (which would be dangerous anyway since they might be in the middle of updating something). What you could do is broadcast a push notification to your users that the PDF has been uploaded and they should sync to see it. I think you could do this by adding a Workflow rule that gets triggered when Executed Contract gets set to Uploaded. Then when Zapier invoked the action which sets the value, your workflow rule gets kicked off and sends your users a push notification so they know they need to sync.

Would something like that work for you?

Regards,
Hughes

Brady_Lovell

I do like the notification idea, and I think that’ll work. But AppSheet is actually the one writing the filename to the spreadsheet, so a basic sync of the app won’t bring in the value I don’t thibk. I think I’d actually have to click on the row itself within the app, and make an adjustment to that row, and save it for it to populate the filename.

If I knew how to use Zapier to insert the filename instead of AppSheet, then a basic sync would bring in thr data, which might be achievable by changing my Zapier workflow slightly.

Regardless of which method to insert thr filename, the action I have set is currently not working. I chose AppSheet 1.0 from the drop down menu in Zapier, as that is the only one I couod get to not throw an error.

Brady_Lovell

Here is the AppSheet action I have set within Zapier. Only thing I’m not too sure of is the Timestamp field, which probably indicates that is where the problem lies, but I am not sure.

Edit: Just realized the timezone is set to PST. I’m in EST. Not sure if that has anything to do with that action not being invoked, but I’ll correct it and see.

hugheshilton

Okay so is the actual problem that you want the file available within the AppSheet app, but you don’t know where to save the file and how to name the value on the worksheet from Zapier in order for AppSheet to see it correctly?

hugheshilton

So for an app based on a Google sheet that has columns of type [File], AppSheet creates a subfolder in the same location as the Google sheet with the app name and “_ Files _” appended to it. What you need to do is have Zapier save the PDF within that folder for your app. Then in the Google Sheet, for the File column, you need to write the relative path to the file.

So if your app is named “My App” and you upload a PDF named “123.pdf”, you would need to put that PDF into the folder “My App_Files_” and then you would need to set the value of the File column in your Google sheet to “My App_Files_/123.pdf”. Does that make sense?

It sounds like part of your problem is that Zapier is saving the files to the location “Documents/Carrier Files/Contract/[MC Number].pdf" but your AppSheet app isn’t going to see them in that location.

Brady_Lovell

I’ve got the filename functionality working just fine, and coming into the app just fine. The file column that you’re referring to is called [Contract] and it’s file path is set to

Documents/Carrier Files/Contract/

Whenever Zapier uploads the file to gDrive, it uploads it to that folder as well. So the first two steps of the Zap work fine. The problem is when I add the AppSheet step, it’s not doing anything.

hugheshilton

Hmm, okay then maybe I don’t understand the problem.

What do you mean by the AppSheet step? Do you mean the Action that sets the Executed Contract to Uploaded? As in, Zapier is not successfully calling the action or the action isn’t setting the value correctly? Or do you mean that you want AppSheet to set the path to the uploaded PDF file in the Google Sheet and that’s not happening?

Brady_Lovell

I’m almost certain that Zapier’s AppSheet step is not calling the AppSheet action in my app. Here are the 3 steps of my Zap. The first 2 work perfectly. The 3rd step, the AppSheet step, is not working correctly.

Brady_Lovell

hugheshilton

There are pretty detailed instructions for calling AppSheet from Zapier here:

It says in that article to use AppSheet 2.0.0 so I think that may be part of your problem. It looks like it’s trying to use that Timestamp field as a key value to find the right row in your table, but the “ErrorDescription” in your screen shot says there is no row in the table with that key value. Maybe with AppSheet 2.0.0 you can specify the key values rather than using a Timestamp like that. Does that help?

Brady_Lovell

I’ve read through all of the AppSheet resources, including the one you mentioned, and am still having a hard time getting the link from Zapier to AppSheet working properly. I ended up setting the Timestamp value in Zapier to the [Timestamp] column of the table, which is also the Key. So I think I have everything filled out correctly, but am now getting an error saying it can’t find the Timestamp value in the row. But if you look in the error message, the Timestamp is exactly 7 hours difference. My current speculation is it may have something to do with a Timezone setting somewhere, I’ve checked Timezone settings in Zapier and gSheets and both are set to EST, which is correct. So i’m not sure what’s going on but I’m still working on it. I appreciate your help.

hugheshilton

Hmmm, I’m guessing this may be a UTC thing. As I send this reply right now, it’s now 3 AM UTC, so it seems entirely possible that at the point you took that screen shot earlier that it was 11:53 PM UTC. So maybe you need to add/subtract an offset to put your timestamp into UTC time to make this work.

Phil

If you look in Audit History for the REST API request, it will show you the data values that are arriving at the server including the key values. See https://help.appsheet.com/en/articles/2247996-troubleshooting-api-calls

Brady_Lovell

Thanks for that info, I checked the history and it looks like the REST API was successful:

However, the AppSheet action did not fire. I am assuming that means the error is on Zapier’s side, which again probably has something to do with the Timezone difference. I tried using Zapier’s Formatter and adjusting the [Timestamp] column +5 hours. When I went back and ran the test again, it adjusted the value of the Timestamp like it should, but then the red error box’s time adjusted with it, and ending up with no results. So i’m currently trying to wrap my head around what all that means, and what my next step for troubleshooting is.

Brady_Lovell

One thing I just noticed, is that the time values in the Audit History are exactly 5 hours greater than my current time. So maybe it’s something on AppSheet’s side?

Brady_Lovell

Here is the REST API Invoke error. The error is 5 hours ahead of the Timestamp value in the table. I have no idea where or what is making it look for a Timestamp 5 hours from the time it reads in the gSheet.

**This is the Timestamp in my gSheet:( 3/1/2020 20:36:03 )

**Why is it looking for: ( Row having key ‘3/2/2020 1:36:03 AM’ not found )

Error:
“Row having key ‘3/2/2020 1:36:03 AM’ not found.”
Properties:
{
“RestAPIVersion”: 2,
“TableName”: “Carrier Invite”,
“AppTemplateVersion”: “1.000702”,
“Action”: “action_contract_uploaded”,
“RestActionType”: “Action”,
“DataAction”: “Data: set the values of some columns”,
“locale”: “en-US”,
“runAsUserEmail”: “”,
“selector”: “”,
“timezone”: “US Eastern Standard Time”,
“tzOffset”: “300”,
“userId”: 415658,
“Rows”: 1,
“RowSize”: 19,
“AppTemplateName”: “4d382c60-ef66-4f07-b5df-232dd37ed233”,
“Operation”: “REST API invoke”,
“RecordType”: “Stop”,
“ResultSuccess”: false,
“StatusCode”: “NotFound”,
“Performance”: “{“Version”:1,“Time”:“00:00:00.9207279”,“PerformanceTimingRoot”:{“Mid”:317,“Timer”:{“Time”:“00:00:00.9207279”},“Children”:[{“Mid”:62,“Timer”:{“Time”:“00:00:00.1053537”}},{“Mid”:318,“Timer”:{“Time”:“00:00:00.8119325”},“Children”:[{“Mid”:313,“Timer”:{“Time”:“00:00:00.8119109”},“Children”:[{“Mid”:336,“Timer”:{“Time”:“00:00:00.8117611”},“Children”:[{“Mid”:66,“Timer”:{“Time”:“00:00:00.8117424”},“Children”:[{“Mid”:32,“Params”:{“ParamList”:[{“Pid”:13,“Value”:“Carrier Invite”}]},“Timer”:{“Time”:“00:00:00.8116997”},“Children”:[{“Mid”:28,“Timer”:{“Time”:“00:00:00.0088710”},“Children”:},{“Mid”:33,“Params”:{“ParamList”:[{“Pid”:9,“Value”:“0”}]},“Timer”:{“Time”:“00:00:00.8025593”},“Children”:[{“Mid”:133,“Timer”:{“Time”:“00:00:00.8024830”},“Children”:[{“Mid”:95,“Timer”:{“Time”:“00:00:00.3685893”},“Children”:[{“Mid”:94,“Timer”:{“Time”:“00:00:00.3685234”}}]},{“Mid”:238,“Timer”:{“Time”:“00:00:00.4335440”},“Children”:[{“Mid”:240,“Timer”:{“Time”:“00:00:00.4270733”}},{“Mid”:239,“Params”:{“ParamList”:[{“Pid”:9,“Value”:“776”},{“Pid”:3,“Value”:“25”}]},“Timer”:{“Time”:“00:00:00.0057065”}}]}]}]}]}]}]}]}]}]},“IsEmpty”:false}”,
“Result”: “Failure”
}

Brady_Lovell

I finally got it working after changing the Key column. I was really trying to avoid having to do this, and would still prefer the original Key, but it ended up solving the issue. I am not sure what was causing the Timestamp to want to read +5 hours from what the data was reading in the gSheet, but this one will remain unsolved for anyone wanting to use a [Timestamp] column as their key, and they run into this same issue.

Fabian_Weller

You should in general avoid using a TimeStamp column as your key when working with API. Because technically an API could generate 2 new rows at the same time. So you would have 2 rows with the same key.