How to Convert Google Sheets CSV to JSON Using an Apps Script

Google Sheets is a powerful tool for storing and manipulating data, but sometimes you need your data in a different format, like JSON, for use in web applications or data analysis. In this guide, we’ll walk you through the process of using a custom Google Apps Script to convert your Google Sheets CSV to JSON format.

What you’ll need:

• A Google account
• Basic familiarity with Google Sheets
• Your data in a Google Sheets spreadsheet

Step 1: Prepare Your Google Sheet’s CSV Data

1. Open your Google Sheet containing the data you want to convert.
2. Ensure your data is well-structured with headers in the first row.
3. Freeze the first heading row (Click on view > Freeze > 1 row).
4. Make sure there are no empty columns between your data.

Step 2: Open the Apps Script Editor

1. In your Google Sheet, click on “Tools” in the top menu.
2. Select “Script editor” from the dropdown menu.
3. A new tab will open with the Google Apps Script editor.

Step 3: Copy and Paste the Script

1. Delete any existing code in the script editor.
2. Copy the entire script provided here below.

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');
}

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

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

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;
      }
      // Check if the cell contains a comma-separated string
      if (typeof cellData === 'string' && cellData.includes(',')) {
        // Split the string into an array and trim whitespace
        object[keys[j]] = cellData.split(',').map(function(item) {
          return item.trim();
        });
      } else {
        object[keys[j]] = cellData;
      }
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

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

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

function isCellEmpty_(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

function isAlnum_(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit_(char);
}

function isDigit_(char) {
  return char >= '0' && char <= '9';
}

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

3. Paste the above code into the editor.
4. Click on File > Save and give your project a name, e.g., “CSV to JSON Converter”.

Step 4: Customize the Script (Optional)

You can customize the script by modifying the default values:

var DEFAULT_FORMAT = FORMAT_PRETTY;
var DEFAULT_LANGUAGE = LANGUAGE_JS;
var DEFAULT_STRUCTURE = STRUCTURE_LIST;

• FORMAT_PRETTY: Outputs formatted JSON with indentation
• LANGUAGE_JS: Outputs standard JSON (use LANGUAGE_PYTHON for Python-compatible output)
• STRUCTURE_LIST: Outputs data as an array of objects (use STRUCTURE_HASH for object with ID keys)

Step 5: Run the Script

1. Close the script editor tab and return to your Google Sheet.
2. Refresh the page. You should now see a new menu item called “Export JSON”.
3. Click on “Export JSON” and choose either “Export JSON for this sheet” or “Export JSON for all sheets”.

Step 6: View and Copy the JSON Output

1. A dialog box with your data will appear in JSON format.
2. Review the output to ensure it’s correct.
3. Copy the JSON data for use in your project.

Advanced Usage:

1. Handling Comma-Separated Values: The script automatically converts comma-separated values in cells into JSON arrays. For example, a cell containing “red, green, blue” will become [“red”, “green”, “blue”] in the JSON output.
2. Exporting Multiple Sheets: If you choose “Export JSON for all sheets”, the script will create a JSON object where each key is a sheet name, and the value is the data from that sheet.
3. Custom Formatting: The script offers three formatting options: One-line, Multi-line, and Pretty. You can modify the makeJSON_ function to customize the output format further.

Troubleshooting:

1. If the “Export JSON” menu doesn’t appear, try refreshing the page or reopening the spreadsheet.
2. Ensure your sheet has headers in the first row; otherwise, the script may not work correctly.
3. Check for any error messages in the script editor’s logs (View > Logs) if the export fails.
4. Use a free online tool like JSONLint to validate your JSON code.

Best Practices

1. Always back up your data before running scripts on your spreadsheets.
2. Test the script on a copy of your data first, especially when working with large datasets.
3. Be mindful of Google’s quotas and limitations for Apps Script, especially for large spreadsheets.

Wrap Up

This powerful Google Apps Script allows you to easily convert your Google Sheets CSV data into JSON format, making it simple to use your data in various applications and programming environments. By following this guide, you can streamline your data conversion process and integrate your Google Sheets data with other systems more efficiently.