gsheets functions inside the cells like `=GOOGLEFINANCE("LON:WISE", "price", WORKDAY(TODAY()-365, -1))` fail randomly

[onnimonni]

Hey,

I wanted to try if I could read values from =INDEX(GOOGLEFINANCE("LON:WISE", "close", TODAY()-365), 2, 2) to get stock prices from 1 year ago.

It works wonderfully in this test spreadsheet: https://docs.google.com/spreadsheets/d/1DXGmNryZp-0PjLTubpRrGFoHtg5k9AboDG3FDI3OEqo/edit

But when I try to read from it will randomly give "N/A":

D FROM read_gsheet('https://docs.google.com/spreadsheets/d/1DXGmNryZp-0PjLTubpRrGFoHtg5k9AboDG3FDI3OEqo/edit?gid=0#gid=0');
┌──────────┬─────────────┬──────────────────┬───────────────────┐
│  ticker  │ value today │ value 1 year ago │ value 3 years ago │
│ varchar  │   double    │     varchar      │      varchar      │
├──────────┼─────────────┼──────────────────┼───────────────────┤
│ LON:WISE │       853.5 │ #N/A             │ #N/A              │
│ LON:BRBY │      1212.0 │ #N/A             │ #N/A              │
└──────────┴─────────────┴──────────────────┴───────────────────┘

And sometimes it will work:

D FROM read_gsheet('https://docs.google.com/spreadsheets/d/1DXGmNryZp-0PjLTubpRrGFoHtg5k9AboDG3FDI3OEqo/edit');
┌──────────┬─────────────┬──────────────────┬───────────────────┐
│  ticker  │ value today │ value 1 year ago │ value 3 years ago │
│ varchar  │   double    │      double      │      double       │
├──────────┼─────────────┼──────────────────┼───────────────────┤
│ LON:WISE │       853.5 │            920.0 │             659.8 │
│ LON:BRBY │      1212.0 │            896.2 │            2133.0 │
└──────────┴─────────────┴──────────────────┴───────────────────┘

When I'm reading this in the sheet itself it didn't show the #N/A errors 🤔

Is this just normal behaviour or a bug in the gsheets implementation?

[mharrisb1]

Hey @onnimonni, thanks for reporting. I'll take a look 👍

[mharrisb1]

Having a hard time replicating this with the spreadsheet linked. Will update this issue with findings once I have something to add