2e:null 2f:["$","title",null,{"children":"Display JSON as Table | katirg | Observable"}] 34:T460,// Draw Basic Table function dtTbl = { const cssClassNames = { 'headerRow': 'table-header', 'tableRow': 'table-header-cells', 'oddTableRow': 'beige-background', 'selectedTableRow': 'orange-background large-font', 'hoverTableRow': '', //'headerCell': 'gold-border', 'tableCell': '', 'rowNumberCell': 'underline-blue-font' }; const datatable = html`

`; datatable.style.width = '100%'; datatable.style.height = 800; yield datatable; google.charts.load("current", { packages: ["table"] }); google.charts.setOnLoadCallback(drawTable); function drawTable() { // Format jsonData object for Google Tables const o = prepareJSON(jsonData); // Create data table out of JSON data loaded from server. var data = new google.visualization.DataTable(o); const options = { width: '100%', height: '100%', 'showRowNumber': false, 'allowHtml': true, 'cssClassNames': cssClassNames }; const table = new google.visualization.Table(datatable); table.draw(data, options); } }35:T767,// Draw Table function for Customized Table dtTblCust = { const cssClassNames = { 'headerRow': 'table-header', 'tableRow': 'table-header-cells', 'oddTableRow': 'beige-background', 'selectedTableRow': 'orange-background large-font', 'hoverTableRow': '', //'headerCell': 'gold-border', 'tableCell': '', 'rowNumberCell': 'underline-blue-font' }; const datatable = html`

