Clever Geek Handbook

Google Apps Script: transferring a schedule from a spreadsheet to a calendar

Who is this article for?



  • For people who actively use the Google Calendar service
  • For people who have table files with schedules and who would like to work with them more productively - always have upcoming events in front of their eyes, receive notifications with reminders by mail or push notifications
  • For people who want to quickly get to know Google Apps Script, understand what it is and where it can be applied


What is needed



  • Google account
  • Basic knowledge of JavaScript


1. Create a spreadsheet in Google Sheets



This can be done by going to your Google Drive page.





Figure: 1. Right clicking on the workspace opens a context menu where you need to select "Google Sheets" - "Create an empty table"



In the created table, you need to copy the schedule you have, in my case, this is the schedule of classes at the university. It is important that the tables contain the following fields:



  • event title
  • Event date
  • Start time
  • End time or duration (however, this is optional - in the end you can set some general default value for the duration of the event, for example, 1 hour, or even make the event last a whole day)


All other information, in my case - the teacher, the type of lesson (lecture, seminar, etc.), can be sent to the event description.





. 2.



2.



2.1.



, , .





. 3. , "", " "



JavaScript. myFunction. , SetCalendar.





. 4. , — SetCalendar



, , . SetCalendar.



2.2.



JavaScript :



console.log("Hello, world!")


Google Apps Script . console Logger:



function SetCalendar() {
  Logger.log("Hello, world!");
}


, "", "" Ctrl+Enter:





. 5.



2.3. 





function SetCalendar() {
  //         
  const rowStart = 1;
  const colStart = 1;

  //     
  const colsCount = 5;
  const rowsCount = 67;  

  //  
  var sheet = SpreadsheetApp.getActiveSheet();

  //     
  var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount)
  var data = range.getDisplayValues();
}


11.



SpreadsheetApp — , , Google Sheets.



, — , ( ) . getActiveSheet.



sheet.



, range — , . getRange. :





! 1.

15: getDisplayValues.



! getDisplayValues() , . , , - , , .


2.4.



, , , . :



//-   
  const dateCol = 0;
  const timeCol = 1;
  const typeCol = 2;
  const nameCol = 3;
  const teacherCol = 4;


2.



data , . .



  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];
    let classPeriod = row[timeCol];
    let classType = row[typeCol];
    let className = row[nameCol];
    let classTeacher = row[teacherCol];

    Logger.log("[DATE] " + classDate);
    Logger.log("[PERIOD] " + classPeriod);
    Logger.log("[TYPE] " + classType);
    Logger.log("[NAME] " + className);
    Logger.log("[TEACHER] " + classTeacher);
    Logger.log("============================================");
  }


for i data, i — , .



3: data i row — .



5-9: — . , row[dateCol] — dateCol.



5-9 , , , .



, , .



2.5.



, 



function SetCalendar() {
  //         
  const rowStart = 1;
  const colStart = 1;

  //     
  const colsCount = 5;
  const rowsCount = 8;  

  //  
  var sheet = SpreadsheetApp.getActiveSheet();

  //     
  var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount)
  var data = range.getDisplayValues();

  //-   
  const dateCol = 0;
  const timeCol = 1;
  const typeCol = 2;
  const nameCol = 3;
  const teacherCol = 4;

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];
    let classPeriod = row[timeCol];
    let classType = row[typeCol];
    let className = row[nameCol];
    let classTeacher = row[teacherCol];

    Logger.log("[DATE] " + classDate);
    Logger.log("[PERIOD] " + classPeriod);
    Logger.log("[TYPE] " + classType);
    Logger.log("[NAME] " + className);
    Logger.log("[TEACHER] " + classTeacher);
    Logger.log("============================================");
  }
}


, :





. 6.



, , , , . ?



:





. 7.



, . , , .



, , , .



, :



  let savedDate = "";

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];

        //...

    if (classDate.trim() == "")
    {
      classDate = savedDate;
    }
    else
    {
      savedDate = classDate;
    }

    Logger.log("[DATE] " + classDate);
    //...
    Logger.log("============================================");
  }


savedDate — .



. — , savedDate, savedDate.



2.6.



— getDisplayValues(), , , .



, Google Calendar, Date: - , - .



(. 2), dd.mm.yyyy, , , hh:mm-hh.mm.



, Date. Google Apps Script, - . , JS-, - - .



:



