public
Last active

Google Apps Script to generate ChartFusion datasource xml from a Google Spreadsheet

  • Download Gist
code.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
//http://www.google.sc/support/forum/p/apps-script/thread?tid=345591f349a25cb4&hl=en
function setUp() {
ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
var svc = ScriptApp.getService();
if (!svc.isEnabled()) {
// it's not enabled, and should be
svc.enable(svc.Restriction.ALL);
}
}
 
function doGet(e){
// uses 2d array library https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library
var t = HtmlService.createTemplateFromFile("oomfo template");
t.id = e.parameter.id;
//var id ="q1";
var doc = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
var sheet = doc.getSheetByName("ALL");
var data = sheet.getRange(2, 3, sheet.getLastRow(), 2).getValues();
var filtered = ArrayLib.filterByText(data, 0, t.id);
counts = {};
for (i in filtered){
if (counts[filtered[i][1]] == undefined){ // see if tweet is in our object store
counts[filtered[i][1]] = 0; // if not add it using the tweet text as key
}
counts[filtered[i][1]]++;
}
t.counts = counts;
//return t.evaluate();
return ContentService.createTextOutput(t.evaluate().getContent())
.setMimeType(ContentService.MimeType.XML);
}
oomfo template.xml
XML
1 2 3 4 5 6 7 8 9 10
<chart caption="Voting Response for <?= id ?>" yaxisname="Votes" xaxisname="Option" showvalues="0" animation="1" stack100percent="0" themename="Default">
<set label="A" value="<?= counts.A || 0 ?>"/>
<set label="B" value="<?= counts.B || 0 ?>"/>
<set label="C" value="<?= counts.C || 0 ?>"/>
<set label="D" value="<?= counts.D || 0 ?>"/>
<set label="E" value="<?= counts.E || 0 ?>"/>
<PP_Internal>
<chart themename="Default"/>
</PP_Internal>
</chart>

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.