Skip to content

Instantly share code, notes, and snippets.

/XLSForm.js

Created Dec 18, 2012
Embed
What would you like to do?
App script for integrating XLSForm with google spreadsheets.
/* Requires underscore.js */
var SERVER_URL = "http://ec2-50-16-84-43.compute-1.amazonaws.com/xlsform/2/json_workbook/";
/*
//The ideal approach would be to send the workbook as an xlsx blob
//and process it the same way as user uploads.
//Unfortunately I can't figure out how to generate such a blob.
function sendBlob() {
var file = DocsList.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId());
var attachment = file.getAs('application/xlsx');//Doesn't work :(
MailApp.sendEmail("test@test.com", "testing xlsx sending", " Body" , {"fileName": "test.xlsx" , "mimeType" : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" , "content":attachment.getBytes() } );
}
*/
function renderPopup(responseJson) {
// Create the UiInstance object myapp and set the title text
var myapp = UiApp.createApplication().setTitle('XLSForm');
var mypanel = myapp.createVerticalPanel();
myapp.add(mypanel);
if(responseJson.error) {
mypanel.add(myapp.createHTML("ERROR:"));
mypanel.add(myapp.createHTML(responseJson.error));
} else {
var downloadLink = myapp.createAnchor("Download", SERVER_URL +
"../download/form" + responseJson.dir +
"/" + responseJson.name);
var previewLink = myapp.createAnchor("Preview", SERVER_URL +
"../static/default/index.html#formPath=../../download/form" +
responseJson.dir + "/");
myapp.add(myapp.createVerticalPanel().add(downloadLink));
myapp.add(myapp.createVerticalPanel().add(previewLink));
_.each(responseJson.warnings, function(warning) {
//TODO: Add option to parse the warnings and error strings,
// and highlight the rows with errors.
mypanel.add(myapp.createHTML(warning));
});
}
return myapp;
}
function workbookToJson() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var output = {};
_.each(sheets, function(sheet, sheetIdx) {
var outSheet = {};
var sheetArrays = sheet.getDataRange().getValues();
var columnHeaders = sheetArrays[0];
_.each(columnHeaders, function(columnHeader){
if(columnHeader === '_rowNum') {
throw new Error("_rowNum is not a valid column header.");
}
});
var outSheet = _.map(sheetArrays.slice(1), function(row, rowIdx) {
//_rowNum is added to support better error messages.
var outRow = { _rowNum : (rowIdx + 1) };
_.each(row, function(val, valIdx) {
outRow[columnHeaders[valIdx]] = val;
});
return outRow;
});
output[sheet.getName()] = outSheet;
});
return output;
}
/**
* Converts the spreadsheet to an ODK form by serializing it into json
* and sending it to a server.
*/
function convert() {
var response = UrlFetchApp.fetch(SERVER_URL, {
method : "post",
muteHttpExceptions: true,
payload : { workbookJson : JSON.stringify(workbookToJson()) }
});
var app;
if(response.getResponseCode() === 200) {
try {
var responseJson = JSON.parse(response.getContentText());
app = renderPopup(responseJson)
} catch(e) {
app = UiApp.createApplication();
app.add(app.createHTML('ERROR: ' + e.toString()));
app.add(app.createHTML(
(function safe_tags(str) {
return str.replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;') ;
})(response.getContentText())));
}
} else {
app = UiApp.createApplication();
app.add(app.createHTML('ERROR: ' + response.getResponseCode()));
}
SpreadsheetApp.getActiveSpreadsheet().show(app);
};
function showInfoPopup(responseJson) {
var app = UiApp.createApplication().setTitle('XLSForm Info');
var mypanel = app.createVerticalPanel();
app.add(mypanel);
mypanel.add(app.createHTML("XLSForm is a tool for generating forms for use with ODK Survey, and other applications to come."));
var docLink = app.createAnchor("Syntax documentation", "http://code.google.com/p/opendatakit/wiki/XLSForm2Docs");
app.add(docLink);
//TODO: Add settings for choosing conversion target and turning on/off spreadsheet highlighting.
SpreadsheetApp.getActiveSpreadsheet().show(app);
}
/**
* Adds a custom menu to the active spreadsheet.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Convert",
functionName : "convert"
}, {
name : "Settings",
functionName : "showInfoPopup"
}];
sheet.addMenu("XLSForm", entries);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment