We’re rewarding the question askers & reputations are being recalculated! Read more.

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
896 views

Construct query where condition is in the past week?

Using Google Sheets' =QUERY function, is it possible to construct a select such as follows? =QUERY('My Sheet'!B:D,"select A where C = In the Past Week")
4
votes
1answer
2k 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 ...
1
vote
4answers
739 views

Value of cell above and to the left of matching cells

In Google Sheets, I have a sheet that looks like this that I cannot modify: My goal is to create, through formulas, a sheet that looks like this: Where that "summary" sheet displays the value for ...
0
votes
1answer
120 views

I need help with sorting query in Google Sheets

I have a zap that pulls "Email" data from a spreadsheet, I also have a query that pulls "NAME" data from a different spreadsheet, but when I try to sort it, the data gets misplaced, eg. blank rows ...
0
votes
3answers
350 views

Reverse QUERY in chronological order from two standalone spreadsheets

I have a report that pulls data from a separate sheet using a QUERY. The report has a "notes" column (not part of the query) so users can add notes associated with a particular row. This solution ...
-1
votes
1answer
38 views

Google Spreadsheet Separating Rows By Names

I am trying to follow my own mutual fund purchases and trying to input the results into a spreadsheet to calculate the XIRR (internal rate of return). I am planning on entering my data for every ...
0
votes
2answers
451 views

How can you use a function and pass it a column inside a Google Sheets Query?

On Sheet 1, I have data CATEGORY | PATIENT | DATE | AMOUNT Vision | Joe | 1/1/2016 | $100 Vision | Joe | 1/2/2016 | $100 Vision | Joe | 1/3/2016 | $...
0
votes
1answer
396 views

Update Google Sheets from another sheet and keep the history

I have set of data in Google Sheets that look like this: Columns B and C are updated manually every day. On the other sheet, I'm trying to append the new data above. If updated, with the previous ...
1
vote
1answer
63 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
600 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
15k 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, ...
1
vote
3answers
10k 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
575 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
95 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
5k 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
564 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
2k 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
7k 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
90 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
273 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
798 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
77 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
505 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
499 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
486 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
705 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, ...
37
votes
7answers
176k 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
466 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
488 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
1answer
15k 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
134 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
670 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 '"&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 ...
2
votes
2answers
3k 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 ...