Syncing a Spreadsheet with Google Calendar using Google Scripts to be (or at least try) more productive

Remote working requires discipline, that's a fact. And I must admit I have this problem of lack of focus, I am curious and easily get bored, I am always tempted to start a new project because of some random article I read. That's something I have been fighting against since I was 17 years old in my first job in an accountant's office. For a long time, I was told that was my weakness.

Later, at some point I became a developer and realized this curiosity and eagerness to learn something new could also be my biggest strength, as long I could be able to not get lost in my random ideas.

I tried to find tools to help me be more productive and there are lots of books about that, tutorials, coaches' lectures, etc. I tried several approaches through the years but guess what? Furthermore, I didn't was able to use any fancy tool with consistency, no matter how awesome were the tools.

After trying dozens, and hundreds of productivity tools I realized the one that would work for me was that I would be able to follow with consistency, and turns out the obvious answer was right in front of my eyes: Excel.

Microsoft Excel doesn't require a presentation, and I know it can seem boring for developers, but it is a tool I always liked and learned to use in my account work times. Well, I do not know how to do those insane things the guys from the financial market do, but I can do one thing or another. It also was a tool I used with some consistency for making notes and even for controlling my finances.

Why does not apply to control my dev working routine? And what about if I could share it with my team? That would be perfect.
Okay, new awesome project idea. How could I do that?

As a Google products fan, I moved to Google Spreadsheets and started to register my work planning and any new project idea in a Spreadsheet, similar to this one here. I was also using Google Calendar to schedule meetings, etc., so why not synchronize everything?

After some googling, I discovered some product add-ons available, but none of them seemed right for my needs. Then, a few more googling to discover how those product add-ons for Google Workspace were made.

I discovered Google Apps Scripts, and now we finally reach the main point of this post: to share how we can synchronize a Google Spreadsheet with a Google Calendar using Google App Scripts.

Requirements

• Google Account
• Some JavaScript Knowledge
• Google Spreadsheets and Google Calendar familiarity

Because we are going to handle data formats, we need to be careful with the spreadsheet to avoid errors. That's a sample of my current spreadsheet already correctly formatted to work with the following script.

Step 1 - Log in your Google Account

After login to your Google Account, at your spreadsheet menu, go to the tab Extensions > Apps Script

Click on it, and now you are in the Google Apps Script editor:

The Google Apps Script Editor is a web-based integrated development environment (IDE) provided by Google for creating, editing, and deploying custom scripts and automation solutions for various Google Workspace (formerly G Suite) applications. It allows users to write code in JavaScript to extend the functionality of Google Apps, automate tasks, and build custom applications within the Google ecosystem.

Give a name to your project, I called mine Sheet & Calendar Synchronizer:

Step 2 - Write the code

In the web-based IDE, paste the following codes, and make sure to make the proper replacements.

To create or update events

function createorUpdateEvents() {

  /*
  * Open the Calendar
  */
  const calendarId = 'YOUR CALENDAR ID';
  const sheet = SpreadsheetApp.getActiveSheet();

  /*
  * Import events data from the spreadsheet
  */
  const events = sheet.getRange("A2:G1000").getValues();

  /*
  * Event details for creating an event
  */

  let event; // Declare event variable outside the loop

  for ( i = 0; i < events.length; i++ ) {
    const shift = events[i];
    const eventID = shift[0];
    const eventsubject = shift[1];
    const startTime = shift[2];
    const endTime = shift[3];
    const description = shift[4];
    const color = shift[5];


     // Check if all variables are defined
      if (
        eventID !== undefined && 
        eventsubject !== undefined && 
        description !== undefined && 
        color !== undefined && 
        startTime instanceof Date && 
        endTime instanceof Date
        ) {
        const event = {
            id: eventID,
            summary: eventsubject,
            description: description,
            'start': {
                'dateTime': startTime.toISOString(),
                'timeZone': 'America/Sao_Paulo'
            },
            'end': {
                'dateTime': endTime.toISOString(),
                'timeZone': 'America/Sao_Paulo'
            },
            colorId: color
        };

        /** 
        * Insert or update event
        **/
       try {
        let createOrUpdate;
        if (event.id) {
          createOrUpdate = Calendar.Events.update(event, calendarId, eventID);
        } else {
          createOrUpdate = Calendar.Events.insert(event, calendarId);
        }
      } catch (e) {
        if (e.message && e.message.indexOf("Not Found") !== -1) {
          createOrUpdate = Calendar.Events.insert(event, calendarId);
        } else {
          console.error("Error:", e);
        }
      }
    }
  }
}

