Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets-query]

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

2
votes
2answers
529 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 ...
1
vote
2answers
31 views

Using QUERY and IMPORTRANGE to pull records from a specific date range

I have created two spreadsheets. This one is my source data: https://docs.google.com/spreadsheet_A And I would like this one to just be the people who are currently here (so Column D < today and ...
2
votes
1answer
146 views

Aggregate results by week and month for charts

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......
1
vote
1answer
12 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
1
vote
1answer
17 views

Query Importrange with multiple values

This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
1
vote
1answer
84 views

Collated and separated results in one cell for QUERY results across multiple columns

In a Google Sheet I have the following formula: =CONCATENATE(QUERY(Summary!I4:U200,"Select I,U where S='"&A3&"'")) This currently returns each pair of results in sequence (with no spaces) ...
1
vote
1answer
678 views

Query: select cells in column where it is not null

I am trying to pull non null data from a column. I have tried the following. Each import range works independently. =query({IMPORTRANGE("KEY", "Sheet!A4:A34"); IMPORTRANGE("KEY", "Sheet!A4:A34"); ...
1
vote
1answer
215 views

Query based on column header in Google Sheets

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful. I have a sheet like this: in row 1 dates and below songs used that Sunday....
1
vote
1answer
461 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
103 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 ...
1
vote
1answer
298 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 ...
1
vote
1answer
352 views

Querying multiple ranges from form responses to new sheet

Recently, someone on this forum put together a formula for me. It works on a sheet pulling responses from a google form. I'm trying to model a new one after that but I'm having trouble. The working ...
0
votes
1answer
14 views

Google Sheets Using Countifs & Unique Together

I am trying count the number of unique clients of Payroll service who are Won. The answer as per the table below should be 4. How can I achieve this, given that the table will be dynamic?
0
votes
1answer
44 views

Using array formula in Google Sheet

Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name. This is my version of formula: =...
0
votes
1answer
31 views

Google Query, change select columns based on cell reference?

I am trying to improve a manual process that requires filtering through the input from a Google Form. It would be great if we could filter each question individually, without building multiple queries....
0
votes
1answer
25 views

Add numbers to results of Google Sheets query

Is it possible to add a column of numbers to the results of a query? It would be nice to have a number (from 1 to n) next to each records returned from a query.
0
votes
1answer
31 views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
1answer
56 views

Arrayformula and Sum queries. Possible alternatives

I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet Basically in the sheet "Substitutions" all absences and substituions are stored record by record: ...
0
votes
1answer
93 views

Standard deviation in Google Sheets Query

I have data of jobs (A), their executions (B) and duration (C) in my Google Sheet. I have already calculated the average duration of each job with Query language: SELECT A, B, AVG(C) GROUP BY A, B ...
0
votes
1answer
71 views

Google Sheets intersection query

I'm trying to write an intersection query in Google Sheets that lists all the rows in sheet 1 that contain a cell in sheet 1, column A, that matches at least one cell in sheet 2, column P This is the ...
0
votes
1answer
104 views

QUERY remove column and column header if blank

The attached document is a time-sheet document. There are 2 tabs, TimesheetData and InvoiceData. I want to pull through data from TimesheetData tab to the InvoiceData tab using a query. The query I ...
0
votes
1answer
52 views

Date Query in Google Sheets with importrange

I have two tables. Table A= Customer Feedback Table B= KPI In Table A I have a column for a date with format mm/dd/yyyy and in Table B I have 2 columns as from date and to date with the format. I ...
0
votes
1answer
27 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)...
0
votes
1answer
299 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(...
-1
votes
1answer
539 views

Using date as filter for my query formula

I was having problem with my query formula. it does not filter the range of my values which depends on the date ranges that I have put up. Here is my formula: =query(all!B2:G, "select B,C,D,G where ...
-1
votes
1answer
34 views

Google Spreadsheet Separating Rows By Names

I am trying to follow my own mutual fund purchases and trying to input the results into a spreadsheet to calculate the XIRR (internal rate of return). I am planning on entering my data for every ...
-2
votes
1answer
140 views

Count in Query not returning anything

When I use the syntax: =query(Clients!A:J,"select H where (H='Yes')",-1) I only get "Assigned" as a result, no # of records.
-2
votes
1answer
552 views

Conditional data input validation in Google Spreadsheet

I have spreadsheet that contains three pages: Projects Tasks Tasks are related to Projects Calendar Related to Projects and Tasks. When I select Project in Calendar and go to Task drop down I ...
1
vote
0answers
54 views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
0
votes
0answers
16 views

Regex Matching for Google Sheets Query Formulas

I'm trying to return a limited list of cells using a query that references another tab in my Google Sheets book. Please find a sample book here. The returned cells represent social media posts -- I'm ...
0
votes
0answers
21 views

Query Google Spreadsheets select Min and Max dates as Column

I have a large row data sheet with several dates by company, and I need to calculate at column level the leadtime (max date - min date). Raw Data Source: --------------------------------------------...
0
votes
0answers
17 views

Is there a way that I can flag in Google Sheets when multiple cells contain the same values?

I'm wondering if someone can help me in my battle with Google Sheets. I'm creating a rota and I want it to flag if all senior members of staff are either off or all on the same shift (either Early or ...
0
votes
0answers
12 views

Next lowest value query

New to queries. E8 = calculated value based on manually inputted data in B4 D2:D13 = contains set numerical values ranging from .00722 to .003608 from lowest to highest B7 = query field that ...
0
votes
0answers
40 views

Google Sheets Query Not Importing All Data

I am using the google sheets query function to import data from one sheet into another. It is functioning properly and taking only the data I want. However, some of the columns from which data is ...
0
votes
0answers
59 views

How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
0
votes
0answers
37 views

Automatically allocate deposits to invoices

I'm facing a difficult challenge (at least for me) with Google Sheet. I receive invoices all year long and I make deposit all year long that should offset invoices. However deposits values are ...
0
votes
0answers
55 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 column ...
0
votes
0answers
19 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
0answers
83 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
284 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
0answers
200 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 ...
0
votes
0answers
638 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
52 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
9 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
44 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(...
0
votes
0answers
239 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
35 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&"'");...
0
votes
0answers
23 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
23 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 ...
0
votes
0answers
16 views

Document with Hyperlinks from Google Sheets

I use Zapier to automatically send information from blog posts in feedly to a Google Sheet. I want to create document as automatically as possible. I have these columuns: Author Source Title ...