Reference a global query as data within subsequent queries?

user_11689755 0
Viewed 36

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.

google-query-language google-sheets-formula google-sheets syntax arrays
1 Answers

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:

="=QUERY("&A1&", ""select *"", 1)"
user_56326290
Related