Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
7 views

How to reference a cell outside range using query function in Google Sheets

I'm trying to copy cells B to F to another sheet if the text in cell J contains specific text. As an example case, I'm using the query function as: =query('Sheet 1'!B1:J,"Select * Where J ...
0
votes
3answers
704 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
364 views

How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
0
votes
2answers
26 views

How do i use the query in google sheets to return N results from each tab

How do I use the query in Google Sheets to return N results from each tab. So I have this formula: =QUERY({sheet1!A2:D;sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3") ...
0
votes
2answers
27 views

How to make the cells of query function results with borders that are dynamic according to rows/column numbers?

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit?usp=sharing I would like to make the cells that have query results in All_Patients tab with borders instead of ...
1
vote
3answers
23 views

Count instances of character in a column

Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character? Below is an outline of what I'm trying to do. Note that no ...
0
votes
1answer
24 views

How to overwrite or change the value of a cell that has the results of query function if it meets a new criteria?

=query(CASES,"select C,E,F,G,H,I,J,K,L,P,Q,R where Q matches 'Admission|Pending|Transferred' AND R is null ORDER BY R ASC ",0) I use this formula to filter data to a sheet. Then I want to ...
0
votes
1answer
25 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 ...
0
votes
1answer
26 views

How to use query with a criteria where Col1 is equal to a value in a cell?

https://docs.google.com/spreadsheets/d/1kYkfBoDsJR-sBo5J52lJTXKa-EXoFYPgVPnrRGvOx5A/edit?usp=sharing =query(Importrange("1kYkfBoDsJR-sBo5J52lJTXKa-EXoFYPgVPnrRGvOx5A","LastRevision!A5:...
0
votes
1answer
23 views

Sort a column by name with query between tabs

I am pulling information from one tab to another and I believe this is why it will not allow me to sort (because it is not sorted in the original tab). The easy solution would be to sort that tab. ...
0
votes
0answers
12 views

Group By, multiple times?

I'm in the midst of creating some code to query my data. Here's an example of said data: Text version of image Name ToF AOF Jon Cena Buyer 1 Tim Brown Seller 3 Cabel Guy Buyer 5 Cabel Guy ...
0
votes
2answers
1k views

Problem with QUERY(IMPORTRANGE()) to retrieve data

I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another. =QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...
1
vote
1answer
33 views

Combine multiple IF statement/condition into one Google Sheet column

How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below: There will be 2 ...
0
votes
0answers
16 views

Auto-generate unique sheets when you add a new row in Google Sheets?

I'm a fairly new user to Google Sheets, I'm trying to set up a sheet whereby when you add a new line in one sheet, it creates a separate check-list sheet. So when a new user is added to the master ...
0
votes
1answer
20 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
2answers
1k views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
0
votes
1answer
143 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) <- ...
0
votes
1answer
27 views

Transposing a data range and replacing a column with query

I have a data set that looks like the one below in the image. And I'm trying to turn it into the expected table in the image as well. A simple transpose doesn't seem to be working.
1
vote
1answer
22 views

How to use a query to find a row of same name but last entry, then copy a value next to it (update request)

(This is an updated version of this question - I am trying my best to make myself clearer this time.) I am trying to use a query to copy values from one column to another. Both source and destination ...
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 ...
0
votes
1answer
13 views

How to extract desired text from a cell

As you can see below I have these data in single cell I want only car nos as my result
2
votes
1answer
58 views

How to use a query to find a row of same name but last entry, then copy a value next to it

I am trying to use a query to copy values from one column to another. Both source and destination columns have a name on their left columns, and the name must match. The source names could be ...
0
votes
0answers
12 views

Allocate budgets into daily levels based on date range

Is there a way in Google Sheets to allocate cost/units evenly into daily breakouts if a total is entered with start date and end date? Ideally this would happen through a Google Sheets query. I've ...
0
votes
2answers
34 views

Change all unique country abbreviations to country names and place in one cell

I need a formula that can add all the countries with that is in a column. Example: In sheet 'Export' I have the below countries of origin: And in other sheet I need to show all the countries of ...
2
votes
1answer
42 views

Counting rows based on the date it has and the current end of the week

Problem to solve: Given 2 rows, containing the author's name and a date, you have to count how many rows that author has. The tricky part here is that I have to use the date row and check how many ...
0
votes
1answer
31 views

How can gaps be quantified within a combined set of variable arrays?

I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days within a given timeframe that no crops are planted in a bed. In ...
0
votes
1answer
40 views

Look up rate for specific person in effect on or after a date

In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for ...
-1
votes
1answer
467 views

Include filename of source spreadsheet

I have 2 spreadsheets for 2 employees and I combine them into 1. From employee 1: From employee 2: I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by ...
1
vote
1answer
18 views

Problem with results of filter function from tabs that have no data yet

I made a master sheet that combines all data from 12 tabs within the workbook sheet. These tabs use query functions and importrange from other sheets. Some of sheets have no data yet like May 2021 ...
0
votes
1answer
48 views

Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
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 ...
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 ...
0
votes
1answer
28 views

How to use FLATTEN function in query function in Google Sheet

Below in my query result: In the above table "Form Response" table is the response received from Google Form. I want to convert "Form Response" table to "Desired Result" ...
0
votes
3answers
85 views

Average formula for Google Sheets with substitution over some figures

I have a row of figures from 0 to 2, some cells are empty. I need to count an average for them, but with the rule that would count 2 as 1. Thanks
0
votes
2answers
25 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!
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,", ")...
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
2answers
270 views

Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
0
votes
1answer
25 views

Query using starts with

A little help to do the following: I am good with Queries however, can't find if it is possible to do the following: =Query(range, "Select * where B begins with (**want a range from A to Can**) ...
0
votes
2answers
24 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
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 ...
0
votes
1answer
124 views

Google sheets query multi conditional date cell ref

I have a doubt regarding a change in this formulas in sheets: =INDEX(QUERY($A$3:$H; "SELECT sum (D) where C='Pub'");2) =INDEX(QUERY($A$3:$K; "SELECT sum (F) where C='Reg'and K='"& K15&"'");...
0
votes
1answer
22 views

Search through multiple tabs and pull results

Test Sheet for my use case: https://docs.google.com/spreadsheets/d/1_gcyygG8IhVc-4mXf8n09IFG5ZIYuK_UxWxPLQ5av-k/edit?usp=sharing I've had some input from another user on the site helping me search ...
1
vote
1answer
35 views

Can a formula cell reference be a variable cell reference?

Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day --- can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as ...
0
votes
1answer
23 views

Can you filter a column in size order of an imported HTML? I have tried using query with no success

Using the below importhtml I'm hoping to order the numerical data of the third column in size order, but it seems I have lost my way on the model I was following. Any help greatly appreciated. =...
0
votes
1answer
27 views

Query Function Not Putting Consolidated Data in the Right Place

So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
0
votes
1answer
27 views

Google Query: filter one record per day/week per user

I'm collecting repeated measures using Google Forms which only allows 1 or unlimited responses. I need a way to filter out responses that were entered by the same user too near to each other. For ...
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
3k views

Query returning results into merged cells in Google Sheets

Short Question: I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first ...
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
2 3 4 5
15