Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
925
questions
1
vote
1
answer
96
views
sheets query requires splitting last returned column by comma
Table with data in columns A-N. Data in column 'M' contains CSV data.
Using this query can return data, need what's in 5th returned column (E) to be split into additional columns through one command. ...
- 11
2
votes
1
answer
98
views
Google Sheets Query function sort breaks when using drop-down filters
I have a query in my Google Sheet to pull student testing data from a master list based on drop-down menu options. My goal is to sort the filtered data by Column L (Completion Date) so that I can ...
- 23
0
votes
2
answers
201
views
Google Sheets: FIND multiple cells containing specific text
I have a Google Sheets table and one of the columns has email addresses.
I'm filtering that column using this custom formula that works:
=FIND("@gmail.com",I2)
But I need to be able to ...
0
votes
0
answers
65
views
How can I combine data from 200 different google spreadsheets to one master google sheet
I have about 200 google spreadsheets. Each of these google spreadsheet has multiple sheets. I need to gather the data of one of the sheet in all the 200 google sheets to one combine sheets. it is like ...
- 1
0
votes
1
answer
49
views
Add data to certain columns if conditions of separate 1 or more conditions for data in each row are met
I have a table with "manufacturing quotes" and each quote has 1 or more parts in it (please see screenshot below). The data in parts1-3 (columns E-J) are filled, however, in reality, the ...
1
vote
1
answer
79
views
Lookup and Conditional Formatting
I have some columns in sheet 1 (here in image O and P columns), and in another sheet (here in image R, S, T, U, V, W). I want to check the matching Date and Number from sheet 1 with Date and Number ...
0
votes
1
answer
31
views
In Google Sheets how do I return the total number of scenarios that match two conditions?
I am creating a summary within a large spreadsheet and want to be able to show the total number of items that meet two criteria.
Criteria A is in a range in one column, and Criteria B in another. Both ...
- 3
0
votes
1
answer
85
views
Google Sheets - Sum Filter of Table 1 in each row of Table 2
I have table of transactions with categories (let's say 1-5) and assigned dollar values, and another table with a list of each category. I want to sum up all the matching categories from Table 1 with ...
- 25
1
vote
1
answer
423
views
Stack two sets of data into one in google sheets - different columns
I am looking for a solution that helps me stack two sets of data (from diff. columns, different order) into one data sets
EXAMPLE:
columns names + order tables1:
first name, last name, city, age, ...
- 11
0
votes
1
answer
41
views
What is the best approach for filtering rows and counting unique dates in a Google Sheet?
I have a shared activity log of bike rides in Google Sheets that looks like:
[A] Date
[B] Name
[C] Miles
1 Nov 2, 2022
Joe
2.1
[2] Nov 2, 2022
Ray
1.5
[3] Nov 3, 2022
Ray
3.1
[4] Nov 4, 2022
Joe
...
- 155
1
vote
1
answer
23
views
Get series of date
I want a column with date only, like this
DATE
11/05/2022
11/05/2022
11/05/2022
11/06/2022
11/06/2022
11/06/2022
11/07/2022
11/07/2022
11/07/2022
And list goes on. I just want to know how ...
0
votes
1
answer
398
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
227
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
100
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. ...
- 65
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
39
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
158
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
51
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 ...
- 179
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
26
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
369
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")
...
- 179
0
votes
1
answer
45
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 ...
- 179
0
votes
1
answer
23
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 ...
- 179
0
votes
0
answers
16
views
Apply a function for every item within a query
I have a query function that returns a specific columns by condition:
=QUERY(Data!A2:$D, "SELECT B WHERE C = 'fragment' ")
I want to apply an excel function for each item within a QUERY, is ...
- 179
0
votes
1
answer
1k
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 = ......
- 629
1
vote
1
answer
32
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
- 179
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({...
- 179
1
vote
1
answer
12
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
73
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 ...
- 65
1
vote
1
answer
216
views
Add a constant number to google sheets query result
how do i get this result? i want to get the number one in every row
- 11
1
vote
1
answer
58
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
33
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
1
vote
1
answer
119
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 ...
- 11
1
vote
1
answer
323
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
69
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
31
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
83
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
1
answer
16
views
i'm not sure what function I want for this
I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
1
vote
0
answers
25
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
313
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
23
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
75
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
29
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
35
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
18
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
vote
1
answer
32
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;&...
- 11
0
votes
1
answer
25
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
41
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