Questions tagged [google-sheets-query]

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

2
votes
1answer
1k 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 |...
2
votes
1answer
297 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 ...
2
votes
1answer
25 views

How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
2
votes
1answer
411 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
9 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 ...
1
vote
1answer
28 views

How to get data to fill alternative cells when data is present in a zigzag pattern

https://docs.google.com/spreadsheets/d/1kwGbFd88cgifQRK_ZqsahQt1aDLzMTnmxHKs7mML5jA/edit?usp=sharing How to get data entered in one cell to fill another cell in a different location with same values ...
1
vote
1answer
14 views

Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
1
vote
1answer
28 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
1
vote
1answer
172 views

How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
1
vote
1answer
26 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
484 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
686 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
506 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
412 views

Copying values between Google Sheets

I have 2 Google Sheets. I have data on Google Sheet A and I would like it to automatically transfer some values to Google Sheet b. For example, I have some email addresses and some data associated ...
1
vote
1answer
1k 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
208 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
548 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
448 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
9 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
17 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 ...
0
votes
1answer
21 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
11 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 ...
0
votes
1answer
14 views

Split data of a row in multiple rows

I've got some problem with data coming from a Google form. The result are: A|A|A|A|B|B|B|B|C|C|C|C| What I want to get is: A|A|A|A| B|B|B|B| C|C|C|C| Is there a way to get a similar result?
0
votes
1answer
7 views

How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
0
votes
1answer
15 views

How can I do an aggregate function with multiple columns with a Google Sheets query?

I've got two columns of 10 numbers in Google Sheets. I'd like to average the numbers in both columns using a Google Sheets query. Something like this: =query(A1:B11, "select avg(A&B)", 1) <- ...
0
votes
1answer
26 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
0
votes
1answer
16 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
0
votes
1answer
10 views

How can I have a 2nd function start in the cell after the output of the 1st function?

So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
0
votes
1answer
42 views

Filtering a QUERY using MATCH - how to pass column value into function? Or is there a better way?

I want to get a subset of data from another tab. Eventually, this will become a script and pull data from a different sheet. I can use QUERY to get all the data available, but I only want to return ...
0
votes
1answer
25 views

Average first 3 values excluding empty cells in google sheets

Trying to find the average of the first 3 values in the column. I'm already running a query to find the average data within the last 3 days =average(query({$A$10:$A$107,$E$10:$E$107},"Select avg(...
0
votes
1answer
25 views

Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

For Google sheet with the following data in A2:B4 06/29/2019 1:25 PM 1 06/30/2019 1:32 PM 1 07/01/2019 1:40 PM 1 The output will show Monday 1 1 Saturday 1 1 Sunday 1 1 Can this be ...
1
vote
0answers
44 views

Google Spreadsheets - Query Select A, B, MIN(C), group by A only

thanks for the help! Sorry if I'm expressing myself in a confusing manner, I'm a beginner at this... I'm trying to create a spreadsheet to help me plan and analyze grocery expenses more efficiently. ...
0
votes
0answers
16 views

Issue with SUMIFS pulling data from QUERY across multiple sheets

I would like to sum values from a QUERY generated list in the sheet I/S/N/W to the sheet BUDGET for determining a value based on the date in BUDGET!B8. The function in BUDGET!B8 is =MONTH(TODAY()). ...
0
votes
0answers
14 views

ARRAYFORMULA + QUERY + IMPORTRANGE - Final cell reference in IMPORTRANGE is acting like it's absolute when I need it to be relative

I know I'm chaining a lot together here, but it's SO close to functional. Here is my formula with the doc address altered for privacy. =ARRAYFORMULA(IF(ISBLANK($AE$3:$AE),(query(importrange("https://...
0
votes
0answers
9 views

Edited Google Form questions not same when open in spreadsheet

I used google form to create few surveys. I do some edit on the questions, questions sequence, etc. However, the questions sequence in spreadsheet is not the same as in google form. If there are new ...
0
votes
0answers
21 views

Dynamically reference DATA in Query in Google Sheets

I have a query in a Google Sheet structured like so: =QUERY({IMPORTRANGE(Expenses_2018Q4,"DATA_Expenses"); IMPORTRANGE(Expenses_2019Q1,"DATA_Expenses");IMPORTRANGE(Expenses_2019Q2,"DATA_Expenses")},"...
0
votes
0answers
20 views

Order query() by dates using the format Mon/D/YY (Apr/13/19)

I'm importing data from a sheet that uses the Mon/D/YY date format (Apr/13/19). When I try to sort the entries chronologically with the following request, they're instead sorted alphabetically (A is ...
0
votes
0answers
242 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
98 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
46 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
15 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
175 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
41 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
106 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
574 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
935 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
66 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
63 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
18 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 ...