Create a Google Calendar Event from Google Sheets Row

Question

I have a dataset in Google Sheets with each row being a job with columns for date, frequency, place etc.

Name   #    Email    Phone    Type     Freq    Date    Start   End    Dur    Place    ID     
Jim    1    xxxxx    xxxxx    Hourly   Weekly  12/12   9am     10am   2      xxxxx    Job 1  
Bob    2    xxxxx    xxxxx    By Size  Once    12/12   2pm     5pm    6      xxxxx    Job 2
Tim    1    xxxxx    xxxxx    Hourly   Monthly 12/12   1pm     3pm    4      xxxxx    Job 3
 

A new job will populate a new row at the bottom. What I would like to automate is that for each new job that comes into the Google Sheet, a corresponding Google Calendar event is created. A couple of cavets:

• Events must only be created for jobs with col # equal to 1
• Event repeat frequency must be equal to col Freq
• Event colour must be equal to #fbd75b

I want the format to be like below for the event (row 3 for example):

Summary

col ID                 Job 3

Description

col Type "job"         Hourly Job
col Dur "Hours"        4 Hours
Col Freq               Monthly

col Name               Tim
col Email              xxxxx 
col Phone              xxxxx 

Location

Col Place              xxxxx

Start Date & Time

col Date at col start  1pm

End Date & Time

col Date at col End    3pm

Is this possible using script editor?

Answer 1

Answer

Yes, it is possible

How to do it

1. Use an onEdit trigger to check if a new job is added
2. Use SpreadsheetApp to get the necessary information
3. Use CalendarApp to create the events

Notes

1. You can add a new column with a link to the event to track the jobs that have been processed.
2. If you add a sample spreadsheet I can test it and attach you the final code working.

Update

I have written a small code example to help you get started. In this, there are two functions, one to read all the lines of the sheet and one to process each line. This is because the problem has two subtasks: on the one hand to manage the new data in the sheet once it is written and on the other hand the processing of this data.

subtask 1: manage new data

I don't know if you enter the data manually or through some function or with a different method. Nor if it is important that the events are generated just when you enter the data, that's why there are different solutions:

1. Each time a new row is inserted, check all data and create events for those who have not yet filled in the new eventId column. For this it is convenient to use [triggers][10].

2. Execute a function at the desired time to check the data and create the remaining events. This can be called from the main sheet via a button.

3. There are more options, but depending on the workflow you have it will be more convenient one or another, if you explain it to me I can guide you.

subtask 2: create the event with a single line

On the other hand, once a line has been detected as valid to create an event from it, the necessary information must be obtained and processed to create the event.

In the code you can see how I process line by line once I have obtained all the values of the sheet. Most of the code simply obtains the desired values from an array and stores them in variables that will later be used to create the event. The information that requires a little more processing is in obtaining the dates and creating recursive or non-recursive events. Finally, the formatting (color) is applied and the description is added regardless of the type of event it is.

Code

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var values = ss.getDataRange().getDisplayValues()
  var lc = ss.getLastColumn()
  for (var i = 1; i < values.length; i++) {
    var evId = processLine(values[i], lc) // create the event and get id of the event
    ss.getRange(i, lc).setValue(evId)     // write the id of the event in the last column of the sheet
  }
}

function processLine(x, lc) {
  var jobType = x[5]
  var evId = x[lc-1]

  // check job type
  if (jobType == 1 && evId == '') {
    // summary
    var summary = x[4]
    // description
    var type = x[17]
    var dur = x[14]
    var freq = x[10]
    var description = type + ' - ' + dur + ' - ' + freq
    // location
    var location = x[15]
    // date
    var start = x[12]
    var end = x[13]
    var date = x[11]
    // formated date
    var eStart = new Date(date + ' ' + start)
    var eEnd = new Date(date + ' ' + end)
    // options
    var opts = {
      location: location
    }

    // SINGLE EVENTS
    if (freq == 'Once Off') {
      var ev = CalendarApp.createEvent(summary, eStart, eEnd, opts)
    } else {
      // RECURRENT EVENTS
      // CREATE RECURRENCE FREQUENCY
      switch (freq) {
        case 'Monthly':
          var recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(dur)
          break
        case 'Weekly':
          var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(dur)
          break
      }
      // CREATE EVENT
      var ev = CalendarApp.createEventSeries(summary, eStart, eEnd, recurrence, opts)
    }
    // ADD PARAMETERS
    ev.setColor(CalendarApp.EventColor.YELLOW)
    ev.setDescription(description)
    console.log(ev.getId())
    return ev.getId()

  }
}

Reference

• Simple Triggers: onEdit
• SpreadsheetApp
• CalendarApp