Best answer

Alternative to using Importrange on Google Sheet?

  • 13 January 2022
  • 2 replies
  • 1422 views

Userlevel 1
Badge

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.

 

 

icon

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: https://zapier.com/apps/airtable/integrations

Airtable has Views.

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

Userlevel 1
Badge

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!