Adding duration to a start date in Google Script

Question

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
  });
};

Alan Wells · Accepted Answer · 2015-06-25 03:17:26Z

1

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);
};

edited Jun 25, 2015 at 3:17

answered Jun 25, 2015 at 1:10

Alan Wells

31.4k16 gold badges113 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Re-l Over a year ago

It did not work, unfortunately. Is it possible to do something like: endDt = startDt + endDt.setHours(endDt); Essentially, if the endDt input is 3, I want it to be converted to 3 hours, then added to startDt. Sorry if this is a stupid question, I don't know javascript at all.

Alan Wells Over a year ago

Using the debugger may help. Debugger I don't know what your spreadsheet values are, so I'm just guessing. For testing purposes, you can "hard code" some values. See updated answer.

Re-l Over a year ago

The debugger didn't flag anything and the code runs fine, it just doesn't create the even. I updated my original post to make the date format used more clear. Also, here is what the spreadsheet looks like: i.imgur.com/gXuZcWB.png and here's what is produced by my code: i.imgur.com/zvXnLrQ.png

Alan Wells Over a year ago

I updated the answer. Code is a little different. My original code had an error in it also. The debugger is for stepping through the code line by line, and watching the values and variable types as they change.

Alan Wells Over a year ago

The time zone in the Apps Script editor, and the time zone in the spreadsheet can be set to two different zones. If you are sharing a spreadsheet file, and working with people from different time zones, you can get different time and date settings. Is the form being filled out by people in different time zones? You can set the format type of a column to different formats. I actually like to store dates as strings (text).

|

Collectives™ on Stack Overflow

Adding duration to a start date in Google Script

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related