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

exportjson.js

// 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 */
var DEFAULT_FORMAT = FORMAT_PRETTY;
var DEFAULT_LANGUAGE = LANGUAGE_JS;
var DEFAULT_STRUCTURE = STRUCTURE_LIST;


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 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);
  listBox.setVisibleItemCount(1);
  
  var cache = CacheService.getPublicCache();
  var selectedValue = cache.get(name);
  Logger.log(selectedValue);
  for (var i = 0; i < items.length; i++) {
    listBox.addItem(items[i]);
    if (items[1] == selectedValue) {
      listBox.setSelectedIndex(i);
    }
  }
  return listBox;
}

function makeButton(app, parent, name, callback) {
  var button = app.createButton(name);
  app.add(button);
  var handler = app.createServerClickHandler(callback).addCallbackElement(parent);;
  button.addClickHandler(handler);
  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));
  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));
  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);
  
  Logger.log(options);
  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 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, 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.id] = 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)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  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) {
      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"
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;
}

// 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;
}

Found this reading the article about you on lifehacker (http://lifehacker.com/5985434/im-pamela-fox-product-engineer-at-coursera-and-this-is-how-i-work). 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 https://gist.github.com/ralfharing/5810205/revisions .

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

This has been very useful! Thanks a lot :)

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.

@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!

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!

Thank you 😄

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.

Thanks!

This worked like a charm. Thank you for sharing!

Works wonderfully - thanks for sharing.

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.

thanks a lot, 2015 and this still works. we used it for our hobby open source project:
https://github.com/thirdy/durian/

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 http://blog.pamelafox.org/2013/06/exporting-google-spreadsheet-as-json.html).
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.
Amaury

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.

@mmellado

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

@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?

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:
"Books":[
{
"title": "Hallucinations",
"authors":[
{ "name" : "Oliver" },
{"name" : "Jaons"}
]
},.....

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. https://gist.github.com/Ziamor/7f01e5d2882e79c78b2a18dbd6961e01

Hey, thanks a lot for the script!

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.

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 == '_' ||
    isDigit_(char);
}

Thanks!

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!

Thank you mate, this works quite beautifully. Hats off to you, sir!

@flowski, @Ziamor, @Michael-F-Ellis
I've put your suggestions in this Gist:
https://gist.github.com/simonepri/c3e62eb0291d3230d0d0481cf18c3480
These are the new configurations available:

@pamelafox check if you can marge it in your gist.

Found it on Youtube. Thanks for sharing this!

I wasn't able to make this work on Google Sheets. My solution was to export to CSV, then convert CSV to JSON (for which there are many tools available).

Many thanks for this one! It works perfectly well. I am trying to to add an option where specific records (rows) could be selected and exported as Json. Could somebody help me with the code. A stack overflow question is postedhttps://stackoverflow.com/questions/50414505/export-selected-rows-from-google-spreadseet-as-json . Guidance appreciated. I am not a programmer, anyhow do some hobby programming
function exportRecords(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var records = sheet.getActiveCell();
var rowsData = getRowsData_(records, getExportOptions(e));
var json = makeJSON_(rowsData, getExportOptions(e));
return displayText_(json);
}
function getRowsData_(sheet, options) {
var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
var headers = headersRange.getValues()[0];
var selection = sheet.getSelection();
var dataRange = selection.getCurrentCell();
var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
if (options.structure == STRUCTURE_HASH) {
var objectsById = {};
objects.forEach(function(object) {
objectsById[object.id] = object;
});
return objectsById;
} else {
return objects;
}
}

Hi. I used this to export a JSON file but I keep getting an error about the coordinates or dimensions of the range are invalid. how am I suppose to correct that?

@RonaCecilia as @righi mentions here: https://gist.github.com/pamelafox/1878143#gistcomment-778527
just freeze the first row and it will work View > Freeze Rows > Freeze Row 1

Hey, seems like UIApp is deprecated? Any update on this piece of code?

I've investigated this yasterday, probably will updating in this weekend - ping me if i forgot to pass the solution here [EDIT: deprecated, see plugin below, maybe someone else will update this)

even better you can use plugin like this https://chrome.google.com/webstore/detail/export-sheet-data/bfdcopkbamihhchdnjghdknibmcnfplk in my case it was only ID column adaptation

Hi Guys
The line HtmlService.createApplication().setTitle() is an Error now since UiApp is already depreciated. Is there any new code update here? Appreciate much the help. Thanks

Hey, has anybody come up with any solution for this UIApp deprecated problem yet.

I just used the "Export Sheet Data" add-on with no problem.

@mrclay Link pls?

@kadnan https://chrome.google.com/webstore/detail/export-sheet-data/bfdcopkbamihhchdnjghdknibmcnfplk?hl=en

Thank you SO much! This is a perfect quick solution

Is there a way to get this to output to a file? - I only get a dialog box, but really, I want it to go to a file that I can open with another application

