javascript Google Apps 脚本推送到数组问题

Google Apps Script Pushing to Array Issue

提问by Michael

I use Google Sheets to help track pay to our contractors. Sometimes the work order has two or more contractors and rather than using a new row for the same information just a different contractor and pay amount, we separate the contractor's names as well as their pay with a /. So for instance, in the contractor column we have:

我使用 Google 表格来帮助跟踪我们承包商的薪酬。有时，工作订单有两个或多个承包商，而不是对相同的信息使用新行，只是不同的承包商和支付金额，我们将承包商的姓名和他们的工资分开/。例如，在承包商栏中，我们有：

John Doe/Frank

And in the pay column we have:

在薪酬栏中，我们有：

468/65

The pay is respective, so John is owed $468 and Frank $65.

工资是各自的，因此欠约翰 468 美元和欠弗兰克 65 美元。

What I'm trying to do is setup a separate sheet for each of the contractor's so that they can see in almost real time the status of their own order's payments without jeopardizing other contractor's information. I'm using the Google Apps Scripts to transfer information and for the most part it is working correctly. The problem I'm having is when the script reaches a row where there are two contractors for an order. Strangely, John's name and pay are written to the correct corresponding array (johnDest), but then Frank's name and pay overwrites the John's previous entry for this row. I setup the function for writing to the arrays to be dependent on the individual name of the contractor I'm passing in, not the full value of the contractor's name cell. I start with arrays for each of the contractors the spreadsheet id's for copying:

我想要做的是为每个承包商设置一个单独的表格，以便他们几乎可以实时查看自己订单的付款状态，而不会危及其他承包商的信息。我正在使用 Google Apps Scripts 传输信息，并且在大多数情况下它工作正常。我遇到的问题是当脚本到达一个订单有两个承包商的行时。奇怪的是，John 的姓名和工资被写入了正确的对应数组 (johnDest)，但随后弗兰克的姓名和工资覆盖了约翰在该行的先前条目。我将写入数组的函数设置为依赖于我传入的承包商的个人名称，而不是承包商名称单元格的完整值。

var johnDest = [];
var john = "this-is-the-link-to-johns-sheet";
var frankDest = [];
var frank = "this-is-the-link-to-franks-sheet";

Then I enter a loop to add the row values to these arrays so that in the end I can write the array to their respective spreadsheets:

然后我进入一个循环，将行值添加到这些数组中，以便最后我可以将数组写入它们各自的电子表格：

function exportData() {

    var columnM = thisWorksheet.getRange(2, 1, thisWorksheet.getLastRow(), thisWorksheet.getLastColumn());
    var mValues = columnM.getValues();

    for(var i = 0; i < mValues.length; i++){
      var mName = mValues[i][0];
      if(mName.indexOf('/') > -1){ //If contractor name column contains a '/' split it.
        var names = mName.split('/');
        var pays = mValues[i][1];
        pays = pays.split('/');
        for(var g = 0; g < names.length; g++){ //For each name in split array, get name and corresponding pay to add to array.
           var cName = names[g];
           mValues[i][0] = names[g];
           mValues[i][1] = pays[g];
           Logger.log(cName); //To log the contractor's name that I am currently working with in the loop.
           switchcontractor(cName, mValues[i]);
        }
      }else{
        switchcontractor(mName, mValues[i]);
      }
    }
    copyData(john, johnDest); //Once loop is through and arrays are completed, copy data to respective sheets.
    copyData(frank, frankDest);
}

Here is the switchcontractorfunction:

这是switchcontractor函数：

function switchcontractor(cName, contValues){
  Logger.log(johnDest.length + ' ' + cName); //Log the length of johnDest and the current contractor in the loop.
  if(cName == 'John'){
    johnDest.push(contValues);
  }else if(cName == 'Frank'){
    frankDest.push(contValues);
    Logger.log(johnDest.length + ' ' + cName + ' ' + contValues);
  }
}

If I run the script as is, the Logger shows this information:

