Extract data from JSON in Google Sheets

Question

Extract data from JSON in Google Sheets

UPDATE: entry.content.$t is actually not the correct field to access individual cells. The proper method is using entry.gsx$[cell column header]. Thank you for pointing out this mistake and assisting in finding a solution.

Initial inquiry:

I am currently attempting to analyze JSON data from a Google Spreadsheet. The issue at hand is that the entries field provides a string representing an entire row of the spreadsheet, but it seems malformed as an object. How have others managed to parse this data? Below is an example of what the content node resembles:

"content":
{
    "type"   :"text",
    "$t"     :"location: 780 Valencia St San Francisco, CA 94110,
               phonenumber: (555) 555-5555,
               website: http://www.780cafe.com,
               latitude: 37.760505,
               longitude: -122.421447"
},

Upon closer inspection, the $t field yields a complete string representing a row in the Google spreadsheet. Therefore, entry.content.$t delivers a string like:

location: 780 Valencia St San Francisco, CA 94110, phonenumber: (555) 555-5555...

Another complicating factor is that certain cells within the spreadsheet contain commas (such as addresses) that are not properly escaped or enclosed in quotes. This means that something similar to

jQuery.parseJSON(entry.content.$t)

or

eval('('+ entry.content.$t + ')')

results in an error. While regex might be an option, I am hopeful that there could be a more refined solution employed by others. Your assistance is greatly appreciated!

javascript json google-api google-sheets

Answer 1

Answer №1

The content within the $t attribute is not formatted as JSON. As per the documentation, text nodes are converted into $t attributes, which means the content may not be in a JSON format.

Instead of relying on the structure of the $t attribute, I recommend using a regular expression for parsing. However, please be advised that handling this data may require some advanced techniques. You may need to use assertions since simple methods like splitting on commas will not work. One approach could be searching for patterns like (\w+): to identify elements, and then capturing everything up to the next occurrence of (\w+): without consuming it entirely. It can be achieved with careful manipulation.

Answer 2

The content within the $t attribute is not formatted as JSON. As per the documentation, text nodes are converted into $t attributes, which means the content may not be in a JSON format.

Instead of relying on the structure of the $t attribute, I recommend using a regular expression for parsing. However, please be advised that handling this data may require some advanced techniques. You may need to use assertions since simple methods like splitting on commas will not work. One approach could be searching for patterns like (\w+): to identify elements, and then capturing everything up to the next occurrence of (\w+): without consuming it entirely. It can be achieved with careful manipulation.

Answer 3

Answer №2

Not long ago, I encountered the same issue.

If you need to extract data from $t, you can employ this Regular Expression:

/(\w+): (.+?(?=(?:, \w+:|$)))/mgi

This expression will provide key-value pairs.

Here's a JavaScript snippet to demonstrate:

    var currentPropertiesText = jsonEntry['content']['$t'];

    // var propsArray = currentPropertiesText.split(", ");
    var re = /(\w+): (.+?(?=(?:, \w+:|$)))/mgi;
    var propsArray = re.exec(currentPropertiesText)
    var props = {};

    while (propsArray != null) {

        var propName = propsArray[1];
        var propValue = propsArray[2];

        props[propName] = propValue;

        propsArray = re.exec(currentPropertiesText);
    }

This method should prove beneficial :-)

Answer 4

Not long ago, I encountered the same issue.

If you need to extract data from $t, you can employ this Regular Expression:

/(\w+): (.+?(?=(?:, \w+:|$)))/mgi

This expression will provide key-value pairs.

Here's a JavaScript snippet to demonstrate:

    var currentPropertiesText = jsonEntry['content']['$t'];

    // var propsArray = currentPropertiesText.split(", ");
    var re = /(\w+): (.+?(?=(?:, \w+:|$)))/mgi;
    var propsArray = re.exec(currentPropertiesText)
    var props = {};

    while (propsArray != null) {

        var propName = propsArray[1];
        var propValue = propsArray[2];

        props[propName] = propValue;

        propsArray = re.exec(currentPropertiesText);
    }

This method should prove beneficial :-)

Extract data from JSON in Google Sheets

Answer №1

Answer №2

Similar questions

Encountering an "Angularjs 1.2.x Injector:modulerrr" error, even after successfully incorporating ngRoute

React's setState is not reflecting the changes made to the reduced array

How to identify the position of an element while scrolling using JavaScript/jQuery

Transfer responsibilities of events to the canvas and then fetch the Element in the handler

How can I use XPATH to target a div after choosing a nested element?

Tracking user sessions using cookies, not relying on JavaScript like Google does

The error message "TypeError: Cannot read property 'then' of undefined" is commonly seen in Promises and Express 4.x

If I remove my project but still have it saved on my GitHub, do I need to reinstall all the dependencies or can I simply run npm install again?

Steps to reveal a div when a button is clicked within a v-for loop using VueJS

Error: Unable to access properties of an undefined value (trying to read 'type') within Redux Toolkit

Ways to quickly terminate a pipeline in a JavaScript transformation

Transforming numerous key-value pairs into JSON formatting: utilizing sed in conjunction with an awk for loop

The Flux Router in React Native

Tips for avoiding flickering in a background image when it is being changed

Having trouble with the ajax cache not working properly when trying to load an image

Unique Symbols and Characters in JavaScript

Information vanishes on mobile devices

I encountered an error of "Unexpected token '>'" while working with an

Tips on sorting an array within a map function

The conditional statements within the resize function may not always be triggered when the conditions are fulfilled