Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

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.

Hi ​@Tandem Realty 

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

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

 

  1. Trigger - Google Form Submission
  2. Google Sheets Lookup
  3. Pull # in the cell currently
  4. Run formatter with math function
  5. Update the cell with the results of the math function
  6. 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

​@Tandem Realty 

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

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.

Reply


Terms & Conditions