Created
June 10, 2020 10:11
-
-
Save bittu-choudhary/271b0e4aea7ba5ad8b3c45e116b40026 to your computer and use it in GitHub Desktop.
Google App Script - Export google sheet to JSON file and save it on google drive.
This file contains 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
/** | |
* onOpen adds Export JSON option in spreadsheet tool bar | |
* exportJSON prepares json from sheet, save it in same folder as of spreadsheet on google drive and display it in modal with download link | |
* turn on Drive API on script editor, Resources -> Advanced Google Services -> Drive API | |
*/ | |
function onOpen() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var entries = [{ | |
name : "Run", | |
functionName : "exportJSON" | |
}]; | |
sheet.addMenu("Export JSON", entries); | |
}; | |
// triggers parsing and displays results in a text area inside a custom modal window | |
function exportJSON() { | |
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet() | |
var activeSheet = SpreadsheetApp.getActiveSheet(); | |
var jsonData = JSON.stringify( makeJson(activeSheet.getDataRange().getValues()),null, 4) | |
var sheetDrivefile = DriveApp.getFileById(activeSpreadsheet.getId()); | |
var folders = sheetDrivefile.getParents().next().getId(); | |
var d = new Date(); | |
var n = d.getTime(); // add timestamp in file title | |
// https://github.com/googleapis/google-api-nodejs-client/issues/96 | |
var fileSets = { | |
title: 'your_json_file_name' + n + '.json', | |
mimeType: 'application/json', | |
parents: [{id: folders}] | |
}; | |
var blob = Utilities.newBlob(jsonData, "application/vnd.google-apps.script+json"); | |
var file = Drive.Files.insert(fileSets, blob); | |
var output = HtmlService.createHtmlOutput('<title> JSON export results - select all and copy!</title><a download="your_file_name_here'+n+'.json" target="_blank" href="'+ file.webContentLink +'">Download from here</a><pre class="prettyprint">' + jsonData + '</pre>'); | |
SpreadsheetApp.getActiveSpreadsheet().show(output); | |
}; | |
function makeJson(data) { | |
var obj = {}; | |
var result = []; | |
var headers = data[0]; | |
var cols = headers.length; | |
var row = []; | |
for (var i = 1, l = data.length; i < l; i++) | |
{ | |
// get a row to fill the object | |
row = data[i]; | |
// clear object | |
obj = {}; | |
for (var col = 0; col < cols; col++) | |
{ | |
// fill object with new values | |
obj[headers[col]] = row[col]; | |
} | |
// add object in a final result | |
result.push(obj); | |
} | |
// console.log(result) | |
return result; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment