0

I need historical data of currency exchange rates for my gooogle sheet and GOOGLEFINANCE function is just what i need. Here is the sheet with GOOGLEFINANCE data I copied from my project: https://docs.google.com/spreadsheets/d/15cFmrS2IL6QZMJORCJxU16QtWQ-2hFNLORgM5pEgY_E/edit?usp=sharing

The problem is that sometimes (pretty often actually) GOOGLEFINANCE function crashes and returns error breaking the whole project: that's how it looks when it crashes

It returns to normal after a while but it's really disturbing.

I couldn't think of a good way to work around that glitch so I'm asking for your help.

I thought about making cells with the following logic: IF GOOGLEFINANCE value is not blank then copy it ELSE keep old value making GOOGLEFINANCE return persistent but I'm not sure it's possible to do with Google Spreadsheets formulas. For example above if we want to make L4 cell to contain persistent value of C4 cell, I want L4 formula to be like: =IF(ISBLANK(C4),L4,C4) but then cell will refer to itself which is not allowed.

BigBen
  • 46,229
  • 7
  • 24
  • 40
stekatk
  • 13
  • 1
  • 8

1 Answers1

3

This is not possible without Apps Script

This is because sheet functions aka formulae, don't write values to cells. If you need to persist data, then you need Apps Script.

Unfortunately, GOOGLEFINANCE cannot be accessed outside of Spreadsheet formula. From https://workspaceupdates.googleblog.com/2016/09/historical-googlefinance-data-no-longer.html

it (is) not be possible to download historical data or access it outside of Sheets using Apps Script or an API.

Workaround (with Apps Script)

However, it is possible to set up an Apps Script function to read the displayed value of a cell with getDisplayValue(), and copy that to a backup sheet, where you somehow store historical data.

You could have a setup where you have one sheet with the GOOGLEFINANCE formula, another sheet with the backup values, and a final "Report" sheet. Then you could write another Apps Script function to examine both the sheets and build an output.

Further, you could set this up to run with Time-driven triggers, and it would be quite hands off.

References and further reading

iansedano
  • 6,169
  • 2
  • 12
  • 24
  • 1
    Thank you! I came to the same idea, already read about time-driven triggers, going to make an implementation when I have time. – stekatk Mar 23 '21 at 11:57