如何修改Google Apps Script代码仅提取部分列而非全部列

这是此前问题的扩展，需求是修改原有Google Apps Script代码，实现仅提取表格中的部分列而非全部列。

数据源 #

预期结果 #

原有代码 #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('sheetName');

  var [_, ...data] = sheet.getDataRange().getValues();
  const order = ['First', 'Second', 'Third']
  const sorted = data.sort((a, b) => {
    const i1 = order.indexOf(a[0]);
    const i2 = order.indexOf(b[0]);
    const len = data.length;
    return 1 * ((i1 > -1 ? i1 : len) - (i2 > -1 ? i2 : len));
  });
  const merged = sorted.reduce((o, [a, ...b]) => {
    if (o.temp != a) {
      o.merged.push(a);
      o.temp = a;
    }
    o.merged.push(`- ${b.join(",")}`);
    return o;
  }, { merged: [], temp: "" }).merged.join("\n");

  console.log(merged)
  var email = ''
  var Subject = "data";
  var Message = "Hello " + "\n" +
    "Here is the list of data:\n" + merged + "\n" + "\n";

  MailApp.sendEmail(email, Subject, Message);
}

修改后的代码 #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('sheetName');

  // 定义需要提取的列索引（从0开始计数，示例对应Type、Address、Phone列）
  const targetColumns = [0, 2, 3]; 
  
  var [_, ...data] = sheet.getDataRange().getValues();
  // 遍历每行，仅保留指定列的数据
  const filteredData = data.map(row => targetColumns.map(index => row[index]));
  
  const order = ['First', 'Second', 'Third']
  const sorted = filteredData.sort((a, b) => {
    const i1 = order.indexOf(a[0]);
    const i2 = order.indexOf(b[0]);
    const len = filteredData.length;
    return 1 * ((i1 > -1 ? i1 : len) - (i2 > -1 ? i2 : len));
  });
  
  const merged = sorted.reduce((o, [a, ...b]) => {
    if (o.temp != a) {
      o.merged.push(a);
      o.temp = a;
    }
    o.merged.push(`- ${b.join(",")}`);
    return o;
  }, { merged: [], temp: "" }).merged.join("\n");

  console.log(merged)
  var email = ''
  var Subject = "data";
  var Message = "Hello " + "\n" +
    "Here is the list of data:\n" + merged + "\n" + "\n";

  MailApp.sendEmail(email, Subject, Message);
}

关键修改说明 #

• 新增targetColumns数组：通过列索引（从0开始计数）指定需要保留的列，可根据实际需求调整数组内的数值
• 新增数据过滤逻辑：使用map遍历每行数据，仅提取targetColumns中指定的列内容
• 修正排序逻辑的长度引用：将原代码中data.length改为filteredData.length，保证排序逻辑适配过滤后的数据集

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