Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
955
questions
2
votes
2
answers
305
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 ...
- 7,096
0
votes
1
answer
1k
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")},"...
CommunityBot
- 1
1
vote
2
answers
2k
views
How to group data (by row and column) in a Google spreadsheet?
I have data as follows:
Name | Value | Y/N | Value | Y/N
A | 10 | Y | 12 | N
B | 100 | Y | 120 | N
A | 20 | Y | 10 | Y
B | 200 | N | 120 | Y
C | 1000 |...
CommunityBot
- 1
0
votes
1
answer
7
views
Match criteria THEN CountIFs
I am trying to create a formula in Google Sheets for a list of account names.
In Sheet1 I have a product list where each row contains a product, its status,m and associated account.
In another sheet I'...
CommunityBot
- 1
-1
votes
2
answers
1k
views
Using a checkbox to auto-populate a row on another sheet
I have this spreadsheet which is used for receiving and recording work. SST responses sheet is where people submit requests, Workload is where we record all work.
I'm wanting it so that when someone ...
CommunityBot
- 1
2
votes
3
answers
3k
views
Select last entry from spreedsheet
So I have a Google Form dumping data into spreadsheet tab form responses 1.
I have created another tab labeled summary. I need to have the summary tab only show the latest entry for a given pilot ...
CommunityBot
- 1
0
votes
1
answer
251
views
Add a constant number to google sheets query result
I have data in two columns labeled Email and 01 respectively.
I want to use a query to rearrange the data so that there are 3 columns:
Columns 2 and 3 will contain the data from my original two ...
CommunityBot
- 1
-1
votes
0
answers
11
views
Delete or Remove Rows based on search data, these codes worked individually but will not work when combined to one script. Why? [closed]
function removeRows() {
var sheetName = "DBDR_AllDevicesTAB"; // Replace with your sheet name
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var range = sheet....
1
vote
2
answers
1k
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
...
CommunityBot
- 1
0
votes
1
answer
1k
views
Best way to change dates in Google Sheets in all the tabs?
I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
CommunityBot
- 1
0
votes
1
answer
99
views
How do I chart timeseries data organized by one of the columns?
Here's what seems to be a simple common thing in excel/sheets that I can't seem to be able to google in the right way. three columns: timestamp, email, things_done.
I want a chart with time as the X ...
CommunityBot
- 1
1
vote
2
answers
17
views
Filter Rows while also doing SUM columns based in column header values as part of Select statement in Query function in Google spreadsheets
I am trying to set up a pivot table using Query function in Google Spreadsheets, so that this can be used for various different reports.
Below is the sample data i have.
As per the guidance from the ...
CommunityBot
- 1
2
votes
2
answers
3k
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 ...
- 7,096
0
votes
1
answer
42
views
Looking for help on formula while pulling data from a different GSheet using Index & Match?
Facing a #NA error while pulling data from a different google sheet, using Index & Match. Need Test1 (GSheet) data pulled in to Test2 (GSheet) on yellow highlighted cells.
Test1 - https://docs....
- 35
0
votes
1
answer
2k
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:
Data 1
Date
name 1
1/1/2018
name 1
1/2/2018
...
CommunityBot
- 1
1
vote
1
answer
78
views
How do i Import a rows from one Google Sheet to another with filter?
I have a Google Form, the answers go to Google Sheets.
I want to import answers from sheets (Аnswer) into new sheets but with a filter applied.
For example: from sheets (answer) into a new table, I ...
CommunityBot
- 1
0
votes
1
answer
58
views
Get a count after a specific date grouped by person
I have a Google Sheet with dates in Column C, alphanumeric in Column E, and Employee Names in Column F. I am trying to figure out how to build a query to give me a count for each employee listed in ...
CommunityBot
- 1
1
vote
1
answer
875
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 ...
CommunityBot
- 1
0
votes
2
answers
278
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 ...
CommunityBot
- 1
1
vote
1
answer
21
views
Insert sequence number and embed it to my data
I have this data:
A
B
1
X
BIRD
2
X
LION
3
Y
FISH
4
Z
CHICKEN
5
X
DUCK
6
Y
SNAKE
7
X
TIGER
8
X
CROCODILE
9
Z
ELEPHANT
10
Z
DRAGON
11
Z
WORM
I use this formula:
=index(
trim( split( ...
- 7,096
1
vote
2
answers
3k
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 ...
CommunityBot
- 1
3
votes
2
answers
6k
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 ...
CommunityBot
- 1
0
votes
1
answer
45
views
if cell is greater or less than the sum of the range of cells
I've looked around for an answer to this without success.
I have a spreadsheet where I need the "18" change and be highlighted as green if the sum of the next 4 columns with a check is ...
CommunityBot
- 1
1
vote
1
answer
513
views
How to filter out empty cells in Google Sheets with Query & Importrange
I am trying to use this formula but it's not working. Do you hava an idea why?
QUERY(IMPORTRANGE("IMPORTEDDATASHEET";"06_20!M:AJ");"SELECT SUM(Col17) WHERE Col1 = 'Company ...
CommunityBot
- 1
0
votes
1
answer
70
views
Trying to count values with multiple criteria
I have a file that contains a planner with dates in the lines and the roles of my team members in the columns.
I need to be able to count how many people are working per day and per role.
I need to ...
CommunityBot
- 1
2
votes
2
answers
219
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 ...
CommunityBot
- 1
1
vote
1
answer
28
views
Keep matching columns aligned when stacking ranges from different tabs that are missing certain columns
Since a while back, I have been trying to find an alternative to Power Query in Google Sheets to combine several sheets of data into one by matching on column letter.
For example, in tabA I have the ...
CommunityBot
- 1
1
vote
1
answer
111
views
Multiple Gsheets data querys
Hey all so I currently have 3 Gsheets. 2 are sql imports with the below tables. The goal is to get the information based off a Type Id 28353 into my Master sheet. The plan is to get all values from ...
CommunityBot
- 1
0
votes
1
answer
333
views
use a string as a parameter in a equation
I want to use a string as a parameter in an equation to get a value from another tab.
For example, i want to use the string 'OKT' in the gray column as a reference inside the equation in the orange ...
CommunityBot
- 1
0
votes
2
answers
51
views
Want Query to return first column and count across the row
I have multiple google sheets with student names and scores from different courses. I am trying to query those sheets to return the student's name and a count of completed assignments in a totals ...
CommunityBot
- 1
1
vote
1
answer
848
views
How to run a google sheets query against a dynamic range
Background
I have a spreadsheet that displays data internet consumption by users that looks like this
userid feb1 feb2 feb3 . feb29
u1 100 34 23 . 4
u2 ...
CommunityBot
- 1
0
votes
2
answers
981
views
Google Sheets QUERY to return if contains multiple selection and all other values
I have a data set similar to the image in cells A5:D31. I am trying to write a QUERY function that will return the records that match the "filters" in range A2:D3.
In this example, the query ...
CommunityBot
- 1
1
vote
1
answer
1k
views
How to select existing sheet to be populated by a Google form?
I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report.
Now the question is:
How can I assign a particular sheet[already created in ...
CommunityBot
- 1
0
votes
1
answer
17
views
Data from second importrange function is in the wrong column
When I import 2 ranges that are each in Columns A to L in their respective sheets, the second range is being returned in Columns M to X.
How can I fix it so the data from both ranges is returned in ...
- 7,096
1
vote
1
answer
954
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:
=...
CommunityBot
- 1
0
votes
1
answer
43
views
How do I use a query to show a row with a name and also show the next 8 rows below it on Google Sheets?
I am using google sheets to have a group of people fill out when they are available and what it to show up on another spreadsheet and be able to move as well. 0 signifies they are free and 1 means ...
CommunityBot
- 1
1
vote
1
answer
360
views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
CommunityBot
- 1
0
votes
2
answers
334
views
How to Count Combinations In a Set (Including dupes)
Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row.
Example data:
A
B
C
D
E
1
A
A
A
B
...
CommunityBot
- 1
1
vote
1
answer
1k
views
How to ignore certain strings when creating unique list of words in Google Sheets?
In Google Sheets, I am using the formula to create a unique list of words:
=unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+")))
But I want to filter out certain strings.
My unique list ...
CommunityBot
- 1
2
votes
2
answers
501
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 ...
CommunityBot
- 1
1
vote
1
answer
125
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 ...
CommunityBot
- 1
0
votes
1
answer
700
views
How to use FLATTEN function in query function
Below in my query result:
In the above table "Form Response" table is the response received in Google Sheets from Google Form.
I want to convert "Form Response" table to "...
CommunityBot
- 1
0
votes
1
answer
31
views
SumIfs, looking to match part of a string in criteria range with criteria driven from another cell
=SUMIFS('Unspecified Clean URL - Freefor'!B:B,'Unspecified Clean URL - Freefor'!A:A,"*Orlando*")
I can manually type in the City Name I am using as criteria, but I have a column of them and ...
CommunityBot
- 1
0
votes
1
answer
836
views
Summing hours spent on a subject in Google Sheets' "Assignment Tracker"
Hi everyone!
I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject.
Is there a way to automatically ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Get value of merged cells for query function
I'm trying to find if someone has been scheduled in the AM and PM in a table like this:
So I want to write something like this:
=QUERY(A13:B1002,"select A,B where A matches 'AM' and B contains '...
CommunityBot
- 1
0
votes
1
answer
836
views
How can I do an aggregate function with multiple columns with a Google Sheets query?
In Google Sheets I have numbers in both columns of A2:B11.
I'd like to get the combined average of the numbers from both columns using the QUERY function.
The result would be a single number that ...
- 7,096
2
votes
1
answer
25
views
Compare imported range with existing range, and filling corresponded data to the fields
I am not sure how to import data to corresponding cells.
query(IMPORTDATA("https://www.iftach.org/taxmatrix/charts/4Q2020.csv"), "Select Col4 where Col1 = '"&A4:A&"'&...
CommunityBot
- 1
1
vote
1
answer
80
views
SUM across other worksheets (without hardcoding their names)
I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
CommunityBot
- 1
0
votes
1
answer
65
views
Add data to certain columns if conditions of separate 1 or more conditions for data in each row are met
I have a table with "manufacturing quotes" and each quote has 1 or more parts in it (please see screenshot below). The data in parts1-3 (columns E-J) are filled, however, in reality, the ...
CommunityBot
- 1
0
votes
0
answers
9
views
Query/Flatten/Transpose data of muliple types Goole Sheets
I was able to learn some through google and built a formula to flatten, transpose and query data into a new database style format. Unformatately, my ID column has numbers and IDs with letters. So, due ...
