Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen 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
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 ...
11
votes
7answers
50k 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
154 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
480 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
703 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
149 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
4k 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,...
7
votes
3answers
13k 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)? ...
3
votes
1answer
475 views

In Google Sheets, can you apply a regex to a generated range?

I have a list of UK postcodes, looking roughly like this. Entries may not be unique. A1 1AA A1 2BC A1 3PN B2 4XY B2 5LH C3 6NJ They're in a range named Postcodes. I've set up the following ...
2
votes
2answers
4k views

Google Query For Dates in Last 30 Days

I'm trying to figure out how to do a google query for dates in the last 30 days. I'm using a wordpress plugin that lets you display spreadsheet data and do queries. Here is the current working query ...
1
vote
1answer
75 views

Dynamic values in a formula?

I have a Google Sheets where I am using this query to pull data from another sheet: =QUERY(IMPORTRANGE("**SHEETLOCATION**","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
58 views

Retrieve row data based on indirect Max function value

This spreadsheet tracks live song ratings for a popular band. Users input their score in their own column and those scores are averaged into a community score (Column E). Those song averages are ...
2
votes
1answer
4k views

Dynamic date range query

I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1". My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
2
votes
1answer
2k views

IMPORTRANGE + QUERY won't import IMAGE

I use Sheets in my business to create product catalogs for wholesale prices. I have different sheets for different companies but the data in many of them is the same. I'd like these sheets to sync ...
3
votes
1answer
6k views

How to VLOOKUP in a QUERY

I Have this sheet with raw data, basically names, dates, amounts of money and if they paid or not (this is the most important). What I am trying to do is to have in another sheet the same table but ...
0
votes
3answers
2k views

Numeric before query but query results are not a numbers

I have a column of values, let's say column A, that if I do =isnumber on any of the column data it returns TRUE. Now after I =query and select A, the query gives me the right values, but the type ...
1
vote
1answer
723 views

Google spreadsheet - QUERY changes time format

I have this column with amounts of time (format set to number > duration), if I made a query of that column I get it with a different time format and I'm not able to set the proper format back again. ...
2
votes
1answer
98 views

Had a query that worked but now doesn't

I made a copy of the spreadsheet that I'm working on here. The query that I have on on the sheets "DSM", "DiveTank", "WarmUp", "Teach" all have pretty much the same query. That worked before but now ...
0
votes
2answers
1k views

Query reference to several sheets

I have this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 ...
11
votes
1answer
54k 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 ...
5
votes
2answers
3k 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 ...
3
votes
1answer
34 views

Adding particular data to particular sheets within one document

I am a bit new at this and am having a tough time getting this to work... I have multiple sheets, one main one (Submissions) which receives data from a form, then I have the sheets: Level 1, Level 2, ...
1
vote
1answer
2k views

Using SUMPRODUCT with ARRAYFORMULA

I have the following random data in sheet one A orange D yellow C yellow B orange B orange C blue A yellow D blue C orange C blue In sheet two I use the following query function: A1=...
5
votes
3answers
9k 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 ...
0
votes
1answer
204 views

Counting occurrences in Google Spreadsheets

I've been brought on last minute to sort out tent allocation for a festival of about 1500 people. Their ticket sales are a spreadsheet on Google Drive. I have never done this kind of thing before (I'm ...
1
vote
1answer
2k views

List all records with certain criteria

Help. I just cannot get this going even after 2 hours of searching Google. I have a Google Spreadsheet that has a "List" sheet and an "Outstanding" sheet. I want to see what's outstanding from a ...
7
votes
4answers
13k views

Is there any way to use a formula in a Google Sheets filter view?

I have a sheet with a column of numbers. I'd like to create a filter view that only shows rows with a value greater than 500, but I can't find any way to do that. There are lots of different numbers, ...
4
votes
4answers
7k 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 ...
1
vote
3answers
1k views

Google sheet, lookup a value to get whole column

Here is my problem: I have a table starting in A1 with D D C B A 1 2 3 4 5 6 7 8 I want a function that returns me the column of data of whatever letter give it. ie input:=function("C") output: ...
1
vote
1answer
137 views

Sort auto update

I have this table, which is a ranking, whose values are updated automatically (data gathered from other files). What I want is that every time these values are updated the ranking automatically ...
2
votes
3answers
7k views

query all dates within the date range

I have a Google Spreadsheet that I'm querying and the date is set as dd/mm/yyyy. How can I query between the start and finish (ref cells) of each month, so the query only returns dates between the ...
3
votes
3answers
3k views

Import current price from website to spreadsheet

As you can probably gather from the title, the goal is to plug the current price of this product into a spreadsheet. Perhaps I'm over-complicating things, but there must be an easy way to do this. ...
2
votes
2answers
4k views

SUMIFS with text value of cells

Is there a way to use the text value of cells with a conditional statement such as SUMIFS so that on one sheet I can have all the expenses: # A B C 1 R. Tusk Travel 1,...
6
votes
2answers
7k 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: ...
1
vote
1answer
2k views

Query to copy from Sheet A to B based on TRUE/false

I'm trying to copy data from Sheet A to Sheet B based on if one of the columns has TRUE or False. I'm trying to use: =QUERY( 'A'!A1:I200 , "select * where I = 'True'" ) but this only gives me ...
4
votes
1answer
4k views

Query MAX group by

I'm trying to Query in only the most recent entries of each part number. I can usually figure this stuff out but cannot figure out why it only returns the (date) and not the (part number) and (most ...
15
votes
5answers
27k 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 ...
4
votes
1answer
7k views

Multiple conditions look up

I am having trouble building a spreadsheet, the problem its my data table. I want to be able to pull data from the table, something that will match data from column A, and column B (on the same row) ...
4
votes
2answers
12k 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 ...
3
votes
1answer
6k views

How to search multiple columns in Google Spreadsheet and return values in other columns?

I have two spreadsheets and would like to enter a value into cell A1, have it check multiple columns in a spreadsheet named 'data' and output information from other columns if it's a match. ----------...
1
vote
1answer
157 views

Group by and ordering inside a group in Google Spreadsheets

I have the following data in a Google Spreadsheet: 1 shiplu rice 2 sharmin rice 3 sharmin fast food 4 sharmin salad 5 rafiq burger 6 nazia noodles 7 rafiq salad 8 rafiq ...
1
vote
1answer
2k views

Repeat a FILTER or ARRAYFORMULA n times?

I would like to repeat an ARRAYFORMULA or FILTER n times. Let's assume I have: A 1 a 2 b 3 c Now I set =ARRAYFORMULA(A1:A3) and I get the same output. But now I want it to repeat n times, let'...
3
votes
2answers
10k 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 ...
2
votes
1answer
732 views

How to make a query with Google Spreadsheets that returns items from past 15 days?

I have tried using: =QUERY('Noah''s Weight'!A:D, "select * where A > date ' " &text(TODAY()-15;"yyyy-MM-dd" ) &" ' ") Row 1 is the headers, column A is the time stamp from the form, ...
2
votes
2answers
4k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: =query(ImportRange("...
3
votes
1answer
11k views

How to filter, SUM and display multiple values with multiple conditions added daily?

I have a spreadsheet that collects dozens of payments made daily through different means. For example, utilities bills, credit cards bills, personnel fees, expenses, etc. They are made in cash, by ...
9
votes
5answers
36k 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?
3
votes
1answer
8k views

How can you add a conditional statement to a =QUERY function

How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or ...