当前位置： 文江博客话题详情

如何在 Google Apps 脚本中的 2D 数组地图中从一行返回两行

发布于 2025-01-16 22:23:47 字数 5373 浏览 3 评论 0原文

我正在尝试获取从电子表格收到的人口普查数据并将其转换为二维数组以对其进行清理。不幸的是，并非我收到的所有工作表的格式都相同，因此我试图使脚本能够使用多种格式。当每人一行时，我可以使用它，但是当我为每个家属获取一行，为每个员工获取多行时，我的脚本不会返回所需的结果。

工作示例输入：

[
   ["LstNm",    "1st Nm",   "Email",    "EE-SSN",   "Mem-SSN","Relate"]
   ["Smith",    "John",     "[email protected]",  11, 11, "E"]
   ["Wang",     "Jacob",    "[email protected]",  11, 22, "C"]
   ["Da Silva", "Jingle",   "[email protected]",  13, 13, "E"]
   ["Martin",   "Heimer",   "[email protected]",  12, 12, "E"]
   ["Muller",   "Schmidt",  "[email protected]",  12, 22, "S"]
]

问题输入。这是我想返回两行的地方，一行用于员工，一行用于家属。但在我的代码目前的状态下，它不起作用：

[
   ["LstNm",    "1stNm",    "Email", "EE-SSN", "Mem-SSN", "Relate", "DepLstNm", "Dep1stNm", "Dep-SSN",  "Dep-Relate"]
   ["Smith",    "John",     "[email protected]",  11, 11, "E",      "Wang",   "Jacob",    21, "C"]
   ["Da Silva", "Jingle",   "[email protected]",  13, 13, "E",    ,   ,   ,   ]
   ["Martin",   "Heimer",   "[email protected]",  12, 12, "E",      "Muller", "Schmidt",  22, "S"]
]

两种情况下的预期最终结果都是每人一行，其中 EE-SSN 将员工与家属联系起来：

[
   ["Email",    "1stNm",    "LstNm",    "EE-SSN",   "Mem-SSN",  "Relate"]
   ["[email protected]",  "John",     "Smith",    11, 11, "E"]
   ["[email protected]",  "Jacob",    "Wang",     11, 22, "C"]
   ["[email protected]",  "Jingle",   "Da Silva", 13, 13, "E"]
   ["[email protected]",  "Heimer",   "Martin",   12, 12, "E"]
   ["[email protected]",  "Schmidt",  "Muller",   12, 22, "S"]
]

最后，这是代码我正在使用：

function getData(origData, index) {
  var depCheck = index[6]+index[7]+index[8]+index[9]
  if (depCheck < 0) {
    var workData = origData.map(row => ([
      row[index[2]],                      // EMAIL
      row[index[1]],                      // FIRST NAME
      row[index[0]],                      // LAST  NAME
      row[index[3]],                      // SUBSCIBER SSN
      row[index[4]],                      // MEMBER SSN
      row[index[5]],                      // RELATIONSHIP
    ]));
  } else {
    var arr = [[]];
    var workData = origData.flatMap(row => {
      arr.push([
          row[index[2]],                      // EMAIL
          row[index[1]],                      // FIRST NAME
          row[index[0]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[4]],                      // MEMBER SSN
          row[index[5]],                      // RELATIONSHIP
        ]);
      if (typeof row[index[6]] !== "undefined" && typeof row[index[8]] !== "undefined" ) {
        arr.push([
          row[index[2]],                      // EMAIL
          row[index[7]],                      // FIRST NAME
          row[index[6]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[8]],                      // MEMBER SSN
          row[index[9]],                      // RELATIONSHIP
        ]);
      }
      return arr;
    });
  }
  return workData;
}

function main () {
  const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');

  const origData = originalSheet.getRange("A6:F11").getValues();
  console.log(origData);
  var index = [0,1,2,3,4,5,-1,-1,-1,-1];
  var data = getData(origData, index);
  console.log("Single Rows:\n" + data);

  const origData2 = originalSheet.getRange("A13:J16").getValues();
  console.log(origData2);
  var index2 = [0,1,2,3,4,5,6,7,8,9];
  var data2 = getData(origData2, index2);
  console.log("Double Rows:\n" + data2);

  var numRows = data.length;
  var numCols = data[0].length;

  var numRows2 = data2.length;
  var numCols2 = data2[0].length;

  originalSheet.getRange(18,1,numRows,numCols).setValues(data);
  originalSheet.getRange(18,7,numRows2,numCols2).setValues(data2);
}

编辑进一步查看代码，我意识到通过在第二个地图中返回一个二维数组，该数组返回arr[tempEE, tempDep] 实际上正在成为3D 阵列。因此，我将地图切换为 flatMap （参见下面的答案），它给出了所需的 2D 数组。

