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.

Browse More Articles

Tweet

Written by Unknown

We are one of the initiators of the development of information technology in understanding the need for a solution that is familiar and close to us.