Questions tagged [google-sheets-query]
Use for questions about QUERY built-in function from Google Sheets.
831
questions
0
votes
1
answer
21
views
Match value of cell if in between range and text match
Desired outcome:
I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
- 17
-1
votes
0
answers
17
views
Query error on import range [duplicate]
I am importing data from another sheet. I am trying to not import rows that have a null value in a specific column. But no matter what I do it still imports the null row.
=QUERY(IMPORTRANGE("...
0
votes
0
answers
17
views
Extra m appears in com while working on Google Sheets
Check the above image when I add any link my Google Sheet every time an extra m appears in the end of .com. How can I resolve this?
- 1
1
vote
1
answer
34
views
Return matching text value in cell
I have a sheet of products and skus - I would like to have a formula that searches the entire column for matching text strings and returns it in a separate cell as the text string
Is this possible?
- 17
0
votes
1
answer
14
views
How to return multiple rows matching multiple criteria in spreadsheet
In Google Sheets, suppose I have rows like:
name
team memberships
Ben
operations
legal
Kate
legal
UK residents
infrastructure
growth
Carlos
operations
senior management
legal
Tina
operations
UK ...
- 101
0
votes
1
answer
18
views
Pivot Table - How to copy sum value onto another sheet
I have set up 3 sheets in Google sheets in related to budgeting/expenses.
The first contains the full budget (say Sheet A for example);
The second contains the expenses (Sheet B);
and the Third ...
- 3
0
votes
0
answers
9
views
searching and sort large amounts of data - how to do so quickly
I have about 3000 rows in each of two tabs on one Google sheet.
I have 7 columns of data (BH...BN) and 3000 rows in a tab called 'import sheet' and on a second sheet called 'stats', I have about 2000 ...
1
vote
2
answers
33
views
Using Query to select colum based on headers
I have a sheet with a large table of data with a header for each column. I want to import a specific column to another sheet using the header name. I am using the below formula to do so (which has ...
- 11
1
vote
1
answer
31
views
How to stack query results into single cell formula
I'd like to generate a URL based on a list of items that will change over time and I'm having a little trouble. My data looks like this:
Items
URL
Item 1
Item1URL
Item 2
Item2URL
Item 3
Item3URL
...
- 11
0
votes
2
answers
40
views
Vlookup with column search by headers
I am downloading the "Data" report from the system, in which the order of the columns may be different, but their headers will always be the same and the first "ID" column will ...
0
votes
1
answer
30
views
IMPORTRANGE with condition
I Would need help with this one.
I would like to transfer data from one Google workbook to another using importrange but with a condition.
Condition: If column 2 is Apple (transfer data from column 3) ...
- 1
0
votes
1
answer
45
views
In Google Sheets, take the first and last value of a query to sumarice, grouped by user and shorted by date
:Edit -> added a working spreedsheet with the data https://docs.google.com/spreadsheets/d/1RNlZtvYZIpRF97u-7cOEGLS4my4OcLHD_1OJfDRCVCA/edit#gid=2100307022
I have a spreadsheet with a list of users,...
0
votes
1
answer
21
views
How do I make text in a column appear after the rows have been filled?
I have made this spreadsheet to help our mechanical workshop track labour. I have a problem on my spreadsheet where column K displays "follow up". The end users (technicians and foreman) ...
- 3
0
votes
1
answer
17
views
Pull some data, based on a date
I need to pull some data, based on a date.
QUESTION;
what is the value in column D, based on a date for example 'TODAY' or 2/4/2022
I ideally want to populate this detail onto another tab, where it ...
0
votes
1
answer
20
views
Conditional formatting in Google sheets to indicate overages
To help identify issues faster I am trying to add conditional formatting for column Z to turn red if the amount in that column is greater than the amount in the columns AA or AB and only if column AA ...
- 3
1
vote
1
answer
28
views
Student Pilot Spreadsheet - Retrieving date of last landing
Overview
As a student pilot I try to keep record of all my flights, total hours, costs and number of landings in Google Sheets.
As a safety measure our aviation safety agency (EASA) requires pilots to ...
- 11
-1
votes
0
answers
7
views
Email missing the attachment [duplicate]
I am trying to send an email with an attachment. Email arrives with no attachment. Please find below my script:
function EmailQuote() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=...
0
votes
0
answers
31
views
Working with public Google Sheet. Can I decide when my edits go public?
I am working with a public Google Sheet that only I can edit. I would like to prevent others from seeing the intermediate phase in which I am making changes and be able to decide when the public sees ...
- 1
0
votes
1
answer
17
views
Google Sheets: Is it possible to retain an IF/ELSE formula *AND* enable custom number entry into a cell?
I'm using the IF/ELSE formula in a cell:
=IF(logical_expression, "TRUE", "")
My use case is the following:
If the logical_expression is TRUE: A logic-derived value is shown in the ...
0
votes
1
answer
28
views
I want to query a set of data (multiple rows and columns) and return the rows of data based on an input value (search box) - google sheets
Raw data I want to query:
I want to query this data based on an input value that could feature in any of the columns of data to then return the correct rows of data:
For example if I type in 'A001' ...
- 3
0
votes
1
answer
33
views
Getting error using AVERAGEIFS in Google Sheets
I am trying to get the average score that team members in Team 3 in a given quarter gave for a question.
Column D contains team names, Column E contains the scores (on a 0-10 range). Column AK ...
-1
votes
1
answer
77
views
Replacing text of links in google sheets
I have a bunch of links in cells in my google sheets. I want to replace all the text of them all just to say "LINK" so that when I click on them it's still hyperlinked to the correct site.
...
- 1
2
votes
1
answer
81
views
Add iferror() for every error that occurs in sheet but keeps formula in this cell
I have a sheet with a lot of formulas. some of them gives me an error. I'd like to make something similar to ctrl+h and replace values but I'd love to find every cell that is giving an error and ...
- 23
0
votes
1
answer
20
views
How to create an IF function that has multiple outputs
I am trying to create a query that populates information based on the user's selected drop downs. Query is all built and functional, and populates the items in the master data that match the selected ...
0
votes
0
answers
10
views
Google spreadsheet help for putting correct formula to automate operations
Your help can save my lots of time & energy,
I am new to google sheet & don't know much abount coding, i have made a google sheet to automate our therapy clinic operations by internet learning....
user291081
0
votes
1
answer
17
views
How to combine cells of data in Googlesheet to a single/consolidate row and remove duplicates?
I'm trying to consolidate all the invoices that we get from the same contractor to a single row that will have the business name, all the memos/invoices for the week in one cell along with the added ...
0
votes
1
answer
19
views
Formula for Daily Cash Balance Sheet
I have created a Google Form for cashiers to balance out their cash drawers at the end of each day. Currently I have the form set so that they only have to count the amount of each coin/bill and not ...
0
votes
2
answers
70
views
Google Sheets: keep track of days spent in different countries
I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country:
Date & time
From
To
Outbound country
Inbound country
Days in ...
0
votes
0
answers
14
views
QUERY function with date range not working and shows wrong results
I have been working on creating a personal finance spreadsheet and from my experience of using QUERY previously to create dashboards, I thought I would have no issues with this but I was wrong! The ...
- 1
0
votes
0
answers
16
views
Google Sheets– Scatter chart comparing two variables
I'm trying to compare two different variables, one as x and one as y, for 30 different countries on the same graph. Basically, I'm trying to get it to look like the graph below, only with several ...
- 1
0
votes
1
answer
34
views
Google Sheets App Script Sorting
I'm trying to have an auto sort function only impact one sheet in a workbook rather than all of them. I've done this in other projects and def messed this up along the way - any help is appreciated!!
...
0
votes
1
answer
28
views
Highlight value in column based on list of values in other column that's on the same line as another value
Is that subject confusing enough?
Here's my spreadsheet: https://docs.google.com/spreadsheets/d/1pksa5_7R2TuPaufIS8mPl6gxZ8aPoMdEfkebKsx1YKI/edit?usp=sharing
It's for calculating speed@RPM for an ...
- 3
0
votes
1
answer
161
views
Using IMPORTXML in Google Sheets: How to Combine XML Data Sources as a Single Set Before Applying XPATH Query
Using the IMPORTXML function on Google Sheets, I've got two XML data sets at two URLs, respectively in cells A1 and A2. I've got the XPATH query in cell H1. I would like to run a single query on the ...
- 171
0
votes
1
answer
21
views
Query function with variable in the Col1=
Here' the formula:
=query({{$P$3:$P$34;$R$3:$R$34;$T$3:$T$34;$V$3:$V$34;$X$3:$X$34;$Z$3:$Z$34;$AB$3:$AB$34;$AD$3:$AD$34},{$Q$3:$Q$34;$S$3:$S$34;$U$3:$U$34;$W$3:$W$34;$Y$3:$Y$34;$AA$3:$AA$34;$AC$3:$AC$...
- 1
0
votes
0
answers
17
views
Need helping knowing how to process this data in Google Sheets
I need to sort this data in Google Sheets. I have names and then each name has 4 letters assigned to it, one in each column.
An example is I need to know everyone who has a T and an I in column B and ...
- 1
0
votes
1
answer
21
views
Summing groups, Aggregate costs by items in groups
I have a sheet with a grouping by Feature / Subfeature / Task.
Here is an example:
How can I aggregate the cost from individual tasks to subfeatures (eg. rows 147-150 of Sign-In subfeature group) and ...
- 101
0
votes
0
answers
15
views
Google Sheets Merging two sheets together with common rows and uncommon rows
I have two Google sheets each with 4000 columns and 8 rows.
I would like to merge these two sheets together using 6 of the common columns and writing over 2 of the uncommon rows. Into a new sheet. Is ...
- 1
0
votes
0
answers
18
views
Problem with Query Count distinct col with a where clause
https://docs.google.com/spreadsheets/d/e/2PACX-1vSac8-YOPZUih1gKI3uTHbfJpIew3QwQ6ccM0Od7lF-E4eLyb-y2adRsrJ9JpyDulkbsMbhIt-qwXaJ/pubhtml
Link to an example.
On the total tab, I have:
=QUERY(data!1:963, ...
- 1
0
votes
0
answers
10
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 ...
- 1
0
votes
0
answers
65
views
Google Sheets Query - Filter using drop-down list, return all values
Using Query function to filter and sum sales values using up to 4 different criteria:
Agent
Month Sold
Date Sold
Product/MFG
This is in a "Dashboard" where each criteria can be selected ...
- 1
0
votes
1
answer
28
views
Select were Col1 contains range of other column
I would like to ask for help with my query.
Sheet1 contains another sheet that is imported with the formula import range. The first column contains the first names, the second row is the date, and ...
0
votes
1
answer
27
views
Problems merging 4 columns into 1 and charting them in Data Studio
I would need to unify the data from columns Metod_1,Metod_2,Metod_3,Metod_4 and Metod_5 into a single column, but keeping the resolution date data associated to each row:
Having resolved the above ...
- 3
-2
votes
1
answer
34
views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
- 1
0
votes
2
answers
27
views
Is there a way to remove entries based on condition in =filter?
I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
0
votes
0
answers
46
views
Query formula does not update automatically
I am having an issue with queries.
I track spotify data and want to find the top song for each album so I use a formula something like this:
=query('album daily'!$V5:$Y11,"Select V,Y order by Y ...
- 1
0
votes
1
answer
55
views
how to remove sum label from query
=QUERY(INDIRECT(C13&"!$C$5:F"), "select Sum(F) where D='"&$E$12&"' and C>=date '"&TEXT($G$4,"yyyy-mm-dd")&"' and C<=date '"&...
- 1
0
votes
1
answer
30
views
Clear cells script
So I have a google sheet document which houses figured for each hour of the day from 6 - 16:00 (each hour in a different tab.
I’m looking to add a script and a button that will allow me to clear all ...
0
votes
1
answer
52
views
Flip a column of Data vertically Import range
I'm importing the data from another sheet using the import range. How can I flip a column of data vertically in another Google Spreadsheet?
=sort(index(IMPORTRANGE("URL")
E.g. if the column ...
- 1
0
votes
1
answer
46
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 ...
- 1
0
votes
1
answer
32
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 ...
- 1