Convert CSV to JSON for Free Inside Google Sheets

Introduction

Your analytics tool wants JSON and your export is a CSV. Copying values between the two by hand is slow, and a single missing quote invalidates the whole payload.

A spreadsheet can do the conversion without a separate website. The same sheet that holds the records can assemble them into JSON, and the reverse direction writes JSON back into rows.

This guide covers both directions with native functions and a short Apps Script function. Nothing leaves your Google account, and the result can be checked line by line.

How CSV and JSON Line Up

The two formats describe the same table in different shapes. A CSV file is a grid of rows and columns, and a JSON array of objects is a list of records where each field carries its own name.

• Header row: becomes the set of keys, so id, name and city are the field names of every record.
• Data row: becomes one object, with the cell in each column assigned to the key above it.
• Whole table: becomes an array that holds one object per data row.

That mapping is the entire conversion. Everything else is bookkeeping about types, delimiters and escaping.

Types matter more than the mapping suggests. A value written by a formula is always text, while a value read from a cell keeps the type the sheet gives it. Numbers stay numbers and dates serialise as ISO 8601 strings, so the same table can produce JSON with quoted numbers or unquoted numbers depending on the method.

Build JSON with a Formula

For a handful of columns, a formula can assemble one object per row. Put the headers in row 1 and the first record in row 2, then place this formula beside the data:

=LET(q, CHAR(34), "{" & TEXTJOIN(",", TRUE, ARRAYFORMULA(q&A1:C1&q&":"&q&A2:C2&q)) & "}")

The LET names the double-quote character once, so the formula does not need a thicket of doubled quotes. ARRAYFORMULA builds a key and value pair for each column, TEXTJOIN separates the pairs with commas and the outer braces make it an object.

Fill the formula down for every record, join the resulting lines with commas and wrap them in square brackets to form the array. That produces valid JSON for clean data.

The formula is useful for a quick payload, a test fixture or a single lookup. It is a poor choice for a working pipeline, because it copies cell text as it stands. A name such as O'Brien, a product description containing a comma or an address with a line break will break the output, and the failure is invisible until a parser rejects the file.

Convert a Whole Sheet with Apps Script

Apps Script is the reliable route. Open Extensions, then Apps Script, and replace the placeholder code with this function:

function sheetToJson() {
  const values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  const headers = values.shift().map(String);
  const records = values.map(function (row) {
    const record = {};
    headers.forEach(function (header, index) {
      record[header] = row[index];
    });
    return record;
  });
  return JSON.stringify(records, null, 2);
}

The function reads the whole used range, removes the header row and turns every remaining row into an object. The third argument to JSON.stringify adds indentation for review. Call it from a cell as =sheetToJson() and the JSON appears in that cell.

For compact output, drop the final arguments and return JSON.stringify(records). For newline-delimited JSON, where each record sits on its own line, map each object through JSON.stringify and join the results with the line-feed character supplied by String.fromCharCode(10).

The parsing side has an official helper as well. Utilities.parseCsv turns CSV text into a two-dimensional array, with an optional second argument for a tab, semicolon or pipe. That makes it possible to paste raw CSV into a cell and convert it without importing it as a sheet first.

function csvTextToJson(csvText) {
  const rows = Utilities.parseCsv(csvText);
  const headers = rows.shift().map(String);
  const records = rows.map(function (row) {
    const record = {};
    headers.forEach(function (header, index) {
      record[header] = row[index] === undefined ? "" : row[index];
    });
    return record;
  });
  return JSON.stringify(records, null, 2);
}

Both functions rely on documented Apps Script services, and the JSON object itself is part of the V8 runtime that Apps Script uses. There is no library to install and no permission beyond access to the spreadsheet.

Convert JSON Back to CSV

The reverse direction is a parse, a key collection and a write. Paste the JSON into a cell, then run this pair of functions from the editor or a custom menu:

function jsonToRows(jsonText) {
  const records = JSON.parse(jsonText);
  const headers = [];
  records.forEach(function (record) {
    Object.keys(record).forEach(function (key) {
      if (headers.indexOf(key) === -1) {
        headers.push(key);
      }
    });
  });
  const rows = records.map(function (record) {
    return headers.map(function (header) {
      return record[header] === undefined ? "" : record[header];
    });
  });
  return [headers].concat(rows);
}
function writeJsonToSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = jsonToRows(sheet.getRange("B1").getValue());
  const target = SpreadsheetApp.getActiveSpreadsheet().insertSheet("From JSON");
  target.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

