31:[] 2f:{"title":"Vizualize Google Sheets Data","cells":[{"id":0,"value":"

Observable Inputs › Hello

\n\n# Vizualize Google Sheets Data\nPaste the URL to your google sheet below and this will try to read it and let you visualize the data.","pinned":false,"hidden":false,"mode":"md"},{"id":674,"value":"url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQ9olrCUnCosYXECEvyddfb6f7spA24-vmFLCIV1SErUqJF90vbuhDtDAYtMHgcErgR0s_-NeBzj6iP/pub?output=tsv'","pinned":false,"hidden":false,"mode":"ojs"},{"id":683,"value":"viewof groupBy = Inputs.select(columns, {label: \"group by\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":707,"value":"viewof colToShow = Inputs.select(columns, {label: \"show\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":560,"value":"html` \n

${viewof groupvals}

${viewof statsTable}

\n ${viewof separate} \n `","pinned":false,"hidden":false,"mode":"ojs"},{"id":570,"value":"html` \n

${viewof graphResult}

${viewof boxplots}

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":640,"value":"viewof boxplots = Plot.plot({\n x: {\n grid: true,\n inset: 6,\n domain: [0, 1]\n },\n y: {\n label: null,\n },\n marks: [\n Plot.boxX(selectedRows, {y: groupBy, x: colToShow})\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":191,"value":"Inputs.table(selectedRows, {columns})","pinned":false,"hidden":false,"mode":"ojs"},{"id":557,"value":"

Appendix

","pinned":false,"hidden":false,"mode":"html"},{"id":681,"value":"columns = Object.keys(data[0]);","pinned":true,"hidden":false,"mode":"ojs"},{"id":313,"value":"\nselectedRows = data.filter(d => groupvals.includes(d[groupBy]))","pinned":false,"hidden":false,"mode":"ojs"},{"id":534,"value":"dataGroups = d3.group(selectedRows, d=>d[groupBy])","pinned":true,"hidden":false,"mode":"ojs"},{"id":538,"value":"testData = {\n let testData = [];\n dataGroups.forEach((values, keys) => {\n testData.push([keys, d3.mean(values.map( val => val[colToShow] )), d3.deviation(values.map( val => val[colToShow] ))])\n })\n\n return testData;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":184,"value":"viewof groupvals = Inputs.select(data.map(d => d[groupBy]), {sort: true, unique: true, multiple:true});\n","pinned":true,"hidden":false,"mode":"ojs"},{"id":494,"value":"viewof separate = Inputs.toggle({label: \"Separate plots\"})","pinned":true,"hidden":false,"mode":"ojs"},{"id":523,"value":"viewof statsTable = Inputs.table(testData,{\n header:[\"name\", \"mean\", \"sd\"],\nmaxWidth:300})","pinned":true,"hidden":false,"mode":"ojs"},{"id":300,"value":"viewof graphResult = {\n if (separate) { \n return Plot.plot({\n grid: true,\n x: {\n domain: [0, 1]\n },\n y: {\n label: null\n },\n facet: {\n data:selectedRows,\n y:groupBy,\n },\n marks: [\n Plot.rectY(selectedRows, Plot.binX({y: \"count\"}, {x: colToShow, fill: groupBy})),\n Plot.ruleY([0])\n ]\n})\n} else {\n return Plot.plot({\n grid: true,\n x: {\n domain: [0, 1]\n },\n marks: [\n Plot.rectY(selectedRows, Plot.binX({y: \"count\"}, {x: colToShow, fill: groupBy, mixBlendMode: \"multiply\"})),\n Plot.ruleY([0])\n ]\n})\n}\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":676,"value":"data = { // 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"}],"files":"$W31","stdlib":"1","titling":"auto"}