Questions tagged [google-sheets-query]

Use for questions about QUERY built-in function from Google Sheets.

Filter by
Sorted by
Tagged with
1
vote
1answer
260 views

Summing Columns Based off Different Criteria in a group by in a Query Function in Google Sheets?

I would like to know how to sum a column based on different criteria in a group by in google sheets query function. Here's the data And Here's what I want Currently, I have a separate query for each ...
0
votes
1answer
43 views

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

Is there away on google sheets to paste text and automaticly create new rows with every line break or ect? I will paste a lot of text by this format in the example below (700 lines in total): ...
1
vote
1answer
47 views

Query Function - remove parts of entered term and carry out formula

I'm sorry if the title is confusing. In the List to Search tab, I've got a bunch of orders. What I do is select the dishes in a single cell in this manner: And I want to just dump them into the ...
1
vote
1answer
20 views

How to combine two different columns

I have two different columns with emails and I'd like to display all the emails in a separate column in the spreadsheet. Both the columns can be extended further so I can't select all cells statically....
1
vote
1answer
145 views

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/...
1
vote
1answer
113 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
230 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
49 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
1k 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
30 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
859 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
105 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
36 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
54 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
44 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 ...
1
vote
2answers
144 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
49 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
58 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
1answer
534 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
453 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
34 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
218 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
60 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
3k 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
48 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
35 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
323 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
30 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
77 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
262 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
342 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
68 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 ...
0
votes
1answer
59 views

Query or filter the data when using date time format

Example sheet. Sheet2 In cell A2 I have: =QUERY(Sheet1!A1:Q, "Select * where B >= datetime '"&TEXT($A$1,"MM-DD-YYYY HH:mm:ss")&"'") In cell A1 I have: 6/17/...
2
votes
0answers
33 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
289 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?
-1
votes
1answer
313 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
19 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
153 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
47 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
476 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
34 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
25 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
39 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
26 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
79 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
113 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&""&...
1
vote
2answers
459 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
548 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
15 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 ...
1
vote
1answer
351 views

Find the last instance of multiple values

I really need some help with this. I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in ...

1 2 3
4
5
16