javascript - 从一张纸上取值并根据 ID 将它们保存在另一张纸上

Question

我尝试创建一个脚本，该脚本从一张表中获取值并根据 ID 将它们保存在另一张表中。不幸的是，脚本只保存第一个值。怎么了？

function script1() 
{

  // get first sheet 
  var Kwoty = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Kwoty");
  var FirstRow = 10;
  var LastRow = Kwoty.getLastRow();
  var RowRange = LastRow - FirstRow + 1;
  var WholeRange = Kwoty.getRange(FirstRow,6,RowRange,8);
  var AllValues = WholeRange.getValues();
  //Logger.log(AllValues); 
  
  //get 2 sheet
  var rozliczenie = Kwoty.getRange('G6').getValue();//Get sheet name
  var docelowysheet = SpreadsheetApp.openById('1_QgbLn9gKowDhCOwZ8lY9XBe5JmN107xhGDJ2kaGJIE').getSheetByName(rozliczenie);
  var FirstRow2 = 3;
  var LastRow2 = docelowysheet.getLastRow();
  var RowRange2 = LastRow2 - FirstRow2 + 1;
  var WholeRange2 = docelowysheet.getRange(FirstRow2,1,RowRange2,13);
  var AllValues2 = WholeRange2.getValues();
  //Logger.log(AllValues2); 
  
  
  for (var i=0;i<AllValues.length;i++){
     var CurrentRow = AllValues[i];
     var Id1 = CurrentRow[0]; //col with ID Sheet1 
     var kwota = CurrentRow[7]; //col with rate Sheet 1
    Logger.log(CurrentRow); 
     
  for (var i=0;i<AllValues2.length;i++){
     var CurrentRow2 = AllValues2[i];
     var Id2 = CurrentRow2[0]; //Col with ID Sheet2  
     var kwota2 = CurrentRow2[12]; //Col with rate Sheet2
   //Logger.log(Id2);  
     
  //Set Values   
    if (Id1 == Id2){
     var setRow2 = i + FirstRow2;
    docelowysheet.getRange(setRow2, 13).setValue(kwota);
    //Logger.log(CurrentRow[7]);
 
  } 
  }
  }
}

标签： javascriptgoogle-apps-script

Answer 1

最有可能在for循环中出现不一致，因为您在主循环和内部循环中使用相同的变量i作为迭代器，结果值被覆盖。将第二个循环变量更改为j（及其在内部循环中的引用），例如：

for (var i=0;i<AllValues.length;i++){
   var CurrentRow = AllValues[i];
   var Id1 = CurrentRow[0]; //col with ID Sheet1 
   var kwota = CurrentRow[7]; //col with rate Sheet 1
   Logger.log(CurrentRow); 
     
   for (var j=0;j<AllValues2.length;j++){
      var CurrentRow2 = AllValues2[j];
      var Id2 = CurrentRow2[0]; //Col with ID Sheet2  
      var kwota2 = CurrentRow2[12]; //Col with rate Sheet2
      //Logger.log(Id2);  
     
      //Set Values   
      if (Id1 == Id2){
        var setRow2 = j + FirstRow2;
        docelowysheet.getRange(setRow2, 13).setValue(kwota);
        //Logger.log(CurrentRow[7]);
      } 
}