google-apps-script - GAS - 根据列表获取二维数组中的某些列

Question

我正在尝试通过按特定顺序获取某些列来使用 Google 工作表数据构建自定义数组。列/顺序在谷歌表上设置。下面的工作，但我想让第 11 行的回报更加动态。

function buildArray() {
  const s = SpreadsheetApp;
  const ss = s.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Test Sheet');
  const pasteSheet = ss.getSheetByName('Paste');
  const data = sheet.getRange(1,1,10,10).getValues(); //update range
  // const headers = pasteSheet.getRange(1,1,pasteSheet.getLastRow(),1).getValues().map(function(r){return r}).flat(Infinity); //list of headers needed in 1d array
  const colNeed = [0,9,5,6,4,7]; //array index for column numbers

  var customArray = data.map(function(r){ 
    return [r[0], r[9], r[5], r[6], r[4], r[7]]; //Would like to make this more dynamic
  })
  
  debugger;
  pasteSheet.getRange(1, 2, pasteSheet.getLastRow(), pasteSheet.getLastColumn()).clear();
  pasteSheet.getRange(1, 2, customArray.length, customArray[0].length).setValues(customArray);
  debugger;
}

我尝试用 r[colNeed.map(...)] 替换 r[0] 以及一个没有成功的 for 循环。

关于如何完成这项工作的任何想法。

这是最终工作表的屏幕截图。我刚刚在第一列 (A) 中复制/粘贴了标题名称，以确保正确的列被拉出/粘贴。

标签： google-apps-scriptgoogle-sheets

Answer 1

尝试使用循环先组合数据然后返回。还在customArray.

代码：

function buildArray() {
  const s = SpreadsheetApp;
  const ss = s.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Test Sheet');
  const pasteSheet = ss.getSheetByName('Paste');
  const data = sheet.getRange(1,1,10,10).getValues(); // update range
  // get headers as single array (excluding blank cells)
  // since last column of data can go beyond last row of column A
  // headers can include blank cells if there is already data present
  const headers = pasteSheet.getRange(1,1,pasteSheet.getLastRow(),1).getValues().flat().filter(Boolean); 
  // array index for column numbers
  const colNeed = [0,9,5,6,4,7]; 

  var customArray = data.map(function(r){ 
    var output = [];
    colNeed.forEach(function (col){
      output.push(r[col]);
    });
    return output;
  });

  // Add headers at the first element of customArray
  customArray.unshift(headers)

  debugger;
  pasteSheet.getRange(1, 2, pasteSheet.getLastRow(), pasteSheet.getLastColumn()).clear();
  pasteSheet.getRange(1, 2, customArray.length, customArray[0].length).setValues(customArray);
  debugger;
}

粘贴：

测试表：

执行后粘贴工作表buildArray：