A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
3
votes
1answer
8k 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
91 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
290 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
1k 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
834 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
574 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
541 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
4k 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
6k 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
517 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
752 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
2k 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, ...
47
votes
7answers
210k 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
2k 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
482 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
509 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
16k 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
1k 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
141 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
2k 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
711 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
4k 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 ...
6
votes
1answer
14k 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
5k 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
207 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
291 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
42k 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
2answers
14k 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
2k 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 ...
12
votes
7answers
58k 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
157 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
492 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
760 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
1k 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
2k 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 ...
-1
votes
1answer
151 views

Automatically group data by month

I am using Google Sheets and I would like to display monthly in and out. date units unit cost cost 2015-01-05 200 0.24275 -48.55 2015-01-05 1 1.43 -1.43 2015-01-09 ...
1
vote
1answer
5k views

Query(IMPORTRANGE) throws “Formula Parse Error”

I'm working with Google Spreadsheets and I've run into a wall with a query(IMPORTRANGE) function: =Query((IMPORTRANGE("URL","Sheet Name!A2:P1000"),"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,...