Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
954
questions
0
votes
1
answer
590
views
Google Sheets Query & Indirect Formula
I'm trying to figure out how to dynamically update a section of my google query taking a value from another cell.
I'm filtering my query with a date, e.g. WHERE A Date > TODAY()-10
What I'm trying ...
0
votes
1
answer
491
views
Google Sheets Query Not Loading on Refresh
I have a function that works when I paste it in somewhere, but if I refresh the page Sheets will say it's calculating formulates and finish but won't load any results besides the column headers.
I ...
- 21
1
vote
2
answers
650
views
Query formula to add a column in Month/Year format from existing date values
I have a data set where 1 of the columns contains dates in "YYYY-MM-DD" format. I'd like to use a single query formula to select some columns from that data set, including the date column. ...
- 77
0
votes
2
answers
31
views
Get summarised data
I have a table
Name
Salary
Desc2
Desc3
ABC
9
X
X
ABC
9
X
X
ABC
9
X
X
XYZ
4
Y
Y
PQR
1
Z
Z
PQR
1
Z
Z
PQR
1
Z
Z
I want it something like :-
Name
Salary
Desc2
Desc3
ABC
27
X
X
XYZ
4
Y
Y
PQR
3
...
0
votes
2
answers
425
views
How to calculate the remaining working days in a month
I have =INT(B8-NOW())currently running in a cell to show me the remaining days in the month (B8 being a date)
I want to know if there is a way that the output number can calculate only working days, ...
1
vote
1
answer
2k
views
google sheets query function where A matches string in a cell
I am trying to use a cell value to impact a query. The issue seems to be that the cell value is a string which I am unable to use as a dynamic object in the query.
I have based my findings and ...
0
votes
0
answers
91
views
How to stick rows to the column that genarated by the query
I have two sheets, Data1 and Data2. The first column of Data2 is generated based on formula =QUERY(Data1!A2:A) so it just mirror the Data1 A column.
The rest of the columns within Data2 are manually ...
- 189
1
vote
0
answers
8
views
Auto Fill but changing only certain cells
I am trying to make a QR code attendance system and try to organize it so I can see what student comes on what day. I am trying to auto fill everything so I don't have to type everything for every ...
- 11
2
votes
0
answers
35
views
How to link 2 sheets, so when a cell on sheet1 is modified, sheet2 got updated automatically and viceversa
I am trying to have the same updated values in two differents sheets of google spreadsheet.
So when I edit sheet1, the the values in sheet2 got updated. Also the opposite: when I edit something in ...
1
vote
0
answers
14
views
Query Filtering Corresponding Columns
Formula work in progress:
={query(C3:G82,"select * where D like '1' order by C desc, D asc LIMIT 1",0);query(C3:G82,"select * where D like '2' order by E desc LIMIT 1",0);query(C3:...
0
votes
1
answer
645
views
Reference cell url when using filter or query fucntions
I am using functions like FILTER or QUERY to bring data from one sheet to another.
=QUERY(Data!A2:$D, "SELECT B WHERE C = 'fragment' ")
=FILTER(Data!E2:E, Data!C2:C = "fragment")
...
- 189
0
votes
1
answer
61
views
Formula always return one value for each cell
I was querying a hyperlink row from one sheet to another using this formula:
=QUERY(Data!A2:$F, "SELECT E WHERE C = 'fragment' ")
However I discovered that it copying only text but not the ...
- 189
0
votes
1
answer
25
views
How to replace multiple formulas with single one
I have this formula that I am duplicating for each column within a row:
=HYPERLINK(CONCATENATE("http://mysite.com", B2), "url")
I am wondering is there anyway to replace that ...
- 189
0
votes
1
answer
2k
views
sum two grouped columns in Google Sheets QUERY Language
I want QUERY command to provide me with the sum of two columns, and group the result by Column A.
The following is not working (parsing error)
=query(Ledger!A2:G,"SELECT A, sum(D+E) where (B = ......
- 639
1
vote
1
answer
71
views
Joining multiple sheets horizontally
I have a number of spreadsheets
I want to join them horizontally by Id.
How would you achieve that?
p.s I should mention that the id order could be different in each spreadsheet
- 189
0
votes
1
answer
12
views
How to indicate the source sheet when merging multiple sheets into one
There is a lot of tutorials that show how to merge multiple sheets, so basically there is 2 formulas:
={FILTER({sheet#1-range},LEN({sheet#1-range-first-column})>0);
FILTER({sheet#2-range},LEN({...
- 189
1
vote
1
answer
17
views
Which formula can add the same word next to the query result lines // example
I made an example to support what I am trying to explain in the title.
File is https://docs.google.com/spreadsheets/d/1yD49_As8nM6gLq2A1J9eEEoIVZadxfVQBq-LPSDIPLA/edit#gid=0
The objective is : I query ...
- 11
2
votes
1
answer
644
views
Query to extract unique values from 1 column and add matching numeric values
I have multiple sets of 2 columns (COL_LEFT and COL_RIGHT) and would like to extract all the unique values (except the blanks) from COL_LEFT and display matching values from COL_RIGHT alphabetized by ...
- 77
0
votes
1
answer
251
views
Add a constant number to google sheets query result
I have data in two columns labeled Email and 01 respectively.
I want to use a query to rearrange the data so that there are 3 columns:
Columns 2 and 3 will contain the data from my original two ...
- 9
1
vote
1
answer
113
views
How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar
Employees are selected in their corresponding shift on each month tab in a dynamic calendar
See 092022 tab in my example:
I want Pemanence tab will be able to retrieve automatically the values in the ...
- 53
1
vote
1
answer
40
views
Query meeting 2 conditions
I am trying to create a query that pulls information based on two drop downs. The first drop down searches "staff" or "student" in column C of my form response tab, and the second ...
- 11
3
votes
2
answers
165
views
Combine different sheets with importrange for dynamic data from google forms
I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to ...
- 39
1
vote
1
answer
504
views
Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets
I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of ...
- 11
1
vote
2
answers
118
views
Pull a column of data from one sheet to another based on a matching criteria
I am new to Google sheet formula and need some help from the community.
I have two tabs from a spreadsheet, A and B:
Both tabs basically have the same number of columns (col1, col2 and col3) except ...
- 113
1
vote
0
answers
41
views
How do I filter in IMPORTRANGE using SEQUENCE and ARRAYFORMULA?
I am having issues with including a FILTER option in my current working formula listed below:
=QUERY (IMPORTRANGE("1n7qMGR9p8xD-8tyPIhfrcyBEsW8FAUMJMHmgxM7xIVI","Consultant Directory!A6:...
- 11
1
vote
1
answer
138
views
How can I return more than one match result from the same search key? [closed]
Link to sheet used: Testing Sheet
Thanks to another answer, I was able to match and return a single word from a search_key being compared to a specific range using the following formula:
=UNIQUE(...
- 11
1
vote
0
answers
30
views
Add more conditions to the formula
=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
"select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
- 11
1
vote
0
answers
519
views
Query a sheet based on checkbox and order according to check order
In Google Sheets, I have two sheets that work together and I want to achieve the following:
I have a sheet1 with information in rows and a checkbox at the beginning
When the checkbox is checked, the ...
- 111
1
vote
0
answers
32
views
Trying to sort the dates from newest to oldest on google sheets [duplicate]
The dates on this column are filled in by a formula. Something about the formula i think might be causing my issue. This is the formula:
=QUERY({Tables!A1:K;Chairs!A1:K}, "SELECT Col1, SUM(Col11) ...
- 21
1
vote
1
answer
185
views
Shopping List - If true ignore cell
I've made a spreadsheet that tracks what items I need to buy based on what recipies I want to make during the week. This is generated using a few different formulas and then displayed in column C.
I ...
- 13
1
vote
0
answers
46
views
Problem with adding query function with another function
newbie here!
I was testing and practicing the query function, when I came across this function on a website
={QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) '...
- 11
0
votes
0
answers
87
views
How to filter and sort according to a preset order for a Query in Google Sheets
In Google Sheets, I'm using a query to pull all my data from different tabs into one master sheet. And now I'm wondering how to sort the data according to a preset order. The order I want to sort to ...
- 5
0
votes
1
answer
22
views
How to find current employee fee based on name and date from a different sheet
I have a spreadsheet with all employees work reports with info about time spent on a job and other details.
I can't figure out how to get the fee the employee is supposed to get paid because I can ...
- 183
-1
votes
1
answer
64
views
Google Sheet Query Filtering doesnt seem to work numeric values
=IF(E3="Met notificatie";QUERY(CSVupload!A2:I;"select F, D, E, C, B where A = '"&C3&"' AND I>=0 " );IF(E3="Zonder notificatie";QUERY(CSVupload!A2:I;&...
- 7
0
votes
1
answer
58
views
Get a count after a specific date grouped by person
I have a Google Sheet with dates in Column C, alphanumeric in Column E, and Employee Names in Column F. I am trying to figure out how to build a query to give me a count for each employee listed in ...
- 13
0
votes
1
answer
15
views
importing data from several sheets and average without including 0s
I have the following formula:
=query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:...
1
vote
1
answer
65
views
For Google Sheets, what is the function that will populate a cell with specific text when pulling data from multiple tabs of another sheet?
I am building a Google Sheet that pulls data from another Google Sheet with multiple tabs. The source sheet doesn't include one particular data point (i.e. business name), and I want to populate the ...
- 11
0
votes
0
answers
264
views
Using Query & Import Range function to import data range with a date filter
I have been able to use the the Query and Import Range function perfectly.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/13eoPsqGik4ZWFGPURrC9RYdlOuxdbCbQWXzWA9LC1L4/edit#gid=0",...
0
votes
1
answer
9k
views
Google Sheets query() "Unable to parse query string" ADD_COL_TO_GROUP_BY_OR_AGG
I have a sheet where in Column B I have a product name and column D I have the total order value for that product.
And then every row is the individual order.
I've been trying to use query so I can ...
1
vote
1
answer
230
views
Is there a way to query and reference a cell that will change?
Alright, looking for some help. I think I am close to the solution, but I could use some help getting it over the finish line. This spreadsheet link is a replicated document of my situation.
...
-1
votes
1
answer
499
views
Formula to select the name associated with lowest value from data set & a random name from the lowest options if tied
In Google Sheets, I have 4 Names associated with 4 data points that update throughout the month. I'd like to auto pick the name associated with the lowest data point. If there are 2, 3 or all 4 that ...
- 1
0
votes
1
answer
1k
views
Changing timezones for separated cells
So I currently have a work schedule on Google Sheets, and we all work remotely, all over the country, so I want to add a dropdown menu that one can select to change the time zones of the current times....
0
votes
1
answer
1k
views
Search across multiple columns for a value(s) return multiple row results
I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps.
Scenario 1:
Looking at the photo.
I want to search columns C through M for a selected color(s)...
0
votes
1
answer
338
views
Query only returning when 6 or more rows of data are entered
I am using the query function to gather data from other sheets in the same workbook. It only pulls over the data from the "teacher" sheets when at least 6 rows of data are entered. The 6 ...
0
votes
1
answer
202
views
Finding average based on criteria (non-contiguous cells)
I was wondering if someone could assist me. I am trying to research it myself but have become stuck.
Here is a sample of a Subject Mark-Book. Different units have different grade boundaries attached ...
0
votes
1
answer
479
views
Count number of occurrences in Google Sheets, with multiple criteria and not considering duplicates in the row
I'm looking to count the number of rows that contain a certain text (specified in the adjacent cell), from a specific range, on the basis that the criteria have been met.
I currently have this formula ...
0
votes
0
answers
912
views
COUNTIF function in one column if it meets a condition in another column
I would like to count the number of assets I have that are "In Stock" and are "MacBook Air"
See screen shot. I have tried the COUNTIF function. But having trouble writing the ...
0
votes
1
answer
38
views
How to make a query using 2 variables/inputs from 2 different drop down lists
I have a sheet of data that I've been using query functions in. It has worked great so far for single variables, but now I'm trying to make a query from 2 variables, referencing 2 different drop-down ...
0
votes
2
answers
978
views
How to fill a new google sheet with all rows that contain a specific value in a different sheet?
Imagine you have a google sheet which lists your favorite shops, types of shirts they have, and their price in separate columns. To find out which store has which shirts, you wish to filter out all ...
0
votes
0
answers
116
views
IMPORTRANGE contains specific columns with multiple answers
Example, one person can enter 5 events with several options for participating (both days or Sunday only or 1st go....) how do I get master1 sheet to only show contestants on Saturday and master2 only ...
