Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
104
questions
92
votes
10
answers
346k
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
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
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
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 ...
- 107
2
votes
5
answers
2k
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
...
- 26.2k
3
votes
3
answers
33k
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,857
150
votes
9
answers
747k
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,857
34
votes
12
answers
77k
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.1k
9
votes
3
answers
22k
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)?
...
- 340
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.
179
votes
3
answers
290k
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,165
10
votes
6
answers
30k
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
7
answers
39k
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....
- 197
7
votes
4
answers
8k
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,209
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,857
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,857
37
votes
3
answers
79k
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 ...
- 411
15
votes
1
answer
122k
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 ...
- 517
14
votes
3
answers
67k
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
4
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,209
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
4k
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
3k
views
Google Sheets Query - Group by more than one column without a pivot table
I have a timesheet dataset containing 4 columns:
Column 1 - A workers name
Column 2 - The date they worked
Column 3 - The project they worked on
Column 4 - The hours they worked on that date, on ...
- 13
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
9k
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
4k
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
32
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
- 179
1
vote
1
answer
345
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";...
1
vote
1
answer
1k
views
Sum all values with same month and category
So I have this table where I'm writing down my daily expenses.
On one cell I choose the date, the next cell I choose the category and in the third, I insert the amount of money spent.
On another sheet ...
- 13
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
5
answers
45k
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
9
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:
=...
- 572
8
votes
2
answers
28k
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,857
7
votes
4
answers
18k
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
12k
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
281
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
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
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
4
votes
1
answer
15k
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 ...
- 107
4
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 ...
- 71
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 ...
- 6,823
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-...
- 33
3
votes
1
answer
13k
views
How can you add a conditional statement to a =QUERY function
How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or ...
- 2,857
3
votes
1
answer
1k
views
Finding the average from the last 14 days in Google Sheets
I would like to figure out a formula that will look at the last 14 days of data in column D and calculate the average. But I would like it to dynamically update every day so it's always showing the ...
- 33
3
votes
1
answer
16k
views
Query multiple data input ranges into 1 column output
Not 100% sure how to explain this, feel free to edit this with more intelligent language.
I need to pull data from 2 tables in 1 sheet. Meaning, I need multiple query functions. Those query ...
- 133