How plausible is it to use scheduled reports from appfolio, zapier to upload the email attachement to google sheets daily (replacing the ones that are there each day), and then subsequently using that data to populate checklists through process street? (i’ve got a base line of this set up but am curious if anyone else has tried such or has thought of a variant of such situation for utilizing AppFolio data since that is our data hub). -this might be better as just a community post but since i’m here…lol
Thanks Kevin and welcome to the channel!
Hey there Kevin!
I was typing out a step by step guide on how to do this, but decided that it would be better to create it and then film a relatively short video explaining how to get this done. Please enjoy and comment below if this was helpful to you.
Anyone else reading this, the same applies. Please comment below with any questions or suggestions, or let me know if this was or wasn’t helpful to you as well!
So, i’ve definitely gotten that far with it, my biggest struggle that I had was that the daily report and setting up that first zap with the utilities creating rows was that the attachment was larger than the utilities tool was able to convert.
So instead, I set it up so that
Step 1: the appropriate gmail attachment is found and the csv data pulled
Step 2: that csv file is then uploaded to Google Drive and replaces a file with the same name in the same location within google drive (i’ve created a separate file for these uploaded csv files.
Then, the second zap is set up so that
Step 1: when a checklist is created look up from a specific template
Step 2: then zapier looks up sheets from the multiple daily reports (that are replacing themselves daily with a file of the exact same name and type in google drive)
Step 3: The Checklist is updated with the specific row that has the “Property Number” line item that matches the one form field that would be updated in the checklist itself by the employee
I haven’t fully tested it out yet but that’s the baseline i have at the moment. the other struggle that i have with it is that the name column for tenant/owners that is exported through csv ends up in a not-so-user friendly format and i’m not sure how to change that one up. Also trying to figure out how to utilize the “find many spreadsheet rows” aspect but not have the same information be input twice when it’s populating the process street checklist.
P.S. I’m stupid excited about this little community hub.
Ah that is another way to do it, basically working backwards.
One challenge of you approach is that a checklist needs to be run to trigger the second Zap. I assume you have this on a schedule or some other automation to ensure that the naming fits the correct format, otherwise this won’t work.
As for formatting the tenant/owner data, you can do this using formatter by Zapier to potentially modify the data to be more user friendly - if something inside of the data is user friendly that is (lastname-firstname-id -> First Name Last Name (ID) etc.).
I’d probably avoid the ‘Find many spreadsheet rows’ since the trigger is a single object, so you wouldn’t want to return multiple pieces of information (likely). Hope this helps!
Also, super glad that you’re excited, we’re real excited too!
OH one more thing. Another way to run my approach is to use Mailparser.io instead of Zapier’s utility. It also has a limit of 1000 rows (or so), but you can actually request that they increase the limit (how far I’m not sure).
Check the comments of this thread: https://mailparser.io/blog/read-parse-process-email-file-attachments/
I made some scheduled reports in AppFolio for each of the following reports: 1) Owner data, 2) Property data, 3) Unit data, 4) Tenant data, etc. (similar to Tables in a database). Each customized report was emailed to me daily and picked up by Zapier, containing the data needed to build a makeshift database using CSV files. Zapier can catch these emails and convert the attached CSV files into Google Sheets to store the data. Zapier can then extract the data from Google Sheets when needed and pass it to Process Street. Up to this point, everything works great. The problem occurs when you overwrite these Google Sheets with new CSV files (emailed the next day), then the existing zaps won’t work anymore with the new Google Sheets because the sheet ID or something changes (even though the sheet name is the same). Zapier is unable to grab the data without updating the zap to point to the new sheets.
Hey Walt, thanks for your share.
It sounds like you might be creating a new spreadsheet each time, in which case (even if the name is the same) the sheet ID will be different, so you’re right on that point. And this will be why you’re needing to change the zap each time.
In your zap, are you using the step “create spreadsheet rows”? See the example Blake uses in his video https://www.loom.com/share/d90a8cb337b9483cbdd3a66186bd8efb Skip to 4:55 to see the set up in his zap.
The trick is to find the existing sheet and add more rows to that one, so this step should help, instead of using “create worksheet”.
Let me know if that does the trick for you.
Hi Ellie, thanks for the info. The problem is that the CSV files from AppFolio can have hundreds of records and we don’t know what records were changed. I was hoping to replace all the records by finding the existing sheet, deleting all the rows, and replacing them with the CSV file on a daily basis, but I don’t think it’s possible to delete Google sheet rows using Zapier. I think you can only blank them out, which would eventually create too many blank records.
Yeah, I agree with Walt, the biggest struggle is how large the data output is (in particularly for tenants). The idea of creating a new spreadsheet each day to pull from was to make it so that we were always working with the latest data each day.
This is also the reason why I try to pull multiple rows from some of the spreadsheets because some properties have multiple tenants that are fiscally responsible and need to be accounted for. But this has been a bit of a struggle to fine tune in a way that and to not populate/update the checklist with multiple of the same information (i.e. 2 tenants information is pulled from 2 rows, they have different names, emails, and phones numbers, but their deposit amount is the same, their address is usually the same, etc).
However, thinking about it. If we were to add rows to the spreadsheet each day and make it so it’s searching from the bottom of the spreadsheet and not the top it would only find the 1 row that has the most updated information towards to bottom since it’s continuously just adding more and more rows correct?
Yes that’s right you can blank out information but not delete rows in Zapier. I just took a look at the row limits in G sheets, and even though there’s no limit to the number of rows you can have, there is a limit on cells you can have (5 million).
I might ask Blake to pop in on this thread, he’s out for a couple of days, so I’ll check with him to see if there’s another solution for you here.
Hi Kevin, yes the new rows will be added to the bottom, so that might be an option, but you’ll need to consider the max number of cells you can have in any one sheet.
Even though there’s no limit to the number of rows you can have, there is a limit on cells you can have (5 million).
So, I’m trying to make this a solid full cycle automation right, and the amount of data required will have to include more than 1 zap set up but i’m running into a snag with the converting to csv file…
The report that i am emailing as the attachment that Zapier is pulling from initially only outputs 50 or so line items into the spreadsheet when creating rows, where as the amount needed is 315…I’m wondering if this is because of the Cell number limit that is there (i haven’t counted the cell amount yet). I’m not sure where the rest of the data is going or if Zapier just isn’t able to create that many rows/cells at once. Regardless though, i’m not sure how to work with an incomplete pool of information :[
The idea that I did have though was
- to create a Zap for creating spreadsheet rows within the same spreadsheet since just replacing the old spreadsheet doesn’t work for Zapier, which would happen in the morning every morning.
- To create a zap that would happen every night (there would have to be multiple since we can only have 100 actions to a zap and only 1 row can be deleted per action) that work delete all the lines within that checklist.
- That way, the checklist we create every day has a fresh start for all the most recent data to be uploaded into the spreadsheet by creating new rows. But this is where the problem happens with the lack of all the information being transferred into the spreadsheet by creating rows. any suggestions?
Thank you so much Walt!!! It does seem like we are trying to do the exact same thing. I’m gonna be reading those threads as soon as i can (the struggles of being a field agent and the tech guy is lack of time behind the computer!)
thank you very much for the info!! This community thread is already showing results and i’m loving it!! :]
I thought I would share this one on here - I wasn’t able to fully figure out how to automate the AF data sync but i got close. I used Process street to create a checklist for updating the data daily but the checklist itself is auto triggered every morning when the email report comes in from AF. Here’s the template I put together on this front.
Awesome! Sometimes the best thing to start with is a hybrid automation, that makes the manual aspects of the flow easier, and reduces the amount of overall manual steps.
Hi Kevin, thanks for sharing this (I just noticed it). Very nice instructions too!