I have a Google Sheets document where I’m pulling live data using IMPORTHTML. The specific value I need is stored in cell B2 and it changes throughout the day as new data comes in.
I created a separate worksheet that has dates listed in column A starting from today going back to 2017. What I want to do is automatically capture the value from cell B2 at the end of each day and store it in column B next to the matching date.
Is there a way to set this up so it happens automatically without me having to manually copy the values every day? I’m looking for a solution that will make the spreadsheet update itself daily.
Google Apps Script can automate this easily. Create a script that grabs the current B2 value and uses VLOOKUP to match today’s date in column A of your historical sheet. Set it to run at the same time daily so your data gets saved consistently. Just make sure to add error handling for when IMPORTHTML fails - only save actual numeric values so you don’t mess up your historical data.
Google Apps Script is your best bet. I had the same issue capturing stock prices at market close. Set up a time-driven trigger for 11:59 PM each day - this grabs the final value from B2, finds today’s date in your historical sheet, and drops it into column B. Pro tip: add error handling because IMPORTHTML can fail randomly and you don’t want your script crashing. Also think about weekends and holidays when your data source won’t update - build in some logic so you don’t get blank entries messing up your historical data.
yeah, time triggers in apps script are perfect for this. i’d run it 30 mins before midnight tho - gives importhtml time to process if there are delays. also, throw in a backup column in case the script fails or misses a day.