Referencing cell value of a query formula in Google Sheets via apps script does not appear to work

Question

I am attempting to copy the value of a cell to another cell using Apps Script. Source cell's value is produced by a query formula:

=sum(iferror(query(ExportDrop!$A$2:$AJ,"select AJ where A matches '" & join("|",C$3:C) & "' AND AH = '" & A3 & "'",0),0))

Source sheet displays the output of this query formula successfully and correctly.

Working within a script, the following code produces a zero (0), which is different than when viewing the sheet.

cellval = sourceSheet.getRange("B3").getValue();
SpreadsheetApp.getUi().alert(cellval); return;

I have attempted getDisplayValue(), getValue(), getValues() (with corresponding change to the range reference), using setActiveRangeSelection(), copyValuesToRange(), copyTo(,{contentsOnly: true}) etc.

Is this a bug in Google Sheet's query function? I wonder because the same thing occurs when I print to PDF the source sheet containing a query function. The resulting PDF shows zero-values. So it appears not to be an issue with my script, but more like an issue with the source sheet. Tests of a "simpler" query-formula sheet printed work fine.

What am I doing wrong?

EDIT:

Here is an example: https://docs.google.com/spreadsheets/d/1eHHV-TJhC_aVNNLspx3suKhrthtcjRfIcZR0dWdTvOI

Try printing Sheet2. The first PDF "preview" looks good, but upon opening the PDF after export, the sum values are all zeros.

@CodeCamper My apologies, should have included that in the OP. I've edited it to include. — Nat West
The situation gets stranger. Using a fresh Brave Browser window, the sheet originally showed zeros as you suggested. After touching the formulas in column B via Chrome, my Brave window showed the correct values. Opening a fresh Brave browser results in the zero values again. So it looks like a bug, but not related to my issue. It looks like the browser is rendering zeros when it first loads the sheet, but if the sheet is updated while the browser is open, it shows the correct values. — Nat West
@TheMaster it appears matches is not running on the server at all and only locally sort of like how the format boolean feature is just false advertising on google's part. A simple web browser refresh sadly demonstrates this desync glitch. — CodeCamper
@TheMaster I tried the flush it still displays zero even though it is 10 right in your face, the matches is just not making it to the server at all, refreshing brings everything back to zero. — CodeCamper

CodeCamper CodeCamper · Accepted Answer · 2020-04-06T02:57:06

ANSWER: Google Sheets Query function has advertised features that are broken/incomplete.

Example: matches run's locally and never on the google server causing desync issue, format boolean does not work at all.

Work around: Use a series of OR in lieu of the matches

=sum(iferror(query(Sheet1!$A$2:C,"select C where (A = " &join(" OR A = ",filter(C$1:C,C$1:C<>"")) & ") AND B = '" & A1 & "'",0),0))

Referencing cell value of a query formula in Google Sheets via apps script does not appear to work

1 Answers