Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
791
questions
0
votes
1answer
9 views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
0
votes
1answer
12 views
Get value of merged cells for query function
I'm trying to find if someone has been scheduled in the AM and PM in a table like this:
So I want to write something like this:
=QUERY(A13:B1002,"select A,B where A matches 'AM' and B contains '...
0
votes
0answers
20 views
Search all tabs in entire spreadsheet (Google Sheets)
I am making a simple task manager for three users in Google Sheets.
The data is added in separate project sheets:
A summary page splits all data by user (from the "Who" column), and should ...
0
votes
0answers
9 views
How to add a certain amount of months depending on the starting date?
I'm making an inventory for my houseplants.
I want to add a formula that can tell me when I have to give fertilizer to my plant, depending on the date I last gave it some.
Thing is: when I gave ...
1
vote
2answers
54 views
Using Google sheets `QUERY` instead of `VLOOKUP` - function returning empty - why?
I need to use use a "VLOOKUP" type formula to match values across two data sets, but instead of using 1 key value as you would normally with VLOOKUP in need to use 2 key values.
Please see ...
0
votes
2answers
22 views
Using query/import range to combine multiple columns from Workbook A into one column in Workbook B in same order
I have one Workbook A where a client will fill in either column A or B with a value (if there's something in A1, B1 will be empty, etc.), and I'd like to connect it to Workbook B in one column. It's ...
0
votes
1answer
55 views
Query with long search criterion
In a Google sheet I have to search for a column containing a specific number.
My search is
=Query(OPGAVE_TRANS!$A$1:$AD$23;"select AD where A="&A1&" OR B="&A1&"...
0
votes
1answer
13 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,...
0
votes
2answers
17 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?
0
votes
1answer
12 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
1answer
21 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
1answer
21 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 ...
0
votes
0answers
16 views
Hyperlink Image in Google Sheets to cell in same sheet
I am wondering if there is any way to make an inserted image in a Google Sheets spreadsheet that will link to/ reference/highlight a cell in the same document?
I am making a calendar within a content ...
0
votes
1answer
53 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
1answer
21 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
1answer
27 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 ...
0
votes
2answers
24 views
HELP Needed for Syntax to use IF Statement Included in Query (Google Sheets)
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
1answer
21 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
1answer
39 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 ...
0
votes
1answer
45 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)
...
0
votes
1answer
13 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 (...
0
votes
1answer
23 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 ...
0
votes
1answer
22 views
Google Sheets Query: 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 ...
0
votes
1answer
23 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
1answer
55 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 ...
0
votes
0answers
10 views
Totals on query table
I would like to get totals on the bottom of a table created with a query function. I have managed to total one column but when I try and get a total for the next column it displays the total 1 cell ...
0
votes
1answer
35 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 ...
-1
votes
1answer
64 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
2answers
50 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
...
0
votes
1answer
33 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 ...
0
votes
0answers
12 views
COUNTIFS+importrange
There are two different sheets 1 & 2. Both has columns named "number of files completed". I want to create sheet 3 where it shows number of files completed in both sheet 1 & 2 in a ...
0
votes
0answers
25 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
...
0
votes
1answer
33 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
vote
1answer
28 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 ...
0
votes
0answers
21 views
Query Across Several Columsn/Rows of Data
I have tables in a single sheet organized similar to this:
I want to write a query that can summarize the values of each line item uniquely. For example, total "Labour" across all 5 ...
0
votes
1answer
42 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
1answer
35 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 ...
0
votes
1answer
12 views
Google Sheets - Sum if matching options
I'm creating a cashflow management sheets that can count all my expenses and incomes, also to track the usage of my budgets. I can categorize each expenses by selecting from a list of options. My ...
0
votes
0answers
32 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 ...
0
votes
1answer
30 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://...
1
vote
1answer
19 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&"'&...
0
votes
1answer
18 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 ...
0
votes
1answer
15 views
Need help filtering entire rows from reference sheet to master sheet where column text meets criteria
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 ...
0
votes
1answer
36 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, ...
0
votes
1answer
61 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 ...
0
votes
1answer
53 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
1answer
37 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 ...
2
votes
1answer
33 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 ...
0
votes
1answer
29 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
vote
1answer
19 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, ...
