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.

4
votes
1answer
3k views

QUERY where column IS NOT EQUAL to ANY cell in column A of a second sheet

I want it to pull the max value from column F where the corresponding row has C matching the number 14, and A does not have any exact text matches in any cell in column A of a separate sheet. I feel ...
1
vote
1answer
62 views

Is it possible to filter out duplicate rows using the QUERY() function only and how?

I receive answers via a Google Forms, which I want to put in a pivot QUERY. I made a query that does the job for all rows, but in the case of duplicate answers, I only want to include rows with the ...
2
votes
1answer
5k views

Query function with multiple criteria

I want to use the query function in Google Sheets to filter data from one tab (see data below) to another to meet the following criteria: F – No. Order = greater than 1 C – Area = City D – Color =if ...
0
votes
1answer
668 views

Is it possible to edit data pulled using a query formula?

I have simple query formula that pulls rows of data from one master sheet(A) to another tab(B), but like all query data I can't edit the data on the tabs(b). Is there a way to make the data editable ...
-1
votes
3answers
1k views

Query with OR statement in Sheets

I'm trying to get a query working and so far this works: =QUERY({Sheet1!A2:G; 'Sheet2'!A2:G; 'Sheet3'!A2:G;'Sheet4'!A2:G}, "where Col1 is not null order by Col1 asc") When I add the second condition ...
2
votes
1answer
203 views

Tech Inventory - Forms Data Manipulation In Google Sheets

I'm trying to set up a school technology inventory spreadsheet using google forms. Here's the spreadsheet. The header columns are: A timestamp B name C room The data columns are: D ...
1
vote
1answer
21 views

How to display data in QUERY formula cell?

I have an empty cell where my QUERY formula is written, and I would like to display data in that cell. I was hoping to display either the first value of the results in that cell, ie, 'Direct' and '...
2
votes
1answer
3k views

Query Multiple Ranges with Results in Row

I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return. ...
1
vote
1answer
2k views

How to subtract two columns in Google Sheets query?

I checked the documentation but couldn't find a way to subtract two columns from each other. Imagine you have two columns and you want to construct a query that return the subtraction of those two ...
2
votes
2answers
2k views

FILTER or QUERY multiple ranges with blanks removed

I am trying to query or filter multiple ranges but am running into a situation with blank cells as I need all the ranges merged without blanks inbetween. Here is the sheet which has both my query and ...
1
vote
1answer
361 views

How to create chart of stats grouped per month and per user doing them

I have a sheet that counts items acted on by certain users. The problem is that every day the sheet gets refreshed, not with the items done that day, but with the items done up to that day. For ...
0
votes
1answer
841 views

Help with ordering a Google Sheets =QUERY

I'm trying to add an ORDER by F to the following query, but every time I do it returns a syntax error. ORIGINAL QUERY (working) =QUERY(data!A2:Z, "select G, E, F, I, J WHERE A = " & C1, "") ...
1
vote
1answer
1k views

Using query in Google Sheets, function will not pull over all data [duplicate]

I'm using the Query function in Google Sheets to pull over data tracking student behavior (includes name, ID, date, grade, etc.) The query function will pull over everything except the letter 'k' in ...
0
votes
1answer
356 views

Import specific columns from another Google sheet

I used this query after studying from Google: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_Mh5lEqBh1OBzuW5U1Fvr1MK_6doIvKmV23YKp4Fu0c/edit#gid=529591601","'iOS'!E:G"), "Select Col4",1)...
0
votes
1answer
448 views

Assigning responses to different tabs based on the month on timestamp

I am collecting responses from a form and would like to separate them into different tabs based on the month of the timestamp on the response. Kinda lost on using the date/ month function in the query....
-1
votes
1answer
1k views

IMPORTXML shows “array result was not expanded because it would overwrite data in the C4” error

I am able to fetch data from multiple URL's listed in a single column in Google Sheets using IMPORTHTML/IMPORTXML formula. However, when I drag the column with the formula it overwrites the existing ...
-1
votes
1answer
305 views

Query a column where numbers are mixed with blanks

I have a query where in a column there are numbers and blanks. It seems that the query function is treating everything in the column as string and not numbers. So when I sort, it doesn't sort ...
-2
votes
1answer
140 views

Count in Query not returning anything

When I use the syntax: =query(Clients!A:J,"select H where (H='Yes')",-1) I only get "Assigned" as a result, no # of records.
1
vote
2answers
18k views

