How to handle data manipulation when using importrange() in Google Sheets?

Question

I am working on speeding up a workbook in Google sheets that is using importrange(). The purpose of the entire workbook is to import data from a mastersheet and then allow us to manipulate it the way we want to outside of the mastersheet.

The problem: because importrange() doesn't allow you to directly manipulate cells we have Sheet1 acting as the import sheet; it doesn't get touched. Sheet2 is where we do the manipulating but, it was literally just taken as a copy of Sheet1, so it is also using importrange(). This bogs down the entire workbook and makes manipulations very slow.

I am thinking of using !Sheet1A1... and copying that to all the cells in the manipulation sheet, but my concern is that this will still bog down the workbook. There is potential that the import data could grow as large as 10k+ rows, and I'm only at about half that currently and running into this problem. Outside of that, I'm not sure what else there is to try.

Answer 1

The QUERY function can help here and there are some great resources online.

=importrange(spreadsheet_url, range_string)

a typical example is:

=importrange("https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx","Sheet1!A:Z")

You can wrap a QUERY function around this to manipulate your data.

QUERY is like a version of SQL and very powerful. It's in the format:

=QUERY({},"",1)

Your data range importrange("https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx","Sheet1!A:Z") would go within {}.

Then within the "" part of the query, you could write your parameters for manipulating the data.

Example:

select Col1,Col4,Col5 where Col1 is not null and Col6 contains 'hello' order by Col1,Col7 desc label Col1 'new name 1',Col4 'new name 4'

The select bit allows you to specify specific columns from your importrange. If you want the all, then you could use select *.

The where item is where you build up your criteria using various or or and parameters.

is not null is another way of saying you want rows that have data.

contains is useful. You can also have matches, starts with, ends with and like. like can use wildcards %, so where Col1 like '%the%' would find 'hello there'.

order by is ascending unless you add desc, ie. order by Col1,Col2,Col4,Col5 desc,Col3.

label allows you to rename the columns, so let's say input column 1 is called 'Name1' and input column 2 is 'Name2' and you want them to be 'First name' and 'Surname, you would use label Col1 'First name', Col2 'Surname'.

If you like QUERY there are other powerful clauses, and they run in this order within the QUERY(range,"clauses",0):

select

where

group by

pivot

order by

limit

offset

label

format

options

One small point which you may come across, when you use importrange to get your data you need to reference the columns as Col1,Col2,Col3 within the QUERY.

If, however, your range is already in the same sheet (same or different tab), then you would reference column letters instead, eg. select A,B,C where A is not null order by A desc.

To make it more consistent and use the Col1,Col2,Col3 notation, you would put your internal range in an array {}.

QUERY(Sheet1!B:F,"select B,C,D where F is not null order by B,C",0)

would become:

QUERY({Sheet1!B:F},"select Col1,Col2,Col3 where Col5 is not null order by Col1,Col2",0)

{Sheet1!B:F} is smart because you can add columns in front of this range without needing to change your clause. So adding one column in front of Sheet1, would result in:

QUERY({Sheet1!C:G},"select Col1,Col2,Col3 where Col5 is not null order by Col1,Col2",0)

The other method would need you to alter your clause from:

QUERY(Sheet1!B:F,"select B,C,D where F is not null order by B,C",0)

to:

QUERY(Sheet1!C:G,"select C,D,E where G is not null order by C,D",0)

It's a lot to take in, but definitely worth persuing!