D
D
Dmitry Boyarshinov2021-02-17 08:32:36
Google Calendar
Dmitry Boyarshinov, 2021-02-17 08:32:36

Events in google calendar from google spreadsheet?

We in the company need a simple solution to notify employees about customers' birthdays.
The choice fell on Google Spreadsheets + Google Calendar.

A solution was found: https://ignatov-script.blogspot.com/2017/04/google...

The solution works, BUT!
Events in the calendar creates for some reason the previous day.

Can you please tell me how to fix this?
Code used for google spreadsheets:

function saveBirthdays() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var calname = CalendarApp.getCalendarsByName("Дни рождения клиентов");
  if (calname.length > 0) 
{
   var currentCalendar = calname[0];
}
  else 
{
   var currentCalendar = CalendarApp.createCalendar("Дни рождения клиентов");
}
  var events = currentCalendar.getEvents(new Date(2020,01,01),new Date(2023,01,01));
  for (var j = 0 ; j < events.length; j++)
{
   events[j].deleteEvent();
}
  for (var i = 1; i < data.length; i++) 
{
   var Data = new Date(data[i][1]);
 var now = new Date().getMonth(); 
   var month = now+1; 


   if ((Data.getMonth()<=month)&&(Data.getMonth()>=month-1)){
   var Name = data[i][0];
   var Phone = data [i][2]; 
   var event = currentCalendar.createAllDayEventSeries("Сегодня "+Name+" празднует день рождения. Не забудь поздравить \n"+", номер(-а) телефона: "+Phone,Data,CalendarApp.newRecurrence().addYearlyRule());
   
    event.addEmailReminder(0);
    event.addPopupReminder(0)
    }
  }
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
Dmitry Boyarshinov, 2021-02-17
@brshnv

Solution found.
It is necessary to enable the display of appsscript.json in the editor in the project settings 602cb50012e44326272296.png
In the editor, change the time zone to the desired one
602cb50b88c17367574490.png

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question