`; datatable.style.width = '100%'; datatable.style.height = 800; yield datatable; google.charts.load("current", { packages: ["table"] }); google.charts.setOnLoadCallback(drawTable); function drawTable() { // Format jsonData object for Google Tables const o = prepareJSON(jsonData); // Create data table out of JSON data loaded from server. var data = new google.visualization.DataTable(o); var viewColumns = []; for (var i = 0; i < data.getNumberOfColumns(); i++) { viewColumns.push(i); } // next, setup columns to be adjusted // column index 1 = second column let thisCol = 6 viewColumns[thisCol] = { calc: function (dataTable, row) { var columnValue = dataTable.getValue(row, thisCol) || ''; // class name should match definition in CSS return '

' + columnValue + '

'; }, type: 'string', label: data.getColumnLabel(thisCol) }; const options = { width: '100%', height: '100%', 'showRowNumber': false, 'allowHtml': true, 'cssClassNames': cssClassNames }; var view = new google.visualization.DataView(data); view.setColumns(viewColumns); let visualization = new google.visualization.Table(document.getElementById('table_custdiv')); visualization.draw(view, { allowHtml: true, 'cssClassNames': cssClassNames }); } }36:[{"id":"229a7dfb3d806ce493850a68b0452f71d052dd4800b6fe0c5074a0170ed99ddd86e9382a3c71318910673cb63c6eb64c1d67f34c07f9ebbcef75575e39c28de7","href":"https://static.observableusercontent.com/files/229a7dfb3d806ce493850a68b0452f71d052dd4800b6fe0c5074a0170ed99ddd86e9382a3c71318910673cb63c6eb64c1d67f34c07f9ebbcef75575e39c28de7","name":"flu_shot_clinics.json","type":"application/json","lastModified":1639064375559.005,"size":6786}] 32:{"id":"d2884d64bfd59f4f","slug":"test_catalogue_json","title":"Display JSON as Table","thumbnail":"cd33c4a3cf230217994514430056b0ca536d47641831225dcb40fab96643a0e4","access_level":"public","create_time":"$D2021-12-01T21:11:35.207Z","update_time":"$D2022-11-15T16:59:56.199Z","view_time":null,"first_public_time":"$D2021-12-09T16:30:34.619Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"20f3bfaa8fa5c84a","login":"katirg","name":"","avatar_url":"https://avatars-next.observableusercontent.com/a052328a04d246ecd3076cd023a5aade2b30d382f43a2ddfcaa1227ede3e85fd","tier":"starter_2024"},"version":627,"publish_version":627,"role":null,"authored":false,"authors":[{"type":"individual","id":"eff7fee30ca2beb5","login":"katirg","name":"","avatar_url":"https://avatars-next.observableusercontent.com/a052328a04d246ecd3076cd023a5aade2b30d382f43a2ddfcaa1227ede3e85fd","tier":null}],"members":[{"type":"individual","id":"eff7fee30ca2beb5","login":"katirg","name":"","avatar_url":"https://avatars-next.observableusercontent.com/a052328a04d246ecd3076cd023a5aade2b30d382f43a2ddfcaa1227ede3e85fd","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Display JSON as Table","cells":[{"id":510,"value":"# Display JSON as Table\nThis is a test notebook to plot data from a JSON file into an HTML table using Google Charts (https://developers.google.com/chart/interactive/docs/reference#dataparam).\n\n## 1. Setup\n\n### 1.1 Load Data\nUse Flu Shot Clinics JSON file:\nhttps://stage.data.ontario.ca/dataset/flu-shot-clinics\n\nThis data is only provided in JSON format, which cannot automatically be plotted by the ckan Data Viewer.\n\nOnly the data defined in the `properties.language.en` object key are displayed in the table. These data consist of `10` rows, with `7` columns (\"names\", \"locationTypeName\", \"address\", \"postalCode\", \"city\", \"appointmentLink\", \"details\").\n\nIt is possible to further customize the output to plot more data properties, if desired. For example, `geometry.coordinates` holds a coordinate array of lon, lat that can be used to plot locations on a map. (Future notebook!)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":522,"value":"Here, we consider only the data in the `properties.language.en` of each row.\n\nCan then extract the column names for the table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":266,"value":"Object.keys(jsonData[0].properties.language.en) // column names","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":382,"value":"#### Method 1: call URL\nUse `d3.json()`, passing in the URL: \nhttps://stage.data.ontario.ca/dataset/a7aa56cf-eb63-4746-9852-3a88806ccfde/resource/48aa983a-e9b4-4c7a-b699-15ddd42d3248/download/data.json\n\nNote: Cross Origin must be disabled in the browser (can do this in Chrome with plugin https://chrome.google.com/webstore/detail/allow-cors-access-control/lhobafahddgcelffkeicbaginigeejlf/related?hl=en). Otherwise you will get error:\n\n`jsonDataURL = TypeError: NetworkError when attempting to fetch resource.`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":16,"value":"jsonDataURL = d3.json(\"https://stage.data.ontario.ca/dataset/a7aa56cf-eb63-4746-9852-3a88806ccfde/resource/48aa983a-e9b4-4c7a-b699-15ddd42d3248/download/data.json\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":386,"value":"#### Method 2: upload from file\nAlternatively, the data can be uploaded from a file.\n\nThe JSON file was downloaded to the local machine, then attached to this notebook using the File Attachment feature.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":371,"value":"jsonData = FileAttachment(\"flu_shot_clinics.json\").json()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":554,"value":"### 1.2 Set up styling\nBased on CSS of Public Sector Salary Disclosure 2020 table (https://www.ontario.ca/page/public-sector-salary-disclosure-2020-all-sectors-and-seconded-employees).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":457,"value":"// https://observablehq.com/@triptych/google-fonts-in-observable\nfontName = \"Open Sans\"; // \"Fontdiner Swanky\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":543,"value":"\n","pinned":true,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":176,"value":"## 2. Create table using Google Charts\n\nIn this section, we will plot the JSON data using Google Charts.\n\n\n- **Section 2.1**: basic Google chart styled with CSS\n- **Section 2.2**: Google chart customized to increase width of last column\n\ndocumentation:\n- https://developers.google.com/chart/interactive/docs/reference#dataparam\n- https://developers.google.com/chart/interactive/docs/php_example","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":463,"value":"**Load Google Chart library**:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":169,"value":"google = require(\"https://www.gstatic.com/charts/loader.js\").catch(\n () => window.google\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":604,"value":"### 2.1 Prepare JSON data\n\nTo construct a Google Chart table from a JSON object, the object must have the following format:\n```\no = {\n \"cols\": [\n {\"id\": \"\", \"label\": \"COL-1 NAME\", \"pattern\": \"\", \"type\": \"string\"},\n {\"id\": \"\", \"label\": \"COL-2 NAME\", \"pattern\": \"\", \"type\": \"number\"},\n ...\n {\"id\": \"\", \"label\": \"COL-N NAME\", \"pattern\": \"\", \"type\": \"number\"}\n ],\n \"rows\": [\n {\"c\": [{\"v\": \"COL-1 VALUE\", \"f\":null}, ..., {\"v\": COL-N VALUE, \"f\":null}]},\n {\"c\": [{\"v\": \"COL-1 VALUE\", \"f\":null}, ..., {\"v\": COL-N VALUE, \"f\":null}]},\n ...\n {\"c\": [{\"v\": \"COL-1 VALUE\", \"f\":null}, ..., {\"v\": COL-N VALUE, \"f\":null}]}\n ]\n }\n```","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":616,"value":"// Reorganize Flu Shot Clinics JSON file into format required by Google Table\nfunction prepareJSON(jsonData) {\n let o = {};\n o[\"cols\"] = [];\n o[\"rows\"] = [];\n \n // Construct the column part of the object\n const cols = Object.keys(jsonData[0].properties.language.en);\n cols.forEach((d, idx) => {\n o[\"cols\"][idx] = {id: '', label: d, pattern: '', type: 'string'}\n });\n\n // Construct the row part of the object\n jsonData.forEach((d, idx) => {\n let dataRow = d.properties.language.en;\n let thisRow = {};\n thisRow = {\"c\": []};\n cols.forEach(function(col) {\n thisRow[\"c\"].push( {\"v\": dataRow[col],\"f\": null} );\n })\n o[\"rows\"].push(thisRow);\n });\n\n return o;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":620,"value":"prepareJSON(jsonData)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":467,"value":"### 2.2 Basic Google Table\nUsing CSS stylings.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":184,"value":"$34","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":580,"value":"### 2.3 Customized Google Table\nIncrease the column width of the last column.\n\nRefs:\n- https://stackoverflow.com/questions/36331827/how-to-set-a-specific-width-for-each-column","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":590,"value":"\n","pinned":true,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":588,"value":"$35","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":480,"value":"## Load requirements","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":0,"value":"d3 = require(\"d3\") ","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":115,"value":"$$ = require(\"https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W36","stdlib":"1","titling":"auto"}} 33:"$32:body"