Looking for an alternative to Importrange when performing formulas on data imported to Zapier. Importrange is slowing down my sheet to the point of almost being useless. Here’s some context:
Using Zapier to create new spreadsheet rows. It creates rows in sheet 1.
On sheet 2 I want to perform various formulas using the imported data.
So on Sheet 2:
Cell A2 I have =if(sheet1!A2=”A”,1,0)
Cell A3 I have =if(sheet1!A3=”A”,1,0)
and so on down the page.
When Zapier creates a new sheet those cell references automatically move. So now when Zap creates new row 3 in sheet 1 that formula in sheet 2 A3 automatically switches to referencing A4. (Or for some formulas it just breaks and gives you a #ref).
I could used fixed references ($) but that would prevent me from copying the formulas down the thousands of rows.
The only solution I can find to keep the references pointed to the right place and not move is to copy all of sheet 1 using importrange. The problem is it makes the sheet very slow. Any better ways of dealing with this.
Best answer by Troy TessaloneView original
You may want to look into using an operational database app such as Airtable: https://zapier.com/apps/airtable/integrations
Airtable has Views.
Airtable has these advanced Field types: Formulas, Links, Lookups, Rollups
Thanks Troy. Definitely something I should do but we have so much running through Google Sheets right now that that seems like a mammoth project. Appreciate the suggestion!