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
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 ...

1
2 3 4 5
14