How can my team set up automatic google cal reminders for certain tasks?

September 26, 2019 8:14 AM

We just need a way to automatically notify ourselves that “hey, event A is X weeks away - reminder to do keep [particular database] updated!” in google calendar. My office coordinates and administers a large number of international programs and events. We do not have a dedicated project manager on our team nor are we likely to get one soon. I have been asked to look into automating google calendar reminders for the team re: the most crucial of our pre-program tasks.

There is one set of proprietary google sheet templates that we (try to) use and keep updated, even when everything else is flying at 42 billion mph. However not all of us are great about keeping this up to date, because it’s meta-work - manually recording what we have already executed in a spreadsheet format. The purpose is to have a master log of what’s been done that someone could easily step in as needed if the project lead is sick/out/maxed out etc. For reasons, we are only interested in solutions pertaining to google calendar updates.

Is there any way to automate reminders in google calendar, backwards from the event start date? Here’s the closest I can come to illustrating what I mean in text:

Google Calendar event #1
Date: 8 weeks before [EVENT DATE]
Event name: REMINDER: Update Planning Platform for [EVENT NAME]

Google Calendar event #2
Date: 4 weeks before [EVENT DATE]
Event name: REMINDER: Update Planning Platform for [EVENT NAME]

Google Calendar event #3
Date: 2 weeks before [EVENT DATE]
Event name: REMINDER: Update Planning Platform for [EVENT NAME]

Google Calendar event #1
Date: [EVENT DATE]
Event name: REMINDER: [EVENT NAME] starts today

Or is there some other automatic (not manual!!!) solution for setting reminders like this to ourselves? Or other hacks to otherwise simplify this type of meta work?

Hopefully this is clear, thanks for bearing with me - it’s hard to quantify exactly what it is we need, but in a nutshell - google calendar automated reminders that auto-populate backward from a specific date.

posted by seemoorglass to Computers & Internet (6 answers total)

Could you clarify the amount of software development you're willing or able to do? There is a Google Calendar API for creating events. As a programmer, that's how I'd approach the problem. Or you might be able to to use a webservice integration tool like this to hook something up with your spreadsheet, or another product. I know less about these kind of solutions.
posted by serathen at 8:50 AM on September 26, 2019 [1 favorite]

In Google Calendar you can set notifications days or hours ahead if an event, and you can multiple notifications.
4 Calendar events:
Spiffy Account, do initial tasks Notification: 8 weeks
Spiffy Account, Update Materials, Notification: 4 weeks
Spiffy Account, review data, Notification: 2 weeks
Spiffy Account, DataDue, Notification: 1 day

or

1 calendar event
Spiffy Account, Details
□ do initial tasks
□ Update Materials
□ review data
□ DataDue
Notifications: 8 weeks, 4 weeks, 2 weeks, 1 day
posted by theora55 at 8:53 AM on September 26, 2019 [1 favorite]

theora55's answer is great; if you need additional integration between the spreadsheet and Google Calendar, you can use AppsScript to add the calendar entries, though unfortunately this doesn't appear to allow for custom reminders. You'll then have to call addEmailReminder() yourself.
posted by kdar at 9:48 AM on September 26, 2019 [2 favorites]

Response by poster: We don't have any sort of programming/technical role on our team. (I work for a university so all the programming type roles are tied up in our IT department, which is currently over-taxed with a huge campus-wide software switchout). Even if I could learn basic scripting, I'd have to be able to hand the system over to a colleague to be able to maintain/update if I'm out, who will not know scripting. So we're looking for the most basic solution possible.

Theora's solution is looking like the best possible one for our team so far, however, we'd like to be able to set up calendar event notifications for others on our team, not necessarily in our own personal calendars.

If there's anything similar that could automate it even further, not requiring scripting, feel free to throw it out there- I realize I'm likely looking for a magic bullet but we want to make sure we are not missing that great solution under our noses!
posted by seemoorglass at 10:15 AM on September 26, 2019

On gmail you can definitely add people to an event/meeting. Many email systems, including gmail, have rules. I think you want a rule that takes a notification and forwards it to a group. Or that triggers the notifications for people included in the meeting. I am not a gmail expert in an Enterprise(large, corporate) environment, but you could post it to MeFi Jobs.
posted by theora55 at 10:33 AM on September 26, 2019 [1 favorite]

You can share a Google Calendar with a bunch of individuals or with a group (especially a Google Group). If you do that, then everyone gets the reminders for events on that calendar. I would create a special calendar for these items, give at least two (preferably three, if primary is on vacation and the backup gets sick or has a family emergency, having a third helps) people the permission to add, edit, and delete items. If you have people that use calendar tools other than Google in their daily work, GCal lets you provide a link/URL to share the calendar with them (Outlook, for example, lets you add "Internet Calendars") and those tools will regularly refresh their view of the calendar, and I _think_ they will get the reminders too (I'd have to test again, it has been a year or two).

As for automating those reminders: create _one_ appointment/event with all the characteristics you want, then when creating a new item go to that entry and choose 'Duplicate' - it will bring up an event with those characteristics. Edit the event date and time and other things should adjust...
posted by TimHare at 9:32 PM on September 26, 2019 [1 favorite]