Google Sheets are a [great source of notebook data](https://observablehq.com/@bryangingechen/importing-data-from-google-spreadsheets-into-a-notebook-we), but updating them _after_ page load is tricky. Observable has great support for this using the Cloud Files attachments feature, but it doesn't work for public notebooks (as far as I know!).

Use this function to import a published Google Sheet periodically (say, every minute or two). You could use this for:

- Survey results that are imported from Google Forms;

- Data being added from other web services or your team;

- Just about anything else!

(Consider [using the `QUERY` function to screen out data that you **don't** want published!](https://support.blakeschool.org/hc/en-us/articles/231790688-Query-Function-to-Obtain-Data-from-One-Spreadsheet-and-Link-into-another-in-Google-Spreadsheets))

import liveGoogleSheet from "@jimjamslam/live-google-sheet"

Then, call the `liveGoogleSheet` function. You'll need:

- the URL of the _published_ sheet (not the entire workbook! See below),

- the delay (in milliseconds) between data updates

- the starting column (from 1), and

- the end column.

"https://docs.google.com/spreadsheets/d/e/" +

"2PACX-1vQOnTovn4f2VJxFUTR2S65VZtTO7DO3vXn8FQXoZmI81UavBMWv5gFKKyWuBbh32zhL61H9VxgiL3GJ" +

"/pub?gid=0&single=true&output=csv",

60000, 1, 3)

Note that Google Sheets has [some very annoying server-side caching](https://support.google.com/docs/thread/10441863?hl=en&msgid=10502325). We get around it by asking for extra columns, so you'll need to **add some additional column headers next to your requested columns**.

The extra column headers can have anything in them, so long as there's _something_ there. You'll need enough of them to cover about five minutes' worth of updates (so if your delay is set to 30000 milliseconds, you'll need about 10 extra columns).

If you need an example, [check out this example spreadsheet](https://docs.google.com/spreadsheets/d/1e2rEuKmJzWoquSxDdSkl9EqAuYjmq8P-SZqydC5AKmQ/edit?usp=sharing)!

You'll also need to have **published your sheet as a CSV.** You can do this in Google Sheets by going to `File > Share > Publish to Web'. Make sure you've selected CSV, just a single sheet (not the entire workbook!), that it republishes when changes are made and that access isn't restricted.

The ID in the below sheet is the one you'll need (not the URL of the workbook in your browser!). It's in the URL, after `https://docs.google.com/spreadsheets/d/e/`, going right up to `/pub?`.

// calculate number of extra cols based on the delay: enough

// to cover 5 mins of caching

let addColsLimit = (5 * 60 * 1000) / delay;

// this fn will handle the fetch request, and some cleanup to get rid

// of the "extra" fields used to dodge server-side caching

function getLatestData(extraCols = 0) {

return(

d3.csv(

url +

"&range=" + getColumnLabel(startCol) +

"%3A" + getColumnLabel(endCol + extraCols))

.then(latest => {

let wantedCols = latest.columns.slice(0, endCol - startCol + 1);

latest = latest.map(d => lodash.pick(d, wantedCols));

latest.columns = wantedCols;

return(latest);

}));

}

// get the data once...

let extraCols = 0;

yield getLatestData(extraCols);

// ... then get it again periodically

while(true) {

extraCols = extraCols + 1 % addColsLimit;

yield Promises.delay(delay, getLatestData(extraCols));

}

let result = "";

let n = colNum;

while(n > 0) {

let index = (n - 1) % 26;

result += String.fromCharCode(index + "A".charCodeAt());

n = Math.floor((n - 1)/ 26);

}

return(result.split("").reverse().join(""));

// return(result);

.map(d => getColumnLabel(d))