Parsing array of objects to range only does not parse all objects

Question

I need to parse an array of objects to a Google sheet.

I am using code from here

What I get is

affiliations	corresponding	current_organization_id	first_name	last_name	orcid	raw_affiliation	researcher_id	state_code	corresponding	current_organization_id	first_name	last_name	orcid	raw_affiliation	researcher_id
{city=My City, state_code=, city_id=123456.0, state=, country_code=XX, name=University of Bob, country=Country1, id=d.11111.0, raw_affiliation=This That, AA}		d.11111.0	Joe	Aaaa	xxx-yyy-vvv-0000	This That, AA	id.18738465.55		TRUE	d.11111.0	Joe	Aaaa	xxx-yyy-vvv-0000	This That, AA	id.18738465.55
{city_id=7891011.0, raw_affiliation=Something Something, BB, country=Country2, state_code=, name=University of Sally, city=Other Cite, state=, country_code=TT, id=d.22222.0}			Sally	Zzzz		Something Something, BB					Sally	Zzzz		Something Something, BB

What I need

city	city_id	country	country_code	id	name	raw_affiliation	state	state_code	corresponding	current_organization_id	first_name	last_name	orcid	raw_affiliation	researcher_id
My City	123456	Country1	XX	d.11111.0	University of Bob	This That, AA				d.11111.0	Joe	Aaaa	xxx-yyy-vvv-0000	This That, AA	id.18738465.55
Other Cite	7891011	Country2	TT	d.22222.0	University of Sally	Something Something, BB					Sally	Zzzz		Something Something, BB

affiliations is not parsing Everything after corresponding parses correctly

How to parse the affiliations array?

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

function main() {
var data = [{'affiliations': [{'city': 'My City', 'city_id': 123456, 'country': 'Country1', 
                               'country_code': 'XX', 'id': 'd.11111.0', 'name': 'University of Bob',
                               'raw_affiliation': 'This That, AA', 'state': '', 'state_code': ''}], 
                               'corresponding': '', 'current_organization_id': 'd.11111.0',
                               'first_name': 'Joe', 'last_name': 'Aaaa', 'orcid': ['xxx-yyy-vvv-0000'], 
                               'raw_affiliation': ['This That, AA'], 'researcher_id': 'id.18738465.55'}, 
  
  {'affiliations': [{'city': 'Other Cite', 'city_id': 7891011, 'country': 'Country2', 'country_code': 'TT', 
                     'id': 'd.22222.0', 'name': 'University of Sally',
                     'raw_affiliation': 'Something Something, BB', 'state': '', 'state_code': ''}], 
                     'corresponding': '', 'current_organization_id': '', 'first_name': 'Sally', 
                     'last_name': 'Zzzz', 'orcid': [], 'raw_affiliation': ['Something Something, BB'], 
                     'researcher_id': ''}];

  var sheet = SpreadsheetApp.getActiveSheet();

  var result = pattern1(data);
  var result = pattern2(data);

  sheet.getRange('a1').offset(0, 0, result.length, result[0].length).setValues(result);
}

function pattern1(data){
  var ar = [];
  for (var i in data){
    for (var key in data[i]){
      ar.push([key, data[i][key]]);
    }
  }
  return ar;
}

function pattern2(data){
  var ar = [];
  var keys = [];
  var values = [];
  for (var i in data){
    for (var key in data[i]){
      if (i == 0) keys.push(key);
      values.push(data[i][key]);
    }
    if (i == 0){
      ar.push(keys);
      keys = [];
    }
    ar.push(values);
    values = [];
  }
  return ar;
}

The word affiliations is plural and the data structure is an array, designed to hold more than one thing. So what do you want your output to look like if a given row of data has more than one affiliation? — James
– James, Commented Dec 11, 2023 at 18:26

DuuEyn · Accepted Answer · 2023-12-11 19:09:16Z

Workaround:

The way I've approached this is to create two loops; on that retrieves the object keys of each element of the data array and another one that retrieves its values. I've added an if condition to retrieve the keys and values of an object's value if it is another object, as is the case for affiliations

Do try this script:

function main() {
  var data = [{
    'affiliations': [{
      'city': 'My City', 'city_id': 123456, 'country': 'Country1',
      'country_code': 'XX', 'id': 'd.11111.0', 'name': 'University of Bob',
      'raw_affiliation': 'This That, AA', 'state': '', 'state_code': ''
    }],
    'corresponding': '', 'current_organization_id': 'd.11111.0',
    'first_name': 'Joe', 'last_name': 'Aaaa', 'orcid': ['xxx-yyy-vvv-0000'],
    'raw_affiliation': ['This That, AA'], 'researcher_id': 'id.18738465.55'
  },

  {
    'affiliations': [{
      'city': 'Other Cite', 'city_id': 7891011, 'country': 'Country2', 'country_code': 'TT',
      'id': 'd.22222.0', 'name': 'University of Sally',
      'raw_affiliation': 'Something Something, BB', 'state': '', 'state_code': ''
    }],
    'corresponding': '', 'current_organization_id': '', 'first_name': 'Sally',
    'last_name': 'Zzzz', 'orcid': [], 'raw_affiliation': ['Something Something, BB'],
    'researcher_id': ''
  }];

  var sheet = SpreadsheetApp.getActiveSheet();

  var result = pattern1(data);

  sheet.getRange('a1').offset(0, 0, result.length, result[0].length).setValues(result);
}

function pattern1(data) {
  var ar = [];
  for (let header in data[0]) {
    //This for loop gets the object keys
    if (typeof data[0][header][0] == 'object') {
      //If the object's value is another object, its keys are pushed to the array
      ar.push(Object.keys(data[0][header][0]))
    } else {
      ar[0].push(header);
    }
  }

  for (let i = 0; i < data.length; i++) {
    //This for loop gets the object values
    for (let header in data[i]) {
      if (typeof data[i][header][0] == 'object') {
        //If the object's value is another object, its values are pushed to the array
        ar.push(Object.values(data[i][header][0]))
      } else {
        ar[i + 1].push(data[i][header]);
      }
    }
  }
  return ar;
}

Do note that the script above works on the assumption that both objects on the data array have the same keys.

Collectives™ on Stack Overflow

Parsing array of objects to range only does not parse all objects

1 Answer 1

Workaround:

References:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Workaround:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related