The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [google-sheets-query]

Use for questions about QUERY built-in function from Google Sheets.

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

Select All Columns Where Cell In Columns Contain A Word - Google Sheets

I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column. You can see a test sheet here: https://docs.google.com/...
2
votes
1answer
15 views

Google Sheets Query: Making the selected column depend on formula

I would like to use a normal QUERY with where operating in the usual way where A = 'Speed' but with the selected column being determined based on a formula. Specifically, I want to return only the ...
1
vote
2answers
24 views

How can I filter a list based on whether it contains any of a list of substrings?

I have a list of Japanese words. I want to produce a formula to filter that list, based on a filter list of substrings (kanji characters) which I'm looking for. So for example, say my filter list ...
0
votes
0answers
14 views

Loading Data Error Query with Importrange Gsheet

I am trying to use QUERY with importrange function to fetch custom data from another spreadsheet, it worked for a while but now it through loading data error consistently. if I refresh the sheet it ...
0
votes
1answer
11 views

Counting with Query

I am using queries, in order to create a dashboard, =query('Usuarios icorp'!I2:M522; "count(F) when F contains '"&B3&"' ", -1) I am having issues with my query function. I want to count how ...
0
votes
1answer
18 views

In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action

In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
0
votes
0answers
26 views

Unable to filter rows in a sheet populated by QUERY

I am trying to filter one column A-Z to pair items listed in more than one selling app. However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
-1
votes
0answers
19 views

Query returning multiple results in the first cell, followed by rows with the remaining results

I have a Query returning multiple results in the first cell, followed by rows with the remaining results. The query and results are below. I've replaced = with contains, same results. I use many ...
1
vote
1answer
36 views

Can I use “countIFS” with a filter?

https://docs.google.com/spreadsheets/d/1PlPmShZ4SnkxAqBTg5xY_LHaNokGaFli4Wc7MjV_Jqo/edit?usp=sharing I want to count how many paid is for city MB and then how many paid is for city UB. I tried to ...
1
vote
0answers
34 views

Google Sheets - Query Multiple Columns for same Data

We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult ...
0
votes
1answer
32 views

Import Form data into a sheet

I am trying to accomplish something that may not be possible. My work rents equipment to multiple companies and my boss wants me to setup a "database" of rental equipment and where its located and any ...
1
vote
1answer
30 views

Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query

I want to get last 40 days close price of stocks. I used below formula for the same but the result not showing the last day close price. =QUERY( GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),...
2
votes
2answers
62 views

Query or Filter formula to 'search for' and 'return' multiple words

I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet. The filter I have created is ...
1
vote
1answer
64 views

Adding missing dates to Google Sheets

I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function. For ...
0
votes
1answer
32 views

Include filename of source spreadsheet

I have 2 spreadsheets for 2 employees and I combine them into 1. From employee 1: From employee 2: I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
1
vote
1answer
20 views

Output counted list with extra qualifier

I currently have a table with the following columns: Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
1
vote
1answer
21 views

How to combine same value for Google sheet query?

I am trying to create a separate tab in my Google sheet where I can filter the Trainer to see which days they will be conducting a Day 1 and Day 2 Training. Day 1 Training: =iferror( query('...
2
votes
1answer
40 views

Dynamically reference data from multiple tabs and sort it

I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity. I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
0
votes
1answer
43 views

How to sort Column B to match with column A in Google Sheet?

I have 3 columns in Google Sheet. Column A have 95 rows with value. Column B and C have 500 rows with value: I need to sort Column B to match with Column A. Column C represent value associated with ...
2
votes
1answer
23 views

How to sum the values of same vlookup results?

I have this formula it gets the single data from another file from a sheet at column 7. =ARRAYFORMULA( if( len($A2:$A); iferror( vlookup( ...
0
votes
1answer
16 views

Importing answers from a Google Form into another Google Sheet, then adding a column for notes

I have been using a Google Spreadsheet with a master list created from a Google Form. I created a new tab to rearrange the order of the entries to the newest ones on top. However after those cells ...
0
votes
1answer
35 views

Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column

I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below I want to summarise the cost by element, but ...
0
votes
1answer
22 views

Cell Reference in a QUERY statement not working

I'm trying to add a cell reference (on the same sheet where the QUERY function is at) to a QUERY function On B1 there is this value: 13/09/2019 QUERY function: =QUERY(FORMULARIO!A1:S, "SELECT G ...
3
votes
1answer
44 views

Importing a row from one Google Sheet to another

Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week. Doc2: I have an individual sheet setup for each team. I'm looking to pull ...
0
votes
3answers
40 views

How to use Importrange in a Query and filter by dates

I am trying to IMPORTRANGE if the condition in column1 that is text formated ( Column1 = September 4, 2019 alas 5:10 pm ) contains the word September. I am using the following formula. The other ...
0
votes
1answer
24 views

Trying to make a column of “select count” in reference to another column

I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of ...
0
votes
1answer
30 views

How to combine 3 sheets into one using QUERY(IMPORTRANGE)?

I am trying to combine 3 different sheets of data from range A2:O Col1, Col4, Col5, Col6, Col8, Col11, Col12, Col13, Col14, Col15 using Query({IMPORTRANGE(... function. I tried using the formula below ...
1
vote
1answer
37 views

QUERY function to search three columns for the same keyword?

I have a table with data in columns A-U. Rows go down to 368. I would like to pull (onto a different sheet) all the information in columns A-F where it matches a keyword from a dropdown menu (in cell ...
0
votes
2answers
47 views

Extracting last match from a string with REGEXEXTRACT

I've created a simple text filter: =IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE) It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
21 views

How to find the last value in a column when the column is populated from an ARRAYFORMULA

Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...
1
vote
3answers
50 views

How to use QUERY to show data in Google Sheet using tags

I have a Google Sheet with 2 sheets. In sheet 01 I have this table. In sheet 02 I want to have a "Search by Tag" sheet. So, googling I got this formula =QUERY('01'!A2:D4, "select B,C,D where A = '...
0
votes
1answer
22 views

Merging cells based on same ID but divided into categories (columns)

I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
24 views

Show values from only rows that have a checkbox ticked

I have a data table with groups as column headers and names and emails as rows. I want to export data to another spreadsheet in the same document, which would only display members who belong to a ...
0
votes
2answers
33 views

Using TODAY function with QUERY

I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output." =...
0
votes
2answers
48 views

Consolidate columns from a dynamic range into a single column in Google Sheets

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range ...
0
votes
2answers
31 views

Showing top 5 most frequent values and counts of each from a data range

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
1
vote
1answer
32 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
2answers
20 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
0
votes
1answer
20 views

Query runs but returns first row greater than the DATE at 00:00:00 not the row after 10:00:00

Trying to use the following query: =query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(timevalue($A$6), "hh:mm:ss"...
1
vote
1answer
22 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
40 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 ...
0
votes
0answers
4 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
35 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
51 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
55 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
13 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
46 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
32 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
14 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
23 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 ...