32:T4fb,md` Suppose you're the secretary of a club and you've been keeping membership information in [Google Spreadsheets](https://docs.google.com/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: 1. You only want to share a few of the columns (it'd be bad to put everyone's street address online!), 2. 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](https://d3js.org/), 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!** *If you have any questions, feel free to send me a PM on twitter [@blockspins](https://twitter.com/blockspins) or make a post on [the Observable forums](https://talk.observablehq.com)!* `33:[] 30:{"title":"Importing data from Google Spreadsheets into a notebook / webpage","cells":[{"id":0,"value":"md`# Importing data from Google Spreadsheets into a notebook / webpage`","pinned":false,"hidden":false,"mode":"ojs"},{"id":4,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":30,"value":"md`---\n## Step 1: pick out data to make public\n\n[Following this helpful advice on the Google product forums](https://productforums.google.com/forum/#!topic/docs/eUCPKjJQkmA), 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 \\`select\\`s the data from your \"Secret\" sheet into the \\`A1\\` cell of the new sheet:\n\n\\`\\`\\`\n=QUERY(Secret!A:F, \"select A,C,F\",1)\n\\`\\`\\`\n\n(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.)\n\nThe cells in the \"Public\" sheet should automatically populate with the selected data:\n\n

\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":54,"value":"md`---\n## Step 2: publish the \"Public\" sheet\n\nIn 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:\n\n

\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":23,"value":"md`---\n\n## Step 3: test the publish link\n\nIf 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!\n\n[Here's a link](https://docs.google.com/spreadsheets/d/e/2PACX-1vRKr_1kDne212LSVpRLgMAvQQtqItsktEXn-dgBDvCnjXCcoK_Uv4uHlIoZQ-5H_Wl9g-YBKO8cAhTO/pubhtml?gid=1310204758&single=true) to the example spreadsheet I've been showing, and here it is embedded using the \"Embed\" code:\n\n\n\nNone of the secret columns appear, whew!\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":86,"value":"md`---\n\n## Step 4: copy the \\`tsv\\` link and profit!\n\nSelect \"Tab-separated values (.tsv)\" in the right-hand dropdown menu:\n\n

\n\nYou can now use the resulting URL in [\\`d3.tsv()\\`](https://github.com/d3/d3-fetch#api-reference). \n\nThe 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](https://beta.observablehq.com/@mbostock/introduction-to-promises) for some further useful information relevant to loading data into notebooks):`","pinned":false,"hidden":false,"mode":"ojs"},{"id":19,"value":"google_sheet_url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRKr_1kDne212LSVpRLgMAvQQtqItsktEXn-dgBDvCnjXCcoK_Uv4uHlIoZQ-5H_Wl9g-YBKO8cAhTO/pub?gid=1310204758&single=true&output=tsv'","pinned":true,"hidden":false,"mode":"ojs"},{"id":231,"value":"md`**Update** (2020/06/26): As of a few days ago, Google no longer serves this resource with the proper [CORS headers](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS), meaning we have to use a CORS proxy. For now, [\"CORS anywhere\"](https://cors-anywhere.herokuapp.com/) works and is easy-to-use. \n\nSee also Alex Garcia's [\"CORS-Anywhere - for Observables\"](https://observablehq.com/@asg017/cors-anywhere-for-observables) notebook for a solution that may be more reliable:\n\n**Update 2** (2021/02/23): It appears that \"CORS anywhere\" now forces users to complete a challenge (cf. [this issue](https://github.com/Rob--W/cors-anywhere/issues/301)) so it's no longer usable. However, Ariel Aizemberg pointed out that using the Google Sheets URL directly works again. Thanks!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":236,"value":"url = google_sheet_url","pinned":true,"hidden":false,"mode":"ojs"},{"id":16,"value":"names = { // this is a JS Array of Objects; the key-value pairs consist of the column headers and the cell data\n let names =[];\n const spreadsheet = await d3.tsv(url).then(data => data.forEach(d => names.push(d))); // d3.tsv returns a Promise\n return names;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":218,"value":"md`If your data doesn't load, check your browser console. If you're getting a [CORS error](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS/Errors), 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.)`","pinned":false,"hidden":false,"mode":"ojs"},{"id":99,"value":"{\n const randomMember = () => names[Math.floor(Math.random()*names.length)]['First name'];\n const theTime = () => new Date().toLocaleString().replace(\",\",\"\").replace(/:.. /,\" \");\n const output = () => md`${randomMember()} is the member of the minute! (${theTime()})`;\n yield output();\n while (true) yield Promises.delay(60000, output());\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":155,"value":"{\n function toDate (str) {\n const s = str.split('/');\n return new Date(s[2] /* year */, s[0]-1 /* month */, s[1]-1 /* day */);\n }\n\n const tosort = Array.from(names);\n tosort.sort((a,b) => { \n const datea = toDate(a['Join date']);\n const dateb = toDate(b['Join date']);\n let comparison = 0;\n if (!isNaN(datea) && !isNaN(dateb)) comparison = dateb - datea;\n else if (isNaN(datea)) comparison = -1;\n else if (isNaN(dateb)) comparison = 1;\n return comparison;\n } );\n\n return md`Here's a list of our club members from newest to oldest:

${tosort.map(\n d => `${d['First name']} from ${d['State']} (joined ${d['Join date']})`).join(',
')}`;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":95,"value":"md`---\n### import`","pinned":false,"hidden":false,"mode":"ojs"},{"id":14,"value":"d3 = require('d3@5')","pinned":false,"hidden":false,"mode":"ojs"},{"id":228,"value":"md`### Version history\n\n- [2018/10/30](https://observablehq.com/d/216403f7b5a92a2d@228): first publish\n- [2020/06/26](https://observablehq.com/d/216403f7b5a92a2d@245): Google sheets no longer provides CORS headers, so we have to use a CORS proxy. (Thanks to [@EE2dev](https://observablehq.com/@ee2dev) for [the report](https://talk.observablehq.com/t/google-spredsheet-import/3513)!)\n- 2021/02/23: Google sheets link works again, thanks to [Ariel Aizemberg](https://observablehq.com/@aaizemberg) for commenting!\n`","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}