Dataclips has a reliable way to construct the URL of a clip's CSV version:
https://dataclips.heroku.com/<hash>-<description>.csv
Thankfully the description is irrelevant, so we can just get the hash from the web interface
(looks like aujqawhjdmlbbwrqxutcpzzqyika
) and add -1
at the end. Every time we change the
1 for another number, Google Sheets will automatically reload the data from the new URL.
We need to reserve three cells in the sheet to save three variables:
- the dataclip hash,
- the current cache-busting suffix,
- and the final constructed URL for
importData
to use. You can place these cells anywhere but make sure they won't conflict with the rangeimportData
will use.
Paste the hash in the first cell, and put a 1
in the second cell to initialize the
cache-busting suffix, then use this formula for the third:
=CONCATENATE("https://dataclips.heroku.com/", A1, "-", A2, ".csv")
where you replace A1
and A2
with the position of the hash and suffix cells. You should
now see a well-formed Dataclips CSV URL in the third cell.
Use the position of this URL cell as the argument for your importData
, e.g.
=importData(A3)
We now need to create a script that will automatically increment the cache-busting suffix
every 10 minutes. When the script changes this cell, the third cell will automatically
get recalculated, and then the importData
will use the new URL to load the data. Since
the suffix is irrelevant, Dataclips will just serve again the data from your clip.
In your Google Sheet, go to the menu Tools -> Script editor...
. At the top of the page
you can change the name of the project from 'Untitled project' to something like 'My sheet scripts'.
To the left, you can also change the name of your file from Code.gs
to something like
heroku-reload.gs
.
If your file has nothing in it except function myFunction() { }
, replace all that with
function reloadClip(sheet_name, suffix_pos) {
var sheet = SpreadsheetApp.getActive().getSheetByName(sheet_name);
var suffix = sheet.getRange(suffix_pos);
suffix.setValue(suffix.getValue() + 1);
}
function reloadMyClip() { reloadClip("my-sheet", "A2"); }
(If the file already had other code in it when you opened the script editor, just create
a new file from the menu File -> New -> Script file
)
Very important, change the last line to replace my-sheet
with the name of the sheet
containing your 3 variables, and A2
with the position of your cache-busting suffix cell.
You can also change MyClip
in the name of the function to something more relevant.
Now we need to setup the reloadMyClip
function as a trigger. In the script editor, go
to the menu Resources -> Current project's triggers
. There, click on Add new trigger
and
choose the name of your function in the first dropdown (reloadMyClip
or the name you replaced
it with, not reloadClip
). For the remaining dropdowns, choose Time-driven
, Minutes timer
and Every 10 minutes
.
We're done, now every 10 minutes you'll see your suffix cell increase in value, the importData
will flash "Loading..." for a bit and everything will be reloaded.
Really cool! Actually solved the problem of importdata that couldn't fetch the URL because of some cache I suppose. Now it loads fine as it is a different URL. Thank you!