Programming

Add Notification Days to Google Calendar via Apps Script

Modify Google Apps Script to read notification days from Google Sheets column G, calculate minutes before event start, and add custom popup reminders during bidirectional Calendar sync. Updated code, limits, and import tweaks included.

1 answer• 1 view

<|channel|>analysis<|message|>We need to sanitize text: remove all CJK characters. The user text contains Russian words and some Chinese? The text is Russian. We need to remove all CJK characters. The text includes Russian words, but also some Chinese? I see “Календарь” is Cyrillic. No Chinese. But there are some English words. No CJK. So we can keep as is. But we must remove any CJK characters. There are none. So we output the same text. But we must preserve markdown. The user asked for updated code snippet. We need to provide sanitized text. The text is all Cyrillic and English. No CJK. So we output same. Ensure no modifications.<|end|><|start|>assistant<|channel|>final<|message|>How to add a column in Google Sheets for specifying days in advance to trigger notifications in Google Calendar events using Google Apps Script?

I have a Google Sheet named ‘Календарь’ that syncs events bidirectionally with Google Calendar via Apps Script. The import function pulls events from Calendar to Sheet, and the export function deletes existing events and creates new ones from Sheet data.

Goal: Add a new column (e.g., column G: ‘Notification Days’) where I specify the number of days before the task start date to send a notification in Google Calendar. Example:

  • Start date: 10.01.2026
  • End date: 15.01.2026
  • Notification days: 3
  • Notification triggers on 12.01.2026 (e.g., popup or email reminder).

Different events can have different values (e.g., 5 days for another).

Current sheet structure (starting from row 5):
| Start Date (A) | End Date (B) | Event © | Location (D) | Description (E) | Recurring (F) | Notification Days (G) |

Import script (calendarimp):

javascript
function calendarimp () {
 var cal=CalendarApp.getCalendarById("prohousekd@gmail.com");
 var spreadsheet = SpreadsheetApp.getActive();
 
 var teklist = spreadsheet.getSheetByName('Календарь');
 var table = SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(teklist);
 spreadsheet.getRange('A5:E1000').activate();
 spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

 var datn = table.getRange(1,1).getValue();
 var datk = table.getRange(1,2).getValue();

 var dann = cal.getEvents(datn,datk);

 for (var i=0; i < dann.length; i++) {
 var title = dann[i].getTitle();
 table.getRange(i+5,3).setValue(title);

 var vrn = dann[i].getStartTime();
 table.getRange(i+5,1).setValue(vrn);

 var vrk = dann[i].getEndTime();
 table.getRange(i+5,2).setValue(vrk);

 var loc = dann[i].getLocation();
 table.getRange(i+5,4).setValue(loc);

 var prim = dann[i].getDescription();
 table.getRange(i+5,5).setValue(prim);
 var povtor = dann[i].isRecurringEvent();
 table.getRange(i+5,6).setValue(povtor);
 }
}

Export script (calendarexp):

javascript
function calendarexp () {
 var cal = CalendarApp.getCalendarById("prohousekd@gmail.com");
 var spreadsheet = SpreadsheetApp.getActive();
 var teklist = spreadsheet.getSheetByName('Календарь');
 var table = SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(teklist);


 var datn = table.getRange(1,1).getValue();
 var datk = table.getRange(1,2).getValue();
 var dann = cal.getEvents(datn,datk);

 for (var i=0; i < dann.length; i++) {

 dann[i].deleteEvent();
 }

 var dlinna = table.getLastRow();
 for (var i=5; i <= dlinna; i++) {

 var title = table.getRange(i,3).getValue();
 var datn = new Date(table.getRange(i,1).getValue());
 var datk = new Date(table.getRange(i,2).getValue());

 var mesto = table.getRange(i,4).getValue();
 var primech = table.getRange(i,5).getValue();

 cal.createEvent(title,datn,datk,{location:mesto,description:primech});

 }

}

How to modify the calendarexp function to read the notification days from column G and set a custom reminder (e.g., popup) that many days before the start time when creating the event? Provide the updated code snippet.

To set custom notifications in Google Calendar events synced from Google Sheets, update your calendarexp function to read “Notification Days” from column G, convert days to minutes (days × 1440), and call addPopupReminder on each new event—ensuring it stays within Google’s 5-40,320 minute limits. This keeps your bidirectional sync intact while handling per-event variations, like triggering 3 days early for a January 10, 2026 start. You’ll also want to tweak the import script later to pull reminders back into the sheet for full round-trip syncing.

Contents

Adding the Notification Days Column

First things first: your sheet ‘Календарь’ needs that new column G. Head to Google Sheets, select column G starting from row 5, and label it “Notification Days” in G4 or wherever your headers sit.

Use Data Validation to keep inputs clean—right-click the column, Data > Data validation, set to Numbers greater than or equal to 0, maybe up to 28 days to dodge limits. Why bother? Invalid entries like text or negatives could crash your script, and who wants reminders firing years early?

For a quick test row: A5=2026-01-10, B5=2026-01-15, C5=“Project Kickoff”, G5=3. Boom—popup should trigger on January 7.

This setup plays nice with your existing rows 5+ structure. No sheet renaming needed; Apps Script grabs it by name.

Updated calendarexp Function

Here’s the modified calendarexp. The big change? After createEvent (which returns a CalendarEvent object), grab column G’s value, calc minutes, and add the reminder. I kept your delete loop and row scanning intact—zero breakage.

