Adding two time duration values

Question

I have an app script code that adds values in two columns and display the result in a third column.i.e column B value(time duration) is added to column D(time duration) and the result is displayed in column E. But my code shows error what to do

function adder()
 {
 var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet5');
 var lastRow = sheet.getLastRow(); 
 var cell = sheet.getRange('B1:B'+lastRow).getValues();
 var data = sheet.getRange('D1:D'+lastRow).getValues();
  for (var i = 0; i < lastRow; i++){
     var value1 = cell[i][0];
     var value2 = data[i][0];
     var range = sheet.getRange('E'+(i+1)).setFormula(value1+value2);
     var formattedDate = Utilities.formatDate(new Date(range.getValue()), 'GMT', 'dd/MM/yyyy 
                         HH:mm:ss');
     }
     sheet.getRange('E' + (i+1).toString()).setValue(formattedDate);
   }  

the screen shoot of error has been uploaded to

      function dateAdd() 
         {
         var ss = 
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet5');
        var range = ss.getRange('E23').setFormula('D23+B23');
        var formattedDate = Utilities.formatDate(range.getValue(), 'GMT', 
                                        'dd/MM/yyyy HH:mm:ss');
         }   

The above code is working for a single row, I want to set it in a Loop.

Answer 1

Try this:

Reformat the duration columns to "#.##########" just before reading the data and use Range.getDisplayValue()s and then format back to "[h]:mm:ss" you don't run into the string versus number problem.

function runThree() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet5');
  var rgB=sh.getRange(2,2,sh.getLastRow()-1,1);
  var rgD=sh.getRange(2,4,sh.getLastRow()-1,1);
  var rgE=sh.getRange(2,5,sh.getLastRow()-1,1);
  rgE.clearContent();
  rgB.setNumberFormat("#.##########");//increased precision improves round off
  rgD.setNumberFormat("#.##########");
  var vB=rgB.getDisplayValues();
  var vD=rgD.getDisplayValues();
  rgB.setNumberFormat("[h]:mm:ss");
  rgD.setNumberFormat("[h]:mm:ss");
  var vE=[];
  SpreadsheetApp.flush();
  for(var i=0;i<vB.length;i++){
    var sum=vB[i][0]+vD[i][0];
    vE.push([Number(vB[i][0])+Number(vD[i][0])%24]);     
  }
  rgE.setValues(vE);
  rgE.setNumberFormat("[h]:mm:ss")
}  

Sheet 5 before running:

Sheet 5 after running:

I was surprised to find out that the duration will display hour values as high as 1150 hours.

Answer 2

Your failure is value1+ value2 will become text

So I make a little modifiction of your script to be (+0 and +8 in mine , change as your zone I think) :

function adder()
 {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Log');
   var lastRow = sheet.getLastRow(); 
   var cell = sheet.getRange('C2:C'+lastRow).getValues();
   var data = sheet.getRange('D2:D'+lastRow).getValues();
   for (var i = 0; i < lastRow-1; i++){
       var value1 = cell[i][0];
       var value2 = data[i][0];
       var d = value1;
       d.setHours(value1.getHours()+value2.getHours()+0);
       d.setMinutes(value1.getMinutes()+value2.getMinutes()+8);
       var range = sheet.getRange('E'+(i+2)).setValue(d);
   }
 }  

my Update:

function adder()
 {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Log');
   var lastRow = sheet.getLastRow(); 
   var cell = sheet.getRange('B1:B'+lastRow).getValues();
   var data = sheet.getRange('D1:D'+lastRow).getValues();
   for (var i = 0; i < lastRow; i++){
     var value1 = cell[i][0];
       var value2 = data[i][0];
       var d = value1;
       d.setHours(value1.getHours()+value2.getHours()+0);
       d.setMinutes(value1.getMinutes()+value2.getMinutes()+8);
       var range = sheet.getRange('E'+(i+1)).setValue(d);
   }
 }