Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
954
questions
0
votes
1
answer
294
views
Splitting a list contained within a single cell and want to associate/add text from a different cell in the same row to the new separated list
I am using Google Forms to collect information. In google sheet, I have rows of data and one of the cells contains lists that needs to be separated out.
=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,...
- 3
0
votes
2
answers
2k
views
Google Sheets - QUERY - SUM() based on empty column
I need to obtain the sum(B) if the column has empty values I want to reflect 0.
I am automating this process for multiple sheets using app-script and need to use QUERY.
Any suggestions?
- 3
0
votes
2
answers
51
views
Want Query to return first column and count across the row
I have multiple google sheets with student names and scores from different courses. I am trying to query those sheets to return the student's name and a count of completed assignments in a totals ...
0
votes
1
answer
28
views
Add one field in a queried Field
I have to work with a weirdly formatted dataset.
I have a sheet where lines represent different parts of a model (bike)
The first columns are checkboxes to indicate witch models the part is in.
So my ...
0
votes
1
answer
53
views
Google Sheet how to combine data from same sheet to other sheet
I have several tables on the same sheet with different numbers of rows and columns but basically have the same structure, now I want data from these different tables to be united in one master ...
- 3
0
votes
1
answer
141
views
How to use a query with a combination of If and Where
I have a data base on Google sheet. See the spreadsheet at this link:https://docs.google.com/spreadsheets/d/1NGkH56Ga95osN8eBI6d5MhV7SnoEPK4Oe7ymRJVK7M0/edit#gid=1262146092
It s a copy so feel free to ...
0
votes
1
answer
89
views
Query formula to import the data from multiple tab sheets
I am using a query to import the data from multiple sheets, however, the formula is skipping empty cells. Is it possible to not skip empty cells and keep the data in the correct cell on the Master?
...
0
votes
1
answer
164
views
How to sum one row of multiple selected columns where a col matches a condition, but return null string instead of "0" if no match
Say you have one spreadsheet like this:
A
B
C
D
E
F
Timestamp
Name
P1
P2
P3
P4
10/17/2021 16:28:47
Asha
1.2
3
.5
3.1
10/17/2021 23:01:38
Wei
0
0
0
0
Now, in another spreadsheet, you have ...
- 123
1
vote
2
answers
3k
views
Add constant number to query() result in Google Sheets
I've a QUERY() function that results into this automatically generated table:
A
B
S
2
F
5
G
1
I'd like to add a constant value to each cell in column B like (let's say 3):
A
B
S
5
F
8
G
4
- 11
-1
votes
2
answers
586
views
Make QUERY formula works when reference values are blank
I am trying to create a query to pull ALL reports available in the referenced table but then limit down the reports when options are selected.
The formula I have so far is:
=QUERY('Common Standard ...
1
vote
1
answer
192
views
How do I write a Google Sheets Query where I can use the user's input (cell reference) to determine the
The goal of this sheet I am working on is for someone to select the items they want on a report (I created a dropdown with data validation), and based on the options the user select, THIS will be the ...
0
votes
1
answer
3k
views
How to get list of values in one column for distinct values in another column
I need to do some analyses on a set of data for work and cannot figure out how to do this. I am familiar with Google Sheets QUERY, but could not do what I want and could not find anything like it ...
- 1
0
votes
1
answer
1k
views
Query Divide by Sum Column getting Error in Google Sheets
Looking to work around with error message "Add_col_to_group_by_or_agg:Col1" from formula below:
Query(Query({data range},"Select etc."), "Select (Col1*Col3)/Sum(Col1)",0)
...
- 3
0
votes
1
answer
22
views
Google Sheets - Preserving Data after Importing Range
I work in an organization where we give people jobs across 3 of our programs. I am trying to create a spreadsheet that can have a master list of all of our participants and which program they are in (...
- 1
0
votes
1
answer
222
views
Google Sheets Query - Divide by Sum(Col), Error
I'm doing a simple query but keep getting the error "Add_col_to_group_by_or_agg:Col1".
The formula is
=QUERY({L42:N63}, "Select (Col1*Col3)/Sum(Col1)",0)
What I'm looking for is ...
- 3
1
vote
1
answer
532
views
Order By and accented chars
In a Google Sheet, if we have a column with accented character strings, the sort order of menu option "Data -> Order sheet by column" correctly ignores the accents (ordered column A in ...
- 21
0
votes
1
answer
320
views
G Sheets - Can't seem to make not contains and is not null work together
I want to return results for col A & B where B is not blank and where F doesn't contain
any city in California so, ', CA'. I can get each operator to work separately,
=QUERY('805'!A1:F1105, "...
0
votes
1
answer
888
views
google sheet how to import cells based on value in different cells
So I have two sheets.
Sheet A tab X contains two columns, first one with an url for a google doc and in the other column text IDs.
Sheet B tab Y contains a column with text ID of several more files ...
- 3
0
votes
1
answer
2k
views
Filter Google Sheet based on multiple checkboxes
I have a Google Sheet with multiple columns filled with checkboxes with each row being for a specific product, above each column is another checkbox and the idea is to use these top checkboxes to ...
- 103
-1
votes
1
answer
88
views
Inventory Management where main list highlights items checked out/ in
I am working on an inventory management list where barcodes are scanned out and scanned back in. My sheet is attached to forms. I have set the item sku# on the main inventory list to highlight red ...
0
votes
2
answers
696
views
How to filter or query only selected columns based on a dropdown
I have a sheet with data, theres a section laid out 8x9 with a layout approximately similar to below (didn't recreate all 9 rows):
Red
Orange
Yellow
Green
Blue
Indigo
Violet
Grey
X
-
X
-
-
-
-
-
X
...
- 3
1
vote
1
answer
286
views
ORDER BY generating error in Google Sheets' QUERY
I'm getting a '#Value!' error when I try to sort data returned by a QUERY statement. This is the basic statement:
=query('Raw Data'!A:G,"Select A, C, D, sum(G) where B='instagram' and (D='Female ...
- 11
0
votes
0
answers
29
views
Sheets query: Losing string values? [duplicate]
How can I get =query to handle a column of mixed numbers and strings?
I have a column of accounts names. Some are purely numeric (e.g. 123), and some are alphanumeric ("x456").
A
123
...
- 1,247
0
votes
1
answer
300
views
Google Sheets: one query for Sum and Filter?
If I have a sheet with data like this:
A B
x 1
y 2
z 3
x 4
I can sum the values with
=query(A:B, "select A, sum(B) group by A")
How can I then filter this result? For ...
- 1,247
1
vote
1
answer
213
views
Google Sheets - How to use results from INDEX formula in a new QUERY function
In Google Sheets I have an =INDEX function which is producing a number of references for data in another worksheet.
I need to use those references in a QUERY function.
I've not found a good way and ...
1
vote
1
answer
78
views
How do i Import a rows from one Google Sheet to another with filter?
I have a Google Form, the answers go to Google Sheets.
I want to import answers from sheets (Аnswer) into new sheets but with a filter applied.
For example: from sheets (answer) into a new table, I ...
0
votes
1
answer
54
views
Search a Column of Data & Enter a Specific Value based on 2 criteria
I have a sheet, where I will enter a bunch of data with skus into 1 column, and have search terms somewhere else. What I want is to automatically, look at the SKU AND the search term, and then bring ...
- 383
0
votes
0
answers
120
views
Partial match two strings with wildcard operators both ways
I have two lists of property names. Sometimes a name in List A has extra characters; sometimes List B has the extra characters. I want to pull associated data from a column in the tab with List A and ...
- 1
0
votes
1
answer
40
views
How to show the data from another spreadsheet but only the latest week
I have 2 spreadsheet, the first one consists of all of the data of withdrawal and deposit of items. The second spreadsheet is where the summary of the data shown.
Here is the master data link:
https://...
- 105
2
votes
1
answer
25
views
Compare imported range with existing range, and filling corresponded data to the fields
I am not sure how to import data to corresponding cells.
query(IMPORTDATA("https://www.iftach.org/taxmatrix/charts/4Q2020.csv"), "Select Col4 where Col1 = '"&A4:A&"'&...
- 21
0
votes
1
answer
27
views
Needing to change a column to false when Data Validation is changed
I am using Google sheets and have a cell that has data validation and I am putting check boxes (I4:I) for people when items are received, they need to be changed to "False" when the Data ...
1
vote
1
answer
27
views
Need help filtering entire rows from reference sheet to master sheet where column text meets criteria [closed]
I feel like this should be a relatively simple issue but everything I write is clearly incorrect or leaves me needing to do work manually every time new data is made.
Basically I have a series of ...
- 11
0
votes
1
answer
171
views
Google Sheets: Joined queries registering as two arguments in IFERROR() function
I'm joining two queries together using an array: ={query(A1:B,"Select A where B is not null");query(C1:D,"Select C where D is not null")}. I have it wrapped in an iferror() that, ...
- 3
0
votes
1
answer
685
views
QUERY syntax using cell reference more than 1
I'm trying to use a cell reference instead of static values.
=QUERY(Responses!B1:I, "Select * where G = '"&B:B&"'")
I use code like this because the data is varchar, but ...
1
vote
1
answer
1k
views
Using query as a text join
A few days ago I reach the limit of 50000 characters in one cell, I found a solution with "query" but I need a delimiter inside the formula that I haven't been able to find:
This is the ...
0
votes
1
answer
476
views
Google SpreadSheets query: how to join "grouped by" cells in a list?
Suppose I have this table:
| Name | Amount | Category |
| name_1 | 10 | A |
| name_2 | 2 | B |
| name_3 | 3 | B |
| name_4 | 6 | C |
Thanks to this ...
- 103
2
votes
1
answer
119
views
Need to sum the number of instances of texts in a column using Query function in Google Sheets
Still a noob in Google Sheets. I am trying to create a sheet with the statistics with the data coming from Sales sheet . I want to sum the quantity of all items sold according to sizes. For ex. How ...
- 57
0
votes
1
answer
385
views
How to modify Query Options no_format to allow some format, but not others?
I have to replicate a Google Sheet, and am using ImportRange and Query together. I need Query also because the first sheet has urls under them, which I cannot have on the copied sheet. I was able to ...
- 1
1
vote
1
answer
33
views
If cell in Col A is blank substitute with cell in Col B
I'm attempting to combine two columns from another sheet into one through the QUERY function. My plan is to make it so if a cell in Col A is empty then a cell in Col B will be substituted.
However, ...
- 13
0
votes
1
answer
72
views
Combine data from multiple sheets to create a stacked line chart
I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
- 103
0
votes
1
answer
83
views
Cannot get Query to display time
I need help getting to show the result of a dropdown. If you look at C22, there's a dropdown for a specific date. I'm currently working on row 24 and have applied the formulas I think should work. I ...
- 1
0
votes
2
answers
439
views
How to make variables dynamic in ARRAYFORMULA/QUERY Combination
I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names.
I came up ...
1
vote
1
answer
2k
views
How to create Column Identifiers in Google Sheets?
I'm confused by Google's documentation where it states:
Columns are referenced by the identifiers (not by labels). For example, in a Google Spreadsheet, column identifiers are the one or two ...
- 279
0
votes
0
answers
45
views
What is the Google Sheet query for getting rows excluding non matching rows
The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...
1
vote
1
answer
312
views
What's the formula to select non matching rows by comparing rows in Google Sheets query?
In Google Sheets query,
I need to get only non matching debit rows by comparing rows say if amount debited (S1 flag - row 1) matches with amount credited (S1 flag - row 3), it needs to be excluded in ...
0
votes
1
answer
24
views
Filtering views based on a cell having a value
I am trying to sort out a spreadsheet which has multiple columns in but don't really know where to start!
I am looking to filter the sheet where column C has more than 3 errors for the ID on column B.
...
1
vote
1
answer
126
views
How to use use cell references in the WHERE part of a QUERY / IMPORTRANGE formula
Can you tell me what's wrong with my formula?
= QUERY (IMPORTRANGE ("url googlesheets database"; "Data range"); "SELECT Col1 WHERE Col1 =" A3 "AND Col2 =" B3 "")
The "A3" and "B3&...
0
votes
1
answer
3k
views
How to have a query wrapped in an arrayformula auto-populate many rows
I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
- 301
1
vote
1
answer
478
views
Google Sheets - COUNTIFS with timestamp
I have a column in a table that contains the date and time. This is the form
15.06.2020 09:43:24
17.06.2020 08:21:04
18.06.2020 12:11:34
or also
2021-01-21 11:57:34
2021-01-21 11:58:02
2021-01-21 11:...
- 13
2
votes
1
answer
346
views
No query result when one of the columns used in the output never has a value
I have a query where the output involves summing two columns. If either of the columns never has a value (given a set of filter criteria in the where clause), there is no result. Why?
Here is an ...
- 301