Skip to main content
Share Your Experience: Take the 2024 Developer Survey
33 votes
Accepted

How can I import a column from one sheet into another sheet of the same Google Sheets spreadsheet?

One way to do this is with ArrayFormula. If the sheet you want to import from is named Sheet1 and you want to select the entirety of column A, you can do the following: =ArrayFormula('Sheet1'!A:A) ...
Robert Hickman's user avatar
16 votes

IMPORTRANGE with preserved formatting

May be an old question but its still being looked up - so here is the workaround: Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid ...
cryptonic's user avatar
  • 361
15 votes
Accepted

How can I merge two columns by using QUERY?

Short answer QUERY select argument can't merge columns. Explanation The QUERY built-in function uses Google Visualization API Query Language. It doesn't include a concatenate operator. One alternative ...
Rubén - Volunteer Moderator -'s user avatar
12 votes

How to work around the IMPORTRANGE error: "Results too large"?

I was having a similar problem too. Try splitting up your import range with an array formula. Example: Importrange("sheet", "A:ZZ") becomes ARRAYFORMULA({Importrange("sheet", "A:ZZ5000");...
George Hyde's user avatar
9 votes
Accepted

Google Sheets Filter Multi Column List With Another List Using A Function

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))
ttarchala's user avatar
  • 1,679
7 votes

What are the refresh characteristics of the Google Spreadsheet Import functions?

#Short answer You are right, the import functions updates their result at certain intervals. #Explanation From Change a spreadsheet's locale, time zone, recalculation, and language, a help article ...
Rubén - Volunteer Moderator -'s user avatar
7 votes
Accepted

IMPORTRANGE() will not update unless deleted and re-pasted

This was an issue with the sharing settings on the sheets. It looks like a sheet that is set to Specific People in the sharing settings cannot be automatically imported into a second sheet. If you ...
Douglas Gaskell's user avatar
6 votes

How can I merge two columns by using QUERY?

In certain cases you can use Google array literal representation to achieve a join. =query({Sheet1!A:B, Sheet2!C:D}, "select Col1, Col3 where Col2 = Col4") This works only when the number of rows in ...
rehan's user avatar
  • 161
5 votes

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet

Yes, you can bring the two together like so: =filter(IMPORTRANGE("URL","A:J"),INDEX(IMPORTRANGE("URL","A:J"),0,3)="Yes")
gchristo234's user avatar
5 votes

Automatically "Allow Access" for IMPORTRANGEs in Google Sheets?

Short answer Is there a script, or some other solution, that will auto-Allow Access in a spreadsheet? The only solution is that the owner or any editor do the mandatory authorization for each of the ...
Rubén - Volunteer Moderator -'s user avatar
5 votes

IMPORTRANGE() will not update unless deleted and re-pasted

It's not about the way you shared; it's about the locale that you use for each spreadsheet. Go to File -> Spreadsheet Setting -> and make sure you have same Locale and Time zone for both Google ...
Reza K Ghazi's user avatar
5 votes

How can I import a column from one sheet into another sheet of the same Google Sheets spreadsheet?

Also possible: =QUERY(Sheet1!A:A)
David Vielhuber's user avatar
5 votes

Help with a "=QUERY(IMPORTRANGE(..." formula in Google Sheets

IMPORTRANGE requires authorization to access an external spreadsheet, but the authorization pop-up only is shown when IMPORTRANGE is not nested inside of other functions. Once you make the ...
Rubén - Volunteer Moderator -'s user avatar
4 votes

IMPORTRANGE() will not update unless deleted and re-pasted

I just ran into this same issue...we've never had an issue with importrange working across MANY sheets which are selectively shared, but today it randomly stopped working. I ran upon this article in ...
MattSheet's user avatar
4 votes
Accepted

How to use IMPORTRANGE with a variable range-string pulled from a cell?

If your referring to the cell where the regular text is you don't need to add the additional "" around the ="""2016 Data!"&B41&"2:"&B41&"13""" just simply enter: ="2016 Data!"&B41&...
Aurielle Perlmann's user avatar
4 votes
Accepted

Compare dates in query from IMPORTRANGE and cell

The date provided to QUERY must be: a string of format yyyy-mm-dd Try changing from =date '"&$AR3&"'"; 0))) to =date '"&TEXT($AR3;"yyyy-mm-dd")&"'"; 0)))
TheMaster's user avatar
  • 3,342
4 votes
Accepted

Dynamically reference data from multiple tabs and sort it

Use curly brackets within this very simple query: =QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2") The trick here is the curly brackets {....} we ...
marikamitsos's user avatar
  • 12.9k
3 votes
Accepted

How to work around the IMPORTRANGE error: "Results too large"?

My experiments on this would suggest that there is a max limit on the number of items that you can import using importrange() which id not linked to the total number of cells in your Google Sheet. ...
Sam McEwin's user avatar
3 votes

IMPORTRANGE with preserved formatting

Creating a template with formatting and then applying the import range seems to be working
Qriz Cruega's user avatar
3 votes

How can I get Google Sheets to auto-update a reference to another sheet?

As a workaround for the now() roadblock, you can change the settings of the spreadsheet to update every hour. File -> Spreadsheet Settings -> Calculation Update the sheet to recalculate itself on "...
Nikhil Sahu's user avatar
3 votes

How can I get Google Sheets to auto-update a reference to another sheet?

The datestamp workaround doesn't seem to work for me - there's an explicit note that using datestamps and rand functions aren't allowed. My very hacky workaround is to delete the cell and then hit ...
Tom McGuinness's user avatar
3 votes
Accepted

Google Sheets Query group by multiple

Replace and by ,. Final formula: =QUERY(ARRAYFORMULA(IMPORTRANGE( "https://docs.google.com/spreadsheets/d/1Wyewpz0j_IiKK8ESOapX5L4AedjvHccLZOOKS2tZGUA/", "UsuallyAnotherSpreadsheetFile!B2:K")),"...
Rubén - Volunteer Moderator -'s user avatar
3 votes

Google Sheets Filter Multi Column List With Another List Using A Function

@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this: =filter(C52:F57; ISNA (match(C52:C57; I53:I54; 0)))
Markus Frömmel's user avatar
3 votes

Google Sheets Query(Importrange) with named range?

IMPORTRANGE seems to work just fine for me with named ranges, for example. Note that you'll still need to grant access to pull the data from the source sheet even if it's in the same google sheet. For ...
Ralph Callaway's user avatar
3 votes

How do I reference a cell for the sheet name in IMPORTRANGE function in Google Sheets when the sheet is in another workbook?

You do not need the INDIRECT function in your formula. All you need to do is use =IMPORTRANGE("JUST_THE_ID_HERE", ""&D4&"!B2:M8") (where D4 holds AMT or is a cell ...
marikamitsos's user avatar
  • 12.9k
3 votes

Can a Script distinguish IMPORTRANGE N/As due to non-existent Tabs from N/As due to not having access permissions?

I followed the link Automatically "Allow Access" for IMPORTRANGEs in Google Sheets? StackExchange added to the discussion, and learned something new: You actually DON'T HAVE TO specify the ...
Atiq Zabinski's user avatar
3 votes
Accepted

Does importrange not work on Google Sheets embedded XLSX files?

The importrange() function can only read data from Google Sheets and therefore cannot read data from Microsoft Excel files, even though you can view and edit such files with Google Sheets. I do not ...
doubleunary's user avatar
  • 14.7k

Only top scored, non community-wiki answers of a minimum length are eligible