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)
...
- 562
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");...
- 101
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 ...
- 1,199
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")
- 51
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 ...
- 51
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 ...
- 41
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,671
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)
- 201
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 "...
- 131
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.
...
- 46
Only top scored, non community-wiki answers of a minimum length are eligible