aGHz/google-sheets-dataclip-autoupdate.md

## google-sheets-dataclip-autoupdate.md

      
    Raw
  

              google-sheets-dataclip-autoupdate.md
            
          
    Dataclips URLs

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.
Sheet variables

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 range
importData 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)

Sheet scripts

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.
Sheet triggers

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.