javascript
function calendarexp() {
 var cal = CalendarApp.getCalendarById("prohousekd@gmail.com");
 var spreadsheet = SpreadsheetApp.getActive();
 var teklist = spreadsheet.getSheetByName('Календарь');
 var table = SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(teklist);

 var datn = table.getRange(1,1).getValue();
 var datk = table.getRange(1,2).getValue();
 var dann = cal.getEvents(datn,datk);

 // Delete existing events
 for (var i = 0; i < dann.length; i++) {
 dann[i].deleteEvent();
 }

 var dlinna = table.getLastRow();
 for (var i = 5; i <= dlinna; i++) {
 var title = table.getRange(i,3).getValue();
 var startTime = new Date(table.getRange(i,1).getValue());
 var endTime = new Date(table.getRange(i,2).getValue());
 var location = table.getRange(i,4).getValue();
 var description = table.getRange(i,5).getValue();
 var notificationDays = table.getRange(i,7).getValue(); // New: Column G

 var event = cal.createEvent(title, startTime, endTime, {
 location: location,
 description: description
 });

 // Add custom reminder if days specified
 if (notificationDays && notificationDays > 0) {
 var minutesBefore = Math.floor(notificationDays * 1440); // 24 hours * 60 min
 if (minutesBefore >= 5 && minutesBefore <= 40320) { // Google limits
 event.addPopupReminder(minutesBefore);
 console.log('Added ' + minutesBefore + ' min reminder for ' + title);
 } else {
 console.log('Skipped invalid reminder (' + minutesBefore + ' min) for ' + title);
 }
 }
 }
}

Paste this in, save, and run. It logs to the execution transcript for debugging. Notice Math.floor? Dates can be finicky with fractions.

Your recurring column F? createEvent doesn’t auto-set recurrence here—if needed, check CalendarEventSeries for upgrades.

How Reminder Minutes Are Calculated

Google Calendar reminders work in minutes before start time, not days. Simple math: one day = 1440 minutes (24×60). For 3 days? 4320 minutes.

In code: notificationDays * 1440. But cap it—API rejects under 5 min (too spammy) or over 40320 min (~28 days). Your script skips outliers now.

What about timezones? new Date() uses the sheet’s timezone, but CalendarApp normalizes it. Test across zones; use Utilities.formatDate if events span continents.

Ever wonder why not seconds? Google’s picky—check the CalendarEvent reference for why minutes only.

Setting Popup and Email Reminders

addPopupReminder(minutes) is your go-to for browser notifications—reliable, no extra scopes. Want email too? Chain event.addEmailReminder(minutes) after.

For multiples: event.addPopupReminder(4320); event.addEmailReminder(1440);. Clears defaults? event.removeAllReminders() first, then add yours.

Pro tip: Recurring events inherit reminders per instance, but series overrides possible via getEventSeries(). For basics, this single-event approach rocks.

From the official docs, it’s straightforward—no Advanced Calendar Service needed unless you’re scaling to thousands.

Syncing Reminders Back on Import

Your calendarimp pulls basics but skips reminders. Update it to read the first popup (common case) and write days back to G:

javascript
// Inside the for loop, after setting recurring:
var reminders = dann[i].getPopupReminders();
var notificationDays = reminders.length > 0 ? Math.round(reminders[0] / 1440) : '';
table.getRange(i+5,7).setValue(notificationDays);

Full sync! Now edits in Calendar reflect in sheets. Handles empties gracefully.

Error Handling and Best Practices

Scripts crash on bad data—empty G? Zero days? Script skips. Non-numbers? getValue() coerces to NaN, if(notificationDays > 0) fails safely.

Add try-catch for bulk ops:

javascript
try {
 var event = cal.createEvent(...);
 // reminder code
} catch (e) {
 console.log('Event creation failed for row ' + i + ': ' + e);
}

Scopes: Ensure https://www.googleapis.com/auth/calendar in appsscript.json. Quotas? 20k events/day—fine for most.

Community tips from Apps Script forums suggest setReminders([{method: 'popup', minutes: N}]) to override all, but addPopupReminder suffices here.

Testing with Real Examples

Fire up the sheet. Row 5: Start 2026-01-10 09:00, End 2026-01-15 17:00, Title “Tax Deadline”, G=5.

Run calendarexp. Check Calendar—popup 5 days prior (Jan 5, 09:00). Import back? G shows ~5.

Edge cases: G=0 (no reminder), G=30 (skipped, logs warn), blank row (skips). Recurring? Manually set post-create if col F true.

Mobile? Notifications push fine. 2026 dates? No Y2K38 issues here.

Sources

  1. CalendarEvent — Official API for addPopupReminder, getPopupReminders, and reminder limits: https://developers.google.com/apps-script/reference/calendar/calendar-event
  2. CalendarApp — Service for createEvent and getEvents methods used in sync: https://developers.google.com/apps-script/reference/calendar/calendar-app
  3. Calendar — Core calendar operations and event handling reference: https://developers.google.com/apps-script/reference/calendar
  4. CalendarEventSeries — Details on recurring events and series reminders: https://developers.google.com/apps-script/reference/calendar/calendar-event-series
  5. Google Apps Script Community — Thread on overriding default reminders with custom: https://groups.google.com/g/google-apps-script-community/c/v-aiFT5Xn0c

Conclusion

With this tweak, your Google Sheets-Google Calendar sync now handles custom notification days seamlessly—popups fire exactly when you specify, no more one-size-fits-all defaults. Test a few rows, monitor logs, and scale up. For auto-runs, add time-driven triggers. Questions on tweaks? Dive into the docs or tweak the code yourself.

Authors
NeuroAnswers
Author
Verified by moderation
NeuroAnswers
Moderation
Add Notification Days to Google Calendar via Apps Script