Use for questions about QUERY built-in function from Google Sheets.
0
votes
1answer
13 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
16 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
14 views
Search for multiple cities of unload
This is my table:
---------------------------------
| City of load | City of unload |
---------------------------------
| Enzesfeld | Lublin |
---------------------------------
| ...
0
votes
0answers
19 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 ...
1
vote
1answer
15 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
32 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
26 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 ...
0
votes
1answer
24 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 (...
0
votes
0answers
69 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
21 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
15 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
25 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 ...
0
votes
1answer
88 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
33 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
40 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
28 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
60 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
50 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
190 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
28 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
50 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
91 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
71 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
90 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
479 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
41 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
19 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
262 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
559 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
92 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
449 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
323 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 ...
1
vote
0answers
51 views
aggregate results by week, month for graphing using sheets
Is there a way to convert daily values into weekly averages for charting?
Sample source sheet
---------------------------------------------------
| Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
2
votes
1answer
82 views
Refer to the cell directly to the left of the cell with the maximal value in a range
I've got a spreadsheet with about 5,000 rows and roughly 50 columns. The columns are alternating between terms and numeric values associated with the terms (example: baseball / 1000 / football / 500 / ...
0
votes
1answer
222 views
Vlookup across multiple columns
This question is related to the same project in this question, but I'm now at a later step.
I have a Google Sheet with a variety of German translations of a large set of English words. Using a ...
0
votes
1answer
344 views
Adding multiple rows by script
I have this simple Google Sheets script that adds a row after every already existing one. What do I have to change to for example add 12 rows?
function addRows(){
var sheet = SpreadsheetApp....
1
vote
1answer
709 views
importrange with query not working properly
I'm trying to import a column from a separate sheet and add a query to it.
Im using:
=QUERY(ImportRange("URL","SHEETNAME!c:f"),"select Col1 where Col4 contains 'statements' ")
I've been using this ...
0
votes
1answer
59 views
Adjusted script to look for column header name, not the numeric representative of the column
From this discussion, I have a script that nicely updates a column's cell with the date another column's cell was changed. This works well because the TODAY() function always updates to today's actual ...
0
votes
0answers
249 views
google sheets QUERY formula for empty instead of #VALUE! when other table is empty
I am trying to get rid of #VALUE! prompts from the QUERY function in Google Sheets. The closest question I could find was this one below, but it won't answer my question:
Google Sheets Query Get Row ...
0
votes
0answers
36 views
Google spreadsheets: How to get average session per user instead of all users?
In google spreadsheets I'm trying to get the average session per user from Google Analytics, so I tried ga:avgSessionDuration on metrics, ga:date in dimensions, it returned me the time in seconds, so ...
0
votes
0answers
7 views
Get all states from a country in Google Analytics Addon using Google Spreadsheets
I need to get all states from Brazil in Google Spreadsheets and display them in a chart showing the unique visitors for each state. I know how to display data in the chart, I just don't know how to ...
0
votes
0answers
27 views
custom trigger not creating google sheets
The purpose of creating trigger to run everyday from 11 AM to 10:00 PM on every 5 minutes. the below script is running fine but not creating triggers. Please help me
function createTimeBasedTrigger(...
5
votes
1answer
172 views
How can I stop Google Sheets charts from rounding down durations?
I've got a Google Sheets chart that displays the average pace of a day's rowing machine workouts (e.g. average the time to row 500m).
It looks like this:
The Query I'm using to get the data is this: ...
0
votes
0answers
187 views
DATEDIF of latest date of a range (Google Spreadsheet)
I'm trying to get the latest date for a range of cells and Calculates the number of days between two dates using the function DATEDIF.
As you can see from my screenshot example, my calculation should ...
0
votes
0answers
21 views
Google sheets query multi conditional date cell ref
I have a doubt regarding a change in this formulas in sheets:
=INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2)
=INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");...
-2
votes
1answer
107 views
Investing.com data uploaded but i am not able to use the data for email alerts.kindly help me [closed]
I have configured to fetch the data on every 1 min from investing wesite.
The table data is fetching from website. while trying to update date when 15 minutes status D column change to either strong ...
0
votes
0answers
20 views
Auto create URL based on value of the same field
I have a google sheets document that I would like to insert a value into the field and it auto populate a url beginning with "http://jobs.acaalex.com/" and add the value to end of the url.
As in ...
0
votes
0answers
18 views
Remove duplicate rows but preserve specific values in Google Query
I have a google query formula in Google Sheets that outputs a set of data like this:
Col1 Col2
1 Blank
2 A
2 Blank
3 Blank
4 Blank
4 B
4 C
5 Blank
5 D
How could I ...
