Skip to content

Instantly share code, notes, and snippets.

@bennettscience
Last active November 8, 2016 02:34
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save bennettscience/ddaa0ce6d0b6fbf2b7d17704087e0323 to your computer and use it in GitHub Desktop.
Save bennettscience/ddaa0ce6d0b6fbf2b7d17704087e0323 to your computer and use it in GitHub Desktop.
Displaying data using AJAX from a Google Sheet
// Server side scripts to draw and load the page.
// Call the sheet
var ss = SpreadsheetApp.openById('SPREADSHEET_KEY');
// Initialize the HTMLService and draw the page from the template.
function doGet() {
return HtmlService
.createTemplateFromFile("index")
.evaluate();
}
// Include template pages. Best practice in Google Apps Script webapps is to
// use template files for CSS, etc.
function include(file) {
return HtmlService.createHtmlOutputFromFile(file)
.getContent();
}
// Gets the data for the table and returns strings as JSON.
function getData() {
var sheet = ss.getSheets()[3];
var data = sheet.getDataRange().getValues();
Logger.log(data.length);
return JSON.stringify(data);
}
// Get the array from the server and then run the popularVote script
google.script.run.withSuccessHandler(popularVote).getData();
function popularVote(data) {
// Target the divs for the relevant information. Read the data from the server.
var getPcnts = document.getElementsByClassName("percent");
var getActs = document.getElementsByClassName("actual");
var cand = document.getElementsByClassName("cand");
var data = JSON.parse(data);
// Loop through the returned data and add it to the correct `span` element
for(var j=0;j<data.length;j++) {
getPcnts[j].innerHTML = ((data[j][1]/data[j][2]) * 100).toFixed(1) + "%";
getActs[j].innerHTML = data[j][1];
cand[j].innerHTML = data[j][0];
}
setColor();
}
// This alternates the color of the rows in the table for readability
function setColor() {
// Get the container `div` and specify the row `div` nested.
var container = document.getElementById('popular-wrap');
var divs = container.getElementsByClassName('row');
// Loop through the divs and add a CSS class to change the color
for(var i=1;i<divs.length;i++) {
if(i%2 === 0) {
divs[i].className += " " + "even";
}
}
}
// Since this is a live-update dashboard, this runs the scripts every 10 seconds to update from the spreadsheet.
// The Popular Vote and Electoral chart are polled and redrawn to the page as long as it is open.
setInterval(function() {
google.script.run.withSuccessHandler(popularVote).getData();
google.script.run.withSuccessHandler(drawChart).getSpreadsheetData();
}, 10000);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment