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.

THANK YOU. ❤️

I found that the following works on the _new_ Google Sheets that removed googleclock():
IMPORTDATA("https://my.url?"&TEXT(NOW(),"yyyymmddHHmm"))
When used in conjunction with "File > Spreadsheet Settings", Recalculation: "On change and every minute". It should then update once per minute, with the caveat that it frequently gets stuck on "Loading..." after a new minute ticks by 😦

Wonderful. Thanks!! 👍

How to force Import refresh...

Thanks for this, but may be I'm wrong. Since the beginning of October 2015, the parsing with an URL concatenration is no longer supported. The turn arround solution could be a simple script and a trigger on this script. Here is the script working like a charm :

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pricing").getRange('B184').setValue("https:/mywebsiteURL" + Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss"));

Hope this helps

Cheers,

Sébastien

for this:
IMPORTDATA("https://my.url?"&TEXT(NOW(),"yyyymmddHHmm"))

I got message:
"This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()"

this one works:

1. in sheet, type function: =importdata(a1)
2. in script, make the following

ScriptApp.newTrigger("getData")
.timeBased()
.everyMinutes(1)
.create();

function getData() {
SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName("data")
.getRange("a1")
.setValue("yoururl?" + Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss"));
}

@tewnut: I'm getting #REF when I try to do your solution. I renamed the active sheet, but can't manage to get it working. Could you elaborate? I'm getting these errors as well.

I'm in the same situation, it gives a circular dependency error with the A1 cell. While the script is saying there's too many triggers.

I get Formula parse error for all method described above.

What's the actual working solution?

Here's an updated version that I use. Simply update the values of SHEET_NAME and URL as required.

SHEET_NAME="Sheet1"
URL="https://data.sparkfun.com/output/VFEEYZENwLCNwE8LZjDA.csv"

function myFunction() {
  ScriptApp.newTrigger("getData")
  .timeBased()
  .everyMinutes(1)
  .create();
}

function getData() {
  SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheetByName(SHEET_NAME)
  .getRange("a1")
  .setValue("=IMPORTDATA(\"" + URL + "?" + Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss") + "\")");
}

Super hacky fix, but you could also just append a random in there too:

function finalCSVgrab()
{
var URL = "http://yourURL.net/whatever.csv";
var RANDOUT = Math.floor(Math.random() * 1024) + 1;
var FIRSTstring = '=importdata("';
var LASTstring = '")';
var FINALURL = FIRSTstring + URL + "?" + RANDOUT + LASTstring;
SpreadsheetApp.getActive().getRange('A1').setValue(FINALURL)
}

I threw this together to make it as simple as possible for newbies.

@constantm your solution worked best for me and was the by far the most simplest and best described for people who are new to this so I thank you.

Here's an updated version that I use. Simply update the values of SHEET_NAME and URL as required.

SHEET_NAME="Sheet1"
URL="https://data.sparkfun.com/output/VFEEYZENwLCNwE8LZjDA.csv"

function myFunction() {
  ScriptApp.newTrigger("getData")
  .timeBased()
  .everyMinutes(1)
  .create();
}

function getData() {
  SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheetByName(SHEET_NAME)
  .getRange("a1")
  .setValue("=IMPORTDATA(\"" + URL + "?" + Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss") + "\")");
}

LIFE SAVER! Thank you @constantm!

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

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."