原文

I'm trying to take census data I receive from a spreadsheet and convert it to a 2D array to do my cleaning to it. Unfortunately, not all sheets I receive are formatted the same, and thus I am trying to make the script be able to work with multiple formats. I have it working when there is one line per person, but when I get one line per dependent and multiple per employee my script does not return the desired results.

Working Example inputs:

[
   ["LstNm",    "1st Nm",   "Email",    "EE-SSN",   "Mem-SSN","Relate"]
   ["Smith",    "John",     "[email protected]",  11, 11, "E"]
   ["Wang",     "Jacob",    "[email protected]",  11, 22, "C"]
   ["Da Silva", "Jingle",   "[email protected]",  13, 13, "E"]
   ["Martin",   "Heimer",   "[email protected]",  12, 12, "E"]
   ["Muller",   "Schmidt",  "[email protected]",  12, 22, "S"]
]

Problem Inputs. This is where I want to return two rows, one for the employee, and one for the dependent. But in my code's present state, it is not working:

[
   ["LstNm",    "1stNm",    "Email", "EE-SSN", "Mem-SSN", "Relate", "DepLstNm", "Dep1stNm", "Dep-SSN",  "Dep-Relate"]
   ["Smith",    "John",     "[email protected]",  11, 11, "E",      "Wang",   "Jacob",    21, "C"]
   ["Da Silva", "Jingle",   "[email protected]",  13, 13, "E",    ,   ,   ,   ]
   ["Martin",   "Heimer",   "[email protected]",  12, 12, "E",      "Muller", "Schmidt",  22, "S"]
]

The expected end result in both cases would be One row per person with the EE-SSN linking the employee to the dependent:

[
   ["Email",    "1stNm",    "LstNm",    "EE-SSN",   "Mem-SSN",  "Relate"]
   ["[email protected]",  "John",     "Smith",    11, 11, "E"]
   ["[email protected]",  "Jacob",    "Wang",     11, 22, "C"]
   ["[email protected]",  "Jingle",   "Da Silva", 13, 13, "E"]
   ["[email protected]",  "Heimer",   "Martin",   12, 12, "E"]
   ["[email protected]",  "Schmidt",  "Muller",   12, 22, "S"]
]

Finally, here is the code I'm using:

function getData(origData, index) {
  var depCheck = index[6]+index[7]+index[8]+index[9]
  if (depCheck < 0) {
    var workData = origData.map(row => ([
      row[index[2]],                      // EMAIL
      row[index[1]],                      // FIRST NAME
      row[index[0]],                      // LAST  NAME
      row[index[3]],                      // SUBSCIBER SSN
      row[index[4]],                      // MEMBER SSN
      row[index[5]],                      // RELATIONSHIP
    ]));
  } else {
    var arr = [[]];
    var workData = origData.flatMap(row => {
      arr.push([
          row[index[2]],                      // EMAIL
          row[index[1]],                      // FIRST NAME
          row[index[0]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[4]],                      // MEMBER SSN
          row[index[5]],                      // RELATIONSHIP
        ]);
      if (typeof row[index[6]] !== "undefined" && typeof row[index[8]] !== "undefined" ) {
        arr.push([
          row[index[2]],                      // EMAIL
          row[index[7]],                      // FIRST NAME
          row[index[6]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[8]],                      // MEMBER SSN
          row[index[9]],                      // RELATIONSHIP
        ]);
      }
      return arr;
    });
  }
  return workData;
}

function main () {
  const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');

  const origData = originalSheet.getRange("A6:F11").getValues();
  console.log(origData);
  var index = [0,1,2,3,4,5,-1,-1,-1,-1];
  var data = getData(origData, index);
  console.log("Single Rows:\n" + data);

  const origData2 = originalSheet.getRange("A13:J16").getValues();
  console.log(origData2);
  var index2 = [0,1,2,3,4,5,6,7,8,9];
  var data2 = getData(origData2, index2);
  console.log("Double Rows:\n" + data2);

  var numRows = data.length;
  var numCols = data[0].length;

  var numRows2 = data2.length;
  var numCols2 = data2[0].length;

  originalSheet.getRange(18,1,numRows,numCols).setValues(data);
  originalSheet.getRange(18,7,numRows2,numCols2).setValues(data2);
}

EDIT Reviewing the code further, I realized that by returning a 2D array in the second map which returns arr[tempEE, tempDep] is actually becoming a 3D array. As such, I switched the map to flatMap (see answer below), which gives the desired 2D array.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

记忆で 2025-01-23 22:23:47

说明

