python (65.2k questions)

javascript (44.3k questions)

reactjs (22.7k questions)

java (20.8k questions)

c# (17.4k questions)

html (16.3k questions)

r (13.7k questions)

android (13k questions)

Questions - google-query-language

Cajole QUERY to accept text string for the data range?

I have a query statement pulling data from multiple sheets, which works fine: =QUERY({'A Physical Science'!A2:L; 'B Physical Science'!A2:L; 'E Physical Science'!A2:L; 'F Physical Science'!A2:L; 'G Phy...
test-img

user2517718

google-sheets

google-sheets-formula

google-query-language

Votes: 0

Answers: 1

Latest Answer

There is no way to convert a cell value like {'A Physical Science'!A2:L; 'B Physical Science'!A2:L; 'E Physical Science'!A2:L; 'F Physical Science'!A2:L; 'G Physical Science'!A2:L} into an Google She...
test-img

Rubén - People First

Googlesheets count single cell across multiple sheets

Looking for a formula that will count the value of a single cell across multiple (a whole class of students) sheets. I am aware of using + to do this but for 30 students I'd want something more dynami...
test-img

Mr L Fenner

arrays

google-apps-script

google-sheets

countif

google-query-language

Votes: 0

Answers: 1

Latest Answer

either you hardcode it like: =COUNTIF({Anthony!$A1,Lee!$A1,Cornelius!$A1,Liam!$A1}, B1) or use script: function SNAME(option) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.get...
test-img

player0

Table transformation Google Sheets

I'm trying to convert such table: into such table: First table is very big, and is being changed automatically by another user. I'd like to use Google Sheets' query function. Any idea how? Thanks in...
test-img

raduy

if-statement

google-sheets

transpose

flatten

google-query-language

Votes: 0

Answers: 1

Latest Answer

use: =INDEX(QUERY(SPLIT(FLATTEN(B1:D1&"×"&A2:A&"×"&B2:D), "×"), "where Col2 is not null"))
test-img

player0

Sort sheet according to birthday date without considering the year of a date

I am trying to sort sheet according to birthday date, the closest birthday should go in first row, and the last go to the end and so on, to do this it should only consider the day and month without ta...
test-img

Jesus Navarro

arrays

date

sorting

google-sheets

google-query-language

Votes: 0

Answers: 1

Latest Answer

try: =QUERY(SORT(Sheet1!A2:Q27, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), "where Col6 is not null", 0) update: =ARRAYFORMULA(ARRAY_CONSTRAIN({ QUERY(SORT({Sheet1!A2:Q27, TO_DATE(TEX...
test-img

player0

Posts

Questions

Blogs

Jobs