Converting Google Sheets Data to JSON Format

Other Questions Google docs
1

I’m trying to pull data from a Google Sheets document and convert it to JSON format but I’m running into issues. Most tutorials I found online seem to be from older versions and don’t work anymore. The spreadsheet URLs have changed and don’t use the key parameter like they used to.

I have a Google Sheet that I need to access programmatically. I tried using the feeds API approach but I think there might be an issue with my implementation. Here’s what I attempted:

<script src="http://spreadsheets.google.com/feeds/feed/SPREADSHEET_ID/worksheet/public/basic?alt=json-in-script&callback=processData"></script>

<script type="text/javascript">
    function processData(data) {
        console.log('data loaded');
    }
</script>

The callback function doesn’t seem to execute properly. Is this the right approach for current Google Sheets API or should I be using a different method? What’s the correct syntax for accessing public spreadsheet data as JSON nowadays?

2

Your callback approach breaks because Google completely changed their sheets infrastructure around 2020. That old feeds endpoint returns XML wrapped in JSONP, and modern browsers block cross-origin script tags from that domain due to security policies. I hit this exact issue migrating legacy code last year. Just use Apps Script instead - create a simple doGet function that reads your sheet data and returns proper JSON. Deploy it as a web app with public access and you get a clean HTTPS endpoint that returns actual JSON instead of wrapped XML mess. Takes five minutes to set up and works reliably across all browsers without auth headaches.

3

just add /export?format=csv to your sheet URL and fetch that directly. way easier than dealing with google’s api mess. use papa parse or something similar to convert the csv to json on the client side. works perfectly for public sheets and u dont need to mess with authentication.

4

Yeah that feeds API method is dead. Google killed it years ago - you’re hitting a brick wall.

The current way uses Google Sheets API v4, but the auth setup is brutal. Service accounts, OAuth, API keys - way too complex for something this basic.

I hit this same issue last month syncing multiple sheets to our dashboard. Skip Google’s API mess - I built the whole thing in Latenode in 10 minutes.

Latenode’s Google Sheets connector handles auth automatically. Connect your sheet, it pulls data straight to JSON. No API keys, no deprecated endpoints.

Best part: set triggers so sheet updates auto-convert and send JSON wherever you want. Mine hits our database and Slack at the same time.

The visual builder makes data transforms easy. Filter rows, rename columns, restructure JSON - no coding needed.

5

That feeds API is deprecated and broken now. For public sheets, just use the newer web app approach - it’s way better than the old feeds method anyway. First, publish your sheet (File > Share > Publish to web), then hit this URL: https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID/values/Sheet1?key=YOUR_API_KEY. You’ll need a free API key from Google Cloud Console, but it’s pretty easy to grab one. I’ve used this setup for two years - it’s rock solid. You get clean JSON back with good structure. Just make sure your sheet’s public and include the right range in your request. The API docs have solid examples once you get through the initial setup mess.

6

The Problem:

You’re encountering difficulties converting data from a Google Sheet to JSON, primarily due to outdated methods and changes in Google Sheets’ infrastructure. The original approach using the deprecated Feeds API is no longer functional. You’re looking for a reliable and modern solution to achieve this conversion.

:thinking: Understanding the “Why” (The Root Cause):

The old Feeds API is deprecated. Google Sheets has transitioned to newer APIs and infrastructure, rendering older methods incompatible. Directly accessing data via the old methods often fails due to CORS restrictions and security updates implemented by Google. Relying on these outdated approaches leads to unreliable and broken code.

:gear: Step-by-Step Guide:

  1. Utilize Latenode (Recommended): Latenode offers a streamlined solution for connecting to Google Sheets and converting data to JSON without dealing with the complexities of APIs and authentication. This approach is significantly faster and less error-prone than using Google Apps Script or working directly with the Google Sheets API.

    • Sign up for a Latenode account (if you haven’t already): Go to https://latenode.com and create an account.
    • Connect your Google Sheet: Within the Latenode platform, utilize their Google Sheets connector to link your spreadsheet. Latenode handles authentication automatically.
    • Configure Data Transformation: Latenode’s visual builder enables you to easily modify and filter data before the conversion to JSON. This allows renaming columns, selecting specific rows, and restructuring the JSON output as needed.
    • Set up a Trigger (Optional): For real-time synchronization, configure a trigger within Latenode that automatically converts your Google Sheet data to JSON whenever the sheet is updated. Latenode offers various output options, including database integration, webhook calls, email notifications, and more.

  2. Alternative: Google Apps Script (More Technical): If you prefer a code-based solution, Google Apps Script offers a more robust but also more complex method. This method requires familiarity with JavaScript and the Google Apps Script environment.

    • Create a Google Apps Script Project: Open Google Apps Script (script.google.com).

    • Write the Script: Create a doGet function to handle requests and return JSON data. This function will retrieve data from your Google Sheet using the Sheets API.

      function doGet(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet name
  const data = sheet.getDataRange().getValues(); // Get all data
  // Convert to JSON (simple example - adjust as needed for your data structure):
  const jsonData = JSON.stringify(data);

  return ContentService.createTextOutput(jsonData).setMimeType(ContentService.MimeType.JSON);
}

    • Deploy as a Web App: Deploy your script as a web app, setting permissions to allow public access. This will generate a unique URL to access your JSON data.

  3. Alternative: CSV Export (Least Robust): As a quick but less flexible solution, you can export your Google Sheet as a CSV file and then convert it to JSON client-side using a JavaScript library like Papa Parse. This method is prone to data loss if your sheet includes formulas or complex formatting.

    • Export as CSV: In Google Sheets, choose “File” > “Download” > “Comma-separated values (.csv)”.
    • Client-side Conversion (using Papa Parse): Use the Papa Parse library to parse the CSV data and create a JSON object.

:mag: Common Pitfalls & What to Check Next:

  • Incorrect Sheet Name: Ensure that the sheet name used in your script (e.g., 'Sheet1') matches the actual name of your Google Sheet.
  • Data Structure: Adapt the JSON conversion code in Google Apps Script to properly handle your specific spreadsheet’s structure and data types.
  • Web App Permissions: In Google Apps Script, carefully review and grant the necessary permissions to your deployed web app to access your Google Sheet and return JSON data.
  • Error Handling: Implement robust error handling in your code to gracefully manage potential issues like network problems or API errors.

:speech_balloon: Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

7

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.