Importing data from Google Spreadsheets into a notebook / webpage
Suppose you're the secretary of a club and you've been keeping membership information in Google Spreadsheets: The club has tasked you with putting together a website, and you'd like to share some of this data on your "About us" page. Because the club is growing quickly, you'd like to avoid copying the membership list over by hand or editing it manually anywhere outside the Google Spreadsheet. You could just publish the spreadsheet and embed it or link to it, but: You only want to share a few of the columns (it'd be bad to put everyone's street address online!), and you're also thinking of maybe making a fancy visualization of the data later; perhaps you'd like to make a map, or draw a timeline of the join dates. Fortunately, you know a tiny bit of javascript, and you're learning D3, so you'll be ready to push on further... once you're able to import the desired data into a JS Object. This notebook will show you one way to get there!
Step 1: pick out data to make public Following this helpful advice on the Google product forums, let's first create a second "Public" sheet which contains only the columns you'd like to share with the entire world. We do this by putting a QUERY formula which selects the data from your "Secret" sheet into the A1 cell of the new sheet: =QUERY(Secret!A:F, "select A,C,F",1)(Replace Secret with the name of your sheet, A:F with the range of columns in your sheet that you'd like to select from, and A,C,F with the columns you'd like to make public. The 1 is the number of header columns in your spreadsheet.) The cells in the "Public" sheet should automatically populate with the selected data:
Step 2: publish the "Public" sheet In Google Spreadsheets, click on "Publish to the web" from the File menu. In the left dropdown menu, select the "Public" sheet (don't select the secret sheet or "Entire document"!). Then hit Publish:
Step 3: test the publish link If the right-hand dropdown menu doesn't already read "Web page", change it to that and then copy the link and open it in a new tab. Check to make sure you're not exposing any private data to the web. If you are, you can always unpublish. Better to do this before you've exposed the data to anyone else though! Here's a link to the example spreadsheet I've been showing, and here it is embedded using the "Embed" code: None of the secret columns appear, whew!
Step 4: copy the tsv link and profit! Select "Tab-separated values (.tsv)" in the right-hand dropdown menu: You can now use the resulting URL in d3.tsv(). The remaining cells of this notebook show how to load the data and give some simple examples of the data in action. See also the tutorial Introduction to Promises for some further useful information relevant to loading data into notebooks):
url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRKr_1kDne212LSVpRLgMAvQQtqItsktEXn-dgBDvCnjXCcoK_Uv4uHlIoZQ-5H_Wl9g-YBKO8cAhTO/pub?gid=1310204758&single=true&output=tsv'
names = { // this is a JS Array of Objects; the key-value pairs consist of the column headers and the cell data let names =[]; const spreadsheet = await d3.tsv(url).then(data => data.forEach(d => names.push(d))); // d3.tsv returns a Promise return names; }
If your data doesn't load, check your browser console. If you're getting a CORS error, try closing any other windows which might be importing the Google Spreadsheet tsv link (e.g. this can happen if you have a tab for Observable (to test) as well as a tab for a page on your own website (in "production") and both are trying to import the data.)
{ const randomMember = () => names[Math.floor(Math.random()*names.length)]['First name']; const theTime = () => new Date().toLocaleString().replace(",","").replace(/:.. /," "); const output = () => md`${randomMember()} is the member of the minute! (${theTime()})`; yield output(); while (true) yield Promises.delay(60000, output()); }
{ function toDate (str) { const s = str.split('/'); return new Date(s[2] /* year */, s[0]-1 /* month */, s[1]-1 /* day */); } const tosort = Array.from(names); tosort.sort((a,b) => { const datea = toDate(a['Join date']); const dateb = toDate(b['Join date']); let comparison = 0; if (!isNaN(datea) && !isNaN(dateb)) comparison = dateb - datea; else if (isNaN(datea)) comparison = -1; else if (isNaN(dateb)) comparison = 1; return comparison; } ); return md`Here's a list of our club members from newest to oldest: <br><br>${tosort.map( d => `${d['First name']} from ${d['State']} (joined ${d['Join date']})`).join(',<br>')}`; }
import
d3 = require('d3@5')