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.
232
questions
2
votes
1
answer
26
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 ...
0
votes
0
answers
13
views
How to use IMPORTRANGE function [duplicate]
I’m just having an issue. I need to IMPORTRANGE two Google sheet tables, but in the main source table is a filter turned on. I need to connect data from column "D" with name RM and ...
- 1
0
votes
1
answer
1k
views
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: D
I have data in a sheet called "PASTE HERE" that I want to import into another sheet in the same workbook. I don't want to use IMPORTRANGE because then copying the workbook would necessitate ...
- 1
2
votes
1
answer
147
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 ...
- 1,204
2
votes
1
answer
30
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 ...
- 23
1
vote
1
answer
88
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.
...
0
votes
2
answers
319
views
What's the workaround to copy the link for a cell when using IMPORTRANGE?
I want to know what the correct way is to use IMPORTRANGE while it also gets the correct link for another spreadsheet. What ends up happening is that the link directs to the current file instead of ...
- 101
0
votes
0
answers
37
views
Update Google Sheet Column A + B with new data without losing Column C - F data
I have a document tracking the file size of each project on my server (Column A + B) and important information about each project (Columns C - F).
I need to update the project size each month and ...
0
votes
1
answer
29
views
IMPORTRANGE to refer the function but not the value/result
Thanks for the help
Here is my situation
There are 5 different spreadsheets, i.e. Master, 1, 2, 3, 4)
All cell A1 in 5 different spreadsheet has the same function e.g. =count(B1:B5), they count the ...
- 1
0
votes
2
answers
231
views
Can you do an importrange on data from multiple sheets if the data itself has an arrayformula with a textjoin?
I'm trying to summarize a bunch of sheets into one via a query([(importrange)(importrange)(etc.)}. I've done this multiple times without issue, but now the data I'm pulling is has a columns that is ...
- 1
1
vote
2
answers
82
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
93
views
How to create Google sheets range off of 2 separate columns
I need to create a range off of two separate columns within a sheet.
Let's say column A is my index, while column D is third party index.
When using VLOOKUP i can lookup my index to find 3rd party ...
- 11
1
vote
1
answer
126
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 ...
- 13
1
vote
1
answer
65
views
RC notation in IMPORTRANGE
Is there a way to use RC notation in the IMPORTRANGE function? What I need to do is to import a certain column, which I'm finding using MATCH in the first row (headers)
=importrange("linktosheet&...
- 11
0
votes
1
answer
28
views
How to get additional row data to stay with row when linked to another spreadsheet
I have data in columns A:C of Spreadsheet 1 being pulled from Spreadsheet 2 via =importrange. Columns D:Z of Spreadsheet 1 is just text related to the imported data:
Spreadsheet 1 pulls start/end ...
0
votes
1
answer
24
views
How to build a table of imported data from conditional data sources
I have many spreadsheets where data will be added manually and which are all identical. Let's call these UserInputSpreadsheets.
Inside each user spreadsheet are tabs with data which are also identical....
- 101
1
vote
1
answer
388
views
How to use IMPORTRANGE across discontinuous columns? (Or, how to omit columns?)
How are discontinuous columns imported? Here, the import is inclusive from B3 to D7 as:
Here, the formula is:
=importrange("https://docs.google.com/spreadsheets/d/...
- 696
1
vote
1
answer
119
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 ...
- 11
1
vote
2
answers
308
views
Updating IMPORTRANGE based on date
I have a spreadsheet I have created with multiple tabs for users to input data on Wednesday of each week. I have a cover worksheet with an IMPORTRANGE formula to pull the data from the users sheets so ...
- 11
1
vote
0
answers
31
views
How do I filter in IMPORTRANGE using SEQUENCE and ARRAYFORMULA?
I am having issues with including a FILTER option in my current working formula listed below:
=QUERY (IMPORTRANGE("1n7qMGR9p8xD-8tyPIhfrcyBEsW8FAUMJMHmgxM7xIVI","Consultant Directory!A6:...
- 11
1
vote
0
answers
25
views
Add more conditions to the formula
=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
"select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
- 11
1
vote
0
answers
121
views
Get the url-sheet from an other cell in Importrange
I am looking for a way to change the url in an importrange without actually opening the formula, but just changing the url in an other cell.
I tried with putting the cell reference, with the hyperlink ...
- 11
0
votes
1
answer
39
views
How do I relative the cell value only?
For example I have a sheet and there is a col with cell-like A2 and A2 has a hyperlink. A2 value is 'Apple' and the hyperlink is 'stackexchange.com'.
When I am trying to use importrange formula with ...
- 9
0
votes
0
answers
189
views
Importrange changes the format only for a couple cells
I am using importrange and for some cells google sheets changes the format of only a couple cells. I have attached a screenshot. The original worksheet has the correct dates M/DD/YYYY. So I am ...
- 1
0
votes
0
answers
192
views
Using Query & Import Range function to import data range with a date filter
I have been able to use the the Query and Import Range function perfectly.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/13eoPsqGik4ZWFGPURrC9RYdlOuxdbCbQWXzWA9LC1L4/edit#gid=0",...
0
votes
1
answer
173
views
Countifs between 2 dates from an import range
Looking for some help on a Countifs formula from an import range (if this is the right thing to use(?))
I have a sheet tracking rejected bookings on certain dates, and I want to pull this into another ...
- 3
0
votes
1
answer
265
views
Indirect ► Transpose ► Importrange = It is not a valid cell/range reference - not an expert, need help
I'm trying to get a range from another spreadsheet, to appear depending on an ID number placed in the current sheet, I've been doing that for other data within the sheet and spreadsheet, but whenever ...
- 11
0
votes
0
answers
74
views
IMPORTRANGE contains specific columns with multiple answers
Example, one person can enter 5 events with several options for participating (both days or Sunday only or 1st go....) how do I get master1 sheet to only show contestants on Saturday and master2 only ...
0
votes
0
answers
101
views
IMPORT RANGE PARSE ERROR
I have this arrayformula where I need to match cells in a column from one worksheet to another and I used Importrange to link these two but it's giving me errors and I'm not sure what i'm missing
here ...
- 1
-1
votes
1
answer
77
views
Import a working formula from a different Google Sheet
I want to import a working formula from doc 1 to doc 2.
I used importrange but then it importet the data from doc 1 to doc 2 and not the formula.
I then used a second spreadsheet in doc 1 to show the ...
- 1
0
votes
1
answer
172
views
IMPORTRANGE keeps broken
I have several raw data files which I have linked with importrange to another files.
For some reason my files where getting broken since I keep getting importrange internal error. Also, they take too ...
- 1
0
votes
3
answers
2k
views
Importrange with multiple criteria
Looking to countifs data from another workbook with multiple criteria.
So far I have
=COUNTIFS(IMPORTRANGE("URL", "Data save!$A$2:$A$5011"),">=01/04/2022")
This ...
0
votes
1
answer
231
views
How do I get importrange and jointext to combine 3 name cells into 1 cell on a new worksheet in Google sheets?
On Wksht A I have cells C5=last name (i.e. Doe), D5=first name (i.e. Jane), E5=first name (i.e. John). This is to cover 2 joint owners (i.e. husband and wife) in our housing complex. I want to import ...
- 11
-2
votes
1
answer
66
views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
- 1
1
vote
1
answer
271
views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
- 11
0
votes
1
answer
566
views
google sheet how to import cells based on value in different cells
So I have two sheets.
Sheet A tab X contains two columns, first one with an url for a google doc and in the other column text IDs.
Sheet B tab Y contains a column with text ID of several more files ...
- 3
1
vote
1
answer
25
views
How to combine/Link values of Spreadsheet A onto B while preserving data added on B Sheets against data coming from A?
There are two spreadsheets that are used by two teams, Team A and Team B. Team A provides directions and posts work orders to Team B. Team B in turn enters comments on the actions completed. Each team ...
- 111
0
votes
1
answer
40
views
How to show the data from another spreadsheet but only the latest week
I have 2 spreadsheet, the first one consists of all of the data of withdrawal and deposit of items. The second spreadsheet is where the summary of the data shown.
Here is the master data link:
https://...
- 105
1
vote
1
answer
843
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"),...
- 11
0
votes
1
answer
37
views
importrange on google shhet
I have a question regarding the importrange() function. This is my formula:
=ImportRange("1u0TjjekVHHb6shYiSWoo0W1ygxuSu2tVvLstnEE1NFo";"Juillet!I5")
I have about 20 rows of data ...
1
vote
1
answer
27
views
Need help filtering entire rows from reference sheet to master sheet where column text meets criteria [closed]
I feel like this should be a relatively simple issue but everything I write is clearly incorrect or leaves me needing to do work manually every time new data is made.
Basically I have a series of ...
- 11
-1
votes
1
answer
288
views
Can I convert an entire sheet into all caps when data is imported using IMPORTRANGE?
I am using a form to collect data from customers. After submitting the form and collecting the data in Form Responses 1 (name of Google Sheet), I've created another sheet which imports most data, not ...
0
votes
1
answer
214
views
How to modify Query Options no_format to allow some format, but not others?
I have to replicate a Google Sheet, and am using ImportRange and Query together. I need Query also because the first sheet has urls under them, which I cannot have on the copied sheet. I was able to ...
- 1
1
vote
2
answers
749
views
Appending / Adding manual data input to end of IMPORTRANGE dataset
I am trying to figure out how to append / enter manual data to the end of an IMPORTRANGE list. In my spreadsheet, each tab is a month. I want to IMPORT the prior month's list (rows) and then be able ...
- 21
1
vote
1
answer
75
views
How to use use cell references in the WHERE part of a QUERY / IMPORTRANGE formula
Can you tell me what's wrong with my formula?
= QUERY (IMPORTRANGE ("url googlesheets database"; "Data range"); "SELECT Col1 WHERE Col1 =" A3 "AND Col2 =" B3 "")
The "A3" and "B3&...
0
votes
0
answers
115
views
Gsheets Query Importrange not giving expected results
I'm having an issue with the Query(Importrange) function of Google Sheets.
I'm using the following formula
=Query(IMPORTRANGE('Importrangelink,"'Sheetname'!X1:AA130"),"select Col1,Col2,...
- 1
0
votes
1
answer
33
views
Creating a sheet that pulls columns from other tabs
I have created a Google Sheet with multiple tabs for individual project schedules. The individual schedules have two columns: one with the tasks, a second with the project's name and the due dates. I ...
- 9
1
vote
2
answers
153
views
Formula parse error on importrange query
I'm trying to recreate what's happening in this video at 7:17
https://youtu.be/5S7laJS9meU?t=437
I want to merge two sheets and remove the empty rows.
=QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20")...
2
votes
2
answers
304
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 ...
0
votes
1
answer
563
views
How to refer to a cell value within importrange function?
I am using this formula to bring data from another spreadsheet file based on name of the month. This time for February..
=query(importrange("1L86iP0GLHTubwfvxs1kJ4ocbmgRAd4Iz0000000M","'...
- 457