Automatic daily time-series updates in Google Docs Script?

Other Questions Google docs
1

Hey everyone, I’m trying to figure out how to make my Google Docs Script smarter. Right now, I’ve got a table with dates and values from an API. The problem is, I have to manually copy-paste the value each day. If I forget, I miss data. Ugh!

Here’s what my table looks like:

25/06/2023  0.02345678
26/06/2023  
27/06/2023  
28/06/2023

I’m wondering if there’s a way to make the script automatically fill in today’s value when I open the doc. Even better, can it keep the old values and only update the current date’s cell?

For example, if it’s the 26th, it should update that row. But when I open it on the 27th, it should leave the 26th alone and update the 27th instead.

Any ideas on how to make this happen? It would save me so much time and make sure I don’t miss any data. Thanks in advance for your help!

2

hey mate, i’ve dealt with this before. you could use the onOpen() trigger to run a function when u open the doc. check the current date, compare it to ur last row, and update if needed. also, store the last update date somewhere (maybe in PropertiesService) to avoid dupes. just watch out for api limits if ur pulling data often!

3

I have used a similar process in Google Sheets for tracking daily stock prices. I used the onOpen() trigger to execute the script when the document is opened and stored the last updated date in a separate cell or the script properties. I then compared the current date with the stored date and, if it was a new day, fetched the value from my API to update the appropriate cell. This method preserves previous data while updating only the current day’s entry. Note that if the document is not opened daily, some data may be missed, so a time-driven trigger should be considered.

4

I’ve tackled a similar challenge with my fitness tracking spreadsheet. I set up a time-triggered function to run daily at midnight, pulling the latest data from my fitness tracker API and appending it to my spreadsheet automatically. This approach ensures that even if I don’t open the document every day, the data is still continuously updated. I also implemented a check to skip updating if today’s cell already contains data, thus preventing accidental overwrites. It’s important to monitor API quotas to avoid any unexpected data gaps.