如果我按原样运行脚本，Logger 会显示以下信息：

[14-11-13 16:16:01:843 MST] John  //Current contractor I'm working with
[14-11-13 16:16:01:843 MST] 23 John  //Current length of johnDest before row information is pushed to it and contractor's name
[14-11-13 16:16:01:844 MST] 24 John John,468  //Updated length of johnDest, current contractor, and row information
[14-11-13 16:16:01:844 MST] Frank  //Current contractor
[14-11-13 16:16:01:844 MST] 24 Frank  //Current length of johnDest and contractor's name
[14-11-13 16:16:01:845 MST] 24 Frank Frank,65  //Length stays the same for johnDest, current contractor and row information 

To verify that the script is actually in the switchcase for Frank, I commented out the Logger line just after the frankDest.push(contValues);line above and I'm given this:

为了验证该脚本实际上是否在 Frank 的 switchcase 中，我在frankDest.push(contValues);上面的行之后注释掉了 Logger 行，我得到了这个：

[14-11-13 16:22:52:684 MST] John
[14-11-13 16:22:52:684 MST] 23 John
[14-11-13 16:22:52:684 MST] 24 John John,468
[14-11-13 16:22:52:685 MST] Frank
[14-11-13 16:22:52:685 MST] 24 Frank
//The Logger line that was commented out in the switchcase for Frank doesn't show, so obviously I'm in that case, right?

However, when Frank's name comes through the loop, it is written to the johnDest array as well as Frank's own array. The end result in John's sheet is this:

但是，当 Frank 的名字通过循环出现时，它会被写入 johnDest 数组以及 Frank 自己的数组。约翰工作表中的最终结果是这样的：

John     |   55   //This is an example of a previous row
Frank    |   65   //This is the row in question
John     |   125  //This is an example of a following row

This is what is on Frank's sheet:

这是弗兰克的工作表上的内容：

Frank    |    25  //This is an example of a previous row
Frank    |    65  //This is the row in question
Frank    |    15  //This is an example of a following row

I'm very confused as to why Frank's information is written to John's array AND his own for the row. Any help is greatly appreciated. Thank you!

我很困惑为什么弗兰克的信息被写入约翰的数组和他自己的行。任何帮助是极大的赞赏。谢谢！

采纳答案by KRR

I see the issue is when you are adding the values to the function switchcontractor(cName, contValues), when the if condition satisfies. Made the below changes to the code and it works.

我看到问题是当您将值添加到函数 switchcontractor(cName, contValues) 时，如果条件满足。对代码进行了以下更改，并且可以正常工作。

function exportData() {

 var sheet = SpreadsheetApp.getActiveSheet();
 var columnM = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
 var mValues = columnM.getValues();
 for(var i = 0; i < mValues.length; i++){
  var mName = mValues[i][0];
  if(mName.indexOf('/') > -1){ //If contractor name column contains a '/' split it.
    var names = mName.split('/');
    var pays = mValues[i][1];
    pays = pays.split('/');

    for(var g = 0; g < names.length; g++){ //For each name in split array, get name and corresponding pay to add to array.
       var cName = names[g];
      var addValues = [];
       addValues[0] = names[g];
       addValues[1] = pays[g];
//           Logger.log(cName); //To log the contractor's name that I am currently working with in the loop.
       switchcontractor(cName, addValues);
    }
  }else{
    switchcontractor(mName, mValues[i]);
  }
}
 Logger.log('John values : ' + johnDest);
 Logger.log('Frank values: ' + frankDest);
 copyData(john, johnDest); //Once loop is through and arrays are completed, copy data to respective sheets.
 copyData(frank, frankDest);
}

function switchcontractor(cName, contValues){
 Logger.log(cName + 'value is ' + contValues); 
 if(cName.search('John Doe') > -1){
   johnDest.push(contValues);
 }else if(cName == 'Frank'){
   frankDest.push(contValues);

 }

}

}

Hope that helps!

希望有帮助！