Блог

Я пытаюсь написать скрипт Google apps, похожий на SUMIF в Google Sheets. Моя цель — суммировать все значения в часах столбца для всех строк, которые содержат определенный идентификатор (id) в течение определенного периода (период столбца), которые помечены как продуктивные (productive = 1), а значение единицы столбца для конкретной строки отличается от строки, указанной ниже. По какой-то причине это не работает. Я новичок в javascript и не могу понять причину, по которой он не работает. Я попытался определить функцию sum, а затем вызвать ее для каждого из значений в столбце id (около 7000 строк).

 //Productive hours  
//=if($A2<> "", SUMIFS(hours,hrmid,$AC2, productive,1,period,$A2)-SUMIFS(hours,hrmid,$AC2, productive,1,period,$A2, units,"Revision Stand-by"), "")
  function sumPrdHrs (item) {
    var hoursToSum = 0;
    var summedHrs = [];
    var hours = ytdSheet.getRange(2,18, ytdSheet.getLastRow()).getValues();
    var productive = ytdSheet.getRange(2,33, ytdSheet.getLastRow()).getValues();
    var units = ytdSheet.getRange(2, 9, ytdSheet.getLastRow()).getValues();
    var id= ytdSheet.getRange(2,29, ytdSheet.getLastRow()).getValues();
    var period = ytdSheet.getRange(2,1, ytdSheet.getLastRow()).getValues();
    //var date = ytdSheet.getRange(2,1, ytdSheet.getLastRow()).getValues();
    
    for (var i=0; i < hours.length; i  ) {
      if ((id[i][0] == item) amp;amp; (productive[i][0] == 1) amp;amp; (period[i][0] == period[i 1][0]) amp;amp; (units[i][0] != "Revision Stand-by")) {
      hoursToSum  = hours[i][0];
      summedHrs.push(hoursToSum);
      }      
    }
    return sumPrdHrs;
    console.log(summedHrs);
}
  
function productiveHrs () {

  var id= ytdSheet.getRange(2,29, ytdSheet.getLastRow()).getValues();
  var sumHours = [];
  
  id.forEach(function(item,i) {
                
     var summedHours = sumPrdHrs (item);    
    
    //sumHours.push(summedHours);
    
    //Logger.log(summedHours);
    
   // ytdSheet.getRange(2,31,hrmId.length,1).setValues(summedHours);
                
                });
  
 
 

}

Я вижу несколько сомнительных строк в вашем коде, я рассмотрю их ниже в сравнении с кодом, который я создал и протестировал, исходя из того, чего вы хотите достичь, и который все еще имеет смысл.

 function sumPrdHrs (item) {
    var hoursToSum = 0; 

    // Removed summedHrs array. Based on your code structure, you want to return the sum of productive hours. 
    // I don't see any use of creating an array when you can already sum it up directly.

    var hours = ytdSheet.getRange(2,18, ytdSheet.getLastRow()).getValues();
    var productive = ytdSheet.getRange(2,33, ytdSheet.getLastRow()).getValues();
    var units = ytdSheet.getRange(2, 9, ytdSheet.getLastRow()).getValues();
    var id= ytdSheet.getRange(2,29, ytdSheet.getLastRow()).getValues();
    var period = ytdSheet.getRange(2,1, ytdSheet.getLastRow()).getValues();
    
    for (var i=0; i < hours.length; i  ) {
      // In the conditions below, I don't get why you want to compare current period to the period below. 
      // It always makes the last item with the same period invalid since there is no last date. 
      // But will include it since it might be important to you
      // You also have another condition mentioned but wasn't added initially so I skipped it for now. But feel free to add it on your code.
      if ((id[i][0] == item) amp;amp; (productive[i][0] == 1) amp;amp; (period[i][0] == period[i 1][0]) amp;amp; (units[i][0] != "Revision Stand-by")) {
        hoursToSum  = hours[i][0]; 
        // Total hours is calculated per item, that's all you need to return below.
      }      
    }
    return hoursToSum;
}

// Based on what you want, this will sum all hours per item that follows the conditions above.
function productiveHrs () {
  // This will transform the 2d array into 1d array 
  var id = ytdSheet.getRange(2,29, ytdSheet.getLastRow()).getValues().flat();
  // We want to loop only the unique items, removing duplicates
  id = id.filter((item, i, ar) => ar.indexOf(item) === i);
  
  var sumHours = [];

  id.forEach(function(item,i) {
    // Skip blank/invalid items
    if(item) {
      var summedHours = sumPrdHrs(item);    
      sumHours.push(summedHours);      
    }
  });

  Logger.log(sumHours);
}
 

Это мой примерный лист

Это результат

Видите, что общее количество элементов b , которое должно быть 14, равно 10, верно? Это из-за условия периода, которое проверяет период ниже. Это то, что я обсуждал выше.

Что вы можете сделать вместо этого с условием «период против периода ниже», так это получить уникальные даты для каждого элемента, а затем суммировать их по дате для каждого элемента.

Это должно быть лучшим способом проверки периода.

 // Added time for per item summation of hours
function sumPrdHrs (item, time) {
    var hoursToSum = 0;
    var hours = ytdSheet.getRange(2,18, ytdSheet.getLastRow()).getValues();
    var productive = ytdSheet.getRange(2,33, ytdSheet.getLastRow()).getValues();
    var units = ytdSheet.getRange(2, 9, ytdSheet.getLastRow()).getValues();
    var id = ytdSheet.getRange(2,29, ytdSheet.getLastRow()).getValues();
    var period = ytdSheet.getRange(2,1, ytdSheet.getLastRow()).getValues();
    
    for (var i=0; i < hours.length; i  ) {
      // Compare period[i][0] to unique period fetched from productiveHrs() instead.
      if ((id[i][0] == item) amp;amp; (productive[i][0] == 1) amp;amp; (period[i][0] == time) amp;amp; (units[i][0] != "Revision Stand-by")) {
        hoursToSum  = hours[i][0];
      }      
    }
    return hoursToSum;
}

function productiveHrs () {
  // Get unique ids and periods
  var id = ytdSheet.getRange(2,29, ytdSheet.getLastRow()).getValues().flat();
  var period = ytdSheet.getRange(2,1, ytdSheet.getLastRow()).getValues().flat();
  id = id.filter((item, i, ar) => ar.indexOf(item) === i);
  period = period.filter((item, i, ar) => ar.indexOf(item) === i);
  
  var sumHours = [];

  // Traverse all combination of id and period
  id.forEach(function(item,i) {
    if(item) {
      period.forEach(function(time,j) {
        if(time) {
          // Pass period to sum hours under item per period
          var summedHours = sumPrdHrs(item, time);    
          // Only push data when where is summedHours
          if (summedHours) { 
            sumHours.push([item, time, summedHours]);  
          }    
        }  
      });
    }
  });

  Logger.log(sumHours);
}
 

Это образец данных:

Это результат:

Как вы можете видеть, более красивый и читаемый вывод и условия, которые имели бы смысл.

Если у вас есть какие-либо разъяснения и дополнительные вопросы, не стесняйтесь комментировать ниже.