Google Sheets Script GetValue/GetDisplayValue returning #N/A when there are values

Question

Sometimes this works and sometimes it doesn't. I have a Google Sheet with formulas containing ARRAYFORMULA and IMPORTRANGE formulas. The values are correctly displayed on the sheet. However, when I use a script to getValues or getDisplayValues, the script returns #N/A. This doesn't happen every time, either. Sometimes, if I refresh the sheet a few times, it works.

Here is an example formula that returns data in the sheet, but returns #N/A in the script: =ARRAYFORMULA(if(A2:A="","",VLOOKUP(A2:A,{{arrayformula(left(Planner!D5:D13,find(" ",Planner!D5:D13)-1))},{Planner!W5:W13}},2,0)))

You can try to use flush() method before get data from the cell: stackoverflow.com/questions/41175326/… — Yuri Khristich
Hmm I just tried that, but it's still returning #N/A. It's so frustrating because I literally see the value in the spreadsheet, but the script is not seeing it. — HeatherLeigh
Another similar solution try to pause the script with Utilities.sleep(1000). How many such formulas you have on your sheet? Is it possible you made too many requests (per second) and hit the quota? — Yuri Khristich

doubleunary doubleunary · Accepted Answer · 2021-05-17T14:30:36

Apps Script instances run in Google's servers rather than in your browser, so this is probably an instance of the Google Sheets client in your browser showing one thing while the back-end spreadsheet engine reports another.

You may want to reset the formula cell and use flush(), perhaps together with Utilities.sleep() to avoid the #N/A issue, like this:

const formulaCell = SpreadsheetApp.getActive().getRange('Sheet1!A1');
const formula = formulaCell.getFormula();
formulaCell.setFormula(null);
SpreadsheetApp.flush();
formulaCell.setFormula(formula);
SpreadsheetApp.flush();
Utilities.sleep(1 * 1000);
const values = formulaCell.offset(0, 0, 10, 10).getValues();
console.log(JSON.stringify(values));

Google Sheets Script GetValue/GetDisplayValue returning #N/A when there are values

1 Answers