Question

How to keep formulas from being erased in my Google Sheet in cells where no new data is being added?

  • 23 April 2021
  • 2 replies
  • 204 views

Userlevel 1

Hi,

My zap transfers new data from my Wix site to a Google Sheets spreadsheet where a new row is created with data in columns, for example, A:D. So the zap triggers and A2:D2 now have new information.

I entered some formulas in my spreadsheet in columns, for example, E:F that depend on the data from A:D. So if a zap triggered and A2:D2 had new information, then the formulas in E2:F2 should change according to the new information. But my formulas in columns E:F are erased. The zap then triggers for A3:D3 and E3:F3 should reflect the new information but they’re also erased and this happens successively for each row.

I know that I can fix this my manually entering my formulas in my zaps but it will take a LONG TIME to complete this. Is there a shortcut towards achieving this? 


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

2 replies

Userlevel 7
Badge +14

Hi @irving.modabea 

Better approach might be to use formulas on another tab to pull thru the data from the master tab then apply the desired formulas.

Try using these formulas:

FILTER: https://support.google.com/docs/answer/3093197?hl=en

QUERY: https://support.google.com/docs/answer/3093343?hl=en

ARRAYFORMULA: https://support.google.com/docs/answer/3093275?hl=en

 

Also, make sure to check the available help docs, about how to structure a GSheet to work with a Zap as well as known limitations: https://zapier.com/apps/google-sheets/help

 

Another alternative would be to use Airtable instead of GSheets.

Airtable has a Formula field: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference

NOTE: Depends on the complexity of your formula.

Userlevel 7
Badge +10

@irving.modabea 
Just checking in to see if you still need help with this?