Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
635
questions
1
vote
2answers
17 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
13 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
votes
0answers
31 views
How I can Sum multiple columns in importrange query in Google Sheets?
I import data form the file master, and make query importrange.
Now I have to sum multiple columns (Column 62 and Column 63).
How can i do it? Can anyone help me?
This is my one column sum query.
=...
1
vote
2answers
42 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
37 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 ...
0
votes
1answer
8 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
0answers
22 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
35 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
18 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
45 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
19 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
21 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
24 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
15 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
20 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
25 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
23 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
18 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
23 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
95 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
66 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
14 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
10 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
55 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
87 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
10 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
88 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
vote
1answer
34 views
Using a query and tick boxes to auto fill another tab
I'm new to this and I'm racking my brains over how to get this to work, I can do basic IF & Vlookup but I believe I need the merge the 2 for this one.
Is there a formula I can use on the "NLN" ...
0
votes
1answer
36 views
Filter for a QUERY function
I need some help here.
So I need a function which imports selected columns from a different sheet out of which one of them contains a specified word but allows you to filter/ignore based on specified ...
1
vote
1answer
37 views
Trying to pull Duplicate data from multiple columns
https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0
I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
1
vote
0answers
13 views
Find Difference from Previous Consecutive Row in Google Query Pivot Table
Say I have source data like the below:
OriginDate|Name|IncidentDate| where Origin Date is constant and the earliest date of my imported data set. I've formatted the dates to numbers so I can perform ...
1
vote
2answers
46 views
Return the closest value from two-dimensional data
I have an input value and a table of two-dimensional data in Google Sheets. I would like to output the value from the table that is closest to the input value, either without going over the input ...
2
votes
1answer
43 views
How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D
I'd like to populate Columns C and D in an entirely new spreadsheet, where Column D still auto-populates the cells just as it does in the original/first sheet (as it does in the photo attached inside ...
1
vote
0answers
10 views
In a large document with many comments how do you review everything, both open and resolved comments, page by page?
The 'Comments' button allows me to see the comments in date order but that doesn't give me the context of the page it was on. I have no idea when some of the comments where made, that I am looking for,...
0
votes
1answer
17 views
Google Query: filter one record per day/week per user
I'm collecting repeated measures using Google Forms which only allows 1 or unlimited responses. I need a way to filter out responses that were entered by the same user too near to each other.
For ...
0
votes
0answers
11 views
Using query function to iterate through columns in Google Sheets
I am trying to produce the average of the difference of two columns under the condition that one column is not null, for 10 different columns. I want the column being subtracted from to iterate in ...
-1
votes
1answer
39 views
Exponential Moving Average
What is the formula of Exponential Moving Average (EMA) in Google sheet ?
How it can be used to calculate Share market stock EMA of different period. Eg 5, 21, 50 Days.
Can anyone share Demo sheet.
1
vote
1answer
32 views
How to sum up to 4 (or more) columns in a query based on checkboxes?
Basically I have 4 checkboxes and I want to sum up to 4 columns based on those, with the formula query:
It's a bit ugly what I di and I was wondering if there was a more elegant way of doing it, ...
1
vote
1answer
62 views
How to populate a cell based on specific name and specific date using a query function?
Hello new to the forum here and relatively new to all this data business. I feel pretty confident in my skills but this one has me stumped.
Below you will see my initial query of dates, ditch_name(...
-2
votes
1answer
24 views
Google Sheets atomisation
I hope you are all well and healthy!
I am looking for a solution in Google Sheets to automate something like this:
and it could auto generate something like this
Thank you in advance
2
votes
1answer
23 views
Google Sheets query how to make MATCHING skip (not match with) blank cells?
I am running a query in Google Sheets to analyze a bank account statement's transaction text (column D) against predefined categories (found in range $T14:$BG14 for this particular row).
My problem ...
0
votes
0answers
25 views
Divide 1 row of data into multiple rows - Registration Data
I have a Google Form for registering multiple children. There is only one mom, but she can have anywhere from 1-3 children she is signing up for daycare. The form give me a sheet with a row with all ...
0
votes
0answers
13 views
Multiple IF statement including formulas as True response
https://docs.google.com/spreadsheets/d/1siXWH_83AFnymr58f72xBmwFlPG1O5ZHZgA1JqQNt0w/edit?usp=sharing
Above is test document. I am trying to import information from sheet "AM Trip" to Sheet "BL AM", I ...
1
vote
1answer
37 views
Query Google Sheets Using Week Num to order dates
I was finishing ordering timestamps and dates, and I can't quite get it to work:
=QUERY(Sheet3!A:I, "
SELECT D, SUM(I)
GROUP BY WEEKNUM(D) ORDER BY D DESC
LABEL SUM(I) 'Balance Semanal'
...
-1
votes
1answer
29 views
Using “IF” or “Query” statement paired with “ImportRange” to bring in data set from one sheet to another
I have a sheet where I input my jobs booked. I want to be able to import information for the first 4 columns (Job Number, Job Name, Salesman, Date booked) if Column F (Permit Required) has a "Yes" in ...
