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
1answer
133 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,...
6
votes
3answers
10k 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
212 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
2k 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
65 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
55 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
857 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
4k 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
2answers
1k views

Numeric before query, query results not a number

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
644 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
94 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 ...
9
votes
1answer
37k 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
2k 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
33 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, ...
4
votes
3answers
7k 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
198 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
9k 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
5k 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
865 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
133 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
5k 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
2k 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
6k 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
1k 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
2k 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 ...
14
votes
4answers
18k 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
6k 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) ...
3
votes
1answer
7k 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
5k 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
110 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
7k 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
635 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("...
2
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 ...
3
votes
1answer
7k 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 ...
1
vote
1answer
238 views

Importing only certain rows

I am attempting to create a spreadsheet based off another spreadsheet. I only wish to import certain rows based on the second column of the first spreadsheet. If that column contains certain users ...
1
vote
2answers
3k views

How to use an IF statement over several cells?

I'm making a budget for myself. Currently, my Google Spreadsheets looks something like this: ----A---- -----B---- food 50 alcohol 20 food 120 dance ...
1
vote
1answer
132 views

Publish last 5 entries from a Google Form

We currently create and html table to list our last 5 Webinars. This is done manually. We are adding external contractors into the mix and would like to give them the ability to enter basic webinar ...
1
vote
2answers
7k views

How to GROUP and ORDER data in a Google Spreadsheets?

I want to group and sort a particular data set. I've prepared the data using a QUERY function and using other functions. Can I order by sum? This is an example file I created: order by sum
1
vote
1answer
4k views

Google Spreadsheet: Query function date formatting not working

I'm trying to make this query work on my spreadsheet on chrome Win7: =QUERY(A1:D5;"SELECT MAX(A), COUNT(A), SUM(C) GROUP BY (A) FORMAT MAX(A) 'MMMM' ") Everything is ok except for the ...
0
votes
2answers
4k views

Query multiple Google spreadsheets

I have the following sheets: ListA ListB ListC Note: at each sheet I have a column which contains YES or NO. How do I query all my lists (spreadsheets) where myBooleanColumn equals to YES/NO?
1
vote
1answer
739 views

How to make a query that returns rows if deadline is two weeks / one month / two months from today, etc

I have a spreadsheet that contains several columns with project information. I want to modify: =QUERY( 'Compilation'!A2:L; " select A, B, F, I, L order by A" ; 0 ) so it returns values in L that ...
4
votes
2answers
5k views

Get rid of row in result of Google Spreadsheets query

I use the following formula in my Google Spreadsheet: QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'") This returns the following (On two different rows that ...
74
votes
3answers
110k views

How to group data in a Google Spreadsheet?

I have the following table in a Google Spreadsheet: Name | Value A | 10 B | 100 A | 20 B | 200 C | 1000 How can I create another table based on a query: select name, sum(value), ...