Hello! Long time zaper first time poster. I’m looking to do some bookkeeping outside of Xero and Quickbooks courtesy of Google Form + Google Sheets. I’d like to submit an income or expense item on the form, have it look up the corresponding sheet for the year of the transaction, then update the cell looking by Column or Row. The format I have is categories on the left in column A and then months in columns b through etc. How would you go about doing this? I’ve made multiple attempts but it just doesn’t seem to work correctly.
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.
+14- Zapier Expert
Hi
Help us have more info by posting these screenshots:
- how your Zap steps are outlined and configured in EDIT mode with the field mappings visible
- how your GSheet worksheet top 3 rows are configured
- Beginner
Hey Troy! I met Diomer at the Nashville meeting last Thursday and he specifically named you as a great resource. What a great feeling that you are looking at this!
I don’t have the zap laid out yet and I deleted my previous attempts from last year to clean up my folders, etc.
The way I was thinking but didn’t work in the past was….
- Trigger - Google Form Submission
- Google Sheets Lookup
- Pull # in the cell currently
- Run formatter with math function
- Update the cell with the results of the math function
- Add the submission to a list of transaction for that month probably on the separate sheet for the month and then make it a link to the uploaded receipt
Here is how I currently have the sheet laid out….
I’m open to any suggestions on changing the layout to make it work with a zap.
I’ve thought about doing an individual tab for each month and then have them mirrored onto the summary/YTD tab. Then I’m thinking I could have it look up the month specific sheet, then the lookup would be easier within the sheet because it would only be two columns. Then run the formatter math function.
Does this make sense?
Mark
+14- Zapier Expert
I’m guessing you will want the solution to work going forward across years. (e.g. Jan 2024, Jan 2025, etc.)
The approach I’d take would be to use Airtable instead of GSheets.
Airtable allows for creating a relational database, meaning records (aka rows) can be linked across tables (aka worksheets).
Concept would be simply to log the transaction, then via the linked records logic, other tables would auto summarize the data based on the Year/Month for the Income/Expense.
It’s really about structuring your data differently to still achieve the desired results for the data
If you are interested in that approach, I can be hired to built it for you, just send me a direct message here: https://community.zapier.com/members/troy-tessalone-2824
- Beginner
Thanks for that input. Yes, it would be something to go for multiple years and be able to provide up-to-date info shared to our partners and cpa/bookkeepers. Do you think Zapier Tables could achieve the same thing? I’m looking to keep the number of logins down as best as I can. I had too many logins and subscriptions for too many years and got a bit overloaded.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.