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
187
votes
6answers
565k views

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

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?
21
votes
10answers
86k 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 ...
12
votes
2answers
54k 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 ...
12
votes
7answers
57k 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 ...
8
votes
6answers
15k 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 ...
7
votes
2answers
29k 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 ...
7
votes
1answer
638 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 "...
6
votes
5answers
24k 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 ...
6
votes
3answers
20k 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 ...
6
votes
2answers
18k 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 ...
6
votes
1answer
4k 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", ...
5
votes
3answers
8k 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 ...
5
votes
1answer
16k 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 ...
5
votes
1answer
7k 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 ...
5
votes
2answers
3k 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 ...
4
votes
5answers
38k 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 ...
4
votes
2answers
16k 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 Name!A2:P1000"), ...
4
votes
1answer
8k 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 ...
4
votes
2answers
3k 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 ...
4
votes
1answer
501 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 ...
4
votes
1answer
295 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 ...
4
votes
1answer
4k 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 ...
3
votes
1answer
12k 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"...
3
votes
1answer
5k 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 ...
3
votes
3answers
214 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() ...
3
votes
1answer
419 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 ...
3
votes
1answer
537 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?
3
votes
1answer
256 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: ...
3
votes
1answer
85 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 ...
3
votes
1answer
76 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 ...
3
votes
1answer
1k 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 ...
3
votes
1answer
848 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 ...
2
votes
2answers
39k 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 ...
2
votes
1answer
2k 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 ...
2
votes
2answers
5k 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("...
2
votes
1answer
212 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 ...
2
votes
1answer
920 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 ...
2
votes
1answer
2k 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 ...
2
votes
4answers
20k 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 ...
2
votes
1answer
4k views

Validation based on multiple sheets

I have two sheets that each contain a column of data that I would like combined and used as a validation list for a column in another sheet. What would the formula look like for this?
2
votes
1answer
43 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: =...
2
votes
1answer
1k 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-...
2
votes
1answer
4k views

FILTER has mismatched range size; referencing the second last cell in a column in range

I am using a multicriteria filter to check if my sheet contains a certain value combination. The formula should automatically include any new rows added to the sheet. However, if I do it this way: =...
2
votes
1answer
658 views

Copying values between Google Sheets

I have 2 Google Sheets. I have data on Google Sheet A and I would like it to automatically transfer some values to Google Sheet b. For example, I have some email addresses and some data associated ...
2
votes
1answer
248 views

How to use a function in a Google sheet parameter

I am using function IMPORTRANGE as follows: =importRange("https://docs.google.com/spreadsheets/d/SPREADSHEETKEY/edit", "2017!D4") which works great. Now I need to substitute the D4 in my parameter ...
2
votes
1answer
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: ...
2
votes
1answer
11k 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 ...
2
votes
2answers
568 views

Can I call IMPORTRANGE function inside a REGEXEXTRACT function?

I want to obtain root domains from a list of URLs with REGEXEXTRACT but want the list of all possible TLDs to come from another sheet, to avoid specifying com|net|org|all other by hand. Basic idea: ...
2
votes
1answer
2k views

Is there a way to import numbers from another sheet every 'x' hours?

I want to link two spreadsheets with IMPORTRANGE, let's say A with B. B is the source. I want A to import numbers from B every 6 hours, because the numbers in B are constantly moving and since the ...
2
votes
1answer
3k views

Is there any reliable alternative to IMPORTRANGE()?

After some time the formula breaks, and shows Error: Loading data..., and it won't work until I change anything in the parameters, e.g. Sheet1!a3:Ag to Sheet1!a3:AG, then it works for a few days, and ...