Records do not need identical keys. The header list grows as new fields appear, and any record missing a field receives an empty cell rather than shifting the other values out of place.

The write function cannot run as a custom function, because a custom function may only return values to the cells it is called from. Run it from the Apps Script editor, or add it to a menu so a colleague can run it without opening the code.

Once the rows are on a tab, choose File, then Download, then Comma-separated values to get a CSV file. Sheets applies the quoting rules during the export, so commas and quotes inside a value survive the round trip.

A Worked Example

Suppose a tab holds this short export:

The Apps Script route returns this JSON, with the IDs kept as numbers because the cells contain numbers:

[
  { "id": 1, "name": "Alice", "city": "Nairobi" },
  { "id": 2, "name": "Bob", "city": "Kampala" }
]

The formula route returns the same shape with every value quoted, because concatenation turns each cell into text. Both results are valid JSON, but an API that expects a numeric ID will treat them differently.

Send the JSON through the reverse function and the header row returns as id, name and city, with one record below it. A round trip through the script preserves the values, not the original cell formatting.

Handle Quotes and Commas

Quoting is where hand-built JSON fails. JSON requires a double quote inside a string to be escaped with a backslash, and the same applies to a backslash itself. Utilities.parseCsv understands quoted fields, so a value such as Acme, Ltd stays in one column, and JSON.stringify escapes it correctly on the way out.

A value containing a line break is written as an escape sequence rather than a raw line break, which keeps one record on one line. A formula that concatenates the cell text instead writes the raw character and produces a file that most parsers reject.

The practical rule is simple. Use a formula for a one-off object you can inspect by eye, and use Apps Script for anything that leaves the sheet.

Troubleshooting Common Issues

• Duplicate headers: an object cannot hold the same key twice, so a second column called email overwrites the first. Rename the headers before converting.
• Wrong delimiter: a semicolon or tab export parsed as comma collapses into one column. Pass the delimiter as the second argument to Utilities.parseCsv, or import the file with the correct separator first.
• Dates shifted by a day: a date cell serialises in UTC, so a date entered at midnight in a timezone ahead of UTC can appear as the previous day. Store dates as text when the exact date matters.
• Leading zeros lost: the sheet parses 001 as the number 1 before the script sees it. Format the column as plain text, or quote the value, when the identifier must keep its zeros.
• Custom function error: a conversion that takes longer than 30 seconds returns an error in the cell. Run the same function from the editor for a large table.

Where the Free Method Stops

The free route covers most spreadsheet-sized jobs, but it has firm edges. A single cell holds up to 50,000 characters, which caps the size of a JSON payload returned by a custom function. A custom function must return within 30 seconds, and a script run from the editor or a menu may run for six minutes.

The script also needs authorisation the first time it runs, because it reads and writes the spreadsheet. That prompt is normal, and it is visible to anyone who opens the file and tries to run the menu item.

Finally, the conversion is per file. When the work becomes a recurring export from several systems, the bottleneck moves from conversion to cleaning and matching, which is a different problem.

Beyond One Conversion

Converting a clean table is predictable. The messy part is what arrives before and after it: names spelled three ways, duplicate customers, trailing spaces and identifiers that only nearly match.

That work belongs in the sheet next to the data. Flookup standardises inconsistent text, the FLOOKUP function matches approximate keys and the workflow templates bundle the steps for user lists and reconciliation.

When a single payload outgrows a cell, or the export runs to hundreds of thousands of rows, import the file in ranges and convert them in passes.

Conversion Should Not Need a Separate Tool

CSV to JSON is a format change, not a project. With the headers in row 1, a formula handles a small object and a twenty-line script handles the whole sheet. The reverse direction is a parse and a write, with the CSV export left to Sheets.

Use the formula when you can see the whole payload, and use the script when the data is real. The escaping is the part that decides which one you need.

When the records also need cleaning and matching before they are useful, Flookup Data Wrangler runs those steps inside the same sheet.

Frequently Asked Questions