Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
108
questions
97
votes
10
answers
366k
views
How can I make some data on a Google Sheets auto-sorting?
Let's say I want Google Sheets to automagically sort some data, for example column C.
How do I do that?
I know that I can manually sort data by right-clicking and selecting Sort data, but this is ...
Diego Petrucci
2
votes
1
answer
4k
views
Transpose Ranges repeated by row
I would like to convert several range of columns into rows, and keeping the association to the original col/row source
Sample source sheet
---------------------------------------------------
| Data ...
- 43
2
votes
1
answer
6k
views
Dynamic date range query
I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1".
My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
- 21
7
votes
3
answers
41k
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 ...
- 107
3
votes
5
answers
3k
views
multiple IF statements with between number ranges alternative
How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column)
with a rule set as follows:
01-10
11-20
...
- 27k
3
votes
3
answers
35k
views
Google Sheets QUERY won't display cell text if other cells have numbers
I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue.
Basically, I'm using a query to pull data ...
- 2,897
153
votes
9
answers
782k
views
Google Sheets formula for "if contains"
I'm trying to figure out how to identify IF a list of items in one cell containing a value or string.
EXAMPLE
Cell A1 contains sites, sheets, docs, slides.
I want cell B1 to display a 1 'if' cell A1 ...
- 2,897
36
votes
13
answers
83k
views
Can I use column headers in a =QUERY?
Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
- 17.4k
10
votes
3
answers
23k
views
How to sort a Google Sheets Query by a custom ordering
I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)?
...
- 339
2
votes
2
answers
2k
views
Google Sheets Split row in multiple rows
I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.
I need a script that can arrange the data like in the blue rectangle.
186
votes
3
answers
322k
views
How to group data in a Google Spreadsheet?
I have the following table in a Google Spreadsheet:
Name | Value
A | 10
B | 100
A | 20
B | 200
C | 1000
How can I create another table based on a query:
select name, sum(value), ...
- 2,235
10
votes
6
answers
31k
views
Split content of one row into multiple rows?
I have a table where each row contains data like this:
Team name | First member | Second member | Third member | Team description
Would it be possible to transform/break this data so that each row ...
- 1,864
8
votes
4
answers
9k
views
Using Query with column headers instead of column letters
I have a formula that I use to dynamically get the column letter for a 1-Dimensional array that are the column headers of a sheet. In order to use QUERY() with column names instead of letters.
...
- 1,249
7
votes
7
answers
41k
views
LEFT JOIN two and more tables in Google Sheets
Say I have two tables. Table A (material requirements):
product_id | material | required
1 | A | 0.5
1 | B | 0.7
2 | A | 0.2
3 | A | 0....
- 187
6
votes
2
answers
10k
views
Query syntax using cell reference
I'm having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets. I'm trying to use a cell reference instead of static values and I'm running into trouble. I keep getting Error: ...
- 2,897
2
votes
3
answers
8k
views
QUERY error for BY column
I'm using the following query to pull data from one sheet into another.
=query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, BB, BZ, CA, BA where J = 'Industry Certification'",false)
The above query ...
- 2,897
1
vote
1
answer
71
views
Joining multiple sheets horizontally
I have a number of spreadsheets
I want to join them horizontally by Id.
How would you achieve that?
p.s I should mention that the id order could be different in each spreadsheet
- 189
43
votes
3
answers
92k
views
FILTER(condition OR condition) syntax?
I am using FILTER() to display a value from Sheet 2 where the Name columns match.
My problem is where I want:
=FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal")
as OR results ...
- 471
16
votes
1
answer
124k
views
Google Sheets Query Get Row When Any of These Cells are Not Empty
So like my title states I would like to select via a query only the rows where certain cells are not blank/empty.
I have 4 pools that I have a form for collecting their chemistry. In separate sheets ...
- 527
14
votes
3
answers
72k
views
How can I create a unique list of data across multiple rows AND columns?
Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.
This works well for column-specific data, however, you have multiple columns and ...
- 297
13
votes
8
answers
59k
views
How to make a query with Google Spreadsheet, that checks for a date
I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4.
...
- 277
7
votes
2
answers
1k
views
How to Create a Google Sheets Script for Reporting 26-Week New Highs using GOOGLEFINANCE Function?
I'm looking to use GoogleSheets to display ticker symbols and prices in real-time of stocks that have created new 26-week highs, i.e. today's highest trading price is greater than any trading price ...
- 81
5
votes
1
answer
4k
views
Query is returning a single concatenated string of values THEN the rows it found
I'm getting some really weird results from my QUERY() Here is an image with an example:
I'm querying for the name where No Show = "No Show". It find the correct name Name 5 but also returns a ...
- 1,249
3
votes
1
answer
3k
views
Adding missing dates to Google Sheets
I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function.
For ...
- 31
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 ...
1
vote
1
answer
10k
views
How to combine two QUERY results into one column
I have the following QUERY
=query(XX!A2:A;" select A where A>'50' ")
and
=query(YY!A2:A;" select A where A>'50' ")
how do I combine these two data sources so I don't have to have two ...
- 183
1
vote
1
answer
3k
views
List all records with certain criteria
Help. I just cannot get this going even after 2 hours of searching Google.
I have a Google Spreadsheet that has a "List" sheet and an "Outstanding" sheet. I want to see what's outstanding from a ...
- 13
1
vote
1
answer
2k
views
How do I return query results without the 'sum' column label?
I have the following formula in Google Sheets:
=QUERY(INDIRECT(C13&"!$C$5:F"),
"SELECT sum(F) WHERE D ='" &$E$12 &"'
AND C >= date'" &TEXT($G$4,&...
- 11
1
vote
1
answer
5k
views
Using SUMPRODUCT with ARRAYFORMULA
I have the following random data in sheet one
A orange
D yellow
C yellow
B orange
B orange
C blue
A yellow
D blue
C orange
C blue
In sheet two I use the following query function:
A1=...
- 11
1
vote
1
answer
5k
views
Defining a custom order in a Google Sheet query?
I'm using a Query in Google Sheets to pull data from another sheet. That query looks something like this:
=QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York'",1 )
What I'd like ...
- 11
1
vote
1
answer
432
views
Pretty print google sheet formula?
Finding answers on Web Apps SE or Reddit is wonderful, but if you don't understand them you can't modify them.
I recently came across the following as part of an answer:
={"Corrected List 2";...
12
votes
4
answers
4k
views
Average of last 7 non-empty non-blank cells in Google Sheets
I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
10
votes
5
answers
9k
views
Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date?
Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes.
Is there a way to write these queries so they don't ...
- 273
10
votes
1
answer
26k
views
Remove header and blank row from the results of a SUM, GROUP BY query
I have a two column table where one column is some sort of key where duplicates are allowed and the other column includes amounts.
I then have the following formula somewhere else on the sheet:
=...
- 602
10
votes
5
answers
46k
views
Count the number of words in a string in Google Sheets
I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
Nishant
8
votes
2
answers
30k
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"...
- 2,897
7
votes
4
answers
19k
views
How to make SQL CROSS JOIN of two tables with Google Spreadsheet?
I am just figuring out how to achieve an apparently simple problem. I use to have data as in database tables in my spreadsheets. It's simple data but I often need to use =QUERY function and would be ...
- 275
7
votes
2
answers
12k
views
Query Multiple Ranges with Results in Row
I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return.
...
- 317
5
votes
3
answers
13k
views
Query or filter the data where the start time is past 3:00 pm
I've started using Google Sheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the start time ...
- 51
5
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 ...
- 127
5
votes
2
answers
3k
views
Dropdown list based on the result of another cell's dropdown
I want to populate a dropdown list based on a selection of another cell's dropdown list.
Here is my spreadsheet.
E.g., if the user does not make a choice from D20 dropdown menu, therefore F20 ...
- 81
5
votes
2
answers
8k
views
Get rid of row in result of Google Spreadsheets query
I use the following formula in my Google Spreadsheet:
QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'")
This returns the following (On two different rows that ...
- 153
5
votes
1
answer
295
views
How to address the columns of an inline array?
Suppose you want to run a query against an inline array:
QUERY({"apple"; "orange"; "banana"}, "select A")
This yields Unable to parse query string for Function QUERY parameter 2: NO_COLUMNA.
Is ...
- 185
4
votes
1
answer
19k
views
Data Validation with an IF formula
I've got a list of items that I want to include in a Drop Down. Using data validation, all I need to do is add the range and this works. However, the list has various types of data and I want to ...
Jordan Tuck
4
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 ...
- 6,999
4
votes
3
answers
4k
views
Sorting unique rows by a related SUMIF column
I am putting together a worksheet to keep track of expenses. I currently have the following source table called "Expenses":
A
B
C
D
1
Date
Description
Category
Amount
2
1-Jan
...
- 41
3
votes
1
answer
2k
views
Google Sheets - Using IMPORTRANGE with a variable number of rows
Google Sheets - Using IMPORTRANGE with a variable number of rows
...is it possible to add the required number of blank rows to avoid the IMPORTRANGE's overwrite error?
Test Input: https://docs....
- 33
3
votes
1
answer
8k
views
How to search multiple columns in Google Spreadsheet and return values in other columns?
I have two spreadsheets and would like to enter a value into cell A1, have it check multiple columns in a spreadsheet named 'data' and output information from other columns if it's a match.
----------...
- 31
3
votes
3
answers
8k
views
Import current price from website to spreadsheet
As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet.
Perhaps I'm over-complicating things, but there must be an easy way to do this.
The ...
- 31
3
votes
2
answers
32k
views
How to GROUP and ORDER data in a Google Spreadsheets?
I want to group and sort a particular data set. I've prepared the data using a QUERY function and using other functions.
Can I order by sum?
This is an example file I created: order by sum
