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.
234
questions
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 ...
2
votes
1
answer
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
1
answer
168
views
Looking up two criteria in a table, one vertical, one horizontal and finding a value
I have a large table of data.
Column A is names
Columns C-I are monthly commissions. (Column C is March 2023, Column D is April 2023, etc)
Columns C-I are going to change regularly as we are pulling ...
2
votes
1
answer
48
views
IMPORTRANGE and INDIRECT combination error
I am trying to dynamically import data from another Google sheet (sheet2), to current Google sheet (sheet 1).
In sheet 1, I would like to input the starting cell A2 and ending cell D20 in cells B2 and ...
2
votes
1
answer
453
views
importing from multiple workbooks for Google Sheets to ignore workbooks that have nothing listed, instead of returning an error
={FILTER(IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "Trade and Manufacture!P17:T"), IMPORTRANGE("116uNZi52pAQe1_D_ZLNuD18gSx3zfB1P08TL2cT7Vdk", "...
2
votes
1
answer
44
views
Import specific rows and formulas from master sheet
I have been looking for hours now how to solve my problem, as it follows :
I want to duplicate this master sheet a hundred times and be able to let the end-users fill in the green cells, without ...
2
votes
1
answer
3k
views
Create table showing difference between values in two pivot tables
Our bookstore has two separate documents in Google Sheets (one containing purchase data and one with sales data). Each document contains a sheet with a pivot table that sums up total purchases/sales ...
2
votes
1
answer
1k
views
Google Sheets Query Where Cell value
I'm looking for help editing a Google Sheets formula. I want the formula to Import Range from another worksheet where value equals a specified cell. I can only make the where in the formula equal a ...
2
votes
1
answer
7k
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
1
answer
868
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
1
answer
271
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
2
answers
1k
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
1
answer
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
1
answer
4k
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 ...
2
votes
1
answer
147
views
Using QUERY with IMPORTRANGE as the data source not allowing referencing columns
(Link to Test Spreadsheet)
I am working on a spreadsheet that has a list of people, and for each person, they have ten choices that correspond to adjacent columns with artist and song name. The data ...
2
votes
2
answers
495
views
I am having trouble using indirect to access a cell in a different Google Spreadsheet
I have 2 spreadsheets, Test1 and Test2. In Test2, Cell B2 I want to access a cell in test1. I want to use the value in cell A1 (the value is test1) indirectly to access cell A2 in test1. This is the ...
2
votes
1
answer
211
views
Google Sheets Import Range Query Error
I'm trying to create a sheet that only shows me LOT Codes of specific products from a different workbook that has all of our products and LOT Codes.
From what I've seen the Query with Import Range ...
2
votes
1
answer
2k
views
Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?
I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
2
votes
1
answer
266
views
How to insert cell text from one cell into an ImportRange Function
Hello! so I have an issue where I am trying to do is have multiple functions in C, D, and E where a link is required. Instead of copying and pasting the link inside all the functions in specific ...
2
votes
1
answer
1k
views
Reference entire row data or row-by-value from other sheets [closed]
I've a Google Spreadsheet where my first sheet is a Products table.
The columns are SKU, Title, Price, Category, Type
I've some tasks in front of me where I need to create some bundles of different ...
1
vote
2
answers
22k
views
Using combined IF THEN with IMPORTRANGE formula
I am looking to understand what the correct syntax would be for a formula for a Google Spreadsheets that would use the IF THEN function inside of an IMPORTRANGE formula.
I want to import all of the ...
1
vote
2
answers
2k
views
Restrict ImportRange to only visible rows [duplicate]
I need to restrict the ImportRange function below to show only visible rows as some of the rows are filtered and some of the rows are grouped.
=importrange("WorkbookID","Sheet1!A2:K13285")
1
vote
1
answer
61
views
Downloading CSV results in empty (#ref) file. Is it a problem with IMPORTRANGE? [duplicate]
I have a multi sheet workbook in Google Sheets. I need to export a compilation of tables from this workbook to CSV. I therefore have added a new sheet where I use IMPORTRANGE to combine values from ...
1
vote
2
answers
92
views
Query+Importrange for multiple sheets based on values in Col26
=QUERY({
importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001");
importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:...
1
vote
1
answer
1k
views
Google Sheets Query with importrange by date range
Trying this Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/18r5cARjmlRsbb0VRaAS6kAMkJm1p2qe0LsPCtYLS1xA/edit?resourcekey#gid=561906440","Form Responses 1!A:X"),...
1
vote
2
answers
4k
views
Row by row comparison with FILTER returning error
I am trying do a row by row comparison of two separate sheets, and display the unequal rows on a third sheet. As an example, rows three and five on Sheet1 are different from Sheet2 and I want to ...
1
vote
1
answer
6k
views
Query(IMPORTRANGE) throws "Formula Parse Error"
I'm working with Google Spreadsheets and I've run into a wall with a query(IMPORTRANGE) function:
=Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,...
1
vote
1
answer
97
views
Dynamic values in a formula?
I have a Google Sheets where I am using this query to pull data from another sheet:
=QUERY(IMPORTRANGE("**SHEETLOCATION**","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
2
answers
807
views
How to get value from cell with query and importrange functions in Google Sheets?
A1 cell has 1. And I try use value from this cell through:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing"; "Sheet1!A:P");
"...
1
vote
1
answer
47
views
How to to get coordinates from addresses coming in from importrange?
I have addresses coming in from importrange and I am using this script to get coordinates.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Geo 4')
var range =...
1
vote
1
answer
69
views
Help Sorting Data
I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link:
https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
1
vote
1
answer
12k
views
How to use external data range for a Google sheet pivot?
I'd like to use another spreadsheet's data as the data range for a pivot table.
In order to import data from another spreadsheet, one can use the impotrange function.
But can it be imported directly ...
1
vote
1
answer
105
views
Recalculation delays are making my spreadsheet difficult to use
I have a worksheet with 13 sheets. the largest one has 2000 rows. I have already deleted all the possible blank rows i could.
I have also read all the guides on how to make a Google Sheet faster and ...
1
vote
1
answer
93
views
How to import/merge data from from 2 separate sheets from another spreadsheet
I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A"
I basically need exactly this but with a function to import ...
1
vote
1
answer
2k
views
ImportXML not stable in Google Sheets, how can I improve that?
I have ImportXML in my spreadsheet.
I am tracking the Alexa ranking of a list of websites.
Here is the formula:
=HYPERLINK(CONCAT("www.alexa.com/siteinfo/",A4), value(ImportXML(CONCATENATE("http://...
1
vote
1
answer
9k
views
QUERY error NO_COLUMN:col1 [duplicate]
=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"),
"select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53
where col53<> ...
1
vote
1
answer
5k
views
Query Importrange with multiple values
This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
1
vote
1
answer
1k
views
IMPORTRANGE syntax when using query on multiple tabs
I am using IMPORTRANGE within a query to extract data from sheet 1 to sheet 2. The following code is in a cell in tab A of sheet 2, and works as intended:
=query(importrange("URL-A","E:J"), "select * ...
1
vote
1
answer
4k
views
Does IMPORTRANGE automatically update the destination if the source is changed?
Not sure if this has been answered else ware but, I'm trying to use the importrange function in Sheets to import a list of dates from spreadsheet A to spreadsheet B. That part works fine, however, if ...
1
vote
2
answers
5k
views
importrange with query not working properly
I'm trying to import a column from a separate sheet and add a query to it.
Im using:
=QUERY(ImportRange("URL","SHEETNAME!c:f"),"select Col1 where Col4 contains 'statements' ")
I've been using this ...
1
vote
1
answer
2k
views
Problem with IMPORTRANGE function in Google Sheets
I’m trying to get IMPORTRANGE to work for me with no success. I made two simple spreadsheets: Destination Sheet and Source Sheet. In the destination sheet I’m using the import range function:
=...
1
vote
1
answer
6k
views
Trigger function when IMPORTRANGE() is updated
I have a sheet (sheet-A) that gets it's information from another sheet (sheet-B) using IMPORTRANGE(). In sheet-B I have a function that formats cells based on the a value in the row. At the moment I ...
1
vote
1
answer
7k
views
IMPORTRANGE and Query say column does not exist [duplicate]
I want to get the sum of hours an employee, whose name is in Cell D1, was absent by referring to a table. The table is on a sheet named Sep. The word Sep is a string typed in A97. I am able to ...
1
vote
1
answer
3k
views
How do I Import a Range from one Google Spreadsheet into another?
I am getting the same error as described here except not for the same reason. I am using the new Google Sheets, so I am supposed to be able to paste the entire source URL into the sheet, like so:
=...
1
vote
1
answer
135
views
Lookup/Copy data from different sheets?
I am creating a schedule sheet in Google Spreadsheets that will have a number of different people inputting their own information. I would like the Director to be able to open a single document that "...
1
vote
1
answer
1k
views
CONTINUE function is annoying me? [closed]
I am trying to pull data from other Google spreadsheets into specific cells on my current sheet by using the IMPORTRANGE function. It's working well in some cells, but it others, it keeps getting the ...
1
vote
1
answer
288
views
Importing only certain rows
I am attempting to create a spreadsheet based off another spreadsheet. I only wish to import certain rows based on the second column of the first spreadsheet. If that column contains certain users ...
1
vote
1
answer
882
views
IMPORTRANGE formula query to query data in another file
Is it possible to create two different files where in the first I put the data and in the second the references query?
I used IMPORTRANGE but this method gives me "=CONTINUE(A1; 2; 1)"... I want to ...
1
vote
1
answer
32
views
How to sum data on one row from multiple selected Google Sheets
I am tracking paid advertising key performing metrics on both Google & Facebook ads platforms on two separate Google Sheets, however, I'd like to combine both datas collected into one master sheet....
1
vote
1
answer
182
views
Google Sheet ImportRange embedded in filter with RegexMatch option
I have a complicated spreadsheet with multiple tabs that filter for various things. The raw data comes from a file that I must download as a .csv file each day. Looking for a way to simplify a bit.
...