CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
bdkauff
bdkauff

Reputation: 245

Setting cell value with two dates

I'm creating a Google Spreadsheet script with Google App scripts that interacts with Google Calendar. What I'm doing is searching for a specific Calendar event by keyword, pulling the start and end dates from that event, and then the goal is to display those two dates into one spreadsheet cell (mostly for readability sake). In other words, the cell should look like '07/14/19 - 07/20/19.' My code below:

function listEvents() {
  var id = "THE ID FOR MY CALENDAR IS HERE";   
  var cal = CalendarApp.getCalendarById(id);
  var startPeriod = new Date();
  startPeriod.setHours(0, 0, 0, 0);
  var endPeriod = new Date(startPeriod);
  endPeriod.setDate(endPeriod.getDate() + 1000);

  var sheet = SpreadsheetApp.getActiveSheet();
  var gig = sheet.getRange(1,1);
  var gigName = gig.getValue(); // get the gig name so you can search for it

  var events = cal.getEvents(startPeriod, endPeriod, {search:gigName});

// find the start date of each event in the calendar
  var starttime = [];
  for (var i = 0; i < events.length; i++) { 
    starttime.push([events[i].getStartTime()]);    
  }

  // find the end date of each event in the calendar
  var endtime = [];
  for (var i = 0; i < events.length; i++) { 
    endtime.push([events[i].getEndTime()]); 

  var cell = sheet.getRange("E5");
  cell.setValue(starttime+endtime);

This is sort of working. I can set starttime in one cell, and endtime in another cell, and they look all nice and pretty ('07/14/19','07/14/19') but when I do cell.setValue(starttime+endtime); the formatting of that cell changes to this messy stuff Sun Jul 14 2019 00:00:00 GMT-0500 (CDT)Sat Jul 20 2019 00:00:00 GMT-0500 (CDT) Just to reiterate, I'd like it to look like '07/14/19-07/20/19.'

I can't tell if this is a code problem or a formatting problem. If anyone can offer some help, it'd be appreciated!

Upvotes: 0

Views: 45

Answers (2)

RobertLCrocker
RobertLCrocker

Reputation: 40

You need to look at the Utilities Service... There is a "Format Date" method there that will allow you to format the date to whatever you want it to look like.

Upvotes: 0

ross
ross

Reputation: 2774

Requirement:

Formatted dates to be concatenated inside .setValue().

Solution:

Format the dates before you concatenate them in your .setValue(). Use the following 3 lines of code to replace the ones currently in your script.

//format the start time
starttime.push([Utilities.formatDate(events[i].getStartTime(), "GMT", "dd/MM/yy")]);

//format the end time
endtime.push([Utilities.formatDate(events[i].getEndTime(), "GMT", "dd/MM/yy")]);

Then your .setValue() should look something like this:

cell.setValue(starttime + ' - ' + endtime);

Example:

function listEvents() {
  var id = "THE ID FOR MY CALENDAR IS HERE";   
  var cal = CalendarApp.getCalendarById(id);
  var startPeriod = new Date();
  startPeriod.setHours(0, 0, 0, 0);
  var endPeriod = new Date(startPeriod);
  endPeriod.setDate(endPeriod.getDate() + 1000);

  var sheet = SpreadsheetApp.getActiveSheet();
  var gig = sheet.getRange(1,1);
  var gigName = gig.getValue(); // get the gig name so you can search for it

  var events = cal.getEvents(startPeriod, endPeriod, {search:gigName});

  // find the start date of each event in the calendar
  var starttime = [];
  for (var i = 0; i < events.length; i++) { 
    starttime.push([Utilities.formatDate(events[i].getStartTime(), "GMT", "dd/MM/yy")]); 
  }

  // find the end date of each event in the calendar
  var endtime = [];
  for (var i = 0; i < events.length; i++) { 
    endtime.push([Utilities.formatDate(events[i].getEndTime(), "GMT", "dd/MM/yy")]);
  }

  var cell = sheet.getRange("E5");
  cell.setValue(starttime + ' - ' + endtime);

Reference:

  1. Utilities.formatDate

Upvotes: 1

Related Questions