Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
654
questions
1
vote
1answer
20 views
Google Sheets: How do you perform a bulk find & replace using a list of search phrases and their replacements?
This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated.
What I'm trying to accomplish
https://docs.google.com/spreadsheets/d/...
0
votes
0answers
36 views
Getting column data and re-arranging it in rows, with a pattern, using formulas
In a worksheet of multiple sheets, I have Sheet1, e.g. with the following: (these rows will be less or more and are manually entered)
Sheet1
A B C
1 APPLE ...
0
votes
0answers
14 views
Google Sheets allow only one unique entry after VLOOKUP
=if(isblank(D2),"",vlookup(D2,Master!$B$1:$F$600,5,false))
I have this working which looksup a UID value to select a user. The users name is returned to the cell.
The problem is now I'd ...
1
vote
1answer
50 views
Search multiple columns for one value
This is roughly what my table looks like:
A B C D E F
Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
1
vote
1answer
74 views
Google Sheets: How to query until reaching a particular sum?
I have a sheet with a lot of data, but to keep this simple let's say column A has a product, column B has a date, and column C has an integer which can be blank, 1, or an integer greater than 1:
...
0
votes
0answers
30 views
Query all Google Sheets data from Google Drive?
How can I query all Dates from all Google Sheets in my Google Drive?
As far as I know, I can do multiple query for all the sheets using importrange, however, I have over 100 Google Sheets:
={query(...
0
votes
0answers
13 views
Conditional formatting google sheets with true false
I am trying to highlight cell A5-G5 using conditional formatting. The range I am applying is A5:G90 with custom formula =if(H5:H90,TRUE). However it's only highlighting just A1. How can I highlight it ...
0
votes
1answer
21 views
Sort a column by name with query between tabs
I am pulling information from one tab to another and I believe this is why it will not allow me to sort (because it is not sorted in the original tab). The easy solution would be to sort that tab. ...
1
vote
1answer
81 views
How to show Day of Week in Order by Day of Week in Google Sheets
I have the following formula that displays a total by Day of Week (weekday) from a date in J and amount in K
Example
J contains 1/1/2020 ... until 9/29/2020
K contains numeric value 123 ... 1999
=...
0
votes
1answer
38 views
Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column
I Included a Google Sheets link
=REGEXEXTRACT(A2:A3, "19..")
To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell
But, it ...
1
vote
0answers
25 views
Array formula to extract N non blank cell in Row 1-16
I have a google form where team coaches input which players played on a given day.
They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored.
When the ...
-1
votes
1answer
40 views
How to change specific cell to Zero
I'm currently using this code I got from Google. But the problem is it changes not only the range I specify which is N9:N14 but it goes til N54 whats wrong with this code
I want only these ...
1
vote
1answer
26 views
Is there a formula to automatically populate invoice with only the products that have been ordered and excludes the products not ordered?
I have a range of products in my order data sheet and I need help creating a formula that automatically fills up my Invoice sheet with only the products that were ordered.
Currently I can only ...
0
votes
0answers
30 views
Is it possible to use a cell containing date as data validation in query statement
Is it possible to use a cell containing a date (as data validation) in a query statement?
1
vote
1answer
21 views
Use importhtml to import a single cell vs a whole column
As the title would read, I am looking for a method to import data from a retail site to my current spreadsheet to have the prices update in real time. Unfortunately I am not educated in this so I am ...
1
vote
1answer
41 views
Aggregate data to show text in one cell
I need to put together multiple entries into one where the results is text and has to be in one cell.
From this
to this
1
vote
1answer
30 views
Insert query into a specific cell based on a dynamic reference
I have 4 queries. The amount of results from each will vary week-by-week.
I have a formula to calculate the number of rows of each query.
I want the queries set out in the Google Sheet so that the ...
0
votes
0answers
25 views
Query from multiple columns
https://docs.google.com/spreadsheets/d/1g7bKBV5Erd4XI8aMuDI278_g3GMbpy-f8UXqEinxUf0/edit?usp=sharing
Here is a sample sheet that has 4 different tabs with 4 columns per tab that I want to 'count' from....
0
votes
1answer
38 views
How to concatenate string in select clause of a google sheet query?
How can I concatenate a string in a select clause of google sheet query function ?
ex: =QUERY(DATAS!B:F; "SELECT B,C,D,'=image('&E&')',F WHERE upper(F) "&J4&" upper('&...
0
votes
2answers
67 views
How i can transpose criteria column and repeat the column A in google sheets
I want to transpose column criteria B C D E and column A repeat in every criteria.
I don't know the query. please help me.
This the link google sheets sample i made, my original data was thousand.
...
1
vote
2answers
25 views
Dashboard based on Todays Date - From a table
I have a list of students with responsibilities and I would like to create a dashboard in Google Sheets with information about the current month.
As an example:
then in September 2020, I want the ...
1
vote
1answer
19 views
Applying a custom sort to Google Sheets string column
I am trying to sort a table of rows by their first column that is comprised of strings with this format (capital letters followed by a dash followed by numbers).
Using the default string sort I get ...
1
vote
2answers
53 views
G sheet How to jump over no value cells with SUMIF function
I would to get the sum of the duration of the column B
+---+----------+
| A | 14:45:00 |
+---+----------+
| A | #Value! |
+---+----------+
| A | 13:34:50 |
+---+----------+
| B | 23:41:00 |
+---+-----...
0
votes
1answer
119 views
Insert Blank Row after Every New Value in Google Sheets
I hope someone could help with this one. I’m not sure if this is possible but I would like to know if there is any way to insert a blank row after every change of Data in ColumnA(Country) which is the ...
-1
votes
1answer
18 views
Parse Error on Google Sheet pull
I’ve setup a google form to autopopulate a google sheet, and pln to use the google sheet to autopopulate other google sheets.
I’ve been able to populate other tabs within the same sheet with the ...
0
votes
1answer
29 views
Filter the data of two different column
I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JIRA which consists of the following columns:
Issue Type Key Status Priority Summary ...
1
vote
1answer
73 views
Google Sheets: aggregate and extrapolate irregular data over time
I have a list in which I now-and-then (no fixed interval) enter the available budget.
Most of all I want to extrapolate this to a future trend of the coming 10 years. (no rocket science, more of an ...
1
vote
2answers
23 views
Want to use a cell to get a limit number
Here is my query. I want "select * limit 5") the 5 to come from a cell. How do I do that?
=ArrayFormula(AVERAGE(QUERY(SORT(Tabell!D4:D33,ROW(Tabell!D4:D33)*ISNUMBER(Tabell!D4:D33),0),"...
1
vote
1answer
46 views
In Google Sheets, find most recent comment for a domain
Using this sheet - https://docs.google.com/spreadsheets/d/1SFXBBlgX6KoffGqQ3HHsRmFcDurGiUjFLYXT3igy8Nk/edit?usp=sharing
On the Demo sheet, please help me display the most Recent Comment from the Log ...
1
vote
1answer
26 views
G Sheet - Sumifs for data from another sheet
I have a sheet with bank statements and a want to set up a summary sheet with the total amounts per client.
I know how to do that from within the statements sheet, e.g. summing up client A:
=SUMIF(B1:...
0
votes
1answer
31 views
How to filter out empty cells in Google Sheets With Query & Importrange
I am trying to use this formula but it's not working. Do you hava an idea why?
QUERY(IMPORTRANGE("IMPORTEDDATASHEET";"06_20!M:AJ");"SELECT SUM(Col17) WHERE Col1 = 'Company ...
0
votes
1answer
28 views
Collate Data in Google Sheets
I am trying to collate date in Google Sheets. I have multiple tabs and want to consolidate them into one tab where each row is from a different tab. If that doesn't make sense, here is my test ...
2
votes
0answers
26 views
Google Sheet Column Tick box validation
Complete newbie here, sorry if this sounds silly.
I have 3 columns and 60 rows of tick boxes, I have the tick boxes adding up at the bottom of the sheet.
Question is I do not want a user to tick more ...
-2
votes
1answer
25 views
Google Sheets: How can I query all names and display it in another sheet according to it's category?
So I have an employee list and a checkbox according to their skills.
How can I get the employee list and display them to another sheet?
0
votes
0answers
28 views
Arrayformula to Query Sum
Good day,
I am trying to run a query that returns the sum of a column based on the query key and have it displayed as an array so that each sum is visible. With my below formula, I am successful.
```=...
0
votes
1answer
42 views
IMPORTRANGE query with checkbox
I'm trying to do an IMPORTRANGE with query with checkbox. However I keep encountering parse error.
=QUERY(IMPORTRANGE("1pzzOzjtJ8GJOWnkBozdO0anKHnbeREvadEc4rzY4ezI",'Guest Speaker Tracking'!...
0
votes
1answer
12 views
Show image if rank equals number
I have a row of 5 cells. I want to show an image above each cell depending on the rank of the cell. So if the rank of the cell equals 1, show a specific image (from a link), if the rank is 2, show a ...
0
votes
1answer
20 views
Calculate average across rows that match an ID and a word - Google Sheets
Table is as follows:
I want calculate the average of values in column C for rows with value "ABC" in column A and value "Yes" in column B.
So, basically, the average of values 2 ...
0
votes
1answer
30 views
How do I make the query look to the next choice?
I am importing data from a Google Form to a Google Spreadsheet.
I have created a query to compile data from the response tab to 4 different tabs in the spreadsheet.
I have put a limit on the number of ...
3
votes
2answers
116 views
How to convert repeating rows into columns in Google Sheets
I have data in Google Sheets that looks like
A B
1 Name: John Smith
2 Email: john@smith.com
3 Name: Dan Jones
4 Email: dan@jones.com
... many rows
which I'd like to get into this format:...
1
vote
1answer
26 views
Search a Google Spreadsheet and return results that are 'clickable' that takes you to the sheet and row
I have a spreadsheet with many sheets. (Data1 Data2 and Data3).
One sheet is named Search.
This search sheet will search all 3 sheets (Data1,Data2,Data3)
Results will return on the sheet named Search. ...
1
vote
1answer
19 views
Getting the column header if the row value is the greatest of the entire row
I think that the best way to describe what I want is with a image
Check the image. Each row will have a description (Of a feature of the programming language, for example) each language will have a ...
1
vote
1answer
21 views
How do I return all results associated with an input cell in Google Sheets?
I have a time study data set that has three different order types and resulting times to fulfill that order. An Order Type will be entered into C8. In cells C9:C, I want to create an auto-updating ...
0
votes
1answer
22 views
How can I include parameters in Google Sheet?
I have a query string to extract data from my master sheet, which looks like this:
=QUERY(IMPORTRANGE("1ABCsxIDRXU-k6YXWJ47Awjk4757VsMFBmye7rkBC123","Master!$A$1:$H"),"SELECT * **WHERE Col2 = 'abc123'...
1
vote
1answer
70 views
Listing and formatting tagged data
I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas.
I want to have a second sheet where the ...
0
votes
1answer
19 views
Google Sheets query cell reference
I'm trying to have my query reference a cell, but I keep getting errors:
=query(Sheet1!A3:X2838,"Select I, H, Q, P, J, N, O, S Where (N>10) and (J>"""&K2&""&...
0
votes
0answers
16 views
Google Sheets: Transforming a single date column in a multi-column query
I'm importing a HTML table into my Google sheet with IMPORTHTML. I need to parse date strings to real dates in the first column so I can format them and sort them properly. The table structure is as ...
1
vote
1answer
77 views
Google Sheets - Get unique values from a column and return the values a single column
I was hoping to get some help and direction. I have two sheets--a 'Raw data' sheet and a 'Dashboard' sheet. A user would dump the raw data in the first sheet and the dashboard will summarise figures ...
2
votes
2answers
96 views
Group by in query Google Sheet select different columns
Want to get the results described on the screenshot. How can I achieve it.
QUERY:
=QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")
Want to ...
1
vote
0answers
13 views
Data has both text and number, I need to move to another sheet from master but only the numbers show up…I need both
I've used this formula
=query('Form Responses'!1:136,"Select * Where E='name'")
but all I get is the numerical value entered in a cell. If someone enters both a number and text the cell is blank. In ...
