Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
1 vote
2 answers
415 views

Search multiple columns for one value

This is roughly what my table looks like: A B C D E F Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
1 vote
1 answer
22 views

Formula to check if a form is filled out each week

The spreadsheet we are attempting to construct using Google Sheets takes form responses across multiple weeks (which come from the same form) and checks to see if that form has been completed for that ...
2 votes
1 answer
29 views

Rearrange columns in Google Sheets

I have data in Google Sheets that I want to rearrange. In each row, starting from the 4th column onwards, there are repeating column pairs of Favorite Fruit # & Favorite Color # where # indicates ...
1 vote
1 answer
187 views

Dynamic & extendible category names

I'm having trouble on something with Google Sheets: I need category names to be dynamic & extendible between 15-200 values, so it's not quite worth an Apps Script, would be super cool to put the ...
0 votes
1 answer
3k views

How to get list of values in one column for distinct values in another column

I need to do some analyses on a set of data for work and cannot figure out how to do this. I am familiar with Google Sheets QUERY, but could not do what I want and could not find anything like it ...
0 votes
1 answer
27 views

Formula to select certain people only

I have a spresdsheet in google sheets and use the following formula =QUERY('Main Data'!A2:U,"select B, sum(J), sum(E),count(E), sum(F), count(F), sum(G), count(U), WHERE A IS NOT NULL AND O>=...
3 votes
2 answers
145 views

Combine different sheets with importrange for dynamic data from google forms

I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to ...
1 vote
1 answer
1k 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!! ...
2 votes
1 answer
3k 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 ...
3 votes
1 answer
6k 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 ...
0 votes
4 answers
2k views

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

Is there away on google sheets to paste text and automaticly create new rows with every line break or ect? I will paste a lot of text by this format in the example below (700 lines in total): ...
1 vote
1 answer
442 views

Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets

I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of ...
0 votes
1 answer
115 views

How do I return the value of a specific cell within a range on Google Sheets based on current date?

I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week. The worksheet is currently ...
2 votes
2 answers
250 views

Aggregate data to show text in one cell

I need to put together multiple entries into one where the result is text and has to be in one cell. From this data in A:B This is a sample; the actual list in Column A may be extremely long. A B 1 ...
1 vote
1 answer
535 views

Google Sheets Query, filtering with a range of values

I want to query data by checking if the data_range's column C (invoice#) matches this tab any value in column B (invoice#), while this tab column B will have variable data size ( copy from another ...
1 vote
1 answer
43 views

Google Sheet Query Filtering doesnt seem to work numeric values

=IF(E3="Met notificatie";QUERY(CSVupload!A2:I;"select F, D, E, C, B where A = '"&C3&"' AND I>=0 " );IF(E3="Zonder notificatie";QUERY(CSVupload!A2:I;&...
1 vote
1 answer
36 views

Query meeting 2 conditions

I am trying to create a query that pulls information based on two drop downs. The first drop down searches "staff" or "student" in column C of my form response tab, and the second ...
0 votes
1 answer
67 views

Cannot get Query to display time

I need help getting to show the result of a dropdown. If you look at C22, there's a dropdown for a specific date. I'm currently working on row 24 and have applied the formulas I think should work. I ...
0 votes
1 answer
127 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
1 answer
189 views

Google sheets query multi conditional date cell ref [duplicate]

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&"'");...
3 votes
3 answers
660 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
1 answer
65 views

How to filter data from various sheets based on dropdown selection

I am setting up a sheet to track renewable services for my company. There will be various tabs, for example, Domains, Hosting, Google Workspace and Phone Numbers. I would like to be able to filter all ...
2 votes
1 answer
210 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 ...
2 votes
2 answers
289 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 | | ...
1 vote
2 answers
197 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 ...
0 votes
1 answer
55 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 ...
0 votes
1 answer
20 views

Improve reliability and speed in Google Sheet query array

In Google Sheet, I'm using a rather large array of queries to select data from X number of Sheets. The number of Sheets is unknown, and manually entered into a list from which the queries pick up ...
0 votes
1 answer
190 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. =...
1 vote
1 answer
52 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
2 answers
49 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
1 answer
418 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 ...
1 vote
1 answer
53 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 ...
3 votes
1 answer
6k 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 ...
0 votes
1 answer
226 views

How can I query columns instead of rows in a =QUERY?

With this formula, I can get a list of the rows with the highest total =QUERY(A2:D5, "Select A,D Order by D Desc Limit 4") But I can't figure out how to do the opposite, find the columns ...
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")},"...
2 votes
1 answer
300 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 ...
1 vote
2 answers
3k views

Why does close price results have a date shift (one day off) while using GOOGLEFINANCE in a Query

I want to get last 40 days close price of stocks. I used below formula for the same but the result not showing the last day close price. =QUERY( GOOGLEFINANCE("nse:"&B2,"all",$A$2-80,81),...
1 vote
2 answers
63 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
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 |...
0 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 ...
1 vote
1 answer
86 views

Stop Google Sheets from removing trailing zeros

Can anyone suggest a Google Sheets formula to stop removing trailing zeros? (but allow truncating to 6 digits after decimal? 00.000000 29.9918650 -93.958005 29.991865 -93.958005 (1mi:29.991865:-...
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 ...
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 ...
0 votes
1 answer
989 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 ...
0 votes
1 answer
25 views

Return only those rows with a date on or before today using Query and Importrange

In Google Sheets I have a QUERY where I want to import only those rows where the dates in Col23 are on or before today's date. This is my current formula: =QUERY( IMPORTRANGE( "...
0 votes
1 answer
65 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 ...
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 ...
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 ...
0 votes
1 answer
35 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 ...
1 vote
1 answer
73 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 ...

1
2 3 4 5
19