Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
16 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
16 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
16 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
23 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
83 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
34 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
18 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
33 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
17 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
23 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
46 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
225 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
48 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
23 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
30 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
67 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 ...
1
vote
1answer
8 views

Help using 2 parameters to output a specific category

I'm looking to be able to enter an "AGE" and a "V02" in a formula that tells me what "category" the person would be in ie; good, excellent, etc.
1
vote
1answer
20 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?
1
vote
1answer
32 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 ...
0
votes
1answer
8 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
21 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) <- ...
2
votes
1answer
26 views

Google Sheets sort question

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
2
votes
1answer
30 views

Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values for those groups. Using the table below as an example, I would like to determine the AVERAGE ...
2
votes
1answer
13 views

Is it possible to edit the column a Google Form response saves to?

I have a client who uses a Google Form to save data to a Google Sheet. They deleted and recreated one question, and the responses to that question are now being stored in a new column. This messes up ...
1
vote
1answer
21 views

Full Join 2 lists with 3 key values

I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing) I have income: I ...
0
votes
2answers
83 views

Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
1
vote
1answer
19 views

How do I refer to data in a column under this month?

In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column: As the dates are automatically generated using a transpose(...
0
votes
0answers
18 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
1answer
17 views

Importing only certain rows after looking at other answered questions

=QUERY(IMPORTRANGE("URL","SHEET1!A5:P85"),"SELECT * WHERE K:K contains 'Name.s'") Error Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "K "" at line 1, ...
1
vote
1answer
24 views

Concatenating cell values to a matching cell

In the following sheet I am trying to grab a list of GUID keys from tab sheet1 and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows: ...
1
vote
2answers
15 views

How to get a true when a character is NOT in range

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch =filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "![...
2
votes
1answer
31 views

Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
0
votes
2answers
19 views

Copy other cells if EITHER column has specific text

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
1
vote
2answers
56 views

Copy the contents of a row to another sheet based on a specific value

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern: A1 A2 B1 B2 C1 C2 etc. I want to copy the rows ...
1
vote
1answer
43 views

Left join 2 sheets on 2 key values, add constant value

I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet, Income: Expense: Add FixedCost to every row/date: ...
1
vote
1answer
479 views

Google Sheet Query smarter way to get multiple criteria

I use a spreadsheet where I want a list of items which contains multiple criteria. (I need all cells in a range which contains the letter 'a - g' The formula I use now is =query(Planning!G5:G37; "...
1
vote
1answer
20 views

Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
0
votes
2answers
15 views

Importing data found to be true from one Google sheet to another

I am trying to find a way to import data (text) from one sheet to another, via the same book. I assume it's going to need an IF or IFS formula because I only want data is that found to be "TRUE" (via ...
1
vote
1answer
27 views

Averaging cells that have both blanks and numbers for values that are in non continous columns with nine columns in between them

I need help averaging cells that contain numbers ranging from -1200 to 1200 approximately, or blank values only. I will be averaging them by groupings of 100 increments ie. -1200 to -1100, -1100 to-...
0
votes
1answer
13 views

How to find a string then find the sum of all cells next to the found string

So I'm trying to get G4 to automatically find "Bananas" in all of C and for every time it finds a value it adds up the corresponding number in D.
0
votes
1answer
513 views

Pivot Table Filters - Custom Formula

In my pivot table, I want to only display the entries in the column "Which Event" if the name contains "Baseball" or "Softball". I can very easily do part of this by adding a filter for the field "...
0
votes
1answer
218 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
18 views

Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...
0
votes
1answer
13 views

How do I turn a SQL database downloaded table into a Google Sheets multi-line chart?

I want to turn this table (comes from an SQL database) ...into a line chart in Google Sheets that looks like this