Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
924
questions
1
vote
1
answer
32
views
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col1
I have a Google Sheet, that I am using as a tracker for a game. This was not written by me, just passed down, and I am having a hard time trying to decipher the issue here. As per the title of the ...
- 13
0
votes
2
answers
30
views
Google Sheets QUERY to return if contains multiple selection and all other values
I have a data set similar to the image in cells A5:D31. I am trying to write a QUERY function that will return the records that match the "filters" in range A2:D3.
In this example, the query ...
- 19
0
votes
0
answers
2
views
Counting weeks and labeling table appropriately
I have been working on an issue for a while and cannot seem to solve this issue, Hopefully somebody on here can help me with the problem.
I have a large set of data, which has dates for all of the ...
0
votes
1
answer
26
views
Column Identifiers as Described in Query Language Reference (Version 0.7) not working
In the Query Language Reference document found at https://developers.google.com/chart/interactive/docs/querylanguage#Table_used_in_Examples it indicates the following as properties of data tables.
&...
1
vote
0
answers
11
views
How do I build a formula to automatically select from dropdown categories column based on strings/text from another column?
I'm hoping to build a formula that automatically selects a category based on keywords/phrases from an imported bank statement. This is a very similar question that Ryan Voigt has asked back in Feb ...
- 11
1
vote
0
answers
14
views
Google Sheet Query function [duplicate]
I am just wondering if below code is possible to convert in Google sheet function QUERY(), please see query below:
Query:
SELECT * FROM tblDate WHERE Date IN ('1/1/2000','1/23/2001','3/1/1996','6/23/...
- 11
2
votes
1
answer
80
views
Can't remove header from mathematically generated column in QUERY
I have the following formula in D1 of my Google spreadsheet:
=TRANSPOSE(QUERY(B:B, "SELECT "&A1&"/Col1"))
It produces the header quotient(5()) in D1 that I don't know how ...
- 123
1
vote
0
answers
18
views
Google Sheets Query doesn't return all data
this is my first time posting and I am a beginner with Google Sheets. I am trying to use QUERY to return 3 columns of data with a condition that the number column is greater than 0. Here is what I ...
1
vote
1
answer
131
views
Multiple large GOOGLEFINANCE formulas make spreadsheet unresponsive
Problem
In Google Sheets I have huge GOOGLEFINANCE formulas that return accurate results however the sheet is often unresponsive, and can take a few hours to recalculate when reloaded.
Is there a way ...
- 11
0
votes
2
answers
125
views
Query function returning blank values instead of text in column with both text and numbers [duplicate]
I'm using this formula: =QUERY(A1:M;"SELECT D")
COLUMN D contains rows with numbers and others with text strings, but the QUERY is only returning the numbers and the text strings are showing ...
- 11
1
vote
1
answer
30
views
How to sum data on one row from multiple selected Google Sheets
I am tracking paid advertising key performing metrics on both Google & Facebook ads platforms on two separate Google Sheets, however, I'd like to combine both datas collected into one master sheet....
- 11
1
vote
0
answers
11
views
Google Sheets query with sum and date? [duplicate]
I'm trying to write a query from the following table:
columns for date (A), description (B), amount (C), category (D)
I have this query:
=query(Transactions!A2:D501, "select D, sum(C) group by D&...
- 11
2
votes
2
answers
50
views
Query form data by date to return counts by item and category
I'm a teacher and need help with a Google Form that collects the following info:
Timestamp
Name
Class
Lunch option
All of the data comes into a Google sheet. I'm using successfully using the QUERY ...
- 21
1
vote
3
answers
52
views
Google Sheets Query: Selecting column, can only order descending
The following query works for me in google sheets:
=QUERY(A3:A,"SELECT A order by A desc")
Changing desc to asc however does not, instead returning #REF!:
=QUERY(A3:A,"SELECT A order ...
- 119
0
votes
1
answer
40
views
QUERY function to return records with multiple criteria
I have a dataset on Google Sheets similar to below:
Name
Team
Account
Date
Jennifer
East
Meta
5/1/2023
Steve
East
Alphabet
4/1/2023
Claire
West
Google
4/23/2023
Roger
West
Apple
6/23/2023
Bob
...
- 19
1
vote
1
answer
33
views
Google Sheets QUERY function to return records with multiple filter criteria based on cell reference
I have a dataset on Google Sheets similar to below.
Name
Team
Account
Date
Jennifer
East
Meta
5/1/2023
Steve
East
Alphabet
4/1/2023
Claire
West
Google
4/23/2023
Roger
West
Apple
6/23/2023
Bob
...
- 19
0
votes
1
answer
2k
views
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: D
I have data in a sheet called "PASTE HERE" that I want to import into another sheet in the same workbook. I don't want to use IMPORTRANGE because then copying the workbook would necessitate ...
- 1
2
votes
1
answer
184
views
How to apply ArrayFormula on a RegexReplace Query formula so that it autopopulates for all values in Column A?
I'm trying to create a single string for each product that includes different unit prices tied to purchase quantity. For example, a product might have a unit price of £10, but the unit price drops to £...
0
votes
2
answers
28
views
Google sheets - copy adjacent cells if value is identical
I have a table like this
Letter
Selected letter
Number
A
B
1
B
D
2
C
3
D
4
I'd like to have as a result:
Selected letter
Number
B
1
D
4
I tried to compare the two firsts columns to ...
- 101
0
votes
0
answers
24
views
Script to determine time after a dropdown status is changed (utilizing the edit history)
I have a Google sheet-based simple CRM for leads from Facebook ads. When I import leads into the sheet, I assign them the status of Raw in a dropdown column for status. My team then contacts them and ...
1
vote
2
answers
45
views
Add cell value in query
When doing a multi-sheet query, I would like to bring in the value of a cell (A2) from each source sheet to a new column in the results.
The query:
=QUERY({CCL!A4:M; HOOD!A4:M; AAL!A4:M; BAC!A4:M;
...
3
votes
1
answer
43
views
How to add a column in query that adds 1 year to date column
I have a Google sheets with a bunch of sheets for each vehicle type. Each sheet is formatted the exact same: column A is the unit number, column B is the inspection date, and column C is the status (...
0
votes
1
answer
100
views
Query ignoring text
When I share data with sub-subcontractors, I use the QUERY function to return a subset based on a company name match. I never had any issues in the past, but lately QUERY has not been returning values ...
- 1
3
votes
1
answer
54
views
Filter a list of lottery results for those matching at least 4 numbers
I have a list of 3,000 winning lottery numbers. Each winning lottery number is on its own row and is comprised of 6 individual numbers, each in their own column. The winning number's draw date is also ...
- 33
1
vote
0
answers
33
views
Google Sheets - pull multiple lines of data from Workbook1 based on A1 in Workbook2 [closed]
Ok. So I've been all over this looking for an answer to my problem. I found so much helpful info, but none of it quite solves my problem. So I am here to ask the experts myself.
I work for a ...
- 19
0
votes
1
answer
45
views
How do I return the value of a specific cell within a range on Google Sheets based on current date?
I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week.
The worksheet is currently ...
- 1
1
vote
1
answer
290
views
Google Sheets Query, filtering with a range of values
I want to query data by checking if the data_range's column C (invoice#) matches this tab any value in column B (invoice#), while this tab column B will have variable data size ( copy from another ...
- 11
0
votes
1
answer
41
views
Formula to check is cells are blank. If the cell is not blank then assign a value
I have a data set on Google Sheets where for example,
Name
Stage 1 Date
Stage 2 Date
Stage 3 Date
Stage 4 Date
Bob
3/1/2023
3/5/2023
3/31/2023
4/15/2023
Joe
2/1/2023
Zoe
3/21/2023
4/1/2023
4/15/...
- 19
0
votes
1
answer
22
views
Query to group files together by a unique box number and return the min/max range
I have a Google sheet that list of files with a corresponding box number(Col1)labeled "Box Number". I want to be able to gather all files by each unique box number and then return the min ...
- 13
2
votes
2
answers
147
views
Sum of all numbers from many columns that match text from multiple rows in another column
I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following:
URL
Sessions
/
59
/?variable=1
2
/?variable=2
37
...
- 135
1
vote
1
answer
177
views
How to QUERY - group SUMed results? [duplicate]
I'm new to query and can't figure out how to get grouped results of sum in two columns.
There's data set in the picture of names and points which are in two columns and query formula to sum and group ...
- 11
-2
votes
2
answers
22
views
i want that webp urls are arranged in the same order as jpg urls
webp urls have the same names as jpg urls. i want that webp urls are arranged in the same order as jpg urls. they're in Y2:Y17 in google sheets. jpg urls are in Y2:Y9 and webp urls are in Y10:Y17. ...
0
votes
2
answers
161
views
How to Count Combinations In a Set (Including dupes)
Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row.
Example data:
A
B
C
D
E
1
A
A
A
B
...
0
votes
1
answer
40
views
SUMIF for unique entries
I need to do a SUMIFS statement in Google Sheets but am having a hard time not double counting duplicates in the data. For example:
Volume
Flavor
Batch #
6000
CLM
BT1000
6500
CLM
BT1000
7000
WBG
...
- 13
0
votes
1
answer
253
views
Google Sheets use count in query without expanding array
My goal is to dedupe results based on column B, filter by last X days in column 13 (omitted in sample data I can work that part out myself), and then count the number of rows that remain with a value ...
- 449
-1
votes
1
answer
29
views
Pair and Sort the Columns in Desc Order [closed]
Hello I have the following data in my table:
Columns A to E indicate the year tag, and columns from F to J indicate the years. Each tag corresponds to a year. For the first line:
T1 -> 2004
T2 -&...
0
votes
1
answer
28
views
Query that combines the contents of 3 columns, but also filters by a date in a 4th column
This is an example of my data that is located on a sheet called Data:
Date
Fruit 1
Fruit 2
Fruit 3
1/1/2020
Apple
Mango
Apple
2/1/2021
Cherry
Banana
Pear
2/1/2021
Orange
1/1/2022
Apple
Cherry
...
0
votes
1
answer
34
views
How to Eliminate Duplicates Within Same Cell with Separator
Say I have one column that contains cells:
Apple||Banana
Banana|Apple
Apple||Apple
Banana|Banana
Apple||Banana||Apple
etc...
How do I eliminate any instance of a word appearing twice (separated by a ...
0
votes
1
answer
381
views
Google Sheets function to create hyperlink based on data in cell, but not if the cell is empty
The solution outlined here is ideal for my needs (converting an article ID in column C into a hyperlink to a CMS that displays in column D).
However, I would like the formula to leave column D blank ...
0
votes
0
answers
7
views
How to get company details using Importxml from this URL? [duplicate]
I need the list of companies from here https://www.capterra.com/event-management-software/ I am trying to use the Importxml formula but there seems to be an error. Can someone pls help.
The formula I ...
- 1
0
votes
1
answer
23
views
Formula to select certain people only
I have a spresdsheet in google sheets and use the following formula
=QUERY('Main Data'!A2:U,"select B, sum(J), sum(E),count(E), sum(F), count(F), sum(G), count(U),
WHERE A IS NOT NULL
AND O>=...
- 1
0
votes
1
answer
98
views
Best way to SUM all the columns between two particular columns
What should be the best approach to get a sum of cells that are between two columns A and E. Column A has a header of "Month Jan" and col E has a header of "Month Feb".
Columns are ...
- 3
-1
votes
1
answer
49
views
Sorting data extracted by QUERY by a new column in the destination list
I am pulling some filtered data out of a master list by QUERY. I am also adding another column, which does not exist in the master list, to the date in the destination list. My question is how should ...
0
votes
1
answer
39
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 19
2
votes
1
answer
61
views
How do I make the weight column in my medical log sheet get updated with the most recent weigh-in?
I am looking to get the weight column in my medical log sheet to be updated automatically when I key in the new weights on my Weight Log sheet. I take the weights weekly and update the info into a new ...
- 23
1
vote
2
answers
89
views
Query+Importrange for multiple sheets based on values in Col26
=QUERY({
importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001");
importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:...
0
votes
1
answer
402
views
Use MATCH & LARGE but with 2 identical highest values in Google Sheets
Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even ...
0
votes
1
answer
257
views
amending query statement difficulties
doubleunary kindly helped with the linked question however i'm struggling to amend the Query() section so as to pull different results. ie. for repairs and revenue also.
Google Sheets - Formula or ...
2
votes
1
answer
102
views
How do I receive the data from Google Form onto Google sheets and allow my students to edit it without other students seeing their work
I am trying to feed data from a Google Form to a Google Sheet. I want to have it separate each student into their sheet. Then, I want to send that information back to the student in a Google Sheet ...
- 21
1
vote
1
answer
51
views
How to filter data from various sheets based on dropdown selection
I am setting up a sheet to track renewable services for my company. There will be various tabs, for example, Domains, Hosting, Google Workspace and Phone Numbers.
I would like to be able to filter all ...
- 11