Sheets API v4 is returning #N/A for specific formulas

Question

Sheets API v4 is returning #N/A when using vlookup formula. This specific error is happening with vlookup formula, but I have seen this very same issue on spreadsheets contains a big number of formulas. Seems like the API does not wait for the spreadsheet to calculate formulas before returning the values.

A small code sample in python:

service.spreadsheets().values().get(
    spreadsheetId='1s-fePyrjzp8_jAWy-p1smystgJXxMirmjXGJjQM91qg',
    range="FxRates salaries filter!A1:D5",
    valueRenderOption="UNFORMATTED_VALUE",
    dateTimeRenderOption="SERIAL_NUMBER"
).execute()

Steps will reproduce the problem?

1. Create a vlookup formula on a Google Spreadsheet
2. Use Sheets API to get the result of the formula

Expected output: The expected output is a number, but instead, the API is returning "#N/A (Did not find value '43531' in VLOOKUP evaluation.)". The spreadsheet itself calculates the formula correctly, but the API returns #N/A

You can easily reproduce the problem on API Explorer by executing the link below: Google Sheets API Explorer

Answer 1

When I checked that the formulas of FxRates salaries filter!A1:D5, the following values were retrieved.

{
  "range": "'FxRates salaries filter'!A1:D5",
  "majorDimension": "ROWS",
  "values": [
    [
      "Date",
      "EUR",
      "USD",
      "BRL"
    ],
    [
      "=TODAY()-1",
      1,
      "=vlookup(A2,EURUSD_D!$A:$C,3,0)",
      "=vlookup(A2,EURBRL_D!$A:$C,3,0)"
    ]
  ]
}

When I checked that the formulas of EURUSD_D!$A:$C, the following values were retrieved. The values retrieved by GOOGLEFINANCE() are using. Also the value of EURBRL_D!$A:$C was the same result.

{
  "range": "EURUSD_D!A1:C2258",
  "majorDimension": "ROWS",
  "values": [
    [
      "Date ONLY",
      "=GOOGLEFINANCE(\"CURRENCY:EURUSD\",\"price\",date(2014,1,2),today())"
    ],
    [
      "=ARRAYFORMULA(IF(B2:B=\"\",\"\",DATE(YEAR(B2:B),MONTH(B2:B),DAY(B2:B))))"
    ]
  ]
}

From above results, it is considered that the reason of your issue is due to "Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets".

Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets

September 27, 2016

We want to make you aware of a small change to the GOOGLEFINANCE function, which makes it easy to pull current or historical securities information directly into your spreadsheets in Google Sheets. Starting today, it will not be possible to download historical data or access it outside of Sheets using Apps Script or an API. If you have a spreadsheet with historical data generated from the GOOGLEFINANCE function and you try to download it or access it via Apps Script or an API, the corresponding cells will show “#N/A.” You will still be able to view that historical data from within the Sheets application, and you will still be able to download current data and access current data via Apps Script or an API. Please keep this in mind when using the GOOGLEFINANCE function going forward.