Google docs cache-busting importdata.md

If you import live data into Google Docs spreadsheets using the importdata function and you want to force a refresh at a certain interval, but you also want to ensure that some cache-busting goes on, append a querystring that's the epoch time value that the refresh occurs, so for a sheet that should grab new data every hour you could force an update like this:

importData("http://example.com/data.csv?" & hour(googleclock()) & ")")

But the url requested looks like this: http://example.com/data.csv?11 if the refresh happened at 11am. The next day at 11, the url will be the same, so there's a chance you may get cached data. To get around this, use an epoch time-based refresh. The formula:

=((date(year(googleclock()),month(googleclock()),day(googleclock())) & " " & time(hour(googleclock()), 0, 0)) - DATE( 1970;1;1))*86400

gives you the epoch timestamp for the time at the current hour. If you wanted the timestamp for the current minute, you'd need to change time(hour(googleclock()), 0, 0) to time(hour(googleclock()), minute(googleclock()), 0)

and then append it to your url as above:

importData("http://example.com/data.csv?" & ((date(year(googleclock()),month(googleclock()),day(googleclock())) & " " & time(hour(googleclock()), 0, 0)) - DATE( 1970;1;1))*86400 & ")")

The your requests will look like this: http://example.com/data.csv?1396440000 where the epoch-driven timestamp ensures that the cache busting always happens.

I tried using the examples above but all of the use ? after the link, and in my case i'm not sure why it wasn't working, so i'm using # instead, here is the code if anyone wants it

function update() {
  URL = '"https://api.binance.com/api/v3/ticker/price?symbol=ADABUSD'
  SpreadsheetApp.getActiveSheet().getRange("B1").setValue('=REGEXEXTRACT(CONCATENATE(IMPORTDATA('+URL+'#'+Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss")+'"'+'));"[0-9]*\.[0-9]+[0-9]+")/100000000');
}

and it's working perfectly

@PiclesHavaiano Your URL won't be working because your original URL already has a ? in it, so instead of a #, you could use an & if you prefer

Error: "The GOOGLECLOCK function has been replaced with the NOW function."