Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets-query]

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

-1
votes
2answers
5k 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
663 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
104 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
1k 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
70 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
1answer
3k 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
375 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
1k 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
1k 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 ...
3
votes
1answer
4k 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
86 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
2k 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
177 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
737 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
615 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
73 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
250 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
1k 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
1k views

I cannot get the QUERY function to work with IMPORTRANGE

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
296 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
1k 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
2k views

IMPORTRANGE and Query say column does not exist

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
3k 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
170 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
474 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
1k 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, ...
1
vote
1answer
1k 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
343 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, ...
1
vote
1answer
352 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 ...
4
votes
1answer
6k 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
713 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(...
4
votes
1answer
64 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
1k 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
1k 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 ...
3
votes
1answer
1k 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 '"&TEXT(B$...
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 ...
7
votes
1answer
2k 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
169 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
1answer
905 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 ...
4
votes
1answer
189 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 ...
3
votes
1answer
2k views

Querying data through importHTML function in Google spreadsheet

I am trying to import table data into a Google spreadsheet using the below function: =ImportHTML( "http://eciresults.nic.in/StatewiseU05.htm" , "table" , 9 ) Following is the result I get out of it. ...
4
votes
2answers
27k 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. ...
4
votes
1answer
7k 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 Name!A2:P1000"), ...
3
votes
1answer
1k 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 ...
6
votes
7answers
29k 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 ...
1
vote
1answer
134 views

How do you reference a cell containing a date in a Query?

I have a table to keep track of hours put in working on an Interior Design project. The table looks something like this: 8/16/2015 6:30:00 PM 8:00:00 PM 1.50 Floorplan 8/19/2015 10:00:00 ...
2
votes
2answers
385 views

Get unique rows and their count in Spreadsheet

I have a sheet as below, | A | B | C | ------------- |abc|123|Yes| |jkl|123|No | |abc|123|Yes| |abc|567|Yes| I want to get the output like, | A | B | C |Count| ------------------- |abc|123|Yes| 2 ...
4
votes
1answer
542 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 ...
0
votes
1answer
983 views

Using Query with IMPORTRANGE and comparison of dates

I created a Google Form that employees complete, The data goes into the main sheet in a spreadsheet. I then want to split that data into separate sheets according to the date in one of the columns. In ...
2
votes
1answer
986 views

CONCATENATE inside IMPORTRANGE inside Query

I found this formula: =QUERY(IMPORTRANGE("key","A:Z"),"SELECT Col1, Col2, Col3 WHERE Col1 contains 'value' ORDER BY Col1") here and I'd like to add a concatenate function where value is taken from ...