This is a long shot…but wondering if anyone has had any experience with the Google Sheets API.
I would like to dynamically plot a line chart, by taking data from a cell (the value of my holdings) on a 24hrs basis, putting that data into hidden sheet.
the plotting a graph with that data on another sheet.
Anyone know if that is possible of has done something similar?
You should be able to do this using “Time-driven (clock)” Triggers and with App scripts. Just be careful, as they charge for this on a per-use basis, although for 365 runs a year it should be free or negligible.
I’ve previously done exactly this with a script. The portfolio value got calculated from the list of holdings and then then script recorded the portfolio value in a new row every day at 11 pm then you can plot the numbers. I still have a Google sheets template if you’d like to have a look, you have to give third party script permissions though.
If you go to the script editor in the spreadsheet you’ll see the code below. When you run it manually you’ll see it adds another row to the history tab (row number to use is on A1 in history tab if you need to reset it). After that you’ll need to set up triggers (button highlighted in picture below), time driven > day timer > 11 pm or something like this.
I’m on the Discord channel if you have any questions, just @DavidM me
It would be great to be able to have a look at the Google Sheet template that you mentioned in the post but the link does not work any more. Do you have an updated link by any chance?