如何在Google Apps Script中仅对指定列应用RichTextValue？

原需求是将API返回的JSON数据写入Google表格，并将id列内容绑定对应的url链接作为富文本。现有实现代码存在嵌套循环频繁调用服务、代码冗余的问题，以下是两种优化方案：

方案一：兼容深层JSON结构的通用优化版 #

该方案保留了原代码中通过键路径提取数据的逻辑，适合JSON结构层级不固定的场景：

function optimized_func() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  const 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"}}
  ];
  
  // 定义表头、数据提取路径、对应id的链接路径
  const headers = ["id", "name", "album"];
  const keyPaths = ["track.id", "track.name", "track.album"];
  const urlKeyPath = "track.url";

  // 写入表头
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // 提取普通数据（排除id列），批量写入表格
  const plainData = data.map(item => 
    keyPaths.map((path, idx) => headers[idx] !== "id" 
      ? path.split('.').reduce((obj, key) => obj[key], item) 
      : null
    )
  );
  if (plainData.length > 0) {
    sheet.getRange(2, 2, plainData.length, headers.length - 1).setValues(plainData.map(row => row.slice(1)));
  }

  // 批量生成并设置id列的富文本链接
  const richTextValues = data.map(item => [
    SpreadsheetApp.newRichTextValue()
      .setText(keyPaths[0].split('.').reduce((obj, key) => obj[key], item))
      .setLinkUrl(urlKeyPath.split('.').reduce((obj, key) => obj[key], item))
      .build()
  ]);
  sheet.getRange(2, 1, richTextValues.length, 1).setRichTextValues(richTextValues);
}

方案二：固定结构下的极简优化版 #

如果JSON数据结构固定为track层级，可直接简化数据提取逻辑，代码更简洁：

function optimized_func_simple() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  const 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"}}
  ];
  
  const headers = ["id", "name", "album"];
  
  // 写入表头
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // 批量写入name和album列
  const plainRows = data.map(item => [item.track.name, item.track.album]);
  sheet.getRange(2, 2, plainRows.length, 2).setValues(plainRows);

  // 批量设置id列的富文本链接
  const richTextRows = data.map(item => [
    SpreadsheetApp.newRichTextValue()
      .setText(item.track.id)
      .setLinkUrl(item.track.url)
      .build()
  ]);
  sheet.getRange(2, 1, richTextRows.length, 1).setRichTextValues(richTextRows);
}

优化核心点 #

• 减少服务调用次数：原代码通过嵌套循环逐单元格调用getRange，优化后改为批量写入普通数据+批量设置富文本列，大幅降低与Spreadsheet服务的交互次数（Google Apps Script中服务调用是主要性能瓶颈）
• 代码简洁性提升：使用数组map方法替代嵌套循环，逻辑更清晰；通用版保留键路径提取逻辑，扩展性更强
• 结构分层清晰：拆分表头写入、普通数据处理、富文本处理三个独立步骤，代码可读性更高

内容的提问来源于stack exchange，提问作者Shantanu