-
-
Save vbarhatov/942fe3559dfc98694b424fbdd03cd367 to your computer and use it in GitHub Desktop.
Google App Script - Spreadsheet JSON export
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Adds a custom menu to the active spreadsheet, containing a single menu item | |
| * for invoking the exportJSON() function specified above. | |
| * The onOpen() function, when defined, is automatically invoked whenever the | |
| * spreadsheet is opened. | |
| * For more information on using the Spreadsheet API, see | |
| * https://developers.google.com/apps-script/service_spreadsheet | |
| */ | |
| function onOpen() { | |
| var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
| var entries = [{ | |
| name : "Do it", | |
| functionName : "exportJSON" | |
| }]; | |
| sheet.addMenu("Export JSON", entries); | |
| }; | |
| // triggers parsing and displays results in a text area inside a custom modal window | |
| function exportJSON() { | |
| var app = UiApp.createApplication().setTitle('JSON export results - select all and copy!'); | |
| var textArea = app.createTextArea(); | |
| textArea.setValue(makeJson(SpreadsheetApp.getActiveSheet().getDataRange())); | |
| app.add(textArea); | |
| textArea.setSize("100%", "100%"); | |
| SpreadsheetApp.getActiveSpreadsheet().show(app); | |
| }; | |
| function makeJson(dataRange) { | |
| var charSep = '"'; | |
| var result = "", thisName = "", thisData = ""; | |
| var frozenRows = SpreadsheetApp.getActiveSheet().getFrozenRows(); | |
| var dataRangeArray = dataRange.getValues(); | |
| var dataWidth = dataRange.getWidth(); | |
| var dataHeight = dataRange.getHeight() - frozenRows; | |
| // range of names - we assume that the last frozen row is the list of properties | |
| var nameRangeArray = dataRangeArray[frozenRows - 1]; | |
| // open JSON object - if there's a extra frozen row on the top wrap results into that as property (only supports one for now) | |
| result += frozenRows > 1 ? '{"' + dataRangeArray[frozenRows - 2][0] + '": [' : '['; | |
| for (var h = 0; h < dataHeight ; ++h) { | |
| result += '{'; | |
| for (var i = 0; i < dataWidth; ++i) { | |
| thisName = nameRangeArray[i]; | |
| thisData = dataRangeArray[h + frozenRows][i]; | |
| // add name | |
| result += charSep + thisName + charSep + ':' | |
| // add data | |
| result += charSep + serializeValue(thisData) + charSep + ', '; | |
| } | |
| //remove last comma and spaces | |
| result = result.slice(0,-2); | |
| result += '},\n'; | |
| } | |
| //remove last comma and line break | |
| result = result.slice(0,-2); | |
| // close object | |
| result += frozenRows > 1 ? ']}' : ']'; | |
| return result; | |
| } | |
| function serializeValue(str) { | |
| if (typeof str === "string" && str !== "") | |
| return str.replace(/\n/g, "\\n").replace(/\r/g, "\\n").replace(/\t/g, "\\t").replace(/\"/g, "\\\""); | |
| if (Object.prototype.toString.call(str) === '[object Date]') | |
| return Utilities.formatDate(str, 'GMT', "yyyy-MM-dd'T'HH:mm:ss'Z'"); | |
| return str; | |
| } |
Author
Author
Serialize dates to ISO
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
String escaping is fixed