Create a gist now

Instantly share code, notes, and snippets.

// 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.
var FORMAT_ONELINE = 'One-line';
var FORMAT_MULTILINE = 'Multi-line';
var FORMAT_PRETTY = 'Pretty';
var LANGUAGE_JS = 'JavaScript';
var LANGUAGE_PYTHON = 'Python';
var STRUCTURE_LIST = 'List';
var STRUCTURE_HASH = 'Hash (keyed by "id" column)';
/* Defaults for this particular spreadsheet, change as desired */
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [
{name: "Export JSON for this sheet", functionName: "exportSheet"},
{name: "Export JSON for all sheets", functionName: "exportAllSheets"},
{name: "Configure export", functionName: "exportOptions"},
ss.addMenu("Export JSON", menuEntries);
function exportOptions() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var app = UiApp.createApplication().setTitle('Export JSON');
var grid = app.createGrid(4, 2);
grid.setWidget(0, 0, makeLabel(app, 'Language:'));
grid.setWidget(0, 1, makeListBox(app, 'language', [LANGUAGE_JS, LANGUAGE_PYTHON]));
grid.setWidget(1, 0, makeLabel(app, 'Format:'));
grid.setWidget(1, 1, makeListBox(app, 'format', [FORMAT_PRETTY, FORMAT_MULTILINE, FORMAT_ONELINE]));
grid.setWidget(2, 0, makeLabel(app, 'Structure:'));
grid.setWidget(2, 1, makeListBox(app, 'structure', [STRUCTURE_LIST, STRUCTURE_HASH]));
grid.setWidget(3, 0, makeButton(app, grid, 'Export Active Sheet', 'exportSheet'));
grid.setWidget(3, 1, makeButton(app, grid, 'Export All Sheets', 'exportAllSheets'));
function makeLabel(app, text, id) {
var lb = app.createLabel(text);
if (id) lb.setId(id);
return lb;
function makeListBox(app, name, items) {
var listBox = app.createListBox().setId(name).setName(name);
var cache = CacheService.getPublicCache();
var selectedValue = cache.get(name);
for (var i = 0; i < items.length; i++) {
if (items[1] == selectedValue) {
return listBox;
function makeButton(app, parent, name, callback) {
var button = app.createButton(name);
var handler = app.createServerClickHandler(callback).addCallbackElement(parent);;
return button;
function makeTextBox(app, name) {
var textArea = app.createTextArea().setWidth('100%').setHeight('200px').setId(name).setName(name);
return textArea;
function exportAllSheets(e) {
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, getExportOptions(e));
var sheetName = sheet.getName();
sheetsData[sheetName] = rowsData;
var json = makeJSON_(sheetsData, getExportOptions(e));
return displayText_(json);
function exportSheet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rowsData = getRowsData_(sheet, getExportOptions(e));
var json = makeJSON_(rowsData, getExportOptions(e));
return displayText_(json);
function getExportOptions(e) {
var options = {};
options.language = e && e.parameter.language || DEFAULT_LANGUAGE;
options.format = e && e.parameter.format || DEFAULT_FORMAT;
options.structure = e && e.parameter.structure || DEFAULT_STRUCTURE;
var cache = CacheService.getPublicCache();
cache.put('language', options.language);
cache.put('format', options.format);
cache.put('structure', options.structure);
return options;
function makeJSON_(object, options) {
if (options.format == FORMAT_PRETTY) {
var jsonString = JSON.stringify(object, null, 4);
} else if (options.format == FORMAT_MULTILINE) {
var jsonString = Utilities.jsonStringify(object);
jsonString = jsonString.replace(/},/gi, '},\n');
jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
jsonString = prettyJSON.replace(/}\],/gi, '}],\n');
} else {
var jsonString = Utilities.jsonStringify(object);
if (options.language == LANGUAGE_PYTHON) {
// add unicode markers
jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"$1": u"');
return jsonString;
function displayText_(text) {
var app = UiApp.createApplication().setTitle('Exported JSON');
app.add(makeTextBox(app, 'json'));
var ss = SpreadsheetApp.getActiveSpreadsheet();;
return app;
// 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, options) {
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());
var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
if (options.structure == STRUCTURE_HASH) {
var objectsById = {};
objects.forEach(function(object) {
objectsById[] = object;
return objectsById;
} else {
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.getNumRows(), 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, 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)) {
object[keys[j]] = cellData;
hasData = true;
if (hasData) {
return objects;
// 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) {
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"
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;
if (!isAlnum_(letter)) {
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' ||
// 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) {
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;

Found this reading the article about you on lifehacker ( I have used Coursera once, and hope to have that pleasure again in the near future. Coursera blows away the online course management system used at the university in which I am currently enrolled (Kennesaw State University, GA). I love your coding style. A couple of colleagues and I are making a move towards naturally-documented code, of which I feel this is a great example. Keep up the great work. It is awesome to find another developer who is not dead set on making their code impossible to understand by mere humans!


Not sure if there's a nice way to notify you on this (doesn't seem like there are pull requests for gists) but I added a quick error message at .

kaartz commented Jun 28, 2013

Hi, is it possible to export a sheet to XML?


This has been very useful! Thanks a lot :)

v3nt commented Feb 27, 2014

mmm - getting an error "The coordinates or dimensions of the range are invalid. Details Dismiss" and details are 'Message details
The coordinates or dimensions of the range are invalid.' Tried selecting just a few rows or all of them but the same error comes up.

righi commented Mar 18, 2014

@pamelafox A million thank yous for this awesome script.

@v3nt I had the same "The coordinates or dimensions of the range are invalid." error, but it went away when I marked the top row as a header by freezing it. (View > Freeze Rows > Freeze Row 1)


Is there a way to make the JSON open in the browser so you can copy the link?


I'm getting an error "prettyJSON is not defined." when I'm trying to use the Multiline format of JSON output.


This is super useful, thanks!

adityab commented Apr 20, 2015

Thank you so much for this.


This is a super newbie question (please forgive). I just want to prevent the normalization process of the headers. What's the most efficient way to bypass this process? Many thanks to anyone who takes pity :)


Dear @raymears

The piece of code that takes care of normalization of headers is

 var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);

Change it for

var headers = arrayTranspose_(headersTmp)[0];

Should do the trick


Works like a charm, thank you!

octobel commented Jul 9, 2015

Thank you 😄

hiep-vo commented Jul 21, 2015

Hi, how can i get a int array in a cell of table?
Example i have a table name is "product"

id products
1 [1,2,3,4,5]

and i want the json like this

   "id": 1,
   "products": [1,2,3]

Thanks! Would love easy Array options.


This worked like a charm. Thank you for sharing!

kanugom commented Dec 19, 2015

Works wonderfully - thanks for sharing.

krbabu commented Dec 23, 2015

I recently made use of this code in converting a Google Spreadsheet into JSON. And, I was thrilled to see the output.

It is a matter of additional pride for me that you are also a graduate of USC CS department.

thirdy commented Jan 1, 2016

thanks a lot, 2015 and this still works. we used it for our hobby open source project:

mmellado commented Jan 9, 2016

Thanks a lot, this works really good! For those requesting for comma separated values to be returned as an array, adding the following snippet to the beginning of makeJSON_ function shall do the trick :)

 var obj, o;
  for (var i = 0; i < object.length; i++) {
    obj = object[i];
    if (obj) {
      for (prop in obj) {
        o = obj[prop].toString();
        if (o && o.indexOf(',') > -1) {
          object[i][prop] = o.split(',');

@righi +1 for your "Freeze row" response - worked for me


Good morning.
I've got two questions (posted into
The first one is about how to export directly the json data into a file with a view to download it (or publish it).
The second is about date format. It seems that date format isn't pretty (spreadsheet number format).
Thank you for your kind collaboration.


Hi ! Thanks a lot. working really great. However I would like to be able to merge some raw with the same ID into only one array.... don't know if I make my self clear.

flowski commented May 25, 2016 edited


Put this code in the beginning of makeJSON_ function to be able to use arrays and objects like [1,2,"string"] or {"a":2,"b:3} in cells

 // START Array/Object addon
  var obj, o;
  for (var i = 0; i < object.length; i++) {
    obj = object[i];
    if (obj) {
      for (prop in obj) {
        o = obj[prop].toString();
        if (o && (o.indexOf('{') == 0 && o.indexOf('}') == o.length-1) || (o.indexOf('[') == 0 && o.indexOf(']') == o.length-1)) {
          object[i][prop] = JSON.parse(o);
  //END Array/Object addon

Thank you so much! This has been so helpful!
Question: Has anybody altered this code to work with a spreadsheet that is column based rather than row based? I am trying to tweak it for my current needs and having trouble

thegdog commented Jun 24, 2016 edited

@mmellado, @flowski,
Tried using each of your code, but neither one works. object.length is undefined so that block never runs.

Any ideas how to address that?

escounpo commented Jul 5, 2016

Suppose I wanted to create children of an item, e.g,. I create a children node with multiple authors. Do you know how you would structure that in the spreadsheet? (I realize your script won't handle that now). I want to create json:
"title": "Hallucinations",
{ "name" : "Oliver" },
{"name" : "Jaons"}

Ziamor commented Jul 29, 2016

The current script was normalizing my headers, changing them to all lower case, which I needed to be untouched for the project I'm using. In the comments I noticed a few other people with this problem so I forked this gist and added a check box to the settings to enable/disable normalizing.


Hey, thanks a lot for the script!

y3nd commented Oct 24, 2016

Thanks !


@pamelafox What a lifesaver this was!! No more exporting then importing into the online converters then pasting out...UGH :) Just like @righi mentioned above; freezing the first row cleared the out of range error. Thank you for creating and sharing this script.

Acen commented Dec 20, 2016

Good stuff.
I needed to retain underscores in my key names, so I modified isAlum_().

function isAlnum_(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    char == '_' ||



Very nice. In function getObjects_() , I added

if ( typeof cellData === 'string' && cellData.trim().toLowerCase() == "null" ) { cellData = null; }

just before
object[keys[j]] = cellData; hasData = true;

This allows the option to specify 'null' (in any capitalization) and get a proper null as the value of the corresponding key. Pamela's script already does the right thing with 'true' and 'false', so this makes it consistent. For my applications, it's cleaner to be able to know that a key exists and check for null than to have to first check if the key has been omitted because the cell was empty -- although my change preserves that possibility for when it may desirable.

Thanks again for creating and sharing this!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment