Reference a global query as data within subsequent queries?

byMR

June 26, 2022

A simple query

QUERY(data, query, [headers])

I would like to externalize a QUERY, to make it global.

The QUERY is used as data within other subsequent queries

Such as

QUERY2(QUERY1(data, query, [headers]), query, [headers])

Becomes

QUERY2(A1, query, [headers])

Where, as a string

A1 = "QUERY1(data, query, [headers])"

I’ve tried

QUERY2("’&A1&’", query, [headers])

But it doesn’t work.

>Solution :

formula (in your case the subquery) written as a text string cannot be converted into actual formula or range without a script that turns plain text string into a fully functional formula. google sheets in the current operating version does not possess an internal mechanism that would read the formula like below, and render it as a function

=QUERY("QUERY(data, query, [headers])", query, [headers])

an example of the script would be:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');  
var src = sheet.getRange("C4");    // The cell which holds the formula
var str = src.getValue();  
var cell = sheet.getRange("C5");   // The cell where I want the results to be
cell.setFormula(str);              // Setting the formula.
}

but then C4 would need to be written as: