A Google apps script to pull json from a spreadsheet

_jsonPuller.md

JSON Puller - Google Apps Script

Transforms the data of a given Spreadsheet Sheet to JSON.

• The frozen rows are taken as keys for the JSON.
• The data taken for the values is only that after the frozen rows

Set up:

exportJSON(Spreadsheet) - transforms the data in the given sheet to JSON.

@params ss - SpreadsheetApp>Spreaddheet Class.

@returns Object[] - Array of JSON objects.

getRowsData(Sheet) iterates row by row in the sheer and returns an array of objects. Each object contains all the data for a given row, indexed by its normalized column name.

@params sheet - SpreadsheetApp>Sheet Class, the sheet object that contains the data to be processed.

@returns Object[] - an Array of objects with the headers as keys.

getObjects(String[], String[]), For every row in the data, generates an object.
Names of object fields are defined in keys.

@params data - JavaScript 2d array.

@params keys - Array of Strings that define the property names for the objects to create.

@returns Object[] - JSON, an Array of objects.

getColumnsData(Sheet Object, RangeElement[], int) iterates column by column in the input range and returns an array of objects. Each object contains all the data for a given column, indexed by its normalized row name.

@params sheet - the sheet object that contains the data to be processed

@params range - the exact range of cells where the data is stored

@params (optional)rowHeadersColumnIndex - specifies the column number where the row names are stored.

@returns Object[] - an Array of objects.

normalizeHeaders(String[]) Returns an Array of normalized Strings.

@params headers - Array of raw headers

@returns String[] - Array of normalized headers.

normalizeHeaders(String[]) Normalizes a string by removing all alphanumeric characters Uses camelCase to separate words. The output will always start with a lower case letter. This function is designed to produce JavaScript object property names.

@params headers - Array of raw headers

@returns String[] - Array of normalized headers.

Example:
    "First Name" -> "firstName"
    "Market Cap (millions) -> "marketCapMillions
    "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"

Usage

Replace the Spreadsheed id with the ID of the Spreadsheet you want to transform

Contributing

If you have useful new methods, or better ways of doing what this sample does, please feel free to...

1. Fork it!
2. Make marvels
3. Submit a pull request :D

History

Based on the original gist from crstamps2

jsonPuller.gs

function doGet(){
  var ss = SpreadsheetApp.openById("<ss-id>");
  return exportJSON(ss);
}

/* 
 * exportJSON(Spreadsheet) transforms the data in the given sheet to JSON.
 * @params ss - SpreadsheetApp>Spreaddheet Class.
 * @returns Object[] - Array of JSON objects.
*/ 
function db_getJSON(ss) {
  var sheet = ss.getSheetByName(ss);
  var rowsData = getRowsData(sheet);
  var result = JSON.stringify(rowsData);
  Logger.log(result);
  return result
}

/* 
 * getRowsData(Sheet) iterates row by row in the sheer and returns an array of objects.
 * Each object contains all the data for a given row, indexed by its normalized column name.
 * @params sheet - SpreadsheetApp>Sheet Class, the sheet object that contains the data to be processed.
 * @returns Object[] - an Array of objects with the headers as keys.
*/ 
function getRowsData(sheet) {
  var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
  var headers = headersRange.getValues()[0];
  var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  return getObjects(dataRange.getValues(), normalizeHeaders(headers));
}

/* 
 * getObjects(String[], String[]), For every row in the data, generates an object.  
 * Names of object fields are defined in keys.
 * @params data - JavaScript 2d array.
 * @params keys - Array of Strings that define the property names for the objects to create.
 * @returns Object[] - JSON, an Array of objects.
*/ 
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

/* 
 * getColumnsData(Sheet Object, RangeElement[], int) iterates column by column in the input range and returns an array of objects.
 * Each object contains all the data for a given column, indexed by its normalized row name.
 * @params sheet - the sheet object that contains the data to be processed
 * @params range - the exact range of cells where the data is stored
 * @params (optional)rowHeadersColumnIndex - specifies the column number where the row names are stored.
 * @returns Object[] - an Array of objects.
*/ 
function getColumnsData(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
  var headers = normalizeHeaders(arrayTranspose(headersTmp)[0]);
  return getObjects(arrayTranspose(range.getValues()), headers);
}

/* 
 * normalizeHeaders(String[]) Returns an Array of normalized Strings.
 * @params headers - Array of raw headers
 * @returns String[] - Array of normalized headers.
*/ 
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

/* 
 * normalizeHeaders(String[]) Normalizes a string by removing all alphanumeric characters 
 * Uses camelCase to separate words. The output will always start with a lower case letter.
 * This function is designed to produce JavaScript object property names.
 * @params headers - Array of raw headers
 * @returns String[] - Array of normalized headers.
 * Examples:
 *   "First Name" -> "firstName"
 *   "Market Cap (millions) -> "marketCapMillions
 *   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
*/ 
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

/* 
 * isCellEmpty(String) Returns true if the cell where cellData was read from is empty.
 * @params cellData - an SpreadsheetApp Cell Object. 
 * @returns boolean - false if the string is empty. 
*/ 
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

/* 
 * isAlnum(char) Returns true if the character char is alphabetical, false otherwise.
 * @params char - a single character.
 * @returns boolean.
*/ 
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

/* 
 * isDigit(char) Returns true if the character char is a digit, false otherwise.
 * @params char - a single character.
 * @returns boolean.
*/ 
function isDigit(char) {
  return char >= '0' && char <= '9';
}

/* 
 * isDigit(String[]) returns the transposed table of given 2d Array.
 * @params data - JavaScript 2d array.
 * @returns String[] - transposed 2d array.
 * Example: 
 *     arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]]
*/ 
function arrayTranspose(data) {
  if (data.length == 0 || data[0].length == 0) {
    return null;
  }
  var ret = [];
  for (var i = 0; i < data[0].length; ++i) {
    ret.push([]);
  }

  for (var i = 0; i < data.length; ++i) {
    for (var j = 0; j < data[i].length; ++j) {
      ret[j][i] = data[i][j];
    }
  }
  return ret;
}