Introduction

Ever wanted to get your Google Sheets data in JSON format that’s easy to use in web apps or other projects, but didn’t want to bother with setting up an API key? Well, here’s some good news: you can get your data in JSON format directly from Google Sheets, and you don’t need to write complex code or use an API key. All it takes is a simple tweak to the URL of your published Google Sheet, and you’re good to go!

Let’s walk through how you can do this, step by step.

What is JSON and Why Should You Care?

Before we jump into how to fetch the data, let’s quickly go over why JSON is so useful. JSON stands for JavaScript Object Notation. It’s a lightweight, easy-to-read format perfect for exchanging data between different systems. When you retrieve your Google Sheets data in JSON format, it becomes easy to feed it into websites, apps, or other programs. Plus, it’s straightforward to work with.

This method allows you to bypass the hassle of setting up API keys and just use a URL to pull your data in JSON format. Here’s how to do it:

1. Publish Your Google Sheet

First things first, you need to make your Google Sheet accessible to the public. Don’t worry—it’s easy and you can control exactly what’s shared. Here’s how:

• Open your Google Sheet.
• Go to the File menu and select Publish to the web.
• In the dialog box that appears, choose whether to publish the entire document or just one sheet.
• Make sure you select Web Page as the format (this is the default).
• Click Start publishing.

Once you do this, Google will provide you with a link to access the data.

2. Modify the URL to Get Google sheet data in JSON Data

Now comes the magic part! After publishing your sheet, you can modify the URL to get the data in JSON format.

• Take the link you got after publishing the sheet. It’ll look something like this:

https://docs.google.com/spreadsheets/d/{spreadsheetId}/pubhtml

• To get the data in JSON format, change the part that says /pubhtml to /gviz/tq?tqx=out:json.
• So, the new URL will look like this:

https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq?tqx=out:json

3. View Your JSON Data

Now, when you paste that modified link into your browser, you’ll see the data from your sheet in JSON format. The data will be wrapped in a script that looks like this:

google.visualization.Query.setResponse({
   "status": "ok",
   "table": {
      "cols": [
         { "label": "Name", "type": "string" },
         { "label": "Age", "type": "number" }
      ],
      "rows": [
         { "c": [ { "v": "John" }, { "v": 30 } ] },
         { "c": [ { "v": "Jane" }, { "v": 25 } ] }
      ]
   }
});

While it might look a little confusing at first, the key data you’ll want to work with is inside the rows array, which contains the actual data from your sheet.

4. How to Use the JSON Data

Once you have your data in JSON format, you can use it in your app, website, or wherever you need it. For example, if you’re using JavaScript, you can access the data like this:

let jsonData = {
   "table": {
      "rows": [
         { "c": [ { "v": "John" }, { "v": 30 } ] },
         { "c": [ { "v": "Jane" }, { "v": 25 } ] }
      ]
   }
};

// Loop through the rows and display the data
jsonData.table.rows.forEach(row => {
   console.log('Name: ' + row.c[0].v + ', Age: ' + row.c[1].v);
});

This way, you can dynamically use your Google Sheets data on your website or in your app without needing to download or manually update the file.

Suggested for you:

• How to Create a REST API with Node.js and Express
• How to Host React App on Vercel?

Why Is This Method So Great?

• No API Key Needed: The best part? You don’t need to set up an API key or worry about authentication.
• Quick Setup: Just publish your sheet and modify the URL—simple and fast.
• Instant Access: Once you’ve modified the URL, your data is ready to use, and any changes made to the sheet are reflected instantly.

Things to Keep in Mind

• Public Data: When you publish a Google Sheet, it becomes publicly accessible. Be sure to only share data that’s okay to be viewed by others.
• Simple Use Case: This method works best for basic needs. If you need to perform more advanced operations with your data (like authentication or real-time updates), using the Google Sheets API might be a better option.

Conclusion

Getting your Google Sheets data in JSON format doesn’t have to be a complicated process. You can easily pull your data into applications, websites, or whatever project you’re working on by simply publishing your sheet to the web and tweaking the URL. No API key is required—just a quick and simple URL change, and you’re all set!

So, if you need to access Google Sheets data in a clean, easy-to-use format, give this method a try today!