Save Google Sheet as JSON

Published in: Google Apps Script - Google Sheets

The ExportSheetAsJSON method will convert the active sheet of a Google Spreadsheet to a JSON file and saves the file to your Google Drive. The header (first row) are used as item attributes in the JSON document.

/* From */

function exportSheetAsJSON() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var numCols = rows.getNumColumns();
  var values = rows.getValues();

  var output = "";
  output += "{\""+sheet.getName()+"\" : {\n";
  var header = values[0];
  for (var i = 1; i < numRows; i++) {
    if (i > 1) output += " , \n";
    var row = values[i];
    output += "\""+row[0]+"\" : {";
    for (var a = 1;a<numCols;a++){
      if (a > 1) output += " , ";
         output += "\""+header[a]+"\" : \""+row[a]+"\"";
    output += "}";
  output += "\n}}";

  DriveApp.createFile(sheet.getName()+".json", output, MimeType.PLAIN_TEXT);

Published in: Google Apps Script - Google Sheets

Looking for something? Find here!

Meet the Author

Web Geek, Google Developer Expert
Amit Agarwal

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India. He is the developer of Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

Get in touch