How to parse JSON response in Google Apps Script for Sheets

Other Questions Google Sheets
1

I’m working with Google Apps Script to get data from an API and put it into my spreadsheet. The API sends back JSON format data but I’m having trouble reading it properly.

When I try this code:

var jsonData = myResponseText;
Browser.msgBox(jsonData.records[0].value);

I get an error message that says:

records[0] is not defined.

What’s the right way to handle JSON data in Google Apps Script? Do I need to convert it first before I can access the properties?

2

Your API response is still a string - that’s the problem. When you get JSON data from an HTTP request in Google Apps Script, it comes back as text. You can’t access properties on a string like it’s an object. Use JSON.parse(myResponseText) to convert it into a JavaScript object first. Then you’ll be able to navigate through it with dot notation and brackets like you expect.

3

yep, u gotta use JSON.parse() first! like this: var jsonData = JSON.parse(myResponseText); after that, access properties easily. without parsing it’s just a string, no go.

4

Your response is a string, not a JavaScript object. I hit this exact same issue with Google Apps Script APIs. Easy fix - just parse the JSON string first. Change var jsonData = myResponseText; to var jsonData = JSON.parse(myResponseText); and you’re good to go. Once it’s parsed, you can access nested properties like normal. Just make sure your API returns valid JSON or JSON.parse() will crash. I’d wrap it in a try-catch block to handle errors.