Google Spreadsheet JSON ExportHow-to at http://blog.pamelafox.org/2013/06/exporting-google-spreadsheet-as-json.html

exportjson.js

const EMPTY_VALUE = undefined;

// Includes functions for exporting active sheet or all sheets as JSON object (also Python object syntax compatible).
// Tweak the makePrettyJSON_ function to customize what kind of JSON to export.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: "Export JSON for this sheet", functionName: "exportSheet"},
    {name: "Export JSON for all sheets", functionName: "exportAllSheets"}
  ];
  ss.addMenu("Export JSON", menuEntries);
}

function exportAllSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetsData = {};
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var rowsData = getRowsData_(sheet);
    var sheetName = sheet.getName(); 
    sheetsData[sheetName] = rowsData;
  }
  var json = makeJSON_(sheetsData);
  displayText_(json);
  return json;
}

function exportSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rowsData = getRowsData_(sheet);
  var json = makeJSON_(rowsData);
  displayText_(json);
}

function makeJSON_(object) {
  var jsonString = JSON.stringify(object, null, 4);
  return jsonString;
}

function displayText_(text) {
  var output = HtmlService.createHtmlOutput("<textarea style='width:100%;' rows='20'>" + text + "</textarea>");
  output.setWidth(400)
  output.setHeight(300);
  SpreadsheetApp.getUi()
      .showModalDialog(output, 'Exported JSON');
}

/**
 * getRowsData iterates row by row in the input range and returns an array of objects.
 * Each object contains all the data for a given row, indexed by its normalized column name.
 * Arguments:
 *   - sheet: the sheet object that contains the data to be processed
 *   - range: the exact range of cells where the data is stored
 *   - columnHeadersRowIndex: specifies the row number where the column names are stored.
 *       This argument is optional and it defaults to the row immediately above range; 
 * Returns an Array of objects.
 */
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.getLastRow(), headers.length);
  var objects = getObjects_(dataRange.getValues(), dataRange.getRichTextValues(), headers);
  return objects;
}

/**
 * getColumnsData 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.
 * Arguments:
 *   - sheet: the sheet object that contains the data to be processed
 *   - range: the exact range of cells where the data is stored
 *   - rowHeadersColumnIndex: specifies the column number where the row names are stored.
 *       This argument is optional and it defaults to the column immediately left of the range; 
 * Returns an Array of objects.
 */
function getColumnsData_(sheet, range, rowHeadersColumnIndex) {
  rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
  var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getLastRow(), 1).getValues();
  var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);
  return getObjects_(arrayTranspose_(range.getValues()), headers);
}

/**
 * For every row of data in data, generates an object that contains the data. Names of
 * object fields are defined in keys.
 * Arguments:
 *   - data: JavaScript 2d array
 *   - keys: Array of Strings that define the property names for the objects to create
 */
function getObjects_(data, richData, keys) {
  var isObject = false;
  var objects = [];
  if (keys[0] === "id") {
    objects = {};
    isObject = true;
  }
  for (var i = 0; i < data.length; ++i) {
    var id = false;
    var object = {};
    var hasData = keys.length;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (keys[j].includes("[richText]")) {
        cellData = htmlEncodeRichText_(richData[i][j]);
      } else {
        cellData = data[i][j];
      }
      if (isCellEmpty_(cellData)) {
        hasData--;
      }
      if (isObject && keys[j] === "id") {
        id = cellData;
      } else {
        setObjectData_(object, keys[j].replace("[richText]", ""), cellData);
      }
    }
    if (hasData !== 0) {
      if (isObject) {
        if (id !== undefined) {
          id = `${id}`;
          let arr = id.split(".");
          let obj = objects;
          for (let i in arr) {
            if (i == (arr.length - 1)) {
              if (Object.keys(object).length === 1 && object.value) {
                obj[arr[i]] = object.value;
              } else {
                  obj[arr[i]] = object;
              }
            } else if (!(arr[i] in obj)) {
              obj[arr[i]] = {};
            }
            obj = obj[arr[i]];
          }
        }
      } else{
          objects.push(object);
      }
    }
  }
  return objects;
}

