CODEWITHSUNDEEP
javascriptgoogle-apps-script
Re-l
Re-l

Reputation: 301

Adding duration to a start date in Google Script

I am working with a script (not mine) that takes two dates (start, end) in M/D/Y HH:MM:SS format and creates an event in Google calendar.

Now, I'm trying to change the form to have one date (start) and an option for hour duration (end). I want the duration to be added to the start date and then have the even created.

I have the script partially working where instead of the duration to show up as hours, it shows up as minutes.

I've tried multiplying endDt by 60 ( startDt + (endDt * 60) ) and a few other things, but nothing has worked so far.

I think this is the relevant portion of the code. The last line is mine.

  var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
  var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
  var endDt = startDt + endDt;

Any help would be appreciated. This is the entire code if it is needed:

var calendarId = "CALENDAR_ID";
var startDtId = 4;
var endDtId = 5;
var titleId = 2;
var titleId2 = 3;
var descId = 6;
var formTimeStampId = 1;

function getLatestAndSubmitToCalendar() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();
  var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
  var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
  var endDt = startDt + endDt;
  var subOn = "Added :"+sheet.getRange(lr,formTimeStampId,1,1).getValue()+" by: "+sheet.getRange(lr,titleId,1,1).getValue();
  var desc = "Comments :"+sheet.getRange(lr,descId,1,1).getValue()+"\n"+subOn;
  var title = sheet.getRange(lr,titleId,1,1).getValue()+" - "+sheet.getRange(lr,titleId2,1,1).getValue();

    createEvent(calendarId,title,startDt,endDt,desc);
};

function createEvent(calendarId,title,startDt,endDt,desc) {
  var cal = CalendarApp.getCalendarById(calendarId);
  var start = new Date(startDt);
  var end = new Date(endDt);
  var loc = 'LOCATION';

  var event = cal.createEvent(title, start, end, {
      description : desc,
      location : loc
  });
};

Upvotes: 1

Views: 1497

Answers (1)

Alan Wells
Alan Wells

Reputation: 31320

When I run that code with the debugger, here is what the output looks like:

Debugger

You'll see that the variable endDt looks like this:

"Thu Jan 15 2015 20:16:19 GMT-0500 (EST)2"

See the "2" on the end. That didn't do any addition, it concatenated text. Also, you'll note, that the middle column shows the variable types. The sheet is an object. The numRows is a Number. The startDt is a Date, the endDt is a String. You must know what the variable type is, on every single line. Javascript "coerces" variable types. So your "date" could be a string on one line, and a date type on another line.

If you want to adjust the end date in hour increments, and change the event ending time in relation to the start time, you could use the JavaScript setHours() Method.

function getLatestAndSubmitToCalendar() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet11'); //getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();
  var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
  var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
  var endDt = new Date(endDt);
  var endDtHour = endDt.getHours();
  var startHour = startDt.getHours();

  var newEndingHr = Number(startHour) + Number(endDt);
  endDt.setHours(newEndingHr);

  var subOn = "Added :"+sheet.getRange(lr,formTimeStampId,1,1).getValue()+" by: "+sheet.getRange(lr,titleId,1,1).getValue();
  var desc = "Comments :"+sheet.getRange(lr,descId,1,1).getValue()+"\n"+subOn;
  var title = sheet.getRange(lr,titleId,1,1).getValue()+" - "+sheet.getRange(lr,titleId2,1,1).getValue();

    createEvent(calendarId,title,startDt,endDt,desc);
};

Upvotes: 1

Related Questions