1

I'm trying to combine date and time into one element in google app script array. This is for converting data from google sheet into google calendar.

I've 4 elements in my array; title, date, start time, end time. Each of them were retrieved by .getValues from google sheet.

title1 | Aug 08,2019 | 7:30 | 8:25
title2 | Aug 10,2019 | 8:30 | 9:25

I want to grab date and time from google sheet then createEvent in calendarApp.

//so with .getValues() in cArr variable from the table above I tried this code:
for (var i = 0; i <= cArr.length; i++){
CalendarApp.getCalendarById("myCalendarID").createEvent(cArr[i][0],cArr[i][2],cArr[i][2]);
};

The script were successfully run without error. But the event didn't appear in my calendar. I assume the events ever create in 1899 since it didn't specified the date in element [2] and [3].

Through some research, my best guess is to modify the array elements to be in 'MMM dd/yyyy, HH:mm' for both element [1] and [3]. But I just can't find a solution to do it. In the end, I want the result array like

[
  ["title1","Aug 08/2019, 7:30","Aug 08/2019, 8:25"],
  ["title2","Aug 10/2019, 8:30","Aug 10/2019, 9:25"]
]

Before I use this new array in .createEvent.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Nattapat Poolyam
  • 11
  • 2
  • You can try adding it `cArr[i][1]+cArr[i][2]` or `new Date(cArr[i][1]+cArr[i][2])` – TheMaster Jul 30 '19 at 08:37
  • Take a look at [Utilities.formatDate](https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate-timezone-format) – Cooper Jul 30 '19 at 15:39
  • @TheMaster The first suggestion concatenated the string together. It didn't add them. The second suggestion didn't work well since there are 2 date objects with their own date and time. For instance, [1] will give [Aug 08 2019 08:00:00] and [2] will be [Jan 01 1899 7:30:00]. – Nattapat Poolyam Aug 05 '19 at 13:03
  • @Cooper with the work around it works! Thanks – Nattapat Poolyam Aug 05 '19 at 13:04

2 Answers2

2

You can use the getDisplayValues() function [1] to obtain the string value of the cell, from this get the date info and create a Date object with that. Here is the code for that:

  var cArr = sheet.getRange(13, 3, 2, 4).getDisplayValues();

  for (var i = 0; i < cArr.length; i++){
    var month = cArr[i][1].substring(0, 3);
    var day = cArr[i][1].substring(4, 6);
    var year = cArr[i][1].substring(7);

    var startMinutes = cArr[i][2].substr(-2);
    var startHours = cArr[i][2].substring(0, 2);
    var endMinutes = cArr[i][3].substr(-2);
    var endHours = cArr[i][3].substring(0, 2);

    var startDate = new Date(month + " " + day + ", " + year + " " + startHours + ":" + startMinutes + ":00");
    var endDate = new Date(month + " " + day + ", " + year + " " + endHours + ":" + endMinutes + ":00");

    CalendarApp.getCalendarById("[mail]").createEvent(cArr[i][0], startDate, endDate);
  };

[1] https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues

Andres Duarte
  • 3,166
  • 1
  • 7
  • 14
  • I got a work around solution which similar to your suggestion, Thanks – Nattapat Poolyam Aug 05 '19 at 13:05
  • @Andres Duarte thanks, finally a proper solution. I applied to my use case here: https://web.archive.org/web/20230719131213/https://i.imgur.com/KneFUkq.png With the script: https://web.archive.org/web/20230719131449/https://pastebin.com/KS49rwzq – Lod Jul 19 '23 at 13:15
-1

Thank you so much for suggestions. I found the work around solution for my problem. I will share it here for references.

The data retrieved from googlesheet were converted in to date object so the idea is to create a new string containing date, month, year, time(hour and minute) using concatenation then apply the string on new Date() function.

var day = Utilities.formatDate(cArr[1],"GMT+07:00","MMM dd");
var year = Utilities.formatDate(cArr[1],"GMT+07:00","yyyy");
var ST = Utilities.formatDate(cArr[2],"GMT+07:08","HH:mm");
var ET = Utilities.formatDate(cArr[3],"GMT+07:08","HH:mm");
//then I concatenate them together
var StartTime = new Date(day + " " + ST + " " + year);
var EndTime = new Date(day + " " + ET + " " + year);

I did this under .map() function on cArr for a better operation time then using a for loop to create event in CalendarApp.getCalendarById("myCalendarID").createEvent().

PS. I don't know why the time zone has to be GMT+7:08 but this is from my trials and errors to get this time-shift to work best for my project. Also I tried to make it correct to the second digit, but the object turned into 'Jan 1 1899 8:00:00' when I tried with GMT+7:07:48.

Nattapat Poolyam
  • 11
  • 2