Questions tagged [importrange]

This tag should be used to differentiate questions about the importation of a range of data from other questions on this site. Use when the topic is the IMPORTRANGE function. Should generally be used in conjunction with a tag for a specific app.

Filter by
Sorted by
Tagged with
203 votes
8 answers
662k views

How do I link a cell in Google Spreadsheets to a cell in another document? [closed]

I have a monthly spreadsheet that relies on figures from the previous month. I'd like to import these values dynamically rather than cutting and pasting them. Is this possible? How do I do it?
Jeff Yates's user avatar
  • 2,565
28 votes
13 answers
142k views

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

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that ...
tlewis3348's user avatar
23 votes
4 answers
80k views

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

I don't look for ImportRange because I want to stay within the same Google Sheets spreadsheet. Instead, I want to import a column from one sheet into a column of another sheet within the same ...
orschiro's user avatar
  • 5,488
19 votes
7 answers
119k views

IMPORTRANGE with preserved formatting

I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells. It seems as though this only works within a sheet: Cell reference with colour ...
Lucas Crossley's user avatar
16 votes
2 answers
97k views

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

I want to bring in data from a separate sheet and filter it. I can get IMPORTRANGE to work like this: =IMPORTRANGE("URL","A:J) But that doesn't filter. I can only get FILTER to work in the same ...
Malcolm Lesley's user avatar
11 votes
4 answers
65k views

How can I merge two columns by using QUERY?

I have a master spreadsheet that contains columns like so: FirstName LastName Gender etc, etc, etc I need to import from that master sheet into another sheet that filters by one of the other ...
Jonathanb's user avatar
  • 213
10 votes
6 answers
30k views

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

The imported data is not always up to date with the source pages when I load the spreadsheet. When this happens, F5 refresh does not help. Nor does Ctl-F5. Apparently there is some kind of caching ...
Chris Noe's user avatar
  • 219
8 votes
2 answers
29k views

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

I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C". List "A" has multiple records for each student ID number. List "B"...
Mr. B's user avatar
  • 2,867
7 votes
6 answers
139k views

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

I'm trying to IMPORTRANGE from a range containing 240,000 cells (40 columns and 6,000 rows). The IMPORTRANGE function is erroring out with "Results too large". I can't seem to find documentation on ...
Douglas Gaskell's user avatar
7 votes
5 answers
41k views

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

IMPORTRANGE is not updating on one of my sheets. I've been using it on hundreds of other sheets with no update issues but it is no longer functioning for me. The sheet is set to update every minute I ...
Douglas Gaskell's user avatar
7 votes
3 answers
40k views

Unable to parse query string for function Query, when using IMPORTRANGE

This is the problem I'm seeing: Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND The formula is of the form: =query(importange("...", "Sheet!A:V"), "select D where ...
jas9531's user avatar
  • 107
7 votes
1 answer
7k views

How do I concatenate data from multiple columns on one sheet into a single column on another sheet?

I need to take separate columns containing first and last names on one spreadsheet, and combine them into a single column on a different sheet. Right now my formula is =CONCATENATE(IMPORTRANGE("URL", ...
Jon's user avatar
  • 207
7 votes
1 answer
32k views

Automatically "Allow Access" for IMPORTRANGEs in Google Sheets?

I have a set of Google Spreadsheets I want to reproduce (not share) for several users. These users are not going to be able to figure out how to Allow Access (for example, some of the IMPORTRANGEs are ...
William's user avatar
  • 93
7 votes
1 answer
867 views

Google Spreadsheets showing "shared with xxx" count greater than actual?

For every one of my Google Spreadsheets, the sharing settings is on "Specific people can access" and each sheet is shared with three people I know. Their names are listed when I click on the blue "...
jason's user avatar
  • 1,034
6 votes
3 answers
40k views

Query(IMPORTRANGE) function to skip columns

I'm currently using the following function to import data from one spreadsheet to another based on a value in Column K of the source spreadsheet: =QUERY( IMPORTRANGE("URL","Sheet ...
SJKM's user avatar
  • 63
6 votes
4 answers
49k views

How to SUM() a column from one Google Spreadsheets in another spreadsheet

Has anyone been able to return the SUM() result of a column in one Google Spreadsheets to a different spreadsheet? I am trying the ImportRange(key, criteria) but I am not having any success. I am ...
H. Ferrence's user avatar
6 votes
1 answer
691 views

Calling IMPORTRANGE from within another function

I have a bunch of separate spreadsheets and I'm creating an overview spreadsheet which pulls data from each of the others. I also need to apply the COUNTUNIQUE formula to that data. If I simply type ...
Nick5a1's user avatar
  • 342
5 votes
4 answers
20k views

How to use INDIRECT inside an IMPORTRANGE function?

I need help to dynamically fetch a value from another Google spreadsheet like this: FILE1 has 0 or 1 values in column G. Now I want to fetch the G column value from FILE1 with a row number specified ...
user1151640's user avatar
5 votes
1 answer
16k views

Allow data from IMPORTRANGE to be overwritten

We have a specific set of data that is being imported from another spreadsheet with: =IMPORTRANGE("spreadsheetkey"; "sheetname!A8:F133") Now we want to change that data in one of the cells within ...
Ioannis's user avatar
  • 51
5 votes
2 answers
5k views

How to nest IMPORTRANGE into the Query function in New Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
JWCole's user avatar
  • 71
4 votes
2 answers
6k views

Google Sheets Query(Importrange) with named range?

I am looking for a way to query an imported range using named range. Something similar to this, however I cannot makeit to work Sheet ID#A has a column J whose named range is "Signed" that may ...
Riccardo's user avatar
  • 579
4 votes
1 answer
16k views

Google Sheets Query group by multiple

My sheet is here, and we are looking on the sheet called "Paste". I would like a query that finds and sums the total dollar amounts when the same customer receives the same service code more than ...
Neill Dorn's user avatar
4 votes
1 answer
2k views

Align the rows of one sheet with the rows of an imported sheet

I have a spreadsheet of employee names. I am using =IMPORTRANGE() to import the names into another spreadsheet that tracks expenses in rows next each of the imported employee names. The problem is ...
GabeBrady's user avatar
  • 141
4 votes
1 answer
410 views

Using QUERY command with dates

I have a column in Sheet1 that contains dates in format DD/MM/YYYY, like this: 12/10/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 26/10/2015 26/10/2015 26/10/2015 26/10/2015 ...
MultiformeIngegno's user avatar
4 votes
1 answer
7k views

Delay in updating referenced cells when using IMPORTRANGE

I'm using the IMPORTRANGE function in Google Sheets to reference data from spreadsheet1 and display it in spreadsheet2. This works fine, but if I edit spreadsheet1 and then go to spreadsheet2 it's not ...
sam's user avatar
  • 6,849
3 votes
1 answer
2k views

Dynamically reference data from multiple tabs and sort it

I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity. I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
sam's user avatar
  • 6,849
3 votes
1 answer
9k views

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

I'm trying to create a user friendly sheet where data is pulled from a different Google Sheets based on input from the user. So far I have this working: =IMPORTRANGE(C40,"2016 Data!W2:W13") Where ...
TrentM's user avatar
  • 33
3 votes
3 answers
257 views

How do I get data from one Google Spreadsheets "A" into Spreadsheets "B"?

How do I get data from one Google spreadsheet A into spreadsheet B so when Spreadsheet A is modified, then Spreadsheet B also changes automatically? A formula using QUERY() and IMPORTRANGE() ...
Kaippally's user avatar
3 votes
1 answer
4k views

IMPORTRANGE + QUERY won't import IMAGE

I use Sheets in my business to create product catalogs for wholesale prices. I have different sheets for different companies but the data in many of them is the same. I'd like these sheets to sync ...
user87855's user avatar
3 votes
2 answers
11k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: =query(ImportRange("...
user54539's user avatar
3 votes
1 answer
3k views

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

I have two workbooks with several sheets in each. I have a dropdown to select the sheet name that I'm using for reference for URLs and to reference other sheets in the main book. I successfully used ...
Kevin Vandenboss's user avatar
3 votes
1 answer
895 views

Import only those cells from a spreadsheet that have certain conditional formatting

I have created a Google Form which links to a spreadsheet. There is an option which asks for the person's name. For the answers in the sheet, I've created conditional formatting to highlight the ...
jas9531's user avatar
  • 107
3 votes
2 answers
145 views

Combine different sheets with importrange for dynamic data from google forms

I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to ...
Merk's user avatar
  • 39
3 votes
1 answer
621 views

Reverse lookup from Google Spreadsheets key

I can't figure out where the data in an IMPORTRANGE function is coming from. How can I use the spreadsheet key to find the the original spreadsheet IMPORTRANGE is pulling from?
Carl's user avatar
  • 133
3 votes
1 answer
330 views

IMPORTRANGE as dependent function

I am trying to write a script which sets the function IMPORTRANGE in a specific cell, using as KEY the content of another cell. I have the list of the KEYs in Sheet2 and here is what I have so far: ...
Francesca's user avatar
3 votes
1 answer
19k views

Only display IMPORTRANGE rows that match specific criteria

In Google Sheets I'm using IMPORTRANGE to pull in a large list of data (about 400 rows, 6 columns) and I want to only display rows where column D matches EXAMPLE TEXT. For example: if column D of the ...
UtahJarhead's user avatar
3 votes
1 answer
661 views

Importing a row from one Google Sheet to another

Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week. Doc2: I have an individual sheet setup for each team. I'm looking to pull ...
Necrovana's user avatar
3 votes
2 answers
8k views

Query(Importrange) or Query with dates

I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success. =query(IMPORTRANGE("1bSyor-...
FVA's user avatar
  • 33
3 votes
1 answer
90 views

What spreadsheets import from my spreadsheet?

Is it possible to get a list of the spreadsheets that are importing data from my sheet? I maintain a spreadsheet that has raw data, and many other spreadsheets use IMPORTRANGE to import that data. I ...
Michael Leahy's user avatar
3 votes
1 answer
2k views

Is there anyway to call an IMPORTRANGE() and have it include all sheets?

Here is my current function that combines the current data with the first archive. I'd like to set it up as a failsafe for the future so when there's a new archive it will be included in the import: ...
Branden S. Smith's user avatar
3 votes
1 answer
1k views

Locking cell positions to another cell if one column is using IMPORTRANGE

Let's say I have a master list of all kinds of Legos and their cost. Only two columns. Column A is the Lego type and column B is the cost for that Lego. Example: 3x3 thin block and 1. or small wheel ...
allindal's user avatar
  • 223
2 votes
2 answers
328 views

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

I'm creating a sheet that will import and process data from other Sheets. On a daily basis, the user will be given a new document to process with it. I have cell B1 reserved for them to enter the URL ...
Atiq Zabinski's user avatar
2 votes
2 answers
46k views

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

Having trouble with a =QUERY(IMPORTRANGE(... formula in grabbing a value from a single cell in another sheet, but only in the row where a particular text value exists in another column in that row. I ...
Mark Brockett's user avatar
2 votes
1 answer
331 views

Does importrange not work on Google Sheets embedded XLSX files?

The ImportRange documentation mentions nothing about not supporting XLSX embedded files. But when I try it, it claims it has no permission (whereas I'm an editor in the file I try to import). Does it ...
LWC's user avatar
  • 1,268
2 votes
2 answers
9k views

Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text

I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical. Formula in ...
que syrah sarah's user avatar
2 votes
1 answer
2k views

How to reference a local cell in QUERY?

I just about have formula 100% perfect. Just one issue: Formula (Works): =QUERY( ImportRange( "https://docs.google.com/spreadsheets/d/1Lf4r_b0fmFh6fClZsGmfZmo8ZD87qf8N_0f-Jaoyu-g/edit#gid=0", "...
Brian4591's user avatar
2 votes
1 answer
124 views

How to aggregate data from multiple Google sheets with a tag

I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet. Then I am aggregating those three sheets into one and also filter them using a query. Ex: =...
direndd's user avatar
  • 123
2 votes
1 answer
2k views

Inserting text from a cell into an IMPORTRANGE formula

I have a spreadsheet (Spreadsheet B) with a number of formulas which require data from another spreadsheet (Spreadsheet A). Many of the formulas in Spreadsheet B reference different sheets (on ...
Bryan's user avatar
  • 21
2 votes
2 answers
7k views

Copying IMPORTRANGE formulas so that they stay relative

How do I copy a formula in Google Sheets that has the address of another sheet in the formula? Now I have to edit the formula for every cell because when I copy it, it does not stay relative. And ...
user110382's user avatar
2 votes
1 answer
3k views

CONCATENATE inside IMPORTRANGE inside Query

I found this formula: =QUERY(IMPORTRANGE("key","A:Z"),"SELECT Col1, Col2, Col3 WHERE Col1 contains 'value' ORDER BY Col1") here and I'd like to add a concatenate function where value is taken from ...
Tim Niemeyer's user avatar

1
2 3 4 5