2b:null 2c:["$","title",null,{"children":"Vizualize Google Sheets Data | kalmdown | Observable"}] 31:[] 2f:{"id":"06de06ba828508b5","slug":null,"title":"Vizualize Google Sheets Data","thumbnail":"a8aa130b0e68c0599557430b02f021e4bbfa486996ba4e89a459cab317dc71e5","access_level":"public","create_time":"$D2023-01-21T05:39:10.612Z","update_time":"$D2023-01-21T05:40:48.914Z","view_time":null,"first_public_time":"$D2023-01-21T05:39:10.612Z","has_unpublished_edits":null,"fork":{"id":"967c69f379b1bad2","slug":"vizualize-google-sheets-data","title":"Vizualize Google Sheets Data","owner":{"id":"d0789414de25c7a4","login":"david-dobervich","name":"David Dobervich"}},"stars":0,"starred":false,"owner":{"type":"team","id":"5139cef5fca98dea","login":"kalmdown","name":"kalmdown","avatar_url":"https://avatars-next.observableusercontent.com/814a7bacd30761488bdf6c656b5f3b344a25453cf8e845fdffdc2b15c37937b4","tier":"starter_2024"},"version":737,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"bc77e0cd5faff006","login":"kalmdown","name":"kalmdown","avatar_url":"https://avatars-next.observableusercontent.com/814a7bacd30761488bdf6c656b5f3b344a25453cf8e845fdffdc2b15c37937b4","tier":null}],"members":[{"type":"individual","id":"bc77e0cd5faff006","login":"kalmdown","name":"kalmdown","avatar_url":"https://avatars-next.observableusercontent.com/814a7bacd30761488bdf6c656b5f3b344a25453cf8e845fdffdc2b15c37937b4","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":674,"value":"url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vTKaJhOOz3s68GLrFVY0bVmszwSvVsgIZ_EB0QkZN4s37FQyJAJ9FEi4heoha6Hz8pppAJ9KL-iEx9e/pub?gid=618962527&single=true&output=tsv'","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":683,"value":"viewof groupBy = Inputs.select(columns, {label: \"group by\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":707,"value":"viewof colToShow = Inputs.select(columns, {label: \"show\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":560,"value":"html` \n

${viewof groupvals}

${viewof statsTable}

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

${viewof graphResult}

${viewof boxplots}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":191,"value":"Inputs.table(selectedRows, {columns})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":557,"value":"

Appendix

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":681,"value":"columns = Object.keys(data[0]);","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":313,"value":"\nselectedRows = data.filter(d => groupvals.includes(d[groupBy]))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":534,"value":"dataGroups = d3.group(selectedRows, d=>d[groupBy])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":494,"value":"viewof separate = Inputs.toggle({label: \"Separate plots\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":523,"value":"viewof statsTable = Inputs.table(testData,{\n header:[\"name\", \"mean\", \"sd\"],\nmaxWidth:300})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"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","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W31","stdlib":"1","titling":"auto"}} 30:"$2f:body"