Best answer

Alternative to using Importrange on Google Sheet?

  • 13 January 2022
  • 2 replies

Userlevel 1

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 Tessalone 13 January 2022, 16:52

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.

2 replies

Userlevel 7
Badge +14

Hi @Shawn_L 

You may want to look into using an operational database app such as Airtable:

Airtable has Views.

Airtable has these advanced Field types: Formulas, Links, Lookups, Rollups

Userlevel 1

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!