Copying a Google Calendar from 2025 to 2026, part 4

In a couple of recent posts (here), I looked at the stage for exporting a Google Calendar as an .ics file, and updating it for reimporting as a new Google calendar.

However, if we get that reimporting WRONG, we need to know how to undo it. I did just that, by trying to get clever and assuming that I could change the dates of my calendar export and then reimport it into the same calendar. Of course it overwrote all this year's dates because the entry ID numbers were the same. Sigh.

Luckily someone has documented how to undo the import for us all to benefit from: and it is a life-saver (GCal Tools, 2020). All we need to do is to open our edited .ics file in some type of text editor (I use Notepad). Do a find and replace of the following:

STATUS:CONFIRMED

For:

STATUS:CANCELLED

Then save the .ics file. We are now able to reimport the .ics file into our Google calendar, and our mistake will be erased. 

All I need to do is now to undo the find and replace, then reimport the .ics file into a new Google calendar to avoid the duplicate problem. And that worked perfectly.

Yay! And next time I was much more careful, first creating a unique annual calendar for the next year so I can safely have duplicate event IDs :-)

Sam

References:

GCal Tools. (2020, November 12). How to undo an import to Google Calendar [video]. YouTube. https://youtu.be/hJdGBmHDafA

Google Calendar Help. (2025, January 1). Copy Google Calendar Events from 2024 Calendar year to 2025. https://support.google.com/calendar/thread/319764201/copy-google-calendar-events-from-2024-calendar-year-to-2025-year?msgid=320864848#

read more "Copying a Google Calendar from 2025 to 2026, part 4"

Copying a Google Calendar from 2025 to 2026, part 3

In a couple of recent posts (here), I have been looking at how to export a Google Calendar as an .ics file, update it using Excel, and then reimport that data into a new .ics Google calendar; effectively duplicating all the events from one year into the next.

Of course it has turned out to be more complicated than I had hoped, but it does work after a fashion. I am hoping that I will find some hacks as I go to further smooth my path.

What I did to change the dates in Excel was  to create an Excel sheet with the following columns:

• A: Headings, a list to filter for "DTSTART" and "DTEND". In this column copied all my raw data from my exported .ics file, then deleted everything from the colon onwards (i.e. find and replace ":*")
• B: Course Name 2025, this column was a repeat of the raw data (to remain unchanged)
• C: Extracted date, trims the date in column D to eight characters. Uses the formula =LEFT(D3,8)
• D: Trim R/H, to filter for the date. Contains the formula =MID(B3,FIND(":",B3)+1,LEN(B3)-FIND("T",B3))
• E: Reorganised date, to create a readable date from column C. Uses the formula =DATE(LEFT(C3,4),MID(C3,5,2),RIGHT(C3,2)) with the date format  [$-en-NZ]dddd, d mmmm yyyy
• F: Course Name 2026, this column was a repeat of the raw data which I will change (and copy out and reimport)
• G: Extracted date, trims the date in column H to eight characters. Uses the formula =LEFT(H3,8)
• H: Trim R/H, to filter for the date. Contains the formula =MID(F3,FIND(":",F3)+1,LEN(F3)-FIND("T",F3))
• I: Actual date, to create a readable date from column G for double-checking. Uses the formula =DATE(LEFT(G3,4),MID(G3,5,2),RIGHT(G3,2)) with the date format  [$-en-NZ]dddd, d mmmm yyyy

After setting up, I (a) filtered column A to DT, (b) changed all years in column F from 2025 to 2026, (c) checked that the day for 2026 was the same day of the week for 2025 (moving the dates on manually at the moment), and (d) reimported the calendar.

Steps c and d took quite a while. But it worked. 

I am sure there are smarter ways of processing the data, but as a trial, this was effective. 

Sam 

read more "Copying a Google Calendar from 2025 to 2026, part 3"

Copying a Google Calendar from 2025 to 2026, part 2

In a recent post (here), I looked at the first stage of exporting a Google Calendar as an .ics file, and the next stage; working out how to update the dates so that I can import the updated version back into Google Calendar.

In the previous post, I had my data in a text file, as a vertical list of text strings containing the dates which need to be updated (depending on the type of calendar item each relates to). There are three 'types' of data in the export, all at various lengths, with the key date information that I want slopping around nicely on the far right in the unbracketed entries below, but lurking somewhere in the middle on the two bracketed examples:

DTSTART;VALUE=DATE:20250210 (or DTSTART:20250210T070000Z or DTSTART;TZID=Pacific/Auckland:20250210T070000)

