#100Devs Google Sheet Updating Script

An easily-navigable & searchable resource for all the timestamps in one place!

While I could easily search the #100Devs assets I've collected, if someone else wanted to search all the timestamps for example, they'd have to traverse through all the videos up on YouTube and search through my posted comment. In addition, there was a Super Frontend Review Office Hours session, it being an Office Hours meant there was no guarantee it'd be reupload to YouTube, therefore there'd be no way for others take advantage of the timestamps at all.

So I decided to take all the timestamps and automate the publishing of them to a Google Sheet, with each different stream taking another worksheet!

Authentication

This wasn't my first time working with Google APIs, nor Google Sheets, and not even via an application like this, yet it took me a bit to figure out how to authenticate properly.

After obtaining my Google Sheets OAuth 2.0 credentials, I needed to manually modify the provided JSON and make "http://localhost" the first URI in the redirect_uris array, of course I don't know why, but following the Node.js quickstart resulted in authentication errors and this fixed it.

Row Generation

As Google Sheets supported a few more features then a CLI, I decided to first throw in some hyperlinks, and go with a three-column layout:

The Twitch & YouTube hyperlinks were to be links to the videos at those times, while the marker hyperlinks would be based on the content.

Marker Links

If the marker was in reference to a Slide, it'd be a link to that slide.

There were also the instances of Raiding, in which the Raiding XYZ marker would be a link to XYZ's Twitch.tv channel.

Worksheet Generation & Updating

As there were only two possibilities for the existence of each Worksheet - only a little bit of code to copy the Template worksheet and rename the copy would be needed to create the new Worksheets - then it would be updated with the rest of them.

Caching

As there'd only be more Worksheets to update, writing to the un-changed ones would be a waste of time, so I implemented some basic caching.

I did this simply by hashing all the rows of the desired Worksheet content, and writing it in the top-right corner of the sheet. Then I could check next time if the new content hash matches the currently-written hash, and skip over the worksheet.

Batching

This would still result in a number of requests, so I decided to start batching them, first with the detection and creation of missing Worksheets, then with the fetching of Worksheet hashes, dropping my number of requests from W - where W is how many Worksheets there were - to 3 + U, in which U is the number of updated Worksheets.

Styling

Lastly was making it somewhat presentable, so I went with Space Mono fonts for the timestamp columns, and the colors used for LearnWithLeon's channel, and decided to add a second table to the Worksheets: a filtered copy of the first only including the Slide, Question of the Day, and Starting/Ending markers, done so via this filter:

=FILTER(A3:C, REGEXMATCH(C3:C, "^#\d+.*") + REGEXMATCH(C3:C, "Question of the Day:.*") + REGEXMATCH(C3:C, ".* (Ended|Started)"))

This was likely the end of the sheet, it does exactly what I set out for it to do: be a location people can find the timestamps if the YouTube video isn't reuploaded.