Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@aGHz
Last active October 6, 2021 18:24
Show Gist options
  • Star 11 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save aGHz/6868a1ea1defbd6b9ed5 to your computer and use it in GitHub Desktop.
Save aGHz/6868a1ea1defbd6b9ed5 to your computer and use it in GitHub Desktop.
Auto-updating importData of Heroku Dataclips in Google Sheets

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.

@ASochat
Copy link

ASochat commented Mar 8, 2018

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!

@jonturneratduke
Copy link

jonturneratduke commented Mar 8, 2018

Agreed - this is very useful. The benefit/convenience/utility of heroku dataclips was always weighed against our frustration with its unpredictable caching/refresh. Thanks for posting this solution; you've solved a nagging issue for us.

@javaguy
Copy link

javaguy commented Aug 8, 2018

Is there a way to get this to work if you protect you add security restrictions to your dataclips?

@agarciaffdc
Copy link

Yes, very useful. However Heroku has changed the way they build the clip. Now it hasn't got a description and include an access token. Any advice to update the script?

@agarciaffdc
Copy link

Just realized that the Import function works by itself but it takes 1h to update any change on dataclip. So far that is enough for me.
Cheers

@ali6p
Copy link

ali6p commented Sep 20, 2019

Hi, Resources -> Current project's triggers changed Edit -> Current project's triggers in script editor.
Thank you for this cool stuff.

@plb454
Copy link

plb454 commented May 21, 2021

Hi. Thanks for this.
Looks like the URL has changed from "https://dataclips.heroku.com/" to "https://data.heroku.com/dataclips/". Could this be why adding the "-1" suffix fails to load the dataclip? Is there an update that fixes this please?

@evhabs
Copy link

evhabs commented Sep 1, 2021

@plb454 were you ever able to figure out a fix for this? Yea it no longer loads the data clip just gives you an error:

<!DOCTYPE html><!--[if IE 7]><html class="ie lt-ie9 lt-ie8"><![endif]--><!--[if IE 8]><html class="ie lt-ie9"><![endif]--><!--[if IE 9]><html class="ie"><![endif]--><!--[if (gte IE 9)|!(IE)]<!--><html><!--<![endif]--><head><meta content="text/html; charset=utf-8" http-equiv="Content-type" /><title>Heroku | Login</title><link href="https://www.herokucdn.com/favicon.ico" rel="shortcut icon" type="image/x-icon" /><link href="https://www0.assets.heroku.com/cookies/2021.05.27/onetrust.min.css" rel="stylesheet" type="text/css" /><script charset="UTF-8" data-domain-script="2273ccf8-8f23-4d20-bca7-8f0b53cd825e" src="https://www0.assets.heroku.com/cookies/2021.05.27/oneTrust_production/scripttemplates/otSDKStubPlusSfdcWwwBase.js" type="text/javascript"></script><script>function OptanonWrapper() { };</script><link href="/assets/8/purple.css" media="screen" rel="stylesheet" type="text/css" /><script src="/assets/8/modernizr.min.js" type="text/javascript"></script><meta content="width=device-width

@plb454
Copy link

plb454 commented Sep 2, 2021

@evhabs
Sadly not.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment