Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
924
questions
175
votes
3
answers
284k
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,125
147
votes
9
answers
741k
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,827
91
votes
10
answers
341k
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
37
votes
3
answers
78k
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
33
votes
12
answers
75k
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 ...
- 17k
31
votes
7
answers
167k
views
How to return DISTINCT/UNIQUE list of text via Google QUERY?
Given the list of categories and sub-categories:
CatA SubCatA
CatA SubCatA
CatB SubCatB
CatB SubCatB
CatC SubCatC
CatC SubCatC
I'd like to return all unique main categories based ...
- 4,242
15
votes
1
answer
121k
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
13
votes
3
answers
66k
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 ...
- 287
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
12
votes
1
answer
13k
views
HAVING clause in Google Sheets QUERY
My Problem
I have built a count query that returns the most common elements from column H in the final sheet:
=QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC")
I would like ...
- 1,793
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
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
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
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
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
9
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,854
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,827
8
votes
7
answers
38k
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
2
answers
91k
views
Query Select Where is not null vs <>'' not filtering properly
I have an issue with my query where I have numeric cells and text cells that if all are empty I do not want to retrieve them however if anyone of the cells are not empty I want to retrieve that row.
...
- 517
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
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
34k
views
Stacking multiple query formulas throws "an Array Literal was missing values for one or more rows"
I am attempting to run multiple queries where it adds rows where if F is blank, then it skips those rows, then runs the query again and replaces row F with row G.
I have a much more complicated ...
- 135
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
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
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
7
votes
2
answers
23k
views
How to count items meeting criteria in multiple columns
In Google Spreadsheets I need to count instances of a certain value but only when they match either of two values in another column. So below I would ask for the count of all brown animals that are ...
- 173
6
votes
3
answers
20k
views
Google Sheets queries: using WHERE IN
My problem
I have a table of ids and terms in Google Sheets. I would like to concatenate a few terms by their ids.
For example, (2,3) should yield Talla de zapato de mujer, Women’s shoe size. I want ...
- 1,793
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,827
6
votes
3
answers
39k
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 ...
- 63
6
votes
3
answers
34k
views
Google Sheets Query to select a large range of columns without manually entering each column
I have a column with dozens and dozens of columns in it that I'm querying data from. I'm using something similar to below to do this, but I'm wondering if there is an easier way? For instance, ...
- 2,827
6
votes
1
answer
7k
views
Calculate Percentage with QUERY on Google Sheets
I have a Google Sheets database like this:
Name | Value | Answer
A | 100 | Y
B | 200 | N
C | 10 | Y
A | 100 | N
A | 1000 | Y
B |...
5
votes
2
answers
21k
views
Google Spreadsheets Query( "where a matches '/[A-Z+]/' ") condition
According to Google Query documentation:
matches - A (preg) regular expression match. haystack matches needle
is true if the regular expression in needle matches haystack.
Examples: where ...
- 293
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
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
5k
views
Google spreadsheets: Find max value for a name
I have spreadsheet with 2 columns - names and values, say:
John 2
Doe 8
Mary 17
John 5
Bill 12
Mary 6
John 3
What formula can I use to find the maximum value for each name? (E.g. in column C ...
- 201
5
votes
1
answer
275
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
1
answer
10k
views
filter or sort Google Spreadsheet based on condition
I have an 8 column spreadsheet.
I want to sort/filter based upon the value of the 4th column.
In a separate sheet within the same spreadsheet, I want to print a list of each row matching a speific ...
- 53
5
votes
2
answers
4k
views
COUNTIFS returns incorrect result when applied to the output of QUERY
In my spreadsheet the chart for Negative Feedback weekly pulls data from the Query sheet, but the logic doesn't work to separates negative feedback from positive feedback. The logic works fine on the ...
- 53
5
votes
2
answers
1k
views
Is it possible to edit the column a Google Form response saves to?
I have a client who uses a Google Form to save data to a Google Sheet. They deleted and recreated one question, and the responses to that question are now being stored in a new column. This messes up ...
5
votes
1
answer
3k
views
How can I stop Google Sheets charts from rounding down durations?
I've got a Google Sheets chart that displays the average pace of a day's rowing machine workouts (e.g. average the time to row 500m).
It looks like this:
The Query I'm using to get the data is this: ...
- 151
5
votes
2
answers
5k
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 ...
- 71
4
votes
2
answers
23k
views
COUNTIFS with multiple OR
I have two sheets, one is a dump sheet of values like below:
and the second sheet is using values from this to the total.
I am trying to get the total count for the corresponding zone and test name ...
- 41
4
votes
3
answers
12k
views
Is there a way in Google Sheets to order blank cells first?
When I sort a column in Google Spreadsheets, the blanks always seem to sink to the bottom. Can I have rows with blank cels be organized first / highest?
Alternatively, is there a function wherein I ...
- 259
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
3
answers
8k
views
Google Sheets, how to sort query results manually (Not in query)
My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable.
The sheet is populated ...
- 579
4
votes
5
answers
26k
views
How automatically to add spaces inbetween each character in a text string?
Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces.
How can I separate those characters so as to be able to create a chart ...
- 41
4
votes
2
answers
9k
views
Escape single quote in cell content in a QUERY
My Problem
I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
- 1,793
4
votes
1
answer
13k
views
QUERY where column IS NOT EQUAL to ANY cell in column A of a second sheet
I want it to pull the max value from column F where the corresponding row has C matching the number 14, and A does not have any exact text matches in any cell in column A of a separate sheet.
I feel ...
- 135
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
4
votes
2
answers
6k
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 ...
- 579