Tuesday, 19 November 2013

From Survey To Google Spreadsheet To Google Document

Earlier today we were looking over the results of a survey we'd put out with Google Forms. The answers were well thought out, very long and textual and impossible to read in a spreadsheet.

As a group we want to read the responses and share our thoughts about them using the comment feature in Google Documents so I whipped up this script to move the all the data from a spreadsheet to a Google Document.


function document_from_spreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var header_range = sheet.getRange(1,1, 1, sheet.getLastColumn())
  var headers = header_range.getValues()[0]
  
  var data_range = sheet.getRange(2,1, sheet.getLastRow(), sheet.getLastColumn())
  var values = data_range.getValues()
  
  
  var doc = DocumentApp.create(ss.getName() + " Exported")
  var body = doc.getBody()
  
  for (var h in headers){
    h = Number(h)
    var header_name = headers[ h ]
    var p = body.appendParagraph(header_name)
    
    p.setHeading( DocumentApp.ParagraphHeading.HEADING1 )
    
    for (i = 0; i < values.length; i++){
      var row = values[i]
      var value = row[h]
      var p = body.appendParagraph(value).setHeading( DocumentApp.ParagraphHeading.NORMAL )
      body.appendHorizontalRule()
      
    }
  body.appendPageBreak()
  }
  doc.saveAndClose()
}

function onOpen(T) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Export to Google Document", functionName: "document_from_spreadsheet"},
                     ];
  ss.addMenu("Admin", menuEntries);
}
  

There was a little cleaning up to do, to remove any choice-based or numerical data items ( we could of course paste those in as images ) but this code was all we need to start easier on the eye and brain analysis of the responses.



No comments:

Post a Comment