Apps Script: How to apply RichTextValue() only to certain column?

Question

In Google Apps Script, I want to write my API JSON data into a Google Sheet. My original request was to simply write the data into Google Sheet for which the following function works well.

function original_func() {

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

  var data = [
    {"track":{"name":"Song1","album":"Album1","id":"ABC","url":"www.example.com/1"}},
    {"track":{"name":"Song2","album":"Album2","id":"XYZ","url":"www.example.com/2"}}
  ];
  var headers = [["id","name","album","url"]];
  var keys = ["track.id","track.name","track.album","track.url"];

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

  // Write to sheet
  for (let i=0; i < data.length; i++) {
    let arr = Array(keys.map(k => k.split('.').reduce((a, b) => a[b], data[i])));
    sheet.getRange(i+2,1,1,headers[0].length).setValues(arr);
  }

}

My requirements have now changed, wherein I want to use the url provided in the data and use the RichTextValue Class to add it to the id itself. I was able to achieve it using the following function,

function modified_func() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    
  var data = [
    {"track":{"name":"Song1","album":"Album1","id":"ABC","url":"www.example.com/1"}},
    {"track":{"name":"Song2","album":"Album2","id":"XYZ","url":"www.example.com/2"}}
  ];

  // Headers
  var columnNames = ["id", "name","album"];
  var columnCount = columnNames.length;

  // Set Headers
  sheet.getRange(1, 1, 1, columnCount).setValues([columnNames]);

  // Write the data to the sheet
  var rowData = data.map(function(obj) {
    return columnNames.map(function(key) {
      var value = obj.track[key];
      if (key === "id") {
        var richTextValue = SpreadsheetApp.newRichTextValue()
          .setText(value)
          .setLinkUrl(obj.track.url)
          .build();
        return richTextValue;
      }
      return value;
    });
  });
  var rowCount = rowData.length;

  // Loop through each cell and set value accordingly
  for (var i = 0; i < rowCount; i++) {
    for (var j = 0; j < columnCount; j++) {
      var cell = sheet.getRange(i + 2, j + 1);
      if (columnNames[j] === "id") {
        cell.setRichTextValue(rowData[i][j]);
      } else {
        cell.setValue(rowData[i][j]);
      }
    }
  }
}

Now, compared to the original method, the modified method does achieve my intended result, but the code doesn't look concise and is not optimized.

How do I optimize my modified_func to achieve the same functionality? Is there a better way?

Answer 1

Although I'm not sure whether I could correctly understand your expected result, how about the following modification?

Modified script:

function modified_func() {
  var data = [
    { "track": { "name": "Song1", "album": "Album1", "id": "ABC", "url": "www.example.com/1" } },
    { "track": { "name": "Song2", "album": "Album2", "id": "XYZ", "url": "www.example.com/2" } },
    { "track": { "name": 1, "album": 2, "id": 3, "url": "www.example.com/2" } }
  ];
  var columnNames = ["id", "name", "album"];

  // Create rich text values.
  var richTextValues = [
    columnNames.map(c => SpreadsheetApp.newRichTextValue().setText(c).build()),
    ...data.map(({ track }) =>
      columnNames.map(c => {
        var rt = SpreadsheetApp.newRichTextValue().setText(track[c]);
        if (c == "id") rt.setLinkUrl(track.url);
        return rt.build();
      })
    )
  ];

  // Put rich text values into cells.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(1, 1, richTextValues.length, richTextValues[0].length).setRichTextValues(richTextValues);
}

• When this script is run, the same result with your modified_func is obtained. And, in this case, the process cost might be able to be reduced a little from your modified_func.

Reference:

• setRichTextValues(values)