Easy Calendar for Your Website

Display Google Calendar Data via Spreadsheets

This article provides an easy way to fetch Google Calendar data

Get the Calendar Id

Under the gear icon open Settings.

Find the settings for the calendar you want to use, in this example it is the Test Calendar

Scroll down to the Integrate calendar settings.

Copy the calendar ID, in this case: jj6cr4s8e9439e51kkijbnbuf0@group.calendar.google.com

You will enter this into the spreadsheet script later.

You DO NOT need to update any of the publishing settings, access will be requested the first time you run the script. Granting it once will allow the script to read from the calendar as needed.

Create a new Google Spreadsheet

You can create a new spreadsheet at https://docs.google.com/.

Create a Spreadsheet Script to Handle Calendar Data

In the spreadsheet, open the script editor in the menu under Extensions/App Script.

That will open an empty script editor:

Click on Untitled Project to edit the name to something useful, like Calendar Exporter.

Paste the code below in, replace YOUR_CALENDAR_ID_HERE with the calendar ID we saved above: jj6cr4s8e9439e51kkijbnbuf0@group.calendar.google.com

function calendarHandler() {
    const calendarIds = ["YOUR_CALENDAR_ID_HERE"];
    const opts = {
        timeMin: (new Date()).toISOString(),
        showDeleted: false,
        singleEvents: true,
        maxResults: 300,
        orderBy: "startTime"
    }
function listUpcomingEvents() {
    const events = calendarIds.flatMap(item=> Calendar.Events.list(item, opts).items);
    const range = "A2:I"+(events.length+1);
    console.log(events[0])
    const simpleEvents = events.map(event => [
        event.id,
        (event.start.dateTime? event.start.dateTime.substring(0,10) : event.start.date.substring(0,10)),
        (event.start.dateTime? event.start.dateTime : event.start.date).substring(0, 16), 
        (event.end.dateTime? event.end.dateTime : event.start.date).substring(0, 16), 
        (event.end.dateTime? (new Date(event.end.dateTime) - new Date(event.start.dateTime))/1000/60: 24*60),
        (event.start.dateTime? event.start.dateTime.substring(19) : null ),
        event.summary,
        event.description,
        event.location
    ]);
    SpreadsheetApp.getActiveSpreadsheet().getRange(range).setValues(simpleEvents);
    }
    listUpcomingEvents();
}

Connect the Script to Sheets and Calendar services

Click the + symbol next to Services on the left hand side of the page.

Scroll down to the Google Calendar API and click the Add button.

Do the same to add the Google Sheets API.

Deploy the Script

Click the Deploy button at the top of the page and choose New deployment.

Select type of Web app under the cog icon.

Add a description and grant access to Only myself.

Authorize Access

Click to authorize access, this only has to be done once.

You will need to sign into your Google account.

You will see a warning about that the script we just created hasn’t been verified by Google.

Click Advanced to see the options. You will need to click the (unsafe) link.

You will see one last confirmation page. Click the Allow button at the bottom of the page.

It will display some deployment info once completed.

Run the Deployed Script

Now that we have a deployed a script with access to the calendar and the spreadsheet we can run it. This will populate the spreadsheet with the calendar data.

You should see results like below.

And you should see your data in the spreadsheet.

Share the Spreadsheet to the web for the public

Share the first sheet as comma-separated (.csv) and click Publish.

You may see another confirmation popup.

The URL provided can be called like an API to return the calendar data in CSV format.
https://docs.google.com/spreadsheets/d/e/2PACX-1vTcEKW9-GGLJgveHn2wXP0YLMV5SVm7vMjOQ_FJnVUGPF-R4GXiO_uYl2_J_9NX5I7eHpVTiDtU4kVh/pub?gid=0&single=true&output=csv

Access CSV Data During Build

That CSV data cab be fetched and converted to JSON as part of a build and deploy process.

const axios = require("axios");
const path = require('path');
const fs = require('fs');
const Papa = require("papaparse");
const target = `https://docs.google.com/spreadsheets/d/e/2PACX-1vSCsoYKoYMd9zxT4MgFTiZMQL3Ie3934XP80EpQDgCZTd5J3XIOg_DiQp78t8ONygJrUurV09JTt-NQ/pub?gid=0&single=true&output=csv`;
// Make request
axios
.get(target)
.then(async (res) => {
  console.log(res.data);
  const parsed = await Papa.parse(res.data, {
  header: true
  })
  console.log(parsed.data);
  const obj = { events: parsed.data}
  const objStr = JSON.stringify(obj)
  const filePath = path.resolve('./data/calendar.json')
  fs.writeFileSync(filePath, objStr);
}).catch((err) => console.log(err));

Access CSV Data from Webpage

The CSV data can also be accessed directly from a webpage with fetch or Axios using the spreadsheet URL.

ttps://docs.google.com/spreadsheets/d/e/2PACX-1vSCsoYKoYMd9zxT4MgFTiZMQL3Ie3934XP80EpQDgCZTd5J3XIOg_DiQp78t8ONygJrUurV09JTt-NQ/pub?gid=0&single=true&output=csv