How to link schedules to Google Calendar with Google Spreadsheets
I tend to use Google products a lot. My main calendar is also Google Calendar. As I became an executive of the group this time, I had to register my members' birthdays on the calendar. While I was just registering one by one, "What am I doing right now?" I'm starting to feel that kind of shame.
The thought of registering the date information (birthday) on the Google sheet at once easily crossed my mind. So I looked it up and... I think it'll be possible if I write a little macro program.
So I worked hard to develop it.
It took 8 hours to register in an hour, so I made a program.
As a result, it was more inefficient. crying
But...
It was inefficient for me, but I think it could be of great help to others if I disclose this code, so I'm going to reveal the code.
Supplies
All you need is a Google spreadsheet and a Google calendar. Of course, it's free.
Google Calendar
First, create a Google Calendar or prepare the calendar you are using.
...on the right side of the calendar you want to apply.Click on- Select
Set up and share.
- Remember your calendar ID. We will use this ID later.
Google Spreadsheet
Create lists and birthdays with Google Spreadsheets.
▲ You can fill it out as above, and the important thing is...
Date of birth must match the date format of the Google Sheet. And Gallinder registration, Calendar status must be required.
- Register calendar : Indicates whether to register or remove from calendar (ADD/ DEL)
- Calendar status : Check if the item is applied to the current calendar (Y/')
Creating a Macro Program
The basic preparations are done. From now on, you can create Apps Script and register the trigger.
Create Apps Script
Apps Script is a programming language designed to enable programming in javascript grammar for Google products. You can use this script to create macros in detail.
- Select
Extensionfrom the top menu of the calendar. - Select
Apps Script.
- First, create a random script name.
- Select Editor on the second of the five menus.
- You can use the default function or press + to create a new function.
- Change the name to create a specific name. (You can use the default function name.) Now you can write a program on this function and copy and apply the code I wrote.
Full code
/****************************************************************************************************
* Ability to automatically register a member's birthday on a calendar
****************************************************************************************************/
function goBirthCreate() {
/*****************************************************************************************************
************** User needs to register ************************************************************
*****************************************************************************************************
* SheetTabName: The name of the Sheettab at the bottom of the spreadsheet
* Header~~: If you enter the header name of the spreadsheet, register the cell in Google Calendar
* startRow : The starting point (row) of the table where the first data starts
* startColumn : The starting point of the table where the first data starts (column
* CalendarId : Found and created in the calendar you want to register
* TitlePrefaceWord: Header that will be commonly used in the title part when registering on the calendar
* descPrefaceWord : It is a delimited word that is commonly included in the content part when registering in the calendar (required because it is necessary to delete calendar events)
* registerYear : year to register in calendar
* alarm : Alarm (reminder) minutes to register with the calendar (number in minutes)
*****************************************************************************************************/
const SheetTabName = "회원생일";
const HeaderTitle = "성명";
const HeaderStartTime = "생년월일";
const HeaderDescription = "";
const HeaderEtc = "";
const HeaderRegYN = "캘린더등록";
const HeaderRegState = "캘린더상태";
const startRow = 3;
const startColumn = 2;
const calendarId = "su***************************lendar.google.com";
const titlePrefaceWord = "[Test Birthday]";
const descPrefaceWord = "[Google Sheet_Birthday]";
const registYear = "2022";
const alarm1 = "10080"; // first alarm (number in minutes)
const alarm2 = "500"; // second alarm (number in minutes)
/****************************************************************************************************/
/****************************************************************************************************/
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetTabName);
const eventCal = CalendarApp.getCalendarById(calendarId);
const endRow = spreadsheet.getLastRow();
const endColumn = spreadsheet.getLastColumn();
const count = spreadsheet.getRange(startRow, startColumn, endRow, endColumn).getValues(); // getRange(row, column, numRows, numColumns)
////////////////////////////////////////////////////////////////////////////////////////////////////
const colHeaderStartTime = spreadsheet.createTextFinder(HeaderStartTime).findNext().getColumnIndex() - startColumn;
const colHeaderTitle = HeaderTitle ? spreadsheet.createTextFinder(HeaderTitle).findNext().getColumnIndex() - startColumn : "";
const colHeaderDescription = HeaderDescription ? spreadsheet.createTextFinder(HeaderDescription).findNext().getColumnIndex() - startColumn : "";
const colHeaderEtc = HeaderEtc ? spreadsheet.createTextFinder(HeaderEtc).findNext().getColumnIndex() - startColumn : "";
const colHeaderRegYN = spreadsheet.createTextFinder(HeaderRegYN).findNext().getColumnIndex() - startColumn;
const colHeaderRegState = spreadsheet.createTextFinder(HeaderRegState).findNext().getColumnIndex() - startColumn;
/////////////////////////////////////////////////////////////////////////////////////////////////////
for (x = 0; x < count.length; x++) {
/**********************************************************************************************/
if (x === 15) Utilities.sleep (2 * 1000); // Error registering many calendars at once
/**********************************************************************************************/
const shift = count[x];
const regYes = shift[colHeaderRegYN];
const title = shift[colHeaderTitle];
const description = shift[colHeaderDescription] ? shift[colHeaderDescription] : "";
const etc = shift[colHeaderEtc] ? "\n" + shift[colHeaderEtc] : "";
const titleSum = titlePrefaceWord + " " + title;
const descriptionSum = descPrefaceWord + " " + description + etc;
/***********************************************************************************************/
/***********************************************************************************************
* StartTime is registered this year or next year, so the registration is processed by excluding the year from the birthday and replacing it with the designated year
***********************************************************************************************/
// Start replacing EST time with KOR time
const KR_TIME_DIFF = 9 * 60 * 60 * 1000;
const startCurr = new Date(shift[colHeaderStartTime]);
const startUtc = startCurr.getTime() + startCurr.getTimezoneOffset() * 60 * 1000;
const startT = new Date(startUtc + KR_TIME_DIFF);
// Replacement of EST time with KOR time is finished
const startTimeMonth = startT.getMonth();
const startTimeDay = startT.getDate();
const startCalendarTime = new Date(registYear, startTimeMonth, startTimeDay);
/***********************************************************************************************/
if (regYes === "DEL" || regYes === "del" || regYes === "D") {
const events = eventCal.getEventsForDay(startCalendarTime, { search: descPrefaceWord });
for (y = 0; y < events.length; y++) {
events[y].deleteEvent();
}
spreadsheet.getRange(Number(startRow + x), colHeaderRegYN + startColumn).setValue("");
spreadsheet.getRange(Number(startRow + x), colHeaderRegState + startColumn).setValue("");
} } else if (regYes === "ADD" || regYes === "add" || regYes === "A") {
const event = {
description: descriptionSum,
guests: "",
};
if (titleSum !== null && titleSum !== "") {
const events = eventCal.getEventsForDay(startCalendarTime, { search: descPrefaceWord });
for (y = 0; y < events.length; y++) {
events[y].deleteEvent();
}
eventCal.createAllDayEvent(titleSum, startCalendarTime, event).addPopupReminder(alarm1).addPopupReminder(alarm2);
spreadsheet.getRange(Number(startRow + x), colHeaderRegYN + startColumn).setValue("");
spreadsheet.getRange(Number(startRow + x), colHeaderRegState + startColumn).setValue("Y");
}
}
}
}
function onOpenBirth() {
const ui = SpreadsheetApp.getUi();
ui.createMenu ("Calendar Sync").addItem ("Member Birthday Update", "goBirthCreate").addToUi();
}
Partial code
The code is largely composed of two functions.
function goBirthCreate() {}
function onOpenBirth() {}
goBirthCreate(): Code to apply birthdayonOpenBirth(): Code that makes the application button appear on the Google sheet
Code Settings Area
The top part is the setting part.
/*******************************************************************/
constSheetTabName = "회원생일"; // Name of Tab at the bottom of the spreadsheet
Const HeaderTitle = "성명"; // [Header Name in Table] What will be registered as the title of the calendar
constHeaderStartTime = "생년월일"; // Date to be registered in [Header Name in Table] calendar
ConstHeaderDescription = ""; // [Header Name in Table] What will be registered as the content of the calendar
Const HeaderEtc = ""; // [Header Name in Table] What will be registered with the contents of the calendar
const HeaderRegYN = "캘린더등록"; // [Header name in table] Set whether to register or delete in calendar (ADD/DEL)
Const HeaderRegState = "캘린더상태"; // [Header Name in Table] Indicates whether or not it is registered in the current calendar
const startRow = 3; // Line number from which the actual data starts
Const startColumn = 2; // The number of columns where the actual data starts
const calendarId = "sunrl******************************dar.google.com"; // calendar ID
constitlePrefaceWord = "[Test Birthday]"; // Horsehead to register in calendar title
constdescPrefaceWord = "[Google Sheet_Birthday]"; // Horsehead to register for calendar content
constregisterYear = "2022"; // Set the year to register in the calendar
const alarm1 = "10080"; // the first alarm to register with the calendar (number in minutes)
const alarm2 = "500"; // Second alarm to register with the calendar (number in minutes) calendar
/******************************************************************/
Register a trigger
Once the code has been registered and set up, you must now write and apply a trigger code so that it can be applied in certain situations.
Code for applying trigger
function onOpenBirth() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("Calendar Sync").addItem("Member Birthday Update", "goBirthCreate").addItem("Member Birthday Update 2", "code").addToUi();
}
If you write the code as shown above, a button is created in the top menu as shown below. And when you click this button, the program runs and is reflected in the calendar.
If you attach .addItem(), the menu will continue to be added down.
- The calendar synchronization button is visible.
- I can see the member birthday update button.
Reflect triggers
You can now register a trigger that runs onOpenBirth() when a particular event occurs.
- Select the
Triggermenu from the left. - Click the
Add Triggerbutton to generate a new trigger. - Select the
onOpenBirthwe created as a function to run. - Select Head.
- We will reflect the events that occur in the
spreadsheet. - When the spreadsheet is opened, it means that you want to run this function.
Immediate notificationif trigger fails. When you open the spreadsheet, you will automatically see the "Synchronize Calendar" button on the top menu. When adding or deleting birthdays, simply press the button to reflect them.
Test it out
If you run it, you'll see that it reflects well.
Comments