I may be the only person in the world tracking my video game achievements on a Google Calendar, but I thought I’d document my process for doing so both for my own reference and in case anyone else finds it useful.
1. I use Metagamerscore.com to actually track my achievements on XBOX Live, Steam, World of Warcraft and elsewhere. As long as your profile for a given game or service is public, Metagamerscore can import achievements for any game it supports.
2. Once you’ve got an account created and your various video game accounts connected, you can go to My Progress–>My Achievements to see a list of Achievements you’ve earned. That list will look something like this.
3. I then select the entire text block minus the headings. This gives me tab-delimited text that looks something like this, which I paste into a text editor:
Squire 189 2016-12-30 22:34 STM Game of Thrones - A Telltale Games Series A Lord's Reception 187 2016-12-30 22:33 STM Game of Thrones - A Telltale Games Series A Lord's Depute 181 2016-12-30 22:10 STM Game of Thrones - A Telltale Games Series A Lord's Judgement 172 2016-12-30 22:01 STM Game of Thrones - A Telltale Games Series We'll be ready! 71 2016-12-30 20:08 STM Written in the Sky New Queen 70 2016-12-30 20:08 STM Written in the Sky
4. I put this into a text editor because I like to do some minor text cleanup there. For example, I do a search and replace to convert “STM” to “Steam Achievement – ” which makes it easier for me to search and view in my Calendar.
5. Once that’s done, I copy the text and paste it into a Google Sheets document to prepare the final CSV that I’ll import into my Video Game Achievements calendar. So my sheet starts off looking like this:
6. So the first thing I do is deleted column B, since I don’t care about the points values that Metagamerscore assigns to achievements. I also move column D in front of Column A, so now my spreadsheet looks like this:
7. I insert a new column “C” after column B and then combine the text in columns A&B using =&A1+” – “&B1. Then I paste that formula in every cell. Finally, I select Column C and press CTRL+C to copy, and then right-click on Column C and select “Paste Special–>Values Only.”
Now I can safely delete columns A and B and move on to fixing the dates.
8. Google Calendar needs a Start Date, Start Time, End Date and End Time, so we’ll create those columns and move the original date column off to the right.
Additionally to create an end time, I typically just add 5 minutes to the start time. So if I got an achievement at 4:34 p.m., I’ll have the Google Calendar event extend from 4:34 p.m. to 4:39 p.m.
This, however, is where spreadsheets get weird about time. Basically as far as the spreadsheet is concerned, a value of 1 is considered an entire 24-hour day. Here we want add 5 minutes to column D, so what we want to add is 5/1440 or .003472. So I’ll create column E which is =+D1+.003472, and then copy that to every cell in the column. So now, the spreadsheet looks like this:
9. Now we can populate the dates and times.
The dates are easy. For Start Date we use =INT(F2) which will give just the date, and then copy that into every cell in column B. For the End Dates, we use =INT(G2), and copy that formula into column D.
For start time, we need to subtract the value in F from new value in B. So, in column C we want to use =+F2-B2, and in column E we want to use =+G2-D2.
You will also need to use the formatting option to format the dates as Date and the times as Time. This gives us a sheet that looks like this:
10. Finally, we want to select each or our Start/End columns individually and first copy the data and then Paste Special–>Values Only. Once this is done for each column, we can safely delete columns F & G.
Now, download the spreadsheet as a CSV file and import into Google Calendar. As I mentioned at the start, I created a special Video Game Achievements calendar to use for this, so I can select or deselect this particular information.