Can't read query() function return values from cells with google apps script

Question

I get a small table into my sheet as query() result from huge database and can use these values in other formulas, copy them, etc. But I can't do the same thing using google apps script because it just can't read these values into my variables. I get #N/A or empty cells. My query function:

=query(IMPORTRANGE("[database link]";"'Database'!A3:AG1000000");"select Col23, Col24, Col25, Col26, Col28, Col29 WHERE Col22 contains '"&$C$3&"' ")

To check cells values I used this code:

var active_spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
var active = active_spread_sheet.getSheetByName('Query result').getActiveCell().getValue();
Browser.msgBox(active);

Interesting thing: when I tried to copy first query() result using another query() function - after around a minute of loading I get the same result like my script reads cells values in first function. #N/A and empty spaces in the appropriate places.

Answer 1

I was experiencing similar issues dealing with a query and appscript. My solution was the activate method (ref). By activating the sheet, it triggered the query to run, which I was then able to copy data returned from the query:

const ss = SpreadsheetApp.getActiveSpreadsheet();

// Clear contents of destination
const destSheet = ss.getSheetByName('Dest');
destSheet.clearContents()

// Activate source sheet to trigger query
const sourceSheet = ss.getSheetByName('Source')
sourceSheet.activate()

// Get values
const values = sourceSheet.getRange(1, 1, ss.getDataRange().getLastRow(), ss.getDataRange().getLastColumn()).getValues();

// Set values on destination sheet
const destRange = destSheet.getRange(1, 1, values.length, values[0].length);
destRange.setValues(values);