30 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) ...
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 ...
  • 335
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 ...
  • 43.1k
10 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");...
7 votes

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

The following comes from Jimmy in this Web Apps answer. In both spreadsheets insert an =now() equation in a random cell, say Z1 In both spreadsheets insert an =importrange() function that references ...
  • 71
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 ...
  • 43.1k
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 ...
7 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))
  • 1,579
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 ...
  • 161
5 votes
Accepted

Query(IMPORTRANGE) function to skip columns

No, it's not possible to "skip columns". Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following. First formula (A1): =...
  • 43.1k
5 votes

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

I found the easiest way was to put a simple if statement around the importrange. B1 = Sheet ID B2 = Sheet Name and Range B3 = Now() =if (B3> now()-1, IMPORTRANGE(B1,B2),) This works every time.
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")
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 ...
  • 43.1k
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 ...
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 ...
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&...
4 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)
4 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 ...
  • 43.1k
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)))
  • 3,222
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 ...
  • 12.1k
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 "...
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 ...
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. ...

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