Syncing a Spreadsheet with Google Calendar using Google Scripts to be (or at least try) more productive
Tags: productivity, google, spreadsheets, calendar
Published: 2023-11-04 17:53:35

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x9zsw3w97mkqwn4djib3.jpg)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iw63vd3sbbe49xy2u09t.png)


**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](https://docs.google.com/spreadsheets/d/1SO8Ealz15EUsJdb51sZYLiMsokyFSk0OQNZXXusKyvU/edit?usp=sharing). 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](https://www.google.com/script/start/), 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.**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vpcltnznbhr1kxi2397c.jpg)


## 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](https://docs.google.com/spreadsheets/d/1SO8Ealz15EUsJdb51sZYLiMsokyFSk0OQNZXXusKyvU/edit?usp=sharing) already correctly formatted to work with the following script.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kq0ychbs49e6yge53rom.png)

### Step 1 - Log in your Google Account

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yty4dez4bu54vnlffspi.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bk6cle94yjqav8bg8dzc.png)

The [Google Apps Script Editor](https://www.google.com/script/start/) 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**:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7bb1enmqsu5fu00d2jut.png)


### 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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/swrmd5hb92kbw9lpflg1.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g5zka5chozz5zfh0ouy6.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/266vancrcu94qjysyfq2.png)


### 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kagaaul8rldwj9rww844.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4dww9f2h56ryoviji9zb.png)

### 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xllx1aps18pl59veax8o.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pcopksnjcaiu8968pg6v.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8fnelmdyt1iix6ifn4jk.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/35x71fo37amgh2j7j1fb.png)

## 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](https://developers.google.com/apps-script/reference/calendar/event-color?hl=pt-br).


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](https://github.com/sarahcssiqueira/google-sheets-calendar-synchronizer). If it was useful for you, please consider leaving a star in the [repository](https://github.com/sarahcssiqueira/google-sheets-calendar-synchronizer). 

## References

- [G Suite Pro Tips](https://workspace.google.com/blog/productivity-collaboration/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar)
- [Class Calendar](<https://developers.google.com/apps-script/reference/calendar/calendar?hl=pt-br#createAllDayEvent(String,Date,Object)>)
- [Google Sheets - Use Apps Script to Create Google Calendar Events Automatically](https://www.youtube.com/watch?v=FxxPq2wXcK4)
- [Custom Colors](https://developers.google.com/apps-script/reference/calendar/event-color?hl=pt-br)
- [Custom Menus](https://developers.google.com/apps-script/guides/menus?hl=pt-br)