/**
 * For every key in the keys array
 * recursively fill the object with data
 * Arguments:
 *   - object: JavaScript object to fill
 *   - keys: Array (or '.' separated String) that deifine the imbricated properties names for the object to fill
 *   - data: value that should be put
 */
function setObjectData_(object, keys, data) {
  if (!keys) return;
  if (!Array.isArray(keys)) {
    keys = keys.split(".");
  }
  if (keys.length == 1) {
    if (keys[0].charAt(0) === "@") {
      object.push({id: keys[0].substring(1), value: data});
    } else if (!(Array.isArray(object) && data == "")) {
      object[keys[0]] = data === "" ? EMPTY_VALUE : data;
    }
  } else {
    if (!object.hasOwnProperty(keys[0])) {
      if (!isNaN(keys[1]) || keys[1].charAt(0) === "@")
        object[keys[0]] = [];
      else
        object[keys[0]] = {};
    }
    setObjectData_(object[keys[0]], keys.slice(1), data);
  }
}

/**
 * Returns an Array of normalized Strings.
 * Arguments:
 *   - headers: Array of Strings to normalize
 */
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;
}

/**
 * Normalizes a string, by removing all alphanumeric characters and using mixed case
 * to separate words. The output will always start with a lower case letter.
 * This function is designed to produce JavaScript object property names.
 * Arguments:
 *   - header: string to normalize
 * Examples:
 *   "First Name" -> "firstName"
 *   "Market Cap (millions) -> "marketCapMillions
 *   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
 * Keeps the "." for imbricated datas
 */
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 (letter !== "_" && letter !== "@" && letter !== "." && !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;
}

/**
 * Returns true if the cell where cellData was read from is empty.
 * Arguments:
 *   - cellData: string
 */
