Questions tagged [google-sheets-query]

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

176 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
0answers
24 views

Sheets: Getting the slope of the 10 cells above current

I'd like a formula for Google Sheets, but I'm having trouble figuring it out. Say I'm on B74, I want the equivalent of =SLOPE(B36:B63,A36:A63). However, I want something that will work even when I add ...
2
votes
1answer
31 views

Query date time

I am writing a function to extract date, where my Col1 consists of date and time and my Col2 consists of date only. =query({Sheet4!A1:Q},”select Col1, Col2 where Col1 = date ‘”&TEXT(today(),”yyyy-...
2
votes
0answers
28 views

Google Sheet Column Tick box validation

Complete newbie here, sorry if this sounds silly. I have 3 columns and 60 rows of tick boxes, I have the tick boxes adding up at the bottom of the sheet. Question is I do not want a user to tick more ...
2
votes
2answers
198 views

Group by in query Google Sheet select different columns

Want to get the results described on the screenshot. How can I achieve it. QUERY: =QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'") Want to ...
2
votes
0answers
126 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
2
votes
0answers
42 views

How to use COUNTIF and UNIQUE

I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
2
votes
1answer
385 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 ...
2
votes
2answers
5k views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
2
votes
3answers
467 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
2
votes
1answer
3k 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
2k 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 ...
2
votes
1answer
757 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 ...
1
vote
2answers
41 views

Select Unique on 1 Column in Google Sheets - Distinct Doesn't

I'm trying to set up a kind of a bid thing, where the top 5 respondents receive a prize. I was hoping to use SQL to get the top 5, but I can't remove duplicate bidders and only take their top bid. I ...
1
vote
1answer
22 views

How to create an average value for every cell which contains a certain phrase?

I need to find the average value of cells containing a certain set of initials to create a scoreboard. The value of the cell being the score and the brackets containing the initials of the score ...
1
vote
0answers
16 views

How to generate URL from cell contents on entering values

I have column A with values such as 345722 456922 71881 I want to generate URLs from these numbers like https://example.com/id/xxxx. I want the column values to be updated and the generated URL to ...
1
vote
0answers
20 views

Combine two separate, working queries

Trying to combine two separate, working queries, so both can be used on a single sheet. The idea is a User selects Data for a Resource (sheet TAB), and then either: a Region (Column D) or a County (...
1
vote
1answer
15 views

Fill in missing dates in Google Sheet query

I have a Google Sheet query like this: =QUERY(A2:I;"select I, sum(C) * -1, count(C) group by I order by I ASC"; 1) And it yield a result like this: In the source data not all months are ...
1
vote
2answers
34 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
1
vote
1answer
17 views

How to combine two different columns

I have two different columns with emails and I'd like to display all the emails in a separate column in the spreadsheet. Both the columns can be extended further so I can't select all cells statically....
1
vote
0answers
28 views

Array formula to extract N non blank cell in Row 1-16

I have a google form where team coaches input which players played on a given day. They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored. When the ...
1
vote
1answer
41 views

Aggregate data to show text in one cell

I need to put together multiple entries into one where the results is text and has to be in one cell. From this to this
1
vote
2answers
23 views

Want to use a cell to get a limit number

Here is my query. I want "select * limit 5") the 5 to come from a cell. How do I do that? =ArrayFormula(AVERAGE(QUERY(SORT(Tabell!D4:D33,ROW(Tabell!D4:D33)*ISNUMBER(Tabell!D4:D33),0),"...
1
vote
1answer
20 views

Getting the column header if the row value is the greatest of the entire row

I think that the best way to describe what I want is with a image Check the image. Each row will have a description (Of a feature of the programming language, for example) each language will have a ...
1
vote
1answer
26 views

How do I return all results associated with an input cell in Google Sheets?

I have a time study data set that has three different order types and resulting times to fulfill that order. An Order Type will be entered into C8. In cells C9:C, I want to create an auto-updating ...
1
vote
2answers
181 views

Google Sheets - Get unique values from a column and return the values a single column

I was hoping to get some help and direction. I have two sheets--a 'Raw data' sheet and a 'Dashboard' sheet. A user would dump the raw data in the first sheet and the dashboard will summarise figures ...
1
vote
0answers
14 views

Data has both text and number, I need to move to another sheet from master but only the numbers show up…I need both

I've used this formula =query('Form Responses'!1:136,"Select * Where E='name'") but all I get is the numerical value entered in a cell. If someone enters both a number and text the cell is blank. In ...
1
vote
1answer
71 views

Trying to pull Duplicate data from multiple columns

https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0 I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
1
vote
0answers
69 views

Find Difference from Previous Consecutive Row in Google Query Pivot Table

Say I have source data like the below: OriginDate|Name|IncidentDate| where Origin Date is constant and the earliest date of my imported data set. I've formatted the dates to numbers so I can perform ...
1
vote
0answers
10 views

In a large document with many comments how do you review everything, both open and resolved comments, page by page?

The 'Comments' button allows me to see the comments in date order but that doesn't give me the context of the page it was on. I have no idea when some of the comments where made, that I am looking for,...
1
vote
0answers
89 views

Importrange, editing from the sheet that its imported too

I currently use importrange to get timesheets to a master sheet. I then download this sheet every week and update to my KPI's As all the information is on the master sheet where all data is ...
1
vote
2answers
171 views

Array formula with index match to return array of matches

I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA. What I'm attempting to do is match a ...
1
vote
1answer
48 views

Help Sorting Data

I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link: https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
1
vote
2answers
56 views

Extracting range from of an array

From the following single cell array: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34 I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell. The Challenge is ...
1
vote
0answers
17 views

Summarize all data from other tabs with same format and column

I have customer sheets which i must summarize into a single tab so i use this query; =QUERY({'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'...
1
vote
0answers
33 views

How to filter a query by one more column

As a data analyst, I'm trying to get a specific string from Google Sheets with a query, so that my table can be more filtered with useful information. This is my current query which is working, but ...
1
vote
0answers
13 views

How do I auto timestamp a sheet when something is moved to it

I am using this function to automatically move a row to a secondary sheet when a set value is entered. function onEdit() { var sheetNameToWatch = "Tracker"; var columnNumberToWatch = 4; var ...
1
vote
0answers
67 views

Date Formatting Issue in Google Sheets Query

I created a shared shipment tracker with google sheets where updates are made in a master tab, then filtered into several regional tabs via query function to better visualize shipment status by region....
1
vote
0answers
330 views

Create new sheet with a “template” based on cell value

I'm trying to do run some stats on the AFL (Australian Football league) here in Australia and want to get some better understanding on players form based on some key stats.= In order to do this, I ...
1
vote
0answers
165 views

Query Import Range returns two rows

I've been using =Query(Importrange( functions for quite a while now and something I can't figure out is why the import is returning with two rows. Here's the formula I've been using: =QUERY(...
1
vote
1answer
20 views

help with query finding multiple entries in 1 column

I am trying to query several different colors from one column. This is what I have so far: =query('Raw Data'!C1:J1000, "Select E, D, I, J, C, F, H where H contains 'Monday' and F='Green' ...
1
vote
0answers
20 views

Using a query on an importrange, not pulling information

I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck. =QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
1
vote
0answers
31 views

Multiple columns and get unique on preferred column

I want to get 3 columns only if a preferred column is unique. What is the correct syntax in Google Sheets? =Query('Sales'!A:AX,"Select A, B, C where A is unique")
1
vote
1answer
38 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 ...
1
vote
1answer
337 views

How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
1
vote
1answer
2k 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
17 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
4k 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 ...
1
vote
0answers
491 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. ...
1
vote
1answer
436 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
2answers
229 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...