Questions tagged [google-sheets-query]

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

Filter by
Sorted by
Tagged with
2 votes
1 answer
12 views

Query to extract unique values from 1 column and add matching numeric values

I have multiple sets of 2 columns (COL_LEFT and COL_RIGHT) and would like to extract all the unique values (except the blanks) from COL_LEFT and display matching values from COL_RIGHT alphabetized by ...
  • 51
1 vote
1 answer
23 views

Add a constant number to google sheets query result

how do i get this result? i want to get the number one in every row
  • 11
0 votes
0 answers
18 views

How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar

Employees are selected in their corresponding shift on each month tab in a dynamic calendar See 092022 tab in my example: I want Pemanence tab will be able to retrieve automatically the values in the ...
  • 21
1 vote
1 answer
24 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 ...
1 vote
1 answer
24 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 ...
  • 11
1 vote
1 answer
30 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 ...
  • 11
1 vote
2 answers
26 views

Pull a column of data from one sheet to another based on a matching criteria

I am new to Google sheet formula and need some help from the community. I have two tabs from a spreadsheet, A and B: Both tabs basically have the same number of columns (col1, col2 and col3) except ...
  • 113
1 vote
0 answers
17 views

How do I filter in IMPORTRANGE using SEQUENCE and ARRAYFORMULA?

