Questions tagged [google-sheets-query]

Use for questions about QUERY built-in function from Google Sheets.

Filter by
Sorted by
Tagged with
1
vote
1answer
69 views

Summarize the best options in another table

I need help to create a sheet for a game. I am providing the INPUT, and I would like to create the OUTPUT in another tab (do not consider the colors, its just for ease of understanding): Mainly, the ...
2
votes
1answer
643 views

How to Query Google Sheet to Group Column Data Many to One

I have a sheet with property managers and the id of the properties they manage: pid name email 34721 Aron Hall ahall@test.com 35173 Aron Hall ahall@test.com 35057 Bobby Blue ...
4
votes
2answers
19k 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
votes
3answers
13k 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 ...
1
vote
2answers
2k 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 ...
0
votes
1answer
1k views

Query a Google Spreadsheets using several different values

I'm trying to figure out how to update the following Google Spreadsheets query function so it can search column "J" for three or more separate values. If any of the values are present in column "J", I ...
2
votes
2answers
2k views

Average time on group by using Google sheet query language

I am trying to do a group by operation on the following table using the query language of Google sheet. Type Value Duration A 50 00:50:00 B 5 00:06:00 A 50 00:...
3
votes
1answer
105 views

Google Sheets Search and Display Location

I have a table like so: +---+-----+ | A | 100 | +---+-----+ | B | 125 | +---+-----+ | C | 454 | +---+-----+ | A | 234 | +---+-----+ | A | 343 | +---+-----+ | B | 222 | +---+-----+ | C | 754 | +---+---...
1
vote
2answers
6k views

Dynamic query select function based on a cell reference

I have several queries. Each week they need to shift one column right, C to D then E...W. I currently manually change C to D in all my queries and I want to simplify. A couple of examples: select B,...
0
votes
1answer
588 views

Match strings with more than one character in Google Sheets

I have the following query: =QUERY('Data'!A2:F,"SELECT A, B, F WHERE C=''") In column E I also have strings which correspond to last names. I want to select all rows where the string in E has more ...
0
votes
1answer
2k views

Using QUERY to filter multiple criteria from another spreadsheet

I have a column with 3 options: Primary Secondary [empty cell] I am currently using this formula to filter all rows that contain "Primary" from one spreadsheet to another and it's working well. =...
3
votes
1answer
3k views

Comma-separated values analysis and pivot tables in Google Spreadsheets

I have an issue with comma-separated values analysis and pivot tables in Google Spreadsheets. What if I have the following dataset: Facebook, Github Developer No answer Developer ...
4
votes
1answer
9k 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 ...
3
votes
1answer
94 views

Google Query language

My goal is to select (count a number of rows with data) based on a condition. My query looks so basic but it does not work. =query(A:B,"select count(B) where (A = '44')",) My result is "count" as ...
1
vote
2answers
3k views

Google Sheets Dynamic Condition in Filter Function

I have a spreadsheet where I am amassing metadata and statistics based on filters. For each row of my summary spreadsheet, I want the full statistics of all of the data in my spreadsheet that is ...
1
vote
1answer
323 views

Evaluate date inside a query

I am attempting to perform a query in Google Sheets, and one of the things I need to match is a date. I have seen other posts on this topic, but I am not having success. The advice that isn't ...
0
votes
1answer
2k views

How to filter range and return only specific columns?

This is what I have tried just yet: =QUERY(FILTER(Recherche!A4:O1002), "select Col1, Col15, Col2, Col3, Col4") based on this format =QUERY(FILTER(B:D,D:D>=2),"select Col2, Col1") What I get is ...
-1
votes
2answers
917 views

Query date in Google Sheets in format >date [duplicate]

