Google Script - Create Calendar Events from Spreadsheet Data

Yesterday I shared how I added reminder dates to a spreadsheet for a person in my department who keeps track of software subscriptions.  Today I'm sharing how we take the entries on that spreadsheet and create calendar events, so the reminders are built into her calendar.

Here are the steps to create calendar events from spreadsheet data:

1. Either open the spreadsheet with the data (or create a new spreadsheet & populate it with data).
This is the spreadsheet where I first added the script to add a "remind date" (yesterday's post).



2.  Open the Script Editor
Go to the Tools Menu and select Script editor


3. Replace the Code
Replace the default code -



with this - (all the text highlighted in gray -  including the final bracket)

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendar = CalendarApp.getCalendarById('calendarID');
 
  var startRow = 2;  // First row of data to process - 2 exempts my header row
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();
 
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
  var data = dataRange.getValues();
 
  var complete = "Done";
 
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[1]; //Item Name
    var vendor = row[2];  //Vendor
    var contact = row[3]; //vendor contact info
    var notes = row[7];  //Notes
    var date = new Date(row[5]);  //renewal date
    var rDate = new Date(row[10]); //remind date
    var eventID = row[11]; //event marked Done
   
    if (eventID != complete) {
      var currentCell = sheet.getRange(startRow + i, numColumns);
      calendar.createEvent(name, rDate, rDate, {
        description: vendor + '\r' + contact + '\r' + date + '\r' + notes
      });
    
      currentCell.setValue(complete);
    }
  }
}




Let's break it down.

First, I'm establishing some variables -
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendar = CalendarApp.getCalendarById('calendarID');
 
  var startRow = 2;  // First row of data to process - 2 exempts my header row
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();
 
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
  var data = dataRange.getValues();


  var complete = "Done";

The most notable of these is the variable calendar - in that one you have to put the calendarID of the calendar you will be writing to (you must have edit rights on the calendar for this to work!).  If you don't know, you grab the calendar ID by going to that calendar's settings, and copying the calendar ID:


Paste this ID in the script so that they are connected.

There are 2 other lines of note in this section - as they are related -
var startRow = 2;  // First row of data to process - 2 exempts my header row
and
var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);