I am having issues with including a FILTER option in my current working formula listed below: =QUERY (IMPORTRANGE("1n7qMGR9p8xD-8tyPIhfrcyBEsW8FAUMJMHmgxM7xIVI","Consultant Directory!A6:...
1 vote
1 answer
34 views

Google Sheets - How can I return more than one match result from the same search key?

Link to sheet used: Testing Sheet Thanks to another answer I was able to match and return a single word from a search_key being compared to a specific range using the following formula: =UNIQUE(...
  • 11
1 vote
1 answer
13 views

i'm not sure what function I want for this

I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
1 vote
0 answers
10 views

Add more conditions to the formula

=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")}, "select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
1 vote
0 answers
12 views

Query a sheet based on checkbox and order according to check order

In Google Sheets, I have two sheets that work together and I want to achieve the following: I have a sheet1 with information in rows and a checkbox at the beginning When the checkbox is checked, the ...
  • 111
1 vote
0 answers
12 views

Trying to sort the dates from newest to oldest on google sheets [duplicate]

The dates on this column are filled in by a formula. Something about the formula i think might be causing my issue. This is the formula: =QUERY({Tables!A1:K;Chairs!A1:K}, "SELECT Col1, SUM(Col11) ...
0 votes
0 answers
14 views

TypeError: Cannot read property 'range' of undefined [duplicate]

function onEdit(e) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // EVENT VARIABLES var range = e.range(); var row = range.getRow(); var col = range.getColumn(); ...
  • 1
1 vote
1 answer
42 views

Shopping List - If true ignore cell

I've made a spreadsheet that tracks what items I need to buy based on what recipies I want to make during the week. This is generated using a few different formulas and then displayed in column C. I ...
  • 13
1 vote
0 answers
19 views

Problem with adding query function with another function

newbie here! I was testing and practicing the query function, when I came across this function on a website ={QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) '...
0 votes
0 answers
20 views

How to filter and sort according to a preset order for a Query in Google Sheets

In Google Sheets, I'm using a query to pull all my data from different tabs into one master sheet. And now I'm wondering how to sort the data according to a preset order. The order I want to sort to ...
  • 5
0 votes
1 answer
17 views

How to find current employee fee based on name and date from a different sheet

I have a spreadsheet with all employees work reports with info about time spent on a job and other details. I can't figure out how to get the fee the employee is supposed to get paid because I can ...
1 vote
1 answer
16 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;&...
  • 11
0 votes
1 answer
22 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 ...
  • 11
0 votes
1 answer
15 views

importing data from several sheets and average without including 0s

I have the following formula: =query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:...
1 vote
1 answer
29 views

For Google Sheets, what is the function that will populate a cell with specific text when pulling data from multiple tabs of another sheet?

I am building a Google Sheet that pulls data from another Google Sheet with multiple tabs. The source sheet doesn't include one particular data point (i.e. business name), and I want to populate the ...
0 votes
0 answers
23 views

Using Query & Import Range function to import data range with a date filter

I have been able to use the the Query and Import Range function perfectly. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/13eoPsqGik4ZWFGPURrC9RYdlOuxdbCbQWXzWA9LC1L4/edit#gid=0",...
0 votes
1 answer
164 views

Google Sheets query() "Unable to parse query string" ADD_COL_TO_GROUP_BY_OR_AGG

I have a sheet where in Column B I have a product name and column D I have the total order value for that product. And then every row is the individual order. I've been trying to use query so I can ...
1 vote
1 answer
17 views

Is there a way to query and reference a cell that will change?

Alright, looking for some help. I think I am close to the solution, but I could use some help getting it over the finish line. This spreadsheet link is a replicated document of my situation. ...
0 votes
1 answer
40 views

Calculating lost followers metric

I am trying to calculate the lost followers and new followers per day for my company’s clients. I have "New followers" and "Profile followers" columns in Google Sheets but, in ...
-1 votes
1 answer
24 views

Formula to select the name associated with lowest value from data set & a random name from the lowest options if tied

In Google Sheets, I have 4 Names associated with 4 data points that update throughout the month. I'd like to auto pick the name associated with the lowest data point. If there are 2, 3 or all 4 that ...
0 votes
1 answer
27 views

Changing timezones for separated cells

So I currently have a work schedule on Google Sheets, and we all work remotely, all over the country, so I want to add a dropdown menu that one can select to change the time zones of the current times....
0 votes
1 answer
16 views

How to array columns into rows while repeating the date in every row

I have a set of data that I receive from a google forms. It has the information of events, with the # of attendees, name of the event and type of event. They can send up to 5 events in one form, and ...
1 vote
1 answer
25 views

Google Sheets - Pulling data from one tab and creating a formatted schedule

I've created a master schedule for a school. I'd love to then be able to create additional tabs with schedules for each class (i.e. a tab with the schedule for 5th grade math only). I tried to do this ...
0 votes
0 answers
27 views

Need help with an array formula to consolidate data

I'm trying to come up with a formula to consolidate information that's being dumped into a spreadsheet from a Google Form. The Form utilizes questions that repeat and allow a user to input multiple ...
0 votes
1 answer
26 views

Search across multiple columns for a value(s) return multiple row results

I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps. Scenario 1: Looking at the photo. I want to search columns C through M for a selected color(s)...
0 votes
1 answer
52 views

Query only returning when 6 or more rows of data are entered

I am using the query function to gather data from other sheets in the same workbook. It only pulls over the data from the "teacher" sheets when at least 6 rows of data are entered. The 6 ...
0 votes
0 answers
13 views

Query function - Multiple sums

I am trying to figure out how to consolidate a table with similar data based on one cell being the same. In the above example, the cells that would need to be added together are E, F, G, H. And they ...
0 votes
0 answers
12 views

Indexing a list of songs with Google Sheet query

I have a big list of songs in Google Sheets which looks a little bit like this: Number of Song Beginning of Song Title of Song Alternate Title of Song A1 Should old Acquaintance Auld Lang Syne A2 ...
  • 1
0 votes
1 answer
23 views

Finding average based on criteria (non-contiguous cells)

I was wondering if someone could assist me. I am trying to research it myself but have become stuck. Here is a sample of a Subject Mark-Book. Different units have different grade boundaries attached ...
0 votes
0 answers
14 views

Serch for multiple strings in importxml

=query( importxml("http://centegix.com", "//@href"), "where Col1 contains 'twitter' ", 0 ) How to make this formula to return just "Twitter" if it contains a ...
  • 3
0 votes
1 answer
12 views

Changing Data Validation drop down lists based on info in another cell?

Ok, I’ll do my best to explain this problem, as I’m not sure that what I want to do is even possible. Assume the following: cell A1 is either going to equal “Happy” or “Sad”. In the event that A1=“...
  • 1
0 votes
0 answers
29 views

Google Finance formula - YTD, 1YR, 5YR, max return

I have got the below formula for YTD: =GOOGLEFINANCE("GOOG","price") / index(GOOGLEFINANCE("GOOG","price", date(year(today()),1,1)) , 2,2) - 1 I have got the ...
0 votes
1 answer
31 views

Count number of occurrences in Google Sheets, with multiple criteria and not considering duplicates in the row

I'm looking to count the number of rows that contain a certain text (specified in the adjacent cell), from a specific range, on the basis that the criteria have been met. I currently have this formula ...
0 votes
0 answers
27 views

COUNTIF function in one column if it meets a condition in another column

I would like to count the number of assets I have that are "In Stock" and are "MacBook Air" See screen shot. I have tried the COUNTIF function. But having trouble writing the ...
0 votes
1 answer
19 views

How to make a query using 2 variables/inputs from 2 different drop down lists

I have a sheet of data that I've been using query functions in. It has worked great so far for single variables, but now I'm trying to make a query from 2 variables, referencing 2 different drop-down ...
0 votes
2 answers
24 views

How to fill a new google sheet with all rows that contain a specific value in a different sheet?

Imagine you have a google sheet which lists your favorite shops, types of shirts they have, and their price in separate columns. To find out which store has which shirts, you wish to filter out all ...
0 votes
0 answers
22 views

IMPORTRANGE contains specific columns with multiple answers

Example, one person can enter 5 events with several options for participating (both days or Sunday only or 1st go....) how do I get master1 sheet to only show contestants on Saturday and master2 only ...
0 votes
1 answer
97 views

Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas. Sample Sheet I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
  • 191
0 votes
1 answer
36 views

Return value by lookup from multiple value

We constantly receive offers from suppliers and requests from customers. The problem is that customers and suppliers usually have several different identifiers for the same product. Like this: ...
1 vote
2 answers
167 views

Google Sheets: Remove column headers from a QUERY()

I'm trying to eliminate (not hide) the column headings from the following QUERY(). The output from the =QUERY() must be in a single row. =QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&...
  • 2,797
0 votes
0 answers
23 views

Keep data from Query

I am trying to import data from Sheet 1 to Sheet 2 using Query, but I have a problem. I need somehow to automatically keep the data that I've imported on sheets 2, after it get deleted from Sheet 1. ...
  • 1
0 votes
1 answer
30 views

Google Sheets I need the column sums() of a table produced by a query()

I have a query() that outputs a table with two columns (A and B) of numbers. Pictured in IMAGE #1. =QUERY(myData!A:G, "Select E,F WHERE D ='Books'") IMAGE #1 But I need a function that ...
  • 2,797
0 votes
1 answer
23 views

Can anybody please help me create a Query function to extract data from rows to columns (ignoring zero data) and using a drop-down box

I have only started to learn Google Sheets instead of Excel. I find it easier to understand if I have a problem to solve but the Query function quite difficult to understand. I have table of fruit ...

1
2 3 4 5
18