Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
-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("...
user avatar
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?
user avatar
  • 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?
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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) ...
user avatar
  • 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,...
user avatar
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) ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
-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=...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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' ...
user avatar
  • 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 ...
user avatar
-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. ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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....
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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!! ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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$...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 '"&...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 1

1
2 3 4 5
17