Putting JSON data from Google sheets into an array

Question

My sheet: https://docs.google.com/spreadsheets/d/1pruXYXrbITR7CoQXZG1I1kyS4DahYBXmGMDEK2T6MpY/edit?usp=sharing

I'm using Google sheets JSON data to display on my website but I want to put some of the data into arrays so it doesn't repeat itself on the website.

For example, this is how it displays, but I would like to be able to just have the one heading for each group (zouk, zen ect) and then all the members to be under the one header. https://www.kentunion.co.uk/test1/

So I am trying to figure out some javascript that adds all the positions of one group into an array. Go from this:

"Zouk Society" : {
      "name" : "Zouk Society",
      "position" : "President",
      "member" : "Currently Vacant"
    },
    "Zouk Society" : {
      "name" : "Zouk Society",
      "position" : "Secretary",
      "member" : "Currently Vacant"
    },
    "Zouk Society" : {
      "name" : "Zouk Society",
      "position" : "Treasurer",
      "member" : "Currently Vacant"
    }

to this:

"Zouk Society" [{
      "position" : "President",
      "member" : "Currently Vacant"
    },
    {
      "position" : "Secretary",
      "member" : "Currently Vacant"
    },
    {
      "position" : "Treasurer",
      "member" : "Currently Vacant"
    }
]

I have tried the following JS but I am just getting an error from it:

 // ID of the Google Spreadsheet
 var spreadsheetID = "1pruXYXrbITR7CoQXZG1I1kyS4DahYBXmGMDEK2T6MpY";

 // Make sure it is public or set to Anyone with link can view 
 var url = "https://spreadsheets.google.com/feeds/list/1pruXYXrbITR7CoQXZG1I1kyS4DahYBXmGMDEK2T6MpY/od6/public/values?alt=json";
     $(document).ready(function () {
         $.getJSON(url, function(data) {

             var entry = data.feed.entry;

             $(entry).each(function(){
                  // Column names are name, age, etc.
                  $('.results').prepend('<h2>'+this.gsx$name.$t+'</h2><p>'+this.gsx$position.$t+'</p><p>'+this.gsx$member.$t+'</p>');
             });
         });

    var grouped = {};

        entry.forEach(function (a) {
          grouped[a.name] = grouped[a.name] || [];
                grouped[a.name].push({ position: a.position, member: a.member });
        });
        document.write('<pre>' + JSON.stringify(grouped, 0, 4) + '</pre>');

  });

This is the error that I get:

Uncaught ReferenceError: entry is not defined at HTMLDocument. (scripts.js:19)

Uncaught ReferenceError: entry is not defined at HTMLDocument.<anonymous> (scripts.js:19) — user2953989
– user2953989, Commented Apr 3, 2017 at 8:57

Matansh · Accepted Answer · 2017-04-03 09:20:07Z

2

You can't access entry outside of the callback scope.

Do your grouping inside the callback, like this:

// ID of the Google Spreadsheet
var spreadsheetID = "1pruXYXrbITR7CoQXZG1I1kyS4DahYBXmGMDEK2T6MpY";

// Make sure it is public or set to Anyone with link can view
var url = "https://spreadsheets.google.com/feeds/list/1pruXYXrbITR7CoQXZG1I1kyS4DahYBXmGMDEK2T6MpY/od6/public/values?alt=json";

$(document).ready(function () {
    $.getJSON(url, function(data) {
        var entry = data.feed.entry;

        $(entry).each(function(){
            // Column names are name, age, etc.
            $('.results').prepend('<h2>'+this.gsx$name.$t+'</h2><p>'+this.gsx$position.$t+'</p><p>'+this.gsx$member.$t+'</p>');
        });

        var grouped = {};

        entry.forEach(function (a) {
            grouped[a.gsx$name.$t] = grouped[a.gsx$name.$t] || [];
            grouped[a.gsx$name.$t].push({ position: a.gsx$position.$t, member: a.gsx$member.$t });
        });

        document.write('<pre>' + JSON.stringify(grouped, 0, 4) + '</pre>');
    });
});

edited Apr 3, 2017 at 9:20

answered Apr 3, 2017 at 9:00

Matansh

7824 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2953989 Over a year ago

this produces these results on my page: { "undefined": [ {}, {}, {}, {}, {}, {}, {}, {}, {}, {} ect

Matansh Over a year ago

Ok, I see. I made a change, look at the edit. Is that what you expected?

Matansh Over a year ago

Happy to help :)

easeccy · Accepted Answer · 2017-04-03 08:58:46Z

0

You cannot push data into object. You should change var grouped = {} into var grouped = []. Then you can push each element to this array of objects and access them with this syntax grouped[0].position.

I don't understand your spesific problem but "array of object" is the solution for your problem.

answered Apr 3, 2017 at 8:58

easeccy

5,1861 gold badge25 silver badges39 bronze badges

1 Comment

Matansh Over a year ago

He/she tries to push into the array at grouped[a.name], not to grouped. The array is initialized okay (grouped[a.name] = grouped[a.name] || [];).

Collectives™ on Stack Overflow

Putting JSON data from Google sheets into an array

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related