Parse Google Sheets JSON Export into JavaScript JSON Object

I work for Akkio, where I'm building out a no-code predictive AI platform. If you're looking to harness the power of AI without needing a data scientist, give us a try!

The Problem

Google lets you conveniently export public sheets into a JSON object using a URL like the following:

https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/gviz/tq?tqx=out:json&sheet={SHEET_NAME}"

However, there's a bit of extra data included in the parse that messes up the JSON parse:

• At the beginning: /O_o/\ngoogle.visualization.Query.setResponse(
• At the end: );

The Solution

We trim the beginning and end bits off and get a perfectly usable JSON object:

const data = await fetch(URL);
let text = await data.text();
text = text.replaceAll(
  "/*O_o*/\ngoogle.visualization.Query.setResponse(",
  ""
);
text = JSON.parse(text.substring(0, text.length - 2));