I have this query =QUERY( 'Monitor'!$A$4:$AB, "select * WHERE " & IF(C2="Yes","A=1 ",IF(C2="No","A=0 ","(A=0 OR A=1) ")) & "AND " & IF(ISBLANK(C3),,"D="&C3&" ") & "AND "&...
0
votes
1answer
78 views

Sort QUERY results to the 4th character of a string

I'm trying to pull a list of people based on their last names. The catch is I only want to QUERY people with last names that fit between an alpha range to the 4th character. Example I only want ...
0
votes
2answers
783 views

QUERY() ignores query when combined with array

Please consider the following Google Spreadsheet Formula: =QUERY( ARRAYFORMULA({ IMPORTRANGE("f333fHFF5sf6YxO4342Y4BIV3242KJTaarqU","Sample!A1:A"), IMPORTRANGE("...
0
votes
1answer
2k views

How do you extract data using QUERY when a reference cell contains an Apostrophe?

=QUERY('DK Salaries'!$1:$1000, `"Select B, C where A='"&A184&"' And B contains'"&O184&"' And B contains'"&N184&"' And F='"&Q184&"'label B'', C''") The cell O184 I ...
0
votes
1answer
2k views

I cannot get the QUERY function to work with IMPORTRANGE [duplicate]

I am using the function below to QUERY an IMPORTRANGE range based on IS NOT NULL conditions: =query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1O5DeZ9LFLpGbp8b2aGst57XLEP7ZiOnc-2b552sQLz0","...
2
votes
1answer
665 views

Query using group by but only displaying the latest entry for each item

I'm working on a spreadsheet that records logistics reports via a Google form. We have 100 locations and logistics personal visit and report on condition of each location, i.e. the location needs ...
1
vote
1answer
2k views

Query by Max Date Google Spreadsheet

I'm working on a spreadsheet that records logistics reports via a google form. We have 100 locations and logistics personal visit and report on condition of each location, i.e. the location needs ...
1
vote
1answer
5k 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
2answers
7k views

Google Sheets Query sum single column based on criteria

I have a spreadsheet laid out with three columns (a,b,c) as such: a b c -- -- -- abc 12 yes abc 8 yes xyz 10 no xyz 5 no lmn 8 ...
0
votes
1answer
620 views

Google Sheets: Extra rows being added by Query

I have a formula that queries a handful of other pages in the same sheet, =SORT( QUERY( {'Design Team Assignments'!B3:G;'Mechanical Team Assignments'!B3:G;'Programming Team Assignments'!...
2
votes
1answer
853 views

Google Sheets query() with “Order By” doesn't print in same order as displayed

I have this issue where Google Sheets does not print a spreadsheet's rows in the same order as it displays them on screen. This specifically applies to dates in a sheet, which has a query() ...
2
votes
1answer
3k views

How select one column in a query only if another column has a specific value

I just discovered the query function and it's a huge game-changer. I've been tinkering a little but would love some help with this one. What I would like to do is =query( 'Sample Data'!A3:D10, ...
62
votes
8answers
290k views

Google Sheets formula for “if contains”

I'm trying to figure out how to identify IF a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell ...
1
vote
1answer
3k views

How to make Query() not break with empty cells

I'm trying to perform a query through an imported range, but whenever I have an empty cell in the first column, it gives me a cell that is a combination of ALL of the cells that have an empty Col1 at ...
2
votes
1answer
522 views

QUERY most recent date prior to today

I have list of dates in column R that are spaced by two week intervals, so 6/1/16, 6/15/16, 6/29/16, etc. In columns S to AF I will mark "y" or "n" each day on the row corresponding to the date, ...
2
votes
1answer
557 views

Querying multiple ranges from form responses to new sheet

Recently, someone on this forum put together a formula for me. It works on a sheet pulling responses from a google form. I'm trying to model a new one after that but I'm having trouble. The working ...
6
votes
2answers
19k 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 ...
0
votes
1answer
2k views

Query formula limitations Google Sheets?

I'm trying to run a query (IMPORTRANGE) formula in a new Google sheet. The data I'm trying to load is from another sheet. The source sheet contains 6,000 rows and 12 columns. I tried: =QUERY(...
5
votes
1answer
164 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 ...
1
vote
1answer
3k views

Group events by day, count them and aggregate the amounts

I have a long list of date times (timestamps) in column A, attached to some data in column B. I want to group this data by day, obtaining the count of events and the totals for each day. For example, ...
1
vote
2answers
2k 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 ...
0
votes
1answer
809 views

Google Spreadsheet Query blanks out text cells in the query range

When I perform a query to essentially filter a range of names, and times, any cell that is not a time gets blanked out. Screenshot of example: I'm keeping this post fairly short since the image ...
3
votes
1answer
2k views

How do I use a query on a timestamp to filter down to “day of the week” and “hour”

Currently I am able to Query my data by the date in a timestamp but now I want to narrow it down to the hour and the day =COUNT(QUERY('Form Responses 1'!$A$2:$E, "Select A where A>=date '"&...
1
vote
1answer
2k 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 ...
2
votes
2answers
5k 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 ...
7
votes
1answer
16k 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: =...
1
vote
1answer
6k views

How do I filter the array returned by a filter or other formula?

I want to filter an array returned by a filter to remove blank columns. Here is a picture of an example sheet: Here is the example sheet. I am filtering the large table by the rows that are of type ...
7
votes
2answers
3k 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. ...
3
votes
3answers
218 views

How do I get data from one Google Spreadsheets “A” into Spreadsheets “B”?

How do I get data from one Google spreadsheet A into spreadsheet B so when Spreadsheet A is modified, then Spreadsheet B also changes automatically? A formula using QUERY() and IMPORTRANGE() ...
5
votes
2answers
2k 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 ...
11
votes
4answers
2k 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 ...
4
votes
2answers
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 ...
4
votes
1answer
305 views

Using QUERY command with dates

I have a column in Sheet1 that contains dates in format DD/MM/YYYY, like this: 12/10/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 28/09/2015 26/10/2015 26/10/2015 26/10/2015 26/10/2015 ...

1
9 10
11
12 13