Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
0
votes
0answers
9 views
Standard deviation in Google Sheets Query
I have data of jobs (A), their executions (B) and duration (C) in my Google Sheet. I have already calculated the average duration of each job with Query language:
SELECT A, B, AVG(C) GROUP BY A, B
...
1
vote
1answer
37 views
Collated and separated results in one cell for QUERY results across multiple columns
In a Google Sheet I have the following formula:
=CONCATENATE(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'"))
This currently returns each pair of results in sequence (with no spaces) ...
0
votes
1answer
22 views
Google Sheets intersection query
I'm trying to write an intersection query in Google Sheets that lists all the rows in sheet 1 that contain a cell in sheet 1, column A, that matches at least one cell in sheet 2, column P
This is the ...
0
votes
1answer
16 views
Query not working unless I change the Date format to Automatic [duplicate]
Query works fine if I change Hudson F to Automatic, if I change it to date it stops working. I need to view F as date and have the query work
=QUERY(Hudson2000!1:6634, "SELECT A, B, C, D, F, G WHERE ...
-2
votes
0answers
11 views
How to auto-increment invoice number [closed]
I would like to do exactly the same as this. but when I copy and paste the code to my script, nothing happened.
I'd saved the function, run it, and put a trigger to it.
but still, nothing happened.
...
0
votes
0answers
17 views
Arrayformula not working with Query?
It looks like arrayFormula doesn't work with query..
If true, How would you convert this, eventually using vlookup?
=iferror(query(Variazioni!$A$3:$K;"select sum (E) where( (H) = 'VM' AND month(B) = "...
1
vote
1answer
36 views
Query: select cells in column where it is not null
I am trying to pull non null data from a column. I have tried the following.
Each import range works independently.
=query({IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); ...
0
votes
1answer
37 views
QUERY remove column and column header if blank
The attached document is a time-sheet document. There are 2 tabs, TimesheetData and InvoiceData.
I want to pull through data from TimesheetData tab to the InvoiceData tab using a query.
The query I ...
1
vote
1answer
34 views
Query based on column header in Google Sheets
I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful.
I have a sheet like this:
in row 1 dates and below songs used that Sunday....
1
vote
1answer
30 views
IMPORTRANGE syntax when using query on multiple tabs
I am using IMPORTRANGE within a query to extract data from sheet 1 to sheet 2. The following code is in a cell in tab A of sheet 2, and works as intended:
=query(importrange("URL-A","E:J"), "select * ...
1
vote
1answer
17 views
Google Sheets query column=column
I am not familiar with google sheets, but I have been sent an assignment and I don't want to take the time to convert the whole thing over to an actual database. Is a way to take one column of values (...
1
vote
3answers
22 views
Pulling data from one sheet to another if
My knowledge is pretty basic when it comes to excel and Google Sheets. I'll explain what I'm trying to do.
I'm trying to find a way to filter information from 1 sheet to another in Google Sheets. ...
2
votes
2answers
73 views
Google Sheets, how to sort query results manually (Not in query)
My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable.
The sheet is populated ...
0
votes
1answer
16 views
Date Query in Google Sheets with importrange
I have two tables.
Table A= Customer Feedback
Table B= KPI
In Table A I have a column for a date with format mm/dd/yyyy
and in Table B I have 2 columns as from date and to date with the format.
I ...
0
votes
2answers
24 views
Scanning a column and partitioning information to other sheets in Google Sheets
I am a membership manager in an international organization and I was hoping to get some help optimizing our members' contact list.
There are three kinds of members, NCs, LCs, and IMs. I have a ...
2
votes
1answer
54 views
How do I list Nth matches in Google Spreadsheet?
On MS XLS I can do some aggregate or leech off a structured reference, but I really don't know how to find the Nth result based on word match on Google Sheets.
I have huge columns - name, role. I ...
0
votes
1answer
17 views
split rows dynamically based on row length (COUNTA) and transpose to 2D array
I have data in this format:
and I need it to be reformatted into this:
Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
1
vote
1answer
54 views
Transform a Google Spreadsheet table? Columns by rows [duplicate]
I need to transform this table
into this one, with de Services columns as rows:
1
vote
1answer
15 views
Search for multiple cities of unload
This is my table:
---------------------------------
| City of load | City of unload |
---------------------------------
| Enzesfeld | Lublin |
---------------------------------
| ...
0
votes
0answers
20 views
Google Spreadsheet email notification when cell value changes
I have a dynamic spreadsheet where data comes every 1 min.
My question is how to get instant email notification when a cell's changes value from blank to alphanumerical value in a column and this ...
2
votes
1answer
52 views
Query Function with Unique References
I am currently using the QUERY function to move relative information to another sheet, which is then used for forecasting. Now what is currently happening is the same business names are showing up in ...
1
vote
1answer
36 views
Check value even if it doesn't match validation
In my google spreadsheet have a column G of dates that interviews are/have been scheduled on. There is a validation set on it to validate for dates with a warning. Every once in a while, when someone ...
0
votes
2answers
35 views
Confirm that values in a column are alternating
I have a column, call it A, of around 1500 cells. Each cell contains either "a" or "b". For the most part, the As and Bs are alternating, but sometimes there are repeating values.
An example of A1:...
0
votes
0answers
34 views
In Google Sheets, SQL Query: How to make multiple rows out of an transposed split of a column?
I have a query that returns this:
=QUERY(BOMs!1:300;"SELECT BU,A,B,E,G,H,K,L,M,BC ";1)
L65P6 PR BOT 33-KLN121-06X BOBINA CHIP 120 OHM L500, L503, L502, L501 2780 4 UN 0,10154
The column ...
0
votes
1answer
37 views
Google sheet query to combine values
From various examples I've hacked together a pretty neat aggregation function which gives me an overview on which activity (strings in G) I've spent non-billable work hours (numbers in E) or billable (...
1
vote
1answer
240 views
Query returning results into merged cells in Google Sheets
Short Question:
I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first ...
1
vote
1answer
23 views
How add column BY to a query?
How do I include the column BY in a query?
I first tried =unique(query(data_table,"SELECT D, BY"))
Then, I tried to surround it with single quotes =unique(query(data_table,"SELECT D, 'BY'"))
0
votes
0answers
18 views
Help with query
I have the following data:
column A is an ID (integer)
column E is DATA (integer)
column F is DATE1 (date)
Column G is DATE2 (date)
The table has multiple rows for each ID. I'd like to get only one ...
0
votes
1answer
17 views
Can we use Column Range for Comparison in Google Spreadsheets Query?
Suppose I have a table with 3 columns in a Google Spreadsheets sheet. Col1 is a name and Col2 and Col3 are numbers. Can we use a column range for number comparison such as in the example below? (what ...
0
votes
2answers
31 views
Google Sheets: Split row value and count each part
My Problem
I have a survey result with a checkbox question in which more than one answer could be checked. The results are given in a single column, separated by a | sign.
I would like to run a ...
1
vote
1answer
301 views
HAVING clause in Google Sheets QUERY
My Problem
I have built a count query that returns the most common elements from column H in the final sheet:
=QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC")
I would like ...
2
votes
1answer
76 views
Find column headers then do multi-criteria lookup
I'm trying to create a report where I need to find each column by header, then do a multi-criteria lookup. (I need to find the proper column because the data file is a csv export from another ...
-1
votes
1answer
44 views
How to use a value in Sheet2 in the WHERE clause of QUERY in Sheet2 whose dataset is in Sheet1
I have a Sheet1 that I want to use as my dataset for a Google Spreadsheet query that looks as follows:
The query is in Sheet 2 and I want to pull across a list of manufacturers who have a Type that ...
2
votes
3answers
35 views
Copying data from one cell in another based on date
I have a spreadsheet, using google sheets, that I have formed into the follow metrics:
Column A is the dates of the month
Column B is the data
I want to be able to copy the data from Column B into ...
0
votes
1answer
232 views
How to keep cells “paired” with query results
I am using an importrange query from other sheet to filter data: https://docs.google.com/spreadsheets/d/1FbeoKhiJ3jqYXqfIgAK_tHXPZ_Z1Xrm33ulu9gomXuE/edit?usp=sharing
The "colors" sheet has data ...
2
votes
2answers
166 views
Google Sheets Query - True/False/Other
I'm trying to get a query based on a tri-state. The possible values are TRUE, FALSE, 'Ignore' or blank. I want this to select all names (B) and emails (C) where a flag column is set to False or ...
0
votes
0answers
237 views
Google sheet - get crypto currency price from Cryptobridge Exchange API
How can I retrieve only the price from this API in a Google spreadsheet? This is the API ticker - https://api.crypto-bridge.org/api/v1/ticker
For example, if I use this function
=INDEX(IMPORTDATA(...
0
votes
0answers
51 views
Arrayformula concatenate both vertical and horizontal
I'm stuck on trying to solve this google spreadsheet issue. I have an table with for example countries and then details. What I would like to do is create a table automatically that concatenates all ...
0
votes
0answers
64 views
Google sheets query: how to select column twice in query
I have a query that is really long (about 50 columns), I don't want to split into 2 queries. But I want a column to show up twice. Is there a way to do this? below is a simple example that doesn't ...
0
votes
1answer
340 views
How to Aggregate and Pivot Simultaneously With Google Sheets Query
Hey I am trying to transform a table from this:
Into this:
Basically for each "Type" I want the totals for the different values for "Exists" which right now is just TRUE and FALSE.
But I can only ...
1
vote
1answer
163 views
In Google Sheets Query Max Date from Dates in Column A for a particular Project ID stored in Col B in Tab 'Data' from Tab 'Main'
Tab Data is as under
Date_A Project_ID
01-May-18 EL-PR-18-08709-1
02-May-18 EL-PR-18-08709-1
01-May-18 EL-PR-18-08709-1
03-May-18 EL-PR-18-08709-1
02-May-18 EL-PR-18-08709-1
01-May-18 ...
0
votes
0answers
176 views
Merging two data sets in order to add default values for missing data
I'm trying to merge two datasets in order to insert default rows for missing data. The use case is that I have a list of dates and attendance numbers for training sessions on those dates, but if I ...
0
votes
1answer
770 views
Google Sheets Query(Importrange) with named range?
I am looking for a way to query an imported range using named range. Something similar to this, however I cannot makeit to work
Sheet ID#A has a column J whose named range is "Signed" that may ...
0
votes
0answers
152 views
Google Sheets: Multiple tab data querying, filtering, and compiling
I have dozens of tabs on a google sheets worksheet that all utilize the same template, each cell utilizes data validation drop down menus--a plant information database of sorts. I would like to ...
2
votes
1answer
20 views
How to return a name alongside certain values in Google Sheets
Let's say I have a sheet of student data.
column A: grade level
column B: student name
[not relevant: columns C - G: absences, parents' phone number, credits completed, etc]
column H: GPA
I want to ...
0
votes
1answer
557 views
Using Query and Importrange to pull Google Form data from one worksheet to another
This is the query code I'm using to get form responses filtered to pull only a specific user ID from a google form in one Google Sheets workbook into another workbook:
=QUERY(IMPORTRANGE("KEYID#","...
2
votes
1answer
1k views
Query multiple data input ranges into 1 column output
Not 100% sure how to explain this, feel free to edit this with more intelligent language.
I need to pull data from 2 tables in 1 sheet. Meaning, I need multiple query functions. Those query ...
0
votes
1answer
272 views
Google Sheets queries: using WHERE IN
My problem
I have a table of ids and terms in Google Sheets. I would like to concatenate a few terms by their ids.
For example, (2,3) should yield Talla de zapato de mujer, Women’s shoe size. I want ...
0
votes
1answer
599 views
Compare dates in query from IMPORTRANGE and cell
for a few days I'm stuck at comparing two dates.
In a big table with dates as columns and products as rows, we want to display the amount of sales per day per product.
What the following query does:
...
1
vote
1answer
472 views
Transpose Ranges repeated by row
I would like to convert several range of columns into rows, and keeping the association to the original col/row source
Sample source sheet
---------------------------------------------------
| Data ...