DTEND;VALUE=DATE:20250210 (or DTEND:20250210T080000Z or DTEND;TZID=Pacific/Auckland:20250210T080000)

Of course it couldn't be too simple, could it!

I needed to work out how to extract the date - 20250210 - from those various length strings, so then I could convert the date to a full date format (I wanted to see the formatted dates and days so I could work out which items needed to be moved to a particular day. This is for me to see which dates need to change to a particular day rather than a year less a day when moving items year to year: Zooms tend to be on a particular night of the week, so being able to see the date in date format with the day makes it much easier to make the right adjustment. To do that, ended up having to do a three step process (despite trying all sorts, I ended up giving up and doing it an easy way!). First, I extracted the data from the colon in each data string, using a 'mid' function in Excel in column I:

=MID(B30,FIND(":",B30)+1,LEN(B30)-FIND("T",B30))

Secondly, in column C, I ran the following:

=LEFT(I30,8)

Thirdly, I then picked up the product of that C30 in D30 with a date reorganise using Excel's 'left', 'mid', and 'right' functions, also with a custom date format with days ([$-en-NZ]dddd, d mmmm yyyy):

=DATE(LEFT(C30,4),MID(C30,5,2),RIGHT(C30,2))

Crikey. And that got me to being able to begin to update the calendar data.

Now I need to work out an easy way to bulk change dates...!

Sam

read more "Copying a Google Calendar from 2025 to 2026, part 2"

Copying a Google Calendar from 2025 to 2026, part 1

Firstly, I would like to say that I am very grateful that Google calendar exists. It allows me to harmonise all my appointments across three systems: my 'trinity' selves. I invite myself at two workplaces, and to my cellphone - across three email addresses - using GCal as my Master calendar. It is a bit complicated, but I have managed to make most things work so I keep track of my commitments.

Secondly: I have a little niggle with GCal. I would love to have a way to "copy" this year's calendar to next year. I have not yet QUITE worked out how to copy one year's appointments in Google calendar to the next year, including moving all the appointments/events/tasks back one day so they all fall on the appropriate day of the week. I have been fiddling around the edges of this problem for years, and not really getting to a clear solution. However, recently I have got a bit closer to a solution.

It began with me posting a question on Google Calendar Help, earlier this year:

Copy Google Calendar Events from 2024 Calendar year to 2025 year
I am sure there must be a way to do this, but I cannot work it out. I am a lecturer. For each of the papers I teach, I create a sub-calendar in Google containing all the reminders, week numbers, assignment starts, topics, meetings and deadlines. However, I have not been able to successfully export LAST year's sub-calendar for each paper - say to Excel, change the dates to THIS year - then reimport it as a new sub-calendar into my master Google calendar. Is there a way? Can someone help?

I got a reply from Priya Chandra, who said that I could "use the export/import feature of Google Calendar to bulk edit events. This video shows you how to do that: https://youtu.be/hJdGBmHDafA", pointing me towards a GCalTools video (2020).

On watching the video, it showed me how to delete a calendar which was wrongly imported. It explained clearly how to export a calendar as an .ics file, open it in a text editor (like Notepad) and edit a repeating line of code to then reimport the calendar to bulk remove the wrongly imported entries (GCalTools, 2020).

While I didn't want to remove imported entries, the video made me think that perhaps I could export one of my calendars, change the dates to next year, and reimport the edited .ics file. I started trying to work out how to do that. I exported one paper's calendar, which had 48 calendar items. I opened the .ics in Notepad, highlighted the export data, and copied the resulting lines of code into Excel. Then I realised it was over 1000 lines long. Ouch.

OK. So maybe I should filter the entries to see if there was a logical set of entries with 2025 in them. That might help me decide which were the key lines of code I should edit to change the appointment dates. Ah: 255 lines containing 2025 for 48 appointments. And I might not need to edit all the lines...

I unfiltered. I could see no obvious 'master' lines for each calendar item. Hmm.... what if I create a 'test' calendar with a couple of different appointment types in it - one multi-day appointment, and one repeating appointment. If I exported the test calendar, I might be able to see what I should be looking for. I did that, and it worked PERFECTLY. It seemed that the lead entries for each new calendar item began with the following two prefix lines:

DTSTART

DTEND

Different types of calendar item had different text following those prefixes, but at least I knew what separated each appointment. Now all I needed to do was to work out which, and how many dates, I might need to change.

So I took another look at my test export data. I wondered what would happen if I only changed the DTSTART and DTEND dates. These were formatted as number strings with the year first, then the month, and lastly the date: 20250210.

I opened my exported Test .ics file, and changed the DTSTART and DTEND dates to one year ahead, and one day back: to 20260209. I reimported it to my next year's version of my Google Calendar for this paper. And it worked perfectly.

However, now I have 48 appointments to change with variable dates, in variable formats. That will be an Excel formula job which I will begin to tinker with in Part 2 (and maybe part 3 if it gets too complicated - we will see) :-)

