Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
875 views

Adjusted script to look for column header name, not the numeric representative of the column

From this discussion, I have a script that nicely updates a column's cell with the date another column's cell was changed. This works well because the TODAY() function always updates to today's actual ...
0
votes
0answers
1k views

google sheets QUERY formula for empty instead of #VALUE! when other table is empty

I am trying to get rid of #VALUE! prompts from the QUERY function in Google Sheets. The closest question I could find was this one below, but it won't answer my question: Google Sheets Query Get Row ...
0
votes
0answers
74 views

Google spreadsheets: How to get average session per user instead of all users?

In google spreadsheets I'm trying to get the average session per user from Google Analytics, so I tried ga:avgSessionDuration on metrics, ga:date in dimensions, it returned me the time in seconds, so ...
5
votes
1answer
1k views

How can I stop Google Sheets charts from rounding down durations?

I've got a Google Sheets chart that displays the average pace of a day's rowing machine workouts (e.g. average the time to row 500m). It looks like this: The Query I'm using to get the data is this: ...
0
votes
1answer
80 views

Google sheets query multi conditional date cell ref

I have a doubt regarding a change in this formulas in sheets: =INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2) =INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");...
-2
votes
1answer
227 views

Investing.com data uploaded but i am not able to use the data for email alerts.kindly help me [closed]

I have configured to fetch the data on every 1 min from investing wesite. The table data is fetching from website. while trying to update date when 15 minutes status D column change to either strong ...
3
votes
1answer
9k views

Google Sheets Filter Multi Column List With Another List Using A Function

I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C". List "A" has multiple records for each student ID number. List "B"...
1
vote
1answer
757 views

SUMIFS with OR criteria

I'm trying to write a sum function for all entries in a certain month (>= start of month, < eomonth + 1) where a certain field has one of several values. (For example: All entries in July, where ...
0
votes
2answers
382 views

Google Sheets Query cannot find some numbers

I'm trying to use a QUERY function to get a maximum value of a range where value in another column equals to a specified value with =QUERY(L$4:M,"select max(M) where L="&K1&" label max(M)''") ...
0
votes
0answers
18 views

Document with Hyperlinks from Google Sheets

I use Zapier to automatically send information from blog posts in feedly to a Google Sheet. I want to create document as automatically as possible. I have these columuns: Author Source Title ...
0
votes
1answer
2k views

Using Conditional Formatting to highlight data in new table that matches data in separate table

Sheets 1 thru 6 all have an Array Formula pulling data from other sheets. The Array Formula runs multiple Query formulas that skip over rows with missing cell data and then goes back to those rows and ...
6
votes
2answers
15k views

Stacking multiple query formulas throws “an Array Literal was missing values for one or more rows”

I am attempting to run multiple queries where it adds rows where if F is blank, then it skips those rows, then runs the query again and replaces row F with row G. I have a much more complicated ...
4
votes
1answer
7k 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
147 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
14k 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
2k 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 ...
0
votes
3answers
6k 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
259 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
47 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 '...
5
votes
2answers
7k 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
4k 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
3k 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
835 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
2k 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
3k 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
779 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
635 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
3k 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
449 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
194 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
33k 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 ...
2
votes
2answers
6k views

COUNTIFS with multiple OR

I have two sheets, one is a dump sheet of values like below: and the second sheet is using values from this to the total. I am trying to get the total count for the corresponding zone and test name ...
1
vote
1answer
391 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
833 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
3k 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
56 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
605 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
405 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
2k 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
4k 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
63 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
737 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
465 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
46 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
4k 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&...
2
votes
2answers
349 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
742 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
39 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
514 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 ...