@pamelafox you're an awesome programer for sure. Nice work.

Thank you, works very well

Good job. Thanks. Headers cannot be in Hebrew but content can be.

Thank you so much! <3

It is not working for me.

• I click export
• then pop-up opens
• it asks me to log in with my google account
• I choose one
• and then I see:

Sign in with Google temporarily disabled for this app
This app has not been verified yet by Google in order to use Google Sign In.

can anybody else reproduce this?

Hello @igorbuts your problem doesn't seem to come from Json export. In this sense, exportJson.gs is a file to add to the app script project.
Your problem comes from Google validation for your appscript project/application. This is a common error, you can see here urlz.fr/cbBs

Thank you, this is so helpful :)

I tried to run but I get this error

The number of rows in the range must be at least 1

I tried to run but I get this error

The number of rows in the range must be at least 1

I think I got that error before. Try freezing your header row. View > Freeze. That's what fixed it for me

thank you, you saved me a lot of time, is still working!
@maggiechen you are right, the same happened to me and it solved when I freeze the first row in all the tabs, thanks for the idea!

I tried to run but I get this error
The number of rows in the range must be at least 1

I think I got that error before. Try freezing your header row. View > Freeze. That's what fixed it for me

In my case, this error occurs because sheet.getFrozenRows () always returns 0.0.

I changed line 139:

var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
to
var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows()+1, sheet.getMaxColumns());

with sheet.getFrozenRows()+1 worked for me

I got an error using this. But found this alternative library which works pretty well https://github.com/odwyersoftware/sheet2api-js#read-rows

How would I edit the code to put data into a reverse order from Google Sheets?

For example, this is my JSON object I exported. I would want the Supplemental Amount at the top and the empID at the bottom.
[
{
"empId": 1234,
"firstName": "Joe",
"lastName": "Shmoe",
"date": "2020-04-01T04:00:00.000Z",
"laborCode1": "ABC",
"laborCode2": "789-1",
"laborCode3": "Shoveling",
"hours": 4,
"payCode": "Reg",
"rate": 20,
"supplementalAmount": 0
},
{
"empId": 1234,
"firstName": "Joe",
"lastName": "Shmoe",
"date": "2020-04-01T04:00:00.000Z",
"laborCode1": "ABC",
"laborCode2": "678-2",
"laborCode3": "Mason",
"hours": 3.5,
"payCode": "Reg",
"rate": 20,
"supplementalAmount": 0
},
{
"empId": 1234,
"firstName": "Joe",
"lastName": "Shmoe",
"date": "2020-04-01T04:00:00.000Z",
"laborCode1": "ABC",
"laborCode2": "678-2",
"laborCode3": "Sales",
"hours": 0.5,
"payCode": "Reg",
"rate": 15,
"supplementalAmount": 100
},
{
"empId": 5678,
"firstName": "Sally",
"lastName": "Smith",
"date": "2020-04-01T04:00:00.000Z",
"laborCode1": "ABC",
"laborCode2": "789-1",
"laborCode3": "Shoveling",
"hours": 8,
"payCode": "Reg",
"rate": 25,
"supplementalAmount": 0
},
{
"empId": 8910,
"firstName": "Tony ",
"lastName": "Stark",
"date": "2020-04-02T04:00:00.000Z",
"laborCode1": "DEF",
"hours": 6.75,
"payCode": "Reg",
"rate": 32.75,
"supplementalAmount": 0
},
{
"empId": 8910,
"firstName": "Tony ",
"lastName": "Stark",
"date": "2020-04-02T04:00:00.000Z",
"laborCode1": "DEF",
"hours": 2.25,
"payCode": "OT",
"rate": 32.75,
"supplementalAmount": 0
}
For example, this is my JSON object I exported. I would want the Supplemental Amount at the top and the empID at the bottom.

Hi,
I'm having trouble with the fourth or fifth element of each JSON export I make.
It's working as expected on Google Spreadsheet, I've my JSON content.
But when I look at my fourth or fifth element, the JSON object ends like this :

{
  property1: 'value',
  property2: 'value',
  property3: 'va
}

Notice the ' missing at the end of property3 value.
I correct it each time and it's working but weird bug.

Anyone encounters this?

I'm using my own version of this gist, which is a compilation of this thread.

@flowsky do you have the problem I have with your piece of code ? 🔝

@jeremyhalin I experience the same issue, but it's always the first instance of when the last field has an apostrophe in it. All subsequent objects are fine.

getColumnsData_() doesn't work... anyone have a fix?

Saved me tons of time, huge thanks!

Thanks, great script. I've noticed a little bug in normalizeHeader()_ function: an output for say "someCamelCaseString" is "somecamelcasestring". I fixed it by inserting upperCase = letter == letter.toUpperCase(); after line 231.

I tried to run but I get this error

The number of rows in the range must be at least 1.

@maggiechen thanks. when i use freeze, i can fix this issue