function isCellEmpty_(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

/**
 *  Returns true if the character char is alphabetical, false otherwise.
 */
function isAlnum_(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit_(char);
}

/**
 *  Returns true if the character char is a digit, false otherwise.
 */
function isDigit_(char) {
  return char >= '0' && char <= '9';
}

/**
 * Given a JavaScript 2d Array, this function returns the transposed table.
 * Arguments:
 *   - data: JavaScript 2d Array
 * Returns a JavaScript 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;
}

/**
 * Given a RichTextValue Object, iterate over the individual runs
 *    and call out to htmlStyleRtRun() to return the text wrapped
 *    in <span> tags with specific styling.
 * @param {RichTextValue} richTextValue a RichTextValue object
 *    from a given Cell.
 * @return {string} HTML encoded text 
 */
function htmlEncodeRichText_(richTextValue) {
  return richTextValue.getRuns().reduce((s, r) => {
    text = r.getText().replace(/\n/g, "<br>");
    style = r.getTextStyle();
    if (style.isStrikethrough()) {
      text = "<strike>" + text + "</strike>";
    }
    if (style.isUnderline()) {
      text = "<u>" + text + "</u>";
    }
    if (style.isBold()) {
      text = "<b>" + text + "</b>";
    }
    if (style.isItalic()) {
      text = "<i>" + text + "</i>";
    }
    if (style.getFontSize() > 11) {
      text = "<big>" + text + "</big>";
    }
    if (style.getFontSize() < 11) {
      text = "<small>" + text + "</small>";
    }
    return s + text;
  }, "");
}

Tutoriel

Every sheet is a collection of objects. If you export This Sheet, you'll get and array of object, if you export All Sheet you'll get a dictionarry of array, keyed by the sheet name.

The first line needs to be frozen (View > Freeze) and will contains the object propertie names.
For imbricated data, use a dot . to separate the inner properties, as a chain.
If you name some inner properties with numbers, it will create an array.

The export default behaviour is an array of objects, but if you name the first column id, it will become a dictionary.
The id collumn follows the same rule as the column name (the dot separated properties chain).

If there is only two column names id and value the object will only take the value of the value column (it won't be an object with value as a property name).

For some edge case scenarii, if you put an @ before an inner property name, it will create and array containing an object with an id and a value propertiy. The id being the name after the @, the value being the cell value.
It may be useful to keep data ordonated in an array, following the columns order.

Enventually, if you add [richText] in a collumn name, it will convert the cell value in HTML text.
It only suports new line, bold, italic, underline, strikethrough, big (fontSize > 10pt) and small (fintSize < 10pt) tags.
You can easily edit that, it's the last function htmlStyleRtRun_

Finally, you may want to change the EMPTY_VALUE constant, that is used to fill the data of empty an cell.

Example

This sheet

Will generate the following JSON

{
    "micheal": {
        "text": "I'm super <b>happy</b>",
        "address": [
            "here",
            "nowhere"
        ],
        "body": {
            "top": "red",
            "bottom": "orange"
        },
        "needs": [
            {
                "id": "watter",
                "value": 3
            },
            {
                "id": "food",
                "value": 2
            }
        ]
    },
    "emilly": {
        "text": "Let's <i>go</i><br>dancing",
        "address": [
            "there",
            "everywhere"
        ],
        "body": {
            "top": "blue",
            "bottom": "yellow"
        },
        "needs": [
            {
                "id": "watter",
                "value": 1
            },
            {
                "id": "food",
                "value": 4
            }
        ],
        "special": "magic"
    }
}

@florentdescroix
I was putting this in the id column (and a few things in the value column)

its output was

    "attribs": {
    "0": {
       "name": "l1mancer_status",
       "current": "completed",
       "max": " ",
       "id": "-5wPjB9toW5LB3Lt52It"
     },
     "1": {
       "name": "version",
       "current": 1,
       "max": " ",
       "id": "-wGib7mWupfS332Ijf5d"
     }
  }
}`

but my goal is to have this be the out put

   "attribs": [
     {
       "name": "l1mancer_status",
       "current": "completed",
       "max": "",
       "id": "-5wPjB9toW5LB3Lt52It"
     },
     {
       "name": "version",
       "current": 1,
       "max": "",
       "id": "-wGib7mWupfS332Ijf5d"`

how could I get this output? I'm at a loss

@jayx3333 right, I haven't implemented the array form on the id column.
To get your result, you should create a sheet call attribs like that :

If you have other datas, create other sheets, for instance here, maybe a sheet called info may do the trick, with the columns id and value as you did.

@florentdescroix
sadly the way you recommended that I solve my problem doesn't meet the format stranded that I am to match.
it should work if the array form was on id column but I do not know how to change that myself

@jayx3333 What is the final output you'd like ?
I did change a bit of code so the id column is used only if it's the first one. Also I add an EMPTY_FIELD constant.

@florentdescroix Hello its been a while since I last commented a response (thankfully the project that I am working on is not time sensitive). After having looked at the way provided there is a problem that I was running into. It added a lot of empty sheets and would not work with additional sheets with names that weren't named after other parts of the output that I am trying to replicate.
In short the only way that I could make this work is if it was housed in one sheet, the easiest way that it seams to solve this is to implement the array form on the id column. I hate to ask but how could I modify the code so I could get this result?

Hi @jayx3333 I'm not sure to understand.
Do you want to export to JSON, only the sheets that are named after the id column of one sheet ?
If so, it's a bit tricky... I think you should make a new Google Spreadsheet file and copy only the sheets you want to export inside.

Otherwise, change the line 19 to 24 of the code to something like that

  let validNames = []
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName(); 
    if (i === 0 || validNames.indexOf(sheetName) !== -1) {
      var rowsData = getRowsData_(sheet);
      sheetsData[sheetName] = rowsData;
      if (i === 0) {
        validNames = rowsData.map(e => e.id)
      }
    }
  }

So it will only export the first sheet (whatever its name), and all the next sheets that have a name contained in the column id of the first sheet.
(I haven't tested the code, hope it works)

You can also fill the validNames array by hand with the values you want and remove the if i === 0 part.
I hope that's clear, I have no idea of you programation skills.

@florentdescroix Just wanted to say thanks for some nice code. Forced me to remember GoogleDoc stuff and I'm no expert so I guess your tutorial, comments and links are good enough!