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
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!
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