Display Google Docs Spreadsheet as JSON.

README.md

Google Docs Spreadsheet as JSON

This code is taken from DJ's Weblog, thanks a lot for this!

How to use:

1. You'll need the key of your spreadsheet, the url format should be like this: https://docs.google.com/spreadsheet/ccc?key=**YOUR-KEY-HERE**=drive_web#gid=0 and also the sheet number (i.e., Sheet 1).
2. In your document file menu, you'll find File Menu > Tools > Script Editor (you will be redirected to Google's script editor.
3. Create a new Script file, and paste the content of the SheetAsJSON.js to the editor stage, and save it.
4. On the file menu, go to Publish > Deploy as Web App and make sure that you set the opions Execute app as: User Accessing the web app and the other option to Who has access to the web app: anyone and update.
5. Now, to use the script/web app you may use the web app like this:

https://script.google.com/macros/s/AKfycbxOLElujQcy1-ZUer1KgEvK16gkTLUqYftApjNCM_IRTL3HSuDk/exec?id=YOUR=SPREADSHEET-KEY&sheet=YOUR-SHEET-NUMBER

SheetAsJSON.js

function doGet(request) {
  var output  = ContentService.createTextOutput(),
      data    = {},
      id      = request.parameters.id,
      sheet   = request.parameters.sheet,
      ss      = SpreadsheetApp.openById(id);

  data.records = readData_(ss, sheet);
  
  var callback = request.parameters.callback;
  
  if (callback === undefined) {
    output.setContent(JSON.stringify(data));
  } else {
    output.setContent(callback + "(" + JSON.stringify(data) + ")");
  }
  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;
}


function readData_(ss, sheetname, properties) {

  if (typeof properties == "undefined") {
    properties = getHeaderRow_(ss, sheetname);
    properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
  }
  
  var rows = getDataRows_(ss, sheetname),
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};

    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    
    data.push(record);

  }
  return data;
}



function getDataRows_(ss, sheetname) {
  var sh = ss.getSheetByName(sheetname);

  return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}


function getHeaderRow_(ss, sheetname) {
  var sh = ss.getSheetByName(sheetname);

  return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];  
}

suppose it returns sheet name eg, "Sheet" but in why mine is "records" ?