以下是如何使用数组函数将员工/受抚养人行分成 2 个单独的行的修订示例。此示例假设每个员工只有一名受抚养人。

脚本

function testCensus() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Census");
    let values = sheet.getDataRange().getValues();
    values.forEach( row => Logger.log(row) );
    let header = values.shift();  // remove the header row
    let results = [[].concat(header[2],header[1],header[0],header.slice(3,6))];
    function sort(row) {
      let temp = [].concat(row[2],row[1],row[0],row.slice(3,6));
      results.push(temp);
      if( row[6] !== '' ) {
        temp = [].concat(row[2],row[7],row[6],row[3],row.slice(-2));
        results.push(temp);
      }
    }
    values.forEach( row => sort(row) );
    results.forEach( row => Logger.log(row) );
  }
  catch(err) {
    console.log("Error in testCensus - "+err)
  }
}

Console.log

10:04:57 AM Notice  Execution started
10:04:58 AM Info    [LstNm, 1stNm, Email, EE-SSN, Mem-SSN, Relate, DepLstNm, Dep1stNm, Dep-SSN, Dep-Relate]
10:04:58 AM Info    [Smith, John, [email protected], 11.0, 11.0, E, Wang, Jacob, 21.0, C]
10:04:58 AM Info    [Da Silva, Jingle, [email protected], 13.0, 13.0, E, , , , ]
10:04:58 AM Info    [Martin, Heimer, [email protected], 12.0, 12.0, E, Muller, Schmidt, 22.0, S]

10:04:58 AM Info    [Email, 1stNm, LstNm, EE-SSN, Mem-SSN, Relate]
10:04:58 AM Info    [[email protected], John, Smith, 11.0, 11.0, E]
10:04:58 AM Info    [[email protected], Jacob, Wang, 11.0, 21.0, C]
10:04:58 AM Info    [[email protected], Jingle, Da Silva, 13.0, 13.0, E]
10:04:58 AM Info    [[email protected], Heimer, Martin, 12.0, 12.0, E]
10:04:58 AM Info    [[email protected], Schmidt, Muller, 12.0, 22.0, S]
10:04:58 AM Notice  Execution completed

参考

Description

Here is a revised example of how to use array functions to break the employee/dependant row into 2 seperate rows . This example assumes there is only one dependant per employee.

Script

function testCensus() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Census");
    let values = sheet.getDataRange().getValues();
    values.forEach( row => Logger.log(row) );
    let header = values.shift();  // remove the header row
    let results = [[].concat(header[2],header[1],header[0],header.slice(3,6))];
    function sort(row) {
      let temp = [].concat(row[2],row[1],row[0],row.slice(3,6));
      results.push(temp);
      if( row[6] !== '' ) {
        temp = [].concat(row[2],row[7],row[6],row[3],row.slice(-2));
        results.push(temp);
      }
    }
    values.forEach( row => sort(row) );
    results.forEach( row => Logger.log(row) );
  }
  catch(err) {
    console.log("Error in testCensus - "+err)
  }
}

Console.log

10:04:57 AM Notice  Execution started
10:04:58 AM Info    [LstNm, 1stNm, Email, EE-SSN, Mem-SSN, Relate, DepLstNm, Dep1stNm, Dep-SSN, Dep-Relate]
10:04:58 AM Info    [Smith, John, [email protected], 11.0, 11.0, E, Wang, Jacob, 21.0, C]
10:04:58 AM Info    [Da Silva, Jingle, [email protected], 13.0, 13.0, E, , , , ]
10:04:58 AM Info    [Martin, Heimer, [email protected], 12.0, 12.0, E, Muller, Schmidt, 22.0, S]

10:04:58 AM Info    [Email, 1stNm, LstNm, EE-SSN, Mem-SSN, Relate]
10:04:58 AM Info    [[email protected], John, Smith, 11.0, 11.0, E]
10:04:58 AM Info    [[email protected], Jacob, Wang, 11.0, 21.0, C]
10:04:58 AM Info    [[email protected], Jingle, Da Silva, 13.0, 13.0, E]
10:04:58 AM Info    [[email protected], Heimer, Martin, 12.0, 12.0, E]
10:04:58 AM Info    [[email protected], Schmidt, Muller, 12.0, 22.0, S]
10:04:58 AM Notice  Execution completed

Reference

回复 原文
独﹏钓一江月 2025-01-23 22:23:47

实际上，将第二个地图更改为 flatMap 确实可以解决问题，这是我使用的最终代码：

