Stack Overflow
Stack Overflow

Questions tagged [google-query-language]

Google Query Language is the part of the Google Visualization API that lets the developer perform various data manipulations with the query to the data source. This could also be used in the Google Sheets QUERY built-in function.

Google Query Language allow developers to perform various data manipulations using a syntax very similar to the SQL syntax. It's part of the and can be used in through the QUERY built-in function.

Before posting a question about the Google Query Language check the reference documentation.

1114 questions
3
votes
3 answers

Google spreadsheets query() is replacing text with null for mostly numeric columns

I have =query(importrange(...);"select * where Col1>' '") formula in my spreadsheet. Importrange() by itself works ok, loading all the cells from source spreadhseet exactly as they are. But Col7 contains few text cells, but mostly numbers, and when…
Gleb
  • 215
  • 1
  • 4
  • 10
2
votes
1 answer

How to convert an IMPORTRANGE() array to a string, for use with QUERY()?

I have the same problem which is discussed here, except that I am working with IMPORTRANGE() instead of a direct array reference (e.g., D12:I119). Somehow I need QUERY() to understand its input as an array of strings. But my attempt to addend a null…
Doubt
  • 1,163
  • 2
  • 15
  • 26
2
votes
1 answer

ABS formula within a QUERY in Google Sheets

With the following Query: =QUERY(Transactions!$A$2:$N$300;"SELECT A, B, C, D, E, F, K, L, M, N") I am trying to get the results from F and N to be absolute values while leaving the rest as is and I am having issues with the syntax of this line. I…
Fran Salinger
  • 23
  • 2
2
votes
1 answer

Google Sheets using QUERY IMPORTRANGE from another sheet but query where references current sheets

I am trying to get the Query to pull from the Outside Sheets range but have the Where look at the number in a cell on a tab within the current sheets. I have reviewed other questions and none of those solutins ahs worked for this. I want this so…
Toni4tonight
  • 33
  • 2
2
votes
2 answers

Copy/pasting query in Google Sheets between cells with automatic replacement of values

I have this query in my sheet: =query(A2:A100, "Order by A Desc Limit 20") I want to copy and paste it to more cells and expect it will update range and column from A2:A100 to B2:B100, C2:C100 etc.. and A in query to B, C etc. =query(B2:B100, "Order…
Ocean Overflow
  • 339
  • 2
  • 14
2
votes
3 answers

Processing a list to output in a particular format

I am processing a list to output its items in chunks separated by blank rows as follows. But the result is not working when there are similar items, as shown with the arrows. The formula I'm using is…
spcsLrg
  • 340
  • 2
  • 11
2
votes
1 answer

Return a specific cell in a Google Sheets QUERY function

Take the following spreadsheet: I want to create a query that returns the values in columns A and B where A matches 'f' and also return the value that is in cell D1 (Test Cell). Here is the Query that I wrote: =QUERY(A2:B7,"select A,B,'"&D1&"'…
Ken Versaw
  • 73
  • 1
  • 4
2
votes
3 answers

group by and select row with the max date, and select all columns

I have the following data in a sheet. event_id event_type event_name date_col 123 critical_event Event A 2021/12/16 456 critical_event Event B 2021/12/25 999 medium_event Event C 2021/12/13 888 medium_event Event D 2021/12/16 I'm…
impy
  • 31
  • 7
2
votes
1 answer

Google Sheets QUERY with WHERE on multiple columns at the same time

Following this post Google Sheets QUERY with WHERE on multiple columns I build up my formula to select specific values from more columns but when I had a condition data start to be confused and not equal to what I selected. I started from this…
Stefano Cardo
  • 23
  • 3
2
votes
3 answers

Google Sheets QUERY Function: Select Columns by Name

I have a Google Sheet with named ranges that extend beyond columns A-Z. The name ranges have header rows. I would like to use the QUERY function to select columns by their header labels. My formula is like this: =QUERY(NamedRange,"SELECT AZ, AX,…
Catalyx
  • 435
  • 2
  • 7
  • 17
2
votes
3 answers

How to clear all commas except for commas in even position in sheet?

I have multiple rows of string where the string is all wrong. Here is one row an an example of the geometry and output expected: id geometry output 1 POLYGON (( 106.812271, -6.361551, 106.812111, -6.361339, 106.81205, -6.361177, 106.81206,…
james iskandar
  • 25
  • 3
2
votes
1 answer

Filter the products where your calculated options generate a profit result

I need to filter for each of the products, which are the distances that are profitable using them. In the example below, using car the profitable distance is 3 and skate the profitable distances are 3 and 5: product (column A) distance (column…
Digital Farmer
  • 1,705
  • 5
  • 17
  • 67
2
votes
2 answers

Google Sheets - transpose irregular column data in groups into rows

Much like the problem with the transposing of data in transpose column data I am stuck trying to transpose a set of data with multiple variables. The biggest issue I face is trying to remove useless data. Table 1 is how the data is…
Nick m
  • 35
  • 6
2
votes
1 answer

vlookup several criteria and a range

Here's my table: Exchange No. Name Tier 30d Volume (higher…
Benjamin Perry
  • 75
  • 6
2
votes
3 answers

search within a list of dates and find the next closest future date

Question: Given a list of pay periods and sales dates. For each sales date, determine the next pay day. Pay periods (given) Sale date (given) Pay day (To be generated) June 4 June 4 June 4 June 17 June 4 June 4 June 30 June 4 June…
Don
  • 45
  • 6
1 2 3
…
74 75