Google App脚本返回多行数组 [英] Google App Script returns array over multiple rows

问题描述

我是Google Apps脚本的新手，但我花了很多时间进行研究，但空无一人.基本上，我正在尝试复制SQL的交叉联接功能.我有2个表，一个12个月的日历表和一个3个客户的客户表.客户表是通过Google表单填充的，今后还会添加新客户.

I'm new to Google Apps Script but I've spent quite a bit of time researching and have come up empty. Basically I'm trying to replicate a cross-join functionality of SQL. I have 2 tables, a calendar table with 12 months, and a customer table with 3 customers. The customer table is populated from a Google form, and will have new customers added going forward.

对于我的报告，我需要每月提供客户数据.因此，我想将客户数据与日历交叉连接，以创建一个包含36行的数据集，其中每个客户都有12行，每月一个.

For my reporting, I need the customer data to be at a monthly grain. Therefore I want to cross-join the customer data with the calendar to create a dataset with 36 rows, where each customer has 12 rows, one per month.

基于我在stackoverflow上找到的其他代码，我已经接近了.现在的问题是，我的两个数据集最终显示在后续行中，这似乎与我处理数组的方式有关.我还尝试过使用push.apply和.concat获得完全相同的结果.

Based on other code I've found on stackoverflow, I've gotten close. My issue now is that my two data sets end up displaying on subsequent rows, which seems like an issue with how I'm handling my arrays. I have also tried using push.apply and .concat with the exact same results.

任何帮助将不胜感激！可以在此处

Any help would be appreciated! Workbook can be found here

此外，日历表不是严格必需的-我只是来自SQL背景，所以我的第一个直觉是制作日历表并创建交叉联接.如果您想出一种更好的方法来接受来自Google表单的单行客户数据并输出连续添加了几个月和几年的X行，请告诉我！

Also, the calendar table isn't strictly necessary - I just come from a SQL background so my first instinct was to make a calendar table and create a cross join. If you can think of a better way to accept a single row of customer data from a Google form and output X rows with consecutive months and years added, let me know!

免责声明::我知道在SQL数据库中可以更好地处理这种交叉联接方法，并且Google表格不是为此目的而设计的.在设计更好的长期解决方案时，这是一个临时解决方法.此解决方法的客户数据集总数可能少于100个客户，并且正在以大约每周1个客户的速度增长.

Disclaimer: I know this cross-join method would be better handled in a SQL database, and that Google Sheets isn't designed for this. This is a temporary workaround while a better long-term solution is being designed. The customer dataset will likely be less than 100 customers total for this workaround, and is growing at a rate of approximately 1 customer per week.

function crossJoin(tabl1, tabl2, header) {
 var output = [];
  var days = tabl1;
  var customer = tabl2;
  if(header) {output.push([header[0][1], header[0][0]])};
  for(var i = 1, iLen = customer.length; i < iLen; i++) {
//     output.push(days[i]);
    for(var j = 1, jLen = days.length; j < jLen; j++) { 
      output.push(days[j],customer[i]);

    }
  }
  return output;  
}

推荐答案

您可以使用像concat这样的数组方法来获得相同的结果.

You can use array methods like concat to achieve the same result.

function sqlCrossJoin(arr1, arr2) {
  arr1 = arr1 || [['jan', 1], ['feb', 2]];
  arr2 = arr2 || [
    ['Jane Doe', 1983, 'PortLand', 'Oregon'],
    ['John Smith', 1984, 'San Francisco', 'California'],
  ];
  var output = [];
  arr1.forEach(function(month) {
    arr2.forEach(function(customer) {
      output.push(month.concat(customer));
    });
  });
  return output;
/* Expected Output:
[ [ 'jan', 1, 'Jane Doe', 1983, 'PortLand', 'Oregon' ],
  [ 'jan', 1, 'John Smith', 1984, 'San Francisco', 'California' ],
  [ 'feb', 2, 'Jane Doe', 1983, 'PortLand', 'Oregon' ],
  [ 'feb', 2, 'John Smith', 1984, 'San Francisco', 'California' ] ]*/
}

参考文献:

• Array#concat
• Array#forEach

References:

• Array#concat
• Array#forEach

这篇关于Google App脚本返回多行数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！