The first line lets me skip over any header rows - so I can set startRow to the first row I want the script to run on (as I don't want calendar events to run on my headers!).  BUT - I need to adjust that second line so that numRows-# will match the number of rows I want processed.  This # will always be my start row number - 1 (in this example, 2-1=1 so numRows-1 is what mine is set at).


The next section of script is setting my loop to grab data from each row (established with i)- 
for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[1]; //Item Name
    var vendor = row[2];  //Vendor
    var contact = row[3]; //vendor contact info
    var notes = row[7];  //Notes
    var date = new Date(row[5]);  //renewal date
    var rDate = new Date(row[10]); //remind date
    var eventID = row[11]; //event marked Done
 


These rows correspond with my spreadsheet headers - note that the  bracketed number is one less than if you count columns starting from 1.  I added the commenting tags so I knew what each variable was.  

Of special note here - the variables setting dates.  Whenever you have a date, you need to establish it is a date - hence the new Date(row[#]) format. Otherwise when you run the script it is unable to read the numbers as a date.  (This piece probably took me the longest to wrangle out...)
 

The next section of script is creating my calendar events -
  if (eventID != complete) {
      var currentCell = sheet.getRange(startRow + i, numColumns);
      calendar.createEvent(name, rDate, rDate, {
        description: vendor + '\r' + contact + '\r' + date + '\r' + notes
      });

I have my last column being marked, so this first checks to see if an event has already been created - if the last column is not marked (the !=complete piece), then it proceeds to create an event with that row's data.  If it is marked, it skips that row.

The variable current Cell is used later, but I established it here.  

The calendar.create Event is set up so that it gives the event a title, sets the start and end dates, then puts information into the description area of the event.  

Some notes about this: 

-I find it easier to establish everything as variables, then reference the variables.  Here is the general setup for an event -
cal.createEvent("Title" or variable, new Date("MM DD, YEAR HR:MM:SS") or variable, new Date("MM DD, YEAR HR:MM:SS") or variable,
  {description:"description" or variable, location:"location" or variable, guests:email1 or variable, email2 or variable}); 

-You can note I've included how to add location and guests (which will add it to their calendars via invitations) to the event as well, though I did not use those in this example.  
-You can create a string of items for the description with plus signs.  The '\r' is a line break.

Finally -
       currentCell.setValue(complete);

This final line marks the last column in the row with the word Done - preventing it from running the script on the same row each time I run it in the future...(which will result in multiple calendar events for the same information - I know this personally...). 

That's it!  You now can create calendar events from a spreadsheet - even one that is populated from a form.  Just think - you can: 
  • auto make appointments that people sign up for via a form  
  • set reminders from data on a spreadsheet
  • create a birthday calendar, and have your students input their birthdate via a form 
 How are you using this?  I'd love to hear the other uses people have for this in the comments! 

Comments

  1. Hi Melinda!!
    Thank you for such helpful info, it really saved my life (work time).
    I did some adjusments to the script, and everything works fine at the first run (Yay!!), but when I ran twice, the events was duplicated, even when these have the "Done" status.
    I paste my code, and I'd really really appreciate if you take a look, please?? (4 eyes see more than 2).

    function scheduleFlights() {
    var spreadsheet = SpreadsheetApp.getActiveSheet();
    var calendarId = spreadsheet.getRange("AA1").getValue();
    var eventCal = CalendarApp.getCalendarById(calendarId);
    var startRow = 2;
    var numRows = spreadsheet.getLastRow();
    var numColumns = spreadsheet.getLastColumn();
    var dataRange = spreadsheet.getRange(startRow, 1, numRows-1, numColumns);
    var data = dataRange.getValues();
    var complete = "Done!";


    for (var i = 0; i < data.length; ++i) {
    var flight = data[i];
    var startTime = new Date(flight[19]);
    var endTime = new Date(flight[20]);
    var title = flight[21];
    var flight = flight[22];
    var airline = flight[23];
    var pax = flight[24];
    var terminal = flight[25];
    var eventID = flight[26];
    if (eventID != complete) {
    var currentCell = spreadsheet.getRange(startRow + i, numColumns);
    eventCal.createEvent(title, startTime, endTime,{
    description: 'Flight: '+flight + ' ' + 'Airline: '+airline + ' ' + 'Pax: '+pax + ' ' + 'Terminal: '+terminal});

    currentCell.setValue(complete);
    }

    }
    }

    function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Sync to Calendar')
    .addItem('Schedule flights', 'scheduleFlights')
    .addToUi();
    }

    ReplyDelete
    Replies
    1. My best guess - I had something similar happen, and it was because in referencing the column I had put the 'Done' I somehow got off by 1. I would suggest double-checking that the correct column is referenced in not getting it to repeat.

      Delete
    2. With a quick readout of above - you might need to add another if (eventID != complete) piece within your onOpen function - prevent it from syncing every item? Sorry I cannot provide more help than that right now!

      Delete
  2. Hi, I've a question. I need to create events with defined start and end time but this script create "all day" events. I've a table formatted for csv export with a row for start date, a row for start time and other two respectively for end time and date.
    The google function startDateTime requires a combined string in iso format including the date, the time and also the timezone.
    How can I combine the two rows to make this iso format?
    Thanks

    ReplyDelete
  3. Hi! Quick question. When I try to run the script I get this error message:

    TypeError: (class)@4413552a is not a function, it is undefined. (line 19, file "Code")


    line 19 is:

    var rDate = new Date(row[3]); //start date and time

    Other than updating the variables to fit my spreadsheet, I haven't changed any of your code. Could you help me out?

    ReplyDelete
  4. function myFunction() {

    }
    function createCalendarEvent() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var calendar = CalendarApp.getCalendarById('nexgen-net.com_b4uiem8522c03nho25mpc0l7lg@group.calendar.google.com');

    var startRow = 2; // First row of data to process - 2 exempts my header row
    var numRows = sheet.getLastRow(); // Number of rows to process
    var numColumns = sheet.getLastColumn();

    var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
    var data = dataRange.getValues();

    var complete = "Done";

    for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[7]; //Item Name
    var vendor = row[2]; //Vendor
    var date = new Date(row[10]); //renewal date
    var eventID = row[12]; //event marked Done

    if (eventID != complete) {
    }
    }
    }

    ReplyDelete
  5. Hi,

    If I would want to delete and event from a calendar how would I go about this? I made the following adjustments to your script but seem to get the error (TypeError: Cannot call method "deleteEvent" of null. (line 84, file "Code").

    function createCalendarEvent(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Absence Notification - Acknowledged');
    var calendar = CalendarApp.getCalendarById('phase-team.com_mi891t83cmuo2tqn21mvch8rp4@group.calendar.google.com');

    var startRow = 2;
    var numRows = sheet.getLastRow();
    var numColumns = sheet.getLastColumn();

    var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
    var data = dataRange.getValues();

    var complete = "Done";
    var remove = "Cancel";

    for (var i = 0;i<data.length;++i){
    var row = data[i];
    var name = row[0];
    var absencefrom = new Date(row[1].getTime()+ 43614.0006944445);
    var absenceto = new Date(row[2].getTime()+ 43614.4993055556);
    var type = row[3];
    var eventcancelled = row[4];
    var eventadded = row[5];
    var eventcalendar = row[6];

    if(eventcancelled!=remove){
    var cancelevent = calendar.getEventById(eventcalendar);
    cancelevent.deleteEvent();
    }

    if(eventadded!=complete){
    var currentCell=sheet.getRange(startRow+i,numColumns);
    var newEventTitle = 'Absence: ' + name;
    var event = calendar.createEvent(newEventTitle, absencefrom, absenceto);
    var eventid = event.getId();

    currentCell.setValue(eventid);
    }
    }
    }

    Column 4 displays cancelled if an event is cancelled, Column 5 gives whether the event has been added and column 6 displays the event ID.

    Thanks

    ReplyDelete

Post a Comment

Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

Google Forms - Copy Summary Graph/Diagram to Use Elsewhere

Google Sheets - Flipping Your Rows & Columns

Google - Reset a Form for Reuse with the new School Year