Google Apps Script - Adding/Subtracting Time - Hours/Minutes/Seconds to a particular column in googl-CodePudding

I want to be able to add or subtract Time - Hour/Minutes/Seconds to a particular column with some conditions using google app scripts.

Example - The Image Below.

Column A is the Original Time.

If Column D2 is "Depart To" 30 minutes will be added to the original time (column A2) which will result to the time in Column C2

while If Column D4 is "Arrive from" 30 minutes will be subtracted from the original time (column D2) which will result to the time in Column C4.

which script can I use to achieve this?

CodePudding user response：

Try this formula. Time is hours where 24 hours = 1 so 0.5/24 is a half hour.

=IF(D1="Depart To",A1 (0.5/24),A1-(0.5/24))

CodePudding user response：

Try this:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Sheet1");
  var range = ss.getRange(2, 1, ss.getLastRow()-1, 4); //get A2:D4 range
  var data = range.getDisplayValues(); //get values 
  data.forEach(row => {  // loop each sub-array returned by getDisplayValues
    var time = row[0].split(":"); //split the column a to get hour and minutes
    var hour = time[0];
    var minutes = time[1];
    var fDate = new Date(row[1]) //create new Date object by using column b
    fDate.setHours(hour) //set the hour of new Date Object
    fDate.setMinutes(minutes) //set the minute of new Date Object
    if(row[3].toLowerCase() == "depart to"){ 
      fDate.setMinutes(fDate.getMinutes()   30); // add 30 minutes to current date object
      row[2] = Utilities.formatDate(fDate, Session.getScriptTimeZone(), "H:mm");
    }else if(row[3].toLowerCase() == "arrive from"){
      fDate.setMinutes(fDate.getMinutes() - 30); // minus 30 minutes to current date object
      row[2] = Utilities.formatDate(fDate, Session.getScriptTimeZone(), "H:mm");
    }
  })
  range.setValues(data) //write values to sheet
}

Test Data:

Output:

References: