// 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; | |
} |
This comment has been minimized.
This comment has been minimized.
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 . |
This comment has been minimized.
This comment has been minimized.
Hi, is it possible to export a sheet to XML? |
This comment has been minimized.
This comment has been minimized.
This has been very useful! Thanks a lot :) |
This comment has been minimized.
This comment has been minimized.
mmm - getting an error "The coordinates or dimensions of the range are invalid. Details Dismiss" and details are 'Message details |
This comment has been minimized.
This comment has been minimized.
@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) |
This comment has been minimized.
This comment has been minimized.
Is there a way to make the JSON open in the browser so you can copy the link? |
This comment has been minimized.
This comment has been minimized.
I'm getting an error "prettyJSON is not defined." when I'm trying to use the Multiline format of JSON output. |
This comment has been minimized.
This comment has been minimized.
This is super useful, thanks! |
This comment has been minimized.
This comment has been minimized.
Thank you so much for this. |
This comment has been minimized.
This comment has been minimized.
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 :) |
This comment has been minimized.
This comment has been minimized.
Dear @raymears The piece of code that takes care of normalization of headers is
Change it for var headers = arrayTranspose_(headersTmp)[0]; Should do the trick |
This comment has been minimized.
This comment has been minimized.
Works like a charm, thank you! |
This comment has been minimized.
This comment has been minimized.
Thank you |
This comment has been minimized.
This comment has been minimized.
Hi, how can i get a int array in a cell of table?
and i want the json like this
|
This comment has been minimized.
This comment has been minimized.
Thanks! Would love easy Array options. |
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
This worked like a charm. Thank you for sharing! |
This comment has been minimized.
This comment has been minimized.
Works wonderfully - thanks for sharing. |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
thanks a lot, 2015 and this still works. we used it for our hobby open source project: |
This comment has been minimized.
This comment has been minimized.
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(',');
}
}
}
} |
This comment has been minimized.
This comment has been minimized.
@righi +1 for your "Freeze row" response - worked for me |
This comment has been minimized.
This comment has been minimized.
Good morning. |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
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
|
This comment has been minimized.
This comment has been minimized.
Thank you so much! This has been so helpful! |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
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: |
This comment has been minimized.
This comment has been minimized.
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 |
This comment has been minimized.
This comment has been minimized.
Hey, thanks a lot for the script! |
This comment has been minimized.
This comment has been minimized.
Thanks ! |
This comment has been minimized.
This comment has been minimized.
@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. |
This comment has been minimized.
This comment has been minimized.
Good stuff.
|
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
Very nice. In function getObjects_() , I added
just before 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! |
This comment has been minimized.
This comment has been minimized.
Thank you mate, this works quite beautifully. Hats off to you, sir! |
This comment has been minimized.
This comment has been minimized.
@flowski, @Ziamor, @Michael-F-Ellis |
This comment has been minimized.
This comment has been minimized.
Found it on Youtube. Thanks for sharing this! |
This comment has been minimized.
This comment has been minimized.
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). |
This comment has been minimized.
This comment has been minimized.
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 posted |
This comment has been minimized.
This comment has been minimized.
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? |
This comment has been minimized.
This comment has been minimized.
@RonaCecilia as @righi mentions here: https://gist.github.com/pamelafox/1878143#gistcomment-778527 |
This comment has been minimized.
This comment has been minimized.
Hey, seems like UIApp is deprecated? Any update on this piece of code? |
This comment has been minimized.
This comment has been minimized.
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) |
This comment has been minimized.
This comment has been minimized.
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 |
This comment has been minimized.
This comment has been minimized.
Hi Guys |
This comment has been minimized.
This comment has been minimized.
Hey, has anybody come up with any solution for this UIApp deprecated problem yet. |
This comment has been minimized.
This comment has been minimized.
I just used the "Export Sheet Data" add-on with no problem. |
This comment has been minimized.
This comment has been minimized.
@mrclay Link pls? |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
Thank you SO much! This is a perfect quick solution |
This comment has been minimized.
This comment has been minimized.
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 |
This comment has been minimized.
This comment has been minimized.
@pamelafox you're an awesome programer for sure. Nice work. |
This comment has been minimized.
This comment has been minimized.
Thank you, works very well |
This comment has been minimized.
This comment has been minimized.
Good job. Thanks. Headers cannot be in Hebrew but content can be. |
This comment has been minimized.
This comment has been minimized.
Thank you so much! <3 |
This comment has been minimized.
This comment has been minimized.
It is not working for me.
can anybody else reproduce this? |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
Thank you, this is so helpful :) |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
I think I got that error before. Try freezing your header row. View > Freeze. That's what fixed it for me |
This comment has been minimized.
This comment has been minimized.
thank you, you saved me a lot of time, is still working! |
This comment has been minimized.
This comment has been minimized.
In my case, this error occurs because sheet.getFrozenRows () always returns 0.0. I changed line 139:
with sheet.getFrozenRows()+1 worked for me |
This comment has been minimized.
This comment has been minimized.
I got an error using this. But found this alternative library which works pretty well https://github.com/odwyersoftware/sheet2api-js#read-rows |
This comment has been minimized.
This comment has been minimized.
How would I edit the code to put data into a reverse order from Google Sheets? |
This comment has been minimized.
This comment has been minimized.
For example, this is my JSON object I exported. I would want the Supplemental Amount at the top and the empID at the bottom. |
This comment has been minimized.
This comment has been minimized.
Hi,
Notice the Anyone encounters this? I'm using my own version of this gist, which is a compilation of this thread. |
This comment has been minimized.
This comment has been minimized.
@flowsky do you have the problem I have with your piece of code ? |
This comment has been minimized.
This comment has been minimized.
@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. |
This comment has been minimized.
This comment has been minimized.
getColumnsData_() doesn't work... anyone have a fix? |
This comment has been minimized.
This comment has been minimized.
Saved me tons of time, huge thanks! |
This comment has been minimized.
This comment has been minimized.
Thanks, great script. I've noticed a little bug in normalizeHeader()_ function: an output for say "someCamelCaseString" is "somecamelcasestring". I fixed it by inserting |
This comment has been minimized.
This comment has been minimized.
@maggiechen thanks. when i use freeze, i can fix this issue |
This comment has been minimized.
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!