These are some calendars that are generated by a Google Sheet. Here's the workflow:
The sheet looks like this
Day | Date | Name |
---|---|---|
Sat | 2024-03-02 | Formula1 - Bahrain |
Sat | 2024-03-02 | Formula2 - Bahrain |
Sat | 2024-03-02 | Formula3 - Bahrain |
Sat | 2024-03-09 | Formula1 - Saudi Arabia |
Sat | 2024-03-09 | Formula2 - Saudi Arabian |
Sun | 2024-03-10 | Indycar - Florida |
Sun | 2024-03-10 | MotoGP - Qatar |
Sun | 2024-03-10 | Moto2 - Qatar |
Sun | 2024-03-10 | Moto3 - Qatar |
This is A, B, C --- then in D I have this absolutely overkill formula
=ARRAYFORMULA(
VSTACK(
TOCOL(
VSTACK(
JOIN(CHAR(13),
"BEGIN:VCALENDAR",
"VERSION:2.0",
"PRODID:-// //EN",
"CALSCALE:GREGORIAN",
"X-WR-CALNAME;VALUE=TEXT:Sports"),
IF(ISBLANK(sports!C2:C),,
MAP(
sports!C2:C,
sports!B2:B,
ROW(sports!B2:B),
LAMBDA(
a,b,r,
TEXTJOIN(CHAR(13),1,
"BEGIN:VEVENT",
"SUMMARY:"&a,
"UID:19970901T130000Z-1234"&TEXT(r,"000")&"@boring.party",
"DTSTAMP:"&TEXT(b,"YYYYMMDD")&"T"&TEXT(b-INT(b),"hhmm"),
"DESCRIPTION: Sports",
"DTSTART;VALUE=DATE:"&TEXT(b,"YYYYMMDD"),
"DTEND;VALUE=DATE:"&TEXT(b,"YYYYMMDD"),
"END:VEVENT"))))),
3),
"END:VCALENDAR"))
which generates...
BEGIN:VCALENDAR
VERSION:2.0
PRODID:-// //EN
CALSCALE:GREGORIAN
X-WR-CALNAME;VALUE=TEXT:Sports
BEGIN:VEVENT
SUMMARY:Formula2 - Bahrain
UID:[email protected]
DTSTAMP:20240302T0000
DESCRIPTION: Sports
DTSTART;VALUE=DATE:20240302
DTEND;VALUE=DATE:20240302
END:VEVENT
BEGIN:VEVENT
SUMMARY:Formula3 - Bahrain
UID:[email protected]
DTSTAMP:20240302T0000
DESCRIPTION: Sports
DTSTART;VALUE=DATE:20240302
DTEND;VALUE=DATE:20240302
END:VEVENT
END:VCALENDAR
Next up, publish the workbook and add &single=true&output=tsv&range=D1:D
to the end of the URL. This will output the one column as a tab-separated value, which is really just plaintext since we're only showing the one column.
Last, we feed it through the flow and have it spit out an .ics file that can be used with any calendar app as a subscription.