Converting ICS files to Google Sheets
It’s that time of year for NBA season ticket holders.
Preseason is upon us and the regular season is around the corner.
For me, that means splitting up our games between our various family members.
The way we do this requires a bit of a hack that I thought I would post about this morning in hopes that any of you out there know a better way.
Step 1 – Go to your team’s website, find the schedule, and select “add to calendar” or something like that. This is the page on the Knicks website.
Step 2 – Choose “other” or “download” to get an .ics file downloaded to your computer.
Step 3 – Use this free web app to convert the .ics file you downloaded to a .csv file.
Step 4 – Import that .csv file into Excel or Google Sheets.
The thing I always get stuck on is the Knicks schedule is published in GMT time and so the dates and times for the games always come in incorrectly. Which requires me to either write a script to convert them or just fix them manually. I have not found a way to download the .ics file in EST, which would be a huge benefit.
In any case, I am excited for the NBA season to begin. I am not expecting much from the Knicks this year as they are in full rebuild mode (including the knee of our franchise player).
But there is nothing like a close game in the fourth quarter at MSG and I’m hoping we get some of those this year.
thinking out loud.can you not import the ics into a new google calender. invite the relevant family members to it and then let them type/color-code their game preferences that way. a living breathing editable calender.
So the NBA made this a bit easier by not actually putting it in GMT, but rather in London time (so it’s GMT during non-daylight savings and BST the rest of the time). Assuming you’re willing to tolerate the possibility of one or two games being off during the week where NYC isn’t exactly 5 hours separate from London, the following should work:This is roughly doable by splitting the date and time colums, then using an GSheets formula to move it back 5 hoursChange 1: When converting the ICS to CSV, Select “Split Date and Time” from the “Time in Events” option. This will cause start date to be Column B and the start time to be Column CChange 2: Create a new, empty Column D Called “ET Start” and plug in this formula for D2, dragging down: ‘=DATEVALUE(B2)+TIMEVALUE(left(C2,8))-5/24’This will convert the date and time strings from B2 and C2 into a single new DateValue and then subtract 5 hours from it to move it back into ET.Copy/Paste Values Only to lock it in and delete the Original B & C Columns
Thank you so much. It sounds like you have faced this issue yourself !!
“Which requires me to either write a script”Might be my favorite thing I’ve ever seen you write in this blog…and there have been some really great ones over the years.#Hacker
My hacking skills are to yours like my basketball skills are to LeBron’s
Ha!I’m not good, I’m just committed.
You are probably very good from what I have read here from others, like Fred’s comment above.But, regardless, don’t say “just” committed :-). Commitment is a big deal. Remember what they say about bacon and eggs. The chicken is involved, but the pig is committed.
heh – thanks!Using Fred as inspiration, I’ve gone on year plus streaks of blogging every day…but when it comes to blogging, I’m *totally* a chicken….but when it comes to coding, I’ve pushed code to GitHub every single day for over five years now (which is only the length of the streak because that’s when I switched from subversion to github)…so, in that situation I probably am the pig…which explains, when it comes to coding, why I’ve always got mud all over my face. 😉
The Big Finn messed up his elbow in Chicago. Bulls have a long way to go. But, it’s still baseball season on the north side of Chicago which is nice since that didn’t happen much in the first 50 years of my life.
A sports post? I’m in.Why do American sports franchise owners buy English football clubs and then fail to invest in their acquisition? They should either fully commit, or get the hell out. It must be sad to be an authentic Manchester United or Arsenal fan at the moment….and why oh why are convicted dopers allowed to ride in the UCI’s World Championships? Alejandro Valverde winning in Innsbruck yesterday was a sad spectacle.
Pretty sweet to be a Liverpool owner right now, though, which is under American ownership, as well.
CONTRIBUTORS:We assumed Stanza automatically configured the calendar you downloaded the sports schedule at. (We still show Knicks calendar in one of default calendars.Why is this being made complicated for something developers made easy. No hacks required. Send calendar to intendedparties as an invite.Captain Obvious#UNEQUIVOCALLYUNAPOLOGETICALLYINDEPENDENT
Football may be done too. Go Nets!!
take a digibath – pulling tickets from a hat works.
Hi @fredwilson:disqus .You could also consider using Google Sheets Add-ons. There appear to be a few that are set up to read your Google Calendar entries (none, sadly, that just do an ICS import/export). But you could just sync the Knicks calendar to your Google Calendar and then use an Add-on to import that to a Google Sheet. Would help with the timezone drama too.
Also, just because for me this kinda stuff is fun, and I had an hour to kill, here’s a Google App script that will import ICS for you. Happy hacking!https://gist.github.com/tom…