Replace 'YOUR CALENDAR ID' with your Google Calendar ID.

To get events from Calendar to Sheet

function exportCalendarEventsToSheet() {

  const calendarId = 'YOUR CALENDAR ID';
  const startDate = new Date('2023-01-01');
  const endDate = new Date('2023-12-31');
  const calendar = CalendarApp.getCalendarById(calendarId);
  const sheet = SpreadsheetApp.getActiveSheet();
  const events = calendar.getEvents(startDate, endDate);
  const data = [];


  if (events.length > 0) {
    for (let i = 0; i < events.length; i++) {
      const event = events[i];
      const eventID = event.getId().split('@')[0];
      const eventTitle = event.getTitle();
      const startTime = event.getStartTime();
      const endTime = event.getEndTime();
      const description = event.getDescription();
      const color = event.getColor();

      data.push([eventID, eventTitle, startTime, endTime, description, color]);
    }

    const numRows = data.length;
    const numCols = data[0].length;
    sheet.getRange(2, 1, numRows, numCols).setValues(data);
  } else {
    console.log('No events exist for the specified range');
  }
}

Step 3 - Add services

In the left panel, add the services Google Calendar API and Google Sheets API

Step 4 - Run the code

You need to run the scripts, you can do that by clicking on run in the web-based IDE every time you need.

However, that is not practical for daily basis use. So we will need a few more codes to create a menu that will allow us to run the code directly through the Google spreadsheets menu.

Step 5 - The Menu Code

In the editor, place the following code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync Data with Calendar')
    .addItem('Calendar to Sheet', 'exportCalendarEventsToSheet')
    .addItem('Sheet to Calendar', 'createorUpdateEvents')
    .addSeparator()
    .addSubMenu(
      ui.createMenu('About')
        .addItem('Documentation', 'showDocumentation')
    )
    .addToUi();
}

function showDocumentation() {
  var htmlOutput = HtmlService.createHtmlOutput('<p>For more info, visit <a href="https://github.com/sarahcssiqueira/google-sheets-calendar-synchronizer" target="_blank">this link</a>.');
  var ui = SpreadsheetApp.getUi();
  ui.showModalDialog(htmlOutput, 'Documentation');
}

function closeDialog() {
  google.script.host.close();
}

This will create a menu like the one in the image, and you will be able to execute the scripts directly from the spreadsheet menu.

Step 6 - Give permissions

The first time you try to use the project by clicking on the menu button, you may be asked to give permission. Allow.

Usage

Create the spreadsheet where you intend to save your events and actually list your events, following this format.

Once you hit the Sheet to Calendar button, it will create or updates all events listed in the spreadsheet in the Google Calendar you choose.

You can also drag and drop your events on Calendar, and by hitting the Calendar to Sheet button, these changes will be reflected in the spreadsheet.

About the event's colors

There are eleven colors available we can use, those colors should be listed in the color column or selected in the Calendar. More info here.

This code was written for personal use, so certainly there are room for improvement. Feel free to improve giving your suggestions in the comments below or even through PR in GitHub. If it was useful for you, please consider leaving a star in the repository.

References

• G Suite Pro Tips
• Class Calendar
• Google Sheets - Use Apps Script to Create Google Calendar Events Automatically
• Custom Colors
• Custom Menus