Help with a “=QUERY(IMPORTRANGE(…” formula in Google Sheets

Having trouble with a =QUERY(IMPORTRANGE(... formula in grabbing a value from a single cell in another sheet, but only in the row where a particular text value exists in another column in that row. I ...
1
vote
1answer
281 views

Matching a cell value to a category from patterns table

I'm trying to categorize a cell value in Google Spreadsheets, based on a categories table, so that depending on the value in the cell, the correct category will be determined. Given a categories ...
0
votes
1answer
434 views

How to sort Google Sheets using a criterion [duplicate]

Background/Business Case I'm a recruiter who uses spreadsheets to organize the work I've done with candidates with respect to a certain job vacancy. Like streak, I would like to have a pipeline ...
1
vote
1answer
2k views

How to return array of cells from a table in Google Sheets

We have # EXAMPLE TABLE with product SKUS on the top row, and the first columns is ingredients. Each SKU row is showing the value of the ingredient row. What we want to do is have another sheet with a ...
0
votes
1answer
48 views

How to add a criterion to the data range in a Query

I have this formula: =ArrayFormula(QUERY({'LEADs oGV'!U:U,'LEADs oGV'!U:U},"select Col1, count(Col2) where Col2 IS NOT NULL group by Col1 order by count(Col2) desc limit 3",0)) The data range is ...
1
vote
1answer
374 views

Count blank cells in query formula

I am ranking answer for a form. and I came up with this formula =ArrayFormula(QUERY({'LEADs oGV'!U:U,LEN('LEADs oGV'!U:U)}, "select Col1, count(Col2) where Col2 > 0 group by Col1 order by count(...
0
votes
1answer
20 views

How to combine the numeric values of all columns with a similar second text field

I have a Google Sheets like this: Hours | Project 2.5 ABC 0.5 DEF 1.0 ABC 0.25 GHI 1.5 DEF I would like to create an output that shows me all combined hour values on a per-project ...
1
vote
1answer
278 views

Pivot table on the results of a query

I have a sheet that consolidates raw inventory and sales. Raw inventory takes the first few hundred rows. Then I have a query statement that pulls sales data from Cust_Sales. When I run a pivot ...
2
votes
1answer
1k views

Query specific values based on date ranges

Note: This doesn't have to be a query. Whatever works best! I have the following data (anonymized) set in a Worksheet (File Upload Data) A B C D E ...
0
votes
1answer
2k views

How to create a dynamically updated floating table

So in Excel I'm able to create a floating image which references a set of data and updates dynamically, which effectively creates a dynamically updating floating table. (See How can I create a ...
3
votes
1answer
36 views

Is there a workaround to enable two differing data types to be pulled in a Google Sheets query?

I understand that the results of a query will be of whatever data type majorly dominates a column of data, and the rest will be set to null and appear blank. Is there any sort of workaround for this, ...
-2
votes
1answer
552 views

Conditional data input validation in Google Spreadsheet

I have spreadsheet that contains three pages: Projects Tasks Tasks are related to Projects Calendar Related to Projects and Tasks. When I select Project in Calendar and go to Task drop down I ...
3
votes
1answer
299 views

IMPORTHTML data is sporadically changing to incorrect format

I'm hoping you can help me with my problem using importhtml with Google Sheets. It is a problem similar to Trying to use Google Sheets importHTML() to import a table. It forces content to a date ...
1
vote
1answer
44 views

How can I query for a string on a shared Googlesheet from my Googlesheet?

A business partner has shared their Google sheet with me and I have view only access. I would like to query a field on column C and look for a String value (which is a ticket number) from my Google ...
3
votes
2answers
2k views

Shortcut for multiple columns in query?

What's the shortcut for =QUERY($A:$K, "select A where B contains '"&N1&"' or C contains '"&N1&"' or D contains'"&N1&"' or E contains '"&N1&"' or F contains '"&N1&...
1
vote
2answers
191 views

COUNTIFS Cell Reference Range

I'm trying to get an array into the criteria of a COUNTIFS function. Here is a sample of the data: Name Type Location Progress Name_1 Type_1 Location_1 Stage_1 Name_2 Type_1 ...
-1
votes
1answer
539 views

Using date as filter for my query formula

I was having problem with my query formula. it does not filter the range of my values which depends on the date ranges that I have put up. Here is my formula: =query(all!B2:G, "select B,C,D,G where ...
-1
votes
1answer
38 views

Is there a Google Sheets functions equivalent of mysql_fetch_row? [duplicate]

Is there any way to get the row result in Google just like mysql_fetch_row that returns only the row base on query statement? I hope there has a formula for this. Using this formula =Query(...
0
votes
2answers
346 views

Data transformation in Google Sheets

Because I am using Google Forms to collect data the source of data is not what I want so I need to create a tab and transform the data. Here is an example of the Google Sheets (Exported from Google ...
1
vote
1answer
1k views

How to exclude rows against multiple filter criteria?

I have a sheet where I don't want to see any rows that contain either a "+" or a "[" in a certain column... Can't figure out how to do that. The built-in Filter function only lets me add one ...
0
votes
1answer
34 views

How to get corresponding cells in a column for all cells in a different column that match some criteria?

I have a Google Spreadsheet that goes something like this: 3/10 A 50 6/10 P 600 7/10 Q 75 10/9 A 750 ... I want all elements of Column 1 and Column 3 where Column 2 matches ...
3
votes
1answer
551 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
1k 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
448 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
74 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
117 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
34 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
234 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
284 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
54 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
510 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
8k 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, ...