function extractTime(timeStr, dateStr)
{
  let sepIdx = timeStr.indexOf(":");

  let hoursStr = timeStr.substring(0, sepIdx);
  let minsStr = timeStr.substring(sepIdx + 1);

  sepIdx = dateStr.indexOf(".");

  let dayStr = dateStr.substring(0, sepIdx);
  let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3);

  sepIdx = dateStr.indexOf(".", sepIdx + 1);

  let yearStr = dateStr.substring(sepIdx + 1);

  let t = new Date();
  t.setHours(parseInt(hoursStr), parseInt(minsStr));
  t.setYear(parseInt(yearStr));
  t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr));

  return t;
}

function extractPeriod(periodStr, dateStr)
{
  let sepIdx = periodStr.indexOf("-");

  let fromStr = periodStr.substring(0, sepIdx);
  let toStr = periodStr.substring(sepIdx + 1);

  fromStr = fromStr.trim();
  toStr = toStr.trim();

  return {
    from: extractTime(fromStr, dateStr),
    to: extractTime(toStr, dateStr)
  }
}


- , extractPeriod — , .



2.7. Google Calendar



, 



let classTimeInfo = extractPeriod(classPeriod, classDate);

let classStartTime = classTimeInfo.from;
let classEndTime = classTimeInfo.to;

let info = ": " + classTeacher + "\n : " + classType;

var event = (CalendarApp.getCalendarsByName(""))[0].createEvent
(
  className,
  classStartTime,
  classEndTime,
  {
    description: info
  }
);

Utilities.sleep(50);


1-6: - , , .



, Google Calendar CalendarApp.



getCalendarsByName .



- getDefaultCalendar, . , - Google Calendar , . -, , .



, , , - , - , , , , , , , .



"", . getCalendarsByName "".



, , :



(CalendarApp.getCalendarsByName(""))[0]


- createEvent. :



  • ( )
  • Data — -
  • Data — -
  • — ( — description — )


— 50 . , , , Google Calendar , API .



3.



,



! , 2. , , , .


function extractTime(timeStr, dateStr)
{
  let sepIdx = timeStr.indexOf(":");

  let hoursStr = timeStr.substring(0, sepIdx);
  let minsStr = timeStr.substring(sepIdx + 1);

  sepIdx = dateStr.indexOf(".");

  let dayStr = dateStr.substring(0, sepIdx);
  let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3);

  sepIdx = dateStr.indexOf(".", sepIdx + 1);

  let yearStr = dateStr.substring(sepIdx + 1);

  let t = new Date();
  t.setHours(parseInt(hoursStr), parseInt(minsStr));
  t.setYear(parseInt(yearStr));
  t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr));

  return t;
}

function extractPeriod(periodStr, dateStr)
{
  let sepIdx = periodStr.indexOf("-");

  let fromStr = periodStr.substring(0, sepIdx);
  let toStr = periodStr.substring(sepIdx + 1);

  fromStr = fromStr.trim();
  toStr = toStr.trim();

  return {
    from: extractTime(fromStr, dateStr),
    to: extractTime(toStr, dateStr)
  }
}

function SetCalendar() {
  //         
  const rowStart = 1;
  const colStart = 1;

  //     
  const colsCount = 5;
  const rowsCount = 8;  

  //  
  var sheet = SpreadsheetApp.getActiveSheet();

  //     
  var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount)
  var data = range.getDisplayValues();

  //-   
  const dateCol = 0;
  const timeCol = 1;
  const typeCol = 2;
  const nameCol = 3;
  const teacherCol = 4;

  let savedDate = "";

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];
    let classPeriod = row[timeCol];
    let classType = row[typeCol];
    let className = row[nameCol];
    let classTeacher = row[teacherCol];

    if (classDate.trim() == "")
    {
      classDate = savedDate;
    }
    else
    {
      savedDate = classDate;
    }

    let classTimeInfo = extractPeriod(classPeriod, classDate);

    let classStartTime = classTimeInfo.from;
    let classEndTime = classTimeInfo.to;

    let info = ": " + classTeacher + "\n : " + classType;

    var event = (CalendarApp.getCalendarsByName(""))[0].createEvent
    (
      className,
      classStartTime,
      classEndTime,
      {
        description: info
      }
    );

    Utilities.sleep(50);
  }
}


4.









  • — , , , ( ). , API Google Sheets Google Apps Script , ;
  • JavaScript, , - .


I am open to constructive criticism, I will be glad if you point out the shortcomings, advise what can be improved and what can be changed in the article.



What to read next





More articles:


All Articles