function getData(origData, index) {  
  var depCheck = index[6]+index[7]+index[8]+index[9]
  if (depCheck < 0) {
    var workData = origData.map(row => ([ // The data is simply returned as a 1D arr here
      row[index[2]],                      // EMAIL
      row[index[1]],                      // FIRST NAME
      row[index[0]],                      // LAST  NAME
      row[index[3]],                      // SUBSCIBER SSN
      row[index[4]],                      // MEMBER SSN
      row[index[5]],                      // RELATIONSHIP
    ]));
  } else {
    var arr = [];
    /** Use flatMap here as the returned arr[tempEE, tempDep] is a 2D array being added to an 
      * array workData[arr], flatMap takes the result and flattens it by one level*/
    var workData = origData.flatMap(row => {
      var tempEE = [
          row[index[2]],                      // EMAIL
          row[index[1]],                      // FIRST NAME
          row[index[0]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[4]],                      // MEMBER SSN
          row[index[5]],                      // RELATIONSHIP
        ];
      if (typeof row[index[6]] !== "undefined" && typeof row[index[8]] !== "undefined" ) {
        console.log("Dependent - " + row[index[6]])
        var tempDep = [
          row[index[2]],                      // EMAIL
          row[index[7]],                      // FIRST NAME
          row[index[6]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[8]],                      // MEMBER SSN
          row[index[9]],                      // RELATIONSHIP
        ];
      }
      var arr = [tempEE, tempDep];
      return arr;
    });
  }
  return workData;
}

function main () {
  const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');

  const origData = originalSheet.getRange("A6:F11").getValues();
  console.log(origData);
  var index = [0,1,2,3,4,5,-1,-1,-1,-1];
  var data = getData(origData, index);
  console.log("Single Rows:\n" + data);

  const origData2 = originalSheet.getRange("A13:J16").getValues();
  console.log(origData2);
  var index2 = [0,1,2,3,4,5,6,7,8,9];
  var data2 = getData(origData2, index2);
  console.log("Double Rows:\n" + data2);

  var numRows = data.length;
  var numCols = data[0].length;

  var numRows2 = data2.length;
  var numCols2 = data2[0].length;

  originalSheet.getRange(18,1,numRows,numCols).setValues(data);
  originalSheet.getRange(18,7,numRows2,numCols2).setValues(data2);
}

参考： https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/flatMap

Actually, changing the second map to flatMap does fix the problem, here is the final code I used:

function getData(origData, index) {  
  var depCheck = index[6]+index[7]+index[8]+index[9]
  if (depCheck < 0) {
    var workData = origData.map(row => ([ // The data is simply returned as a 1D arr here
      row[index[2]],                      // EMAIL
      row[index[1]],                      // FIRST NAME
      row[index[0]],                      // LAST  NAME
      row[index[3]],                      // SUBSCIBER SSN
      row[index[4]],                      // MEMBER SSN
      row[index[5]],                      // RELATIONSHIP
    ]));
  } else {
    var arr = [];
    /** Use flatMap here as the returned arr[tempEE, tempDep] is a 2D array being added to an 
      * array workData[arr], flatMap takes the result and flattens it by one level*/
    var workData = origData.flatMap(row => {
      var tempEE = [
          row[index[2]],                      // EMAIL
          row[index[1]],                      // FIRST NAME
          row[index[0]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[4]],                      // MEMBER SSN
          row[index[5]],                      // RELATIONSHIP
        ];
      if (typeof row[index[6]] !== "undefined" && typeof row[index[8]] !== "undefined" ) {
        console.log("Dependent - " + row[index[6]])
        var tempDep = [
          row[index[2]],                      // EMAIL
          row[index[7]],                      // FIRST NAME
          row[index[6]],                      // LAST  NAME
          row[index[3]],                      // SUBSCIBER SSN
          row[index[8]],                      // MEMBER SSN
          row[index[9]],                      // RELATIONSHIP
        ];
      }
      var arr = [tempEE, tempDep];
      return arr;
    });
  }
  return workData;
}

function main () {
  const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');

  const origData = originalSheet.getRange("A6:F11").getValues();
  console.log(origData);
  var index = [0,1,2,3,4,5,-1,-1,-1,-1];
  var data = getData(origData, index);
  console.log("Single Rows:\n" + data);

  const origData2 = originalSheet.getRange("A13:J16").getValues();
  console.log(origData2);
  var index2 = [0,1,2,3,4,5,6,7,8,9];
  var data2 = getData(origData2, index2);
  console.log("Double Rows:\n" + data2);

  var numRows = data.length;
  var numCols = data[0].length;

  var numRows2 = data2.length;
  var numCols2 = data2[0].length;

  originalSheet.getRange(18,1,numRows,numCols).setValues(data);
  originalSheet.getRange(18,7,numRows2,numCols2).setValues(data2);
}

Reference: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/flatMap

回复 原文
~没有更多了~

关于作者

め七分饶幸

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文