Apps Script: How to selectively apply split to an array?

Question

For the following Google Apps Script function, the External IDs column has the value [object Object] appearing for all rows. How to fix the issue so that I get the column values like the following?

Expected Result

| Track ID | Name  | Album  | Added At | External IDs       |
|----------|-------|--------|----------|--------------------|
| ABC      | Song1 | Album1 | today1   | {isrc=123,ean=456} |
| DEF      | Song2 | Album2 | today2   | {isrc=123}         |
| XYZ      | Song3 | Album3 | today3   | {isrc=123,upc=789} |

function func() {

  var data = [
    { "track": { "name": "Song1", "album": {"name":"Album1"}, "id": "ABC", "url": "www.example.com/1", "external_ids":{"isrc":"123","ean":"456"} }, "added_at":"today1" },
    { "track": { "name": "Song2", "album": {"name":"Album2"}, "id": "DEF", "url": "www.example.com/2", "external_ids":{"isrc":"123"} }, "added_at":"today2" },
    { "track": { "name": "Song3", "album": {"name":"Album3"}, "id": "XYZ", "url": "www.example.com/3", "external_ids":{"isrc":"123","upc":"789"} }, "added_at":"today3" }
  ];

  var headers = [["Track ID", "Name", "Album", "Added At","External IDs"]];
  var keys = ["track.id", "track.name", "track.album.name", "added_at", "track.external_ids"];

  var richTextValues = data.map((data) =>
    keys.map(c => {
      // var keys = c.split('.');
      var keys = (c !== 'track.external_ids') ? c.split('.') : ["track","external_ids"];
      var value = keys.reduce((a, b) => a[b], data);
      var rt = SpreadsheetApp.newRichTextValue().setText(value);
      if (keys[0] === "track" && keys[1] === "id") rt.setLinkUrl(data.track.url);
      return rt.build();
    })
  );

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Set headers
  sheet.getRange(1,1,1,headers[0].length).setValues(headers);
  sheet.getRange(2, 1, richTextValues.length, richTextValues[0].length).setRichTextValues(richTextValues);
  
}

Answer 1

I believe your goal is as follows.

• You want to directly show {isrc=123,ean=456} to the cell.

In this case, how about the following modification?

From:

var rt = SpreadsheetApp.newRichTextValue().setText(value);

To:

var rt = SpreadsheetApp.newRichTextValue().setText(typeof value == "object" ? JSON.stringify(value) : value);

Note:

• If you want {isrc=123,ean=456} instead of {"isrc":"123","ean":"456"}, please modify as follows.

    var rt = SpreadsheetApp.newRichTextValue().setText(typeof value == "object" ? JSON.stringify(value).replace(":", "=").replace(/"/g, "") : value);
  

• In this modification, the object is converted to the string type.