Sam

References:

GCal Tools. (2020, November 12). How to undo an import to Google Calendar [video]. YouTube. https://youtu.be/hJdGBmHDafA

Google Calendar Help. (2025, January 1). Copy Google Calendar Events from 2024 Calendar year to 2025. https://support.google.com/calendar/thread/319764201/copy-google-calendar-events-from-2024-calendar-year-to-2025-year?msgid=320864848#

read more "Copying a Google Calendar from 2025 to 2026, part 1"

Changing Google calendar colours

I am a colour junkie. So when I create new sub-calendars in Google, I change the sub-calendar colour to fit with the organisation I am delivering that collection of tasks for, as an additional aide-memoire.

However, each year I struggle to remember HOW to change the sub-calendar colour. I can create calendars hand-over-fist, but changing the colour is simply not an intuitive thing. Each year I expect the option to be in the calendar settings itself, and each year I have to search online - usually wasting a couple of hours - until I stumble across the setting again... usually by accident. 

So here are the instructions!

We create the calendar. We invite those whom we want to send a link to. Then we go out of the calendar settings. We create a couple of appointments for that calendar. Then, in our normal Calendar page view, in the left-hand pane, under the "My calendars" list, we click the three flaming menu dots alongside the new sub-calendar. And lo! The colour options pop up.

Sigh.

Sam

read more "Changing Google calendar colours"

Google Calendar and ics appointments or events

If you use Google Calendar because you are working across multiple platforms - as I do - then you may find the following tip helpful about creating appointments based in Google Calendar when you are sent a Outlook ics file.

The reason I begin all my appointments in Google Calendar is because I work across four devices, and I want all of them to remind me of the same things at the same time (I invite my work self and my home self to all appointments that my Google self sets up - and sometime soon I will find a solution where I don't have to manually add all three of me, but that is a problem for another day!).

To return to the "we have been sent an ics file" in Outlook. We don't add it to Outlook. Instead, we download and save the ics file on our device. Then:

• We log in to our Google calendar
• Over on the top right of the page, we click on the Settings icon (as per the image accompanying this post)
• We select Settings from the drop down list
• Then, in the Settings page, from the left-hand menu bar, click Import & Export (about half-way down the list)
• Click the blue "Select file from your computer" button and navigate to the downloaded ics file
• In the next blue button, we select the calendar we would like to add this event to (the default is our primary calendar).
• Click "Import"

Then we are done!

Sam

References:

Google Calendar Help. (2025). Import events to Google Calendar. https://support.google.com/calendar/answer/37118?hl=en&co=GENIE.Platform=Desktop

read more "Google Calendar and ics appointments or events"

Gmail & Google Calendar Hotkeys

I recently watched a TechRepublic webcast on shortcut commands which detailed some really useful hotkey commands for using Gmail and Google calendar (Detwiler, 25 April 2018).

There are a couple of caveats on using these. Firstly, if some commands don't work, have a scout around in the settings to see if you have some conflicting options ticked. Secondly, some shortcuts won't work if you are inside a field. So ensure you have focus on the window, but aren't clicked into anything in particular.

The list of commands for each are below:

Gmail J - next converation K - previous conversation O - open conversation (at cursor) X - select conversation (not open) C - Compose new message # - Delete selected message U - Go back to previous list R - Reply A - Reply all F - Forward Shift I/U - Mark as read/unread Tab - move through fields

Google Calendar C - open full event screen Q - open quick add box1 - single day view 2 - single week view 3 - single month view 4 - 4 week view 5 - Schedule view J - next period in set view K - previous period in set view

Sam

• Reference: Detwiler, B. (25 April 2018). TechRepublic Dojo: Essential Gemail and Google Apps Keyboard Shortcuts. Retrieved from https://www.techrepublic.com/article/gmail-redesign-everything-you-need-to-know-about-the-new-features/?ftag=TRE684d531&bhid=40778246

read more "Gmail & Google Calendar Hotkeys"