Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
482
questions
0
votes
0answers
16 views
Visualize Data that updates dynamically
TLDR: How can I create a bar chart that updates the x-axis daily? The end goal is to have a chart that shows a side by side comparison of goal completions performance for each source of traffic. Here ...
0
votes
0answers
10 views
Select Column A Where values in Column A are not present in Columns D-G [on hold]
Having trouble with a query.
I want the query to select column A where values in Column A are not present in a Columns D-G.
I have attempted a filter and had no success.
1
vote
1answer
19 views
Querying for the last results with group by
I've got the following data sheet:
date(A) source(B) cost(C) totals(D) val(E)
9-8-2019 Magic 0.00 0 0.00
9-8-2019 Stick 54.72 6820 ...
1
vote
1answer
26 views
Query & date formatting
I have run into an issue where I have a query formula that acts like a filter on multiple criteria. I have been trying to add into it the ability to search between dates as well, with intermittent ...
-1
votes
0answers
8 views
google sheet query: need help finding max value from unique label
Request:
Is there a query to find the ARR sum of, say, the month of august 2018
a way to generate a list with the same conditions
Conditions:
a close date of less than year 2018, but only select ...
0
votes
0answers
3 views
Query to transpose data with col 2 offset for column headers
DATA:
Col 1 = Job numbers
Col 2 = Task numbers
Col 3 = Date
I would like to have:
Col 1 = Job numbers
All other Col = Dates with Col 2 as headers transposed
So something like:
query(DATA,"select * ...
0
votes
2answers
27 views
Is it possible to have Google Sheets query function that accepts a dynamic query argument instead of hard coding?
I have a long OR query function that I will be reusing many times in many contexts and that may change frequently. Following the principles of DRY design, I would like to write this query once and ...
1
vote
1answer
46 views
Get last value entered past a specific row
I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has ...
1
vote
2answers
22 views
Formula for getting the Left most cell value from a 'Range of cells'
Need your support in finding a solution for an issue which I am facing now.
Formula for Column A... Data Range from B to D
Have values in a range of spreadsheet cells (From column B to D)
Value ...
1
vote
1answer
9 views
Google Sheets forumla help
With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have ...
0
votes
1answer
19 views
How to merge data from multiple Google Sheets with formula?
I have 10 columns in 10 Google sheets and in all sheets are the data labeled like
Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of ...
1
vote
1answer
28 views
How to Update a exactly sheet tab row with a value from a imported data in another tab in Google Sheets?
I have this tab in Google spreadsheet
[https://docs.google.com/spreadsheets/d/14ZBff8R6brLYWtfv70V-tyG0HE-sX_ejySvplQqZftM/edit#gid=1450402909]
Sheet All Tasks, Candice Tasks, Others People Tasks
...
1
vote
1answer
12 views
Rank days of the month that occur most
I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many ...
2
votes
1answer
15 views
Google Sheets Import Range Query Error
I'm trying to create a sheet that only shows me LOT Codes of specific products from a different workbook that has all of our products and LOT Codes.
From what I've seen the Query with Import Range ...
0
votes
1answer
27 views
Is there any way to dynamically modify dates based on number of days something would take?
Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines:
Task |Number of days| Start Date | Date Done
Buy plane tickets | ...
-1
votes
2answers
21 views
Query the result of a query in the same cell
I don't know how else to put it of phrase it, I am really stumped here. I will put what I have down below and hopefully, someone will understand what I am getting at.
=QUERY(Sheet3!A:B,"select B ...
1
vote
1answer
25 views
Query sum for unique pairs of rows
I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example.
City Person Days
London Tom ...
0
votes
1answer
18 views
Pull data from another apreadsheet where Row and Column are strings
I am trying to pull data from a different sheet in google sheets by matching row and column header
EG
SheetB
LOC prox dist qty
sjc 12 15 19
dfw 10 16 8
atl
ord
In ...
1
vote
2answers
47 views
Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text
I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical.
Formula in ...
1
vote
1answer
32 views
Simplify multiple OR statements in a QUERY using IMPORTRANGE
I am trying to query values from a different sheet using a range from the current sheet. Is there a way to simplify the formula and get rid of all the ORs? Like maybe using a FOR loop?
=QUERY(...
1
vote
1answer
23 views
How to reference a local cell in QUERY?
I just about have formula 100% perfect. Just one issue:
Formula (Works):
=QUERY( ImportRange( "https://docs.google.com/spreadsheets/d/1Lf4r_b0fmFh6fClZsGmfZmo8ZD87qf8N_0f-Jaoyu-g/edit#gid=0", "...
1
vote
1answer
39 views
Escape single quote in cell content in a QUERY
My Problem
I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
1
vote
1answer
27 views
How to extract data from a table dynamically?
I have the following table
And I would like a formula that would produce the following result
I would like to iterate through the table going from left to right top to bottom and if there is a ...
1
vote
1answer
31 views
How to Iterate between colunns and rows?
I am running the following forumula:
=IF(INDEX(C$2:$2, 1, ROW(1:1)),CONCATENATE(B$2,"-",INDEX(C$1:$1, 1, ROW(1:1))))
On the following table
Basically, If a value is present in column C2, I am ...
1
vote
1answer
18 views
How can I return a string instead of a number when the number is 0
I have a formula that returns average lbs, waist and body fat % for the weeks I've been dieting. The problem is I didn't measure body fat % until a while after I started so some averages are 0. I'd ...
1
vote
1answer
25 views
How can I change the formula of one cell based on the value in a different cell?
COLUMN D: Dropdown list of values
COLUMN E: IF function returning different values depending on COLUMN D contents
I have the functionality described above working properly. My problem is that I want ...
2
votes
2answers
90 views
Google Sheets Split row in multiple rows
I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.
I need a script that can arrange the data like in the blue rectangle.
0
votes
1answer
18 views
Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2
What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list?
Here's the document.
1
vote
1answer
15 views
Only use last X values in a plot in Google Sheets
I'm using a Raspberry Pi to automatically measure the temperature and humidity in my room and append that data to a Google sheet every few minutes. I use several methods to display the current data (...
2
votes
1answer
15 views
Dynamically creating column names based on data in another tab
I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.
The ...
1
vote
1answer
17 views
Pull image or skip to next cell if empty
I am currently using Google Sheets to make a list of videos and thumbnails.
I have various sheets titled: Frying, Stewing, Baking, etc. Within each sheet, there are only two columns which include a ...
0
votes
1answer
15 views
Pull data when cell value is selected
I am Trying to pull data from part of a sheet to another part depending on if dirt level 22 value is filled in. Please see attached Picture, basically, the bottom section pulls data from rows above ...
1
vote
1answer
19 views
Skip arrayformula if the result of that formula is blank
I have an array formula that looks like this:
=ArrayFormula(IF(COUNTIF(B2:B, QUERY('Form Responses'!B2:G, "select G where B != ''"))=0, IFERROR(QUERY('Form Responses'!B2:G, "select B, G where B != ''"...
1
vote
1answer
52 views
Google Sheets Pulling data between a specific date and time, from a timestamp
I am trying to pull specific data from a timestamp that I will be using in several pivot tables.
Here is my workbook https://docs.google.com/spreadsheets/d/1iYHYdATK06Wxl-Jtxe2P92Kh50XEsWgwx5d-...
1
vote
1answer
32 views
How to programmatically drag new rows - running total?
I have the first few rows of my mortgage payments set up, and I want to know how to programmatically print the rest of the rows, as opposed to dragging the little corner box to output the rows.
This ...
1
vote
1answer
31 views
Array Formula with IF function and SUMIFS function
I am trying to convert the below formula into an array formula. I can make it work in an array, but it displays the wrong values, compared to the normal formula which I have to drag down.
Normal ...
1
vote
1answer
16 views
Count unique values in second column corresponding to unique value in first column
I have a Google sheet with cities corresponding to countries like this. Notice that neither countries nor cities are unique and there can be duplicates.
| A | B |
----------------------...
1
vote
1answer
14 views
How to refer to elements of a dynamic array in Google Sheets?
I'm trying to create teaching timetables dynamically, by searching through a large table with all of the information about what time a lesson takes place, who the tutor is, what age group is being ...
2
votes
1answer
25 views
How to aggregate data from multiple Google sheets with a tag
I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet.
Then I am aggregating those three sheets into one and also filter them using a query.
Ex:
=...
1
vote
1answer
12 views
Issue with finding the averages of multiple rows at once
I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
0
votes
1answer
15 views
Google Sheet Query returns first 3 rows in one row
I have made a simple query that combines data from 4 separate sheets:
=query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ")
The objective is to ...
0
votes
1answer
21 views
Graphing two lines in Google Sheets based on values in first column
I'm trying to create two lines on the same graph, with one being 'Line A' based on Data labeled 'A' and 'Line B' based on B.
The X-axis would be the date and would expand over time as more entries ...
1
vote
1answer
30 views
Automatic merging of duplicate cells under common ID
I have data in column A with IDs and a column B with data for those IDs.
Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one ...
0
votes
1answer
124 views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
0
votes
1answer
15 views
Merging data sets from two tables with origin reference [duplicate]
In a Google Sheet, I have multiple tables containing a list of dates.
I want to create a single (sorted) list containing all the dates and the table they came from.
Table foo:
1.1.19
3.1.19
Table ...
1
vote
1answer
47 views
Filter and display duplicates in a table
Here's the link to my Google sheet: https://docs.google.com/spreadsheets/d/1SBD0_Ca0r_p3810u1ZNlbG6MXsvhFlLcwEJndVQJRP0/edit?usp=sharing
The master data will show:
And I would like to have a table ...
1
vote
1answer
21 views
Dynamic Master sheet that grabs red formatted cells from another sheet
The title may be a little too ambiguous but I'll try to give a rundown as best I can;
I have a Google sheet with multiple "Sheets" or Tabs (as I'd like to call them) within it.
The Main tab is blank ...
1
vote
1answer
28 views
IMPORTRANGE from a separate sheet based on closest date to today's date
Sheet1 will have data with different past dates
Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date.
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
16 views
How can I copy a row of data automatically to another spreadsheet when a condition is met in a drop-down menu?
Our small business uses a spreadsheet to track data from inquiries. Once booked, I need the data to automatically copy in the "Current Bookings" tab in the same sheet when "booked" is selected from a ...
1
vote
1answer
47 views
Using ARRAYFORMULA and QUERY to produce results in multiple columns
I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values of two different variables for those groups.
Using the table below as an example, I would ...
