Best answer

How to send data from Square to GSheets without affecting pre-existing formulas on spreadsheet

  • 18 July 2022
  • 1 reply
  • 281 views

Userlevel 1

How do I send transaction information from Square to GSheets via Zapier without affecting the formulas I have preset in certain columns of my spreadsheet?

The zap is basically just taking customer name, purchase quantity, total amount paid, and the date (with a reformatter) and sending it to a new row in a tab of my spreadsheet. 

Then in that same row, I have a few columns (where the zap isn’t sending any information to) that already have preset formulas to do things like calculate my net pay per transaction, adding customer information from a different tab to the row, etc. 

I don’t want Zapier to do anything to these rows, just let the formulas do their work after it submits the information I’m asking it to from Square. But for some reason when Zap sends the information to the sheet, it’s removing the formulas from these columns, causing them to remain blank. 

So the automation is working perfectly fine, besides removing the formulas from my spreadsheet that I already have there. Is there a way to send this data without affecting the formulas? 

Here’s a breakdown of my Zap steps:

  1. Trigger: New order in Square (when order status is completed)
  2. Format date/time to correct format
  3. Create spreadsheet row to Google Sheets (adding data to the following columns: client name, payment date, gross payment, payment type, and total sessions purchased)


I have formulas preset in the following columns in GSheets that I don’t want Zapier to do anything to, just leave the formulas there so they can autofill once the initial data is added by Zapier: 

Net Payment: 

 

Package Sessions:

 

Addon Price:

 

And Current Pay System:

 

Does anybody know how I can set up this Zap to send the data I’m asking to without affecting the formulas I already have set up for these columns? Again, the automation is sending the data I’m asking it to correctly… It’s just deleting the formulas in the process, causing the autofill not to function. 

I appreciate your help. Thank you so much! 

icon

Best answer by christina.d 19 July 2022, 00:07

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

1 reply

Userlevel 7
Badge +9

Hey there, @FFPT LLC - thanks so much for reaching out! Awesome question that unfortunately doesn’t have the best solution. 😔

Google spreadsheets can be a bit picky and need to be setup in a very specific way in order to work with Zapier. You can check out more about that formatting here:

That said, if there are formulas in the cells, Zapier will overwrite them. It’s a bit of a known issue and limitation with Google Sheets. In these instances we typically recommend Airtable as an alternative as it’s a bit more nimble and does allow for this type of workflow.

I know that switching platforms entirely is much easier said than done though. Another alternative could be to use a v-lookup table in a separate sheet with your formulas to pull data from the sheet that Zapier writes to.

I hope the context helps! Please keep us updated with any other q’s and welcome to the Community! 🙂