My dudes... I don't know if I'm the last one to learn this, but I come bearing good news! Google's Apps Script is amazing and makes Spreadsheets & Slides so much more powerful!
Tumblr is hardly the best platform to talk about code on but this was a game changer! As a minor helper on @renegadepublishing's yearly exchange, my scripts let me keep up with the growing quantity of entries and while I only figured it out at the end of this year's labors, it will be so very, very, very helpful for next year's.
Will tuck further ravings under the cut but the short of it is that it (A) let me programmatically pull deets from AO3 to enrich final report and (B) let me programmatically fill the results slide show with data.
Coherently flowing prose often fail me when shifting to a technical discussion, so to break down the work:
this was all in JavaScript. I obviously love Kotlin more these days, but it felt painless/rather accessible being in JS
the primary 'key' passed around for the entries (when participents submitted updates and final entries) tended to just be the fic URL (plus gifter/giftee names) - a good UUID but carries no meta info
almost (tho not all) of the entries were hosted on AO3 which made programatic scraping possible
we definitely needed the author & fic name... which required visiting the page. And once you're on the page, why not grab more details? In the end we wound up scraping: title, author, last update, word count, fandom, rating. And while there's more one could collect, we lacked the space to visually display it along with the results. Remember-- the point is the typesetting!
Apps Script let you make URL requests [getContent]
I used the Cheerio library to process the page's contents and let me comb through it using jQuery. Link goes to GitHub page, which has the Script ID you'll need to import it in the Libraries section.
I found the Google Sheets APIs easy enough to read/work with -- just lots of arrays, really. Also, the quotas that you get seem generous to me (for a free product)
AO3 specific note : had to append "?view_adult=true" to the URLs if I wanted to bypass that check screen, but I also had to handle locked fic, which keep you at a reduced info lock screen. Be sure to test E rated & locked fic when drafting your script (Incognito Window is your friend)
Here is the Gist of the pictured script-- it was a 'round 2' edit where I went back and pulled Rating & Locked status : [LINK]
The above refers mostly to the Sheets side of things. The other most magical feature was the population of Slides w/ sheet data!
Notes as follows:
images are my current weakness-- had to go in later and adjust/layout/upload images manually. SUPER LAME. Next time I'm going to dig into pulling images from the drive...
I was able to embed links programmatically into the slides but it was VERY HACKY code. The generation of the PDF links was still a manual task, and while I imagine I could script that, sounds hairy (I want to avoid all things Auth)
The {{name_pattern}} was/is only a stylistic choice. Doing a find & replace on something so distinct is safest tho.
I did not use Alt Text on my things but I think going forward, it would be how I'd ID/address the non-string replacement actions (finding the right shape to anchor a URL link on was finicky the way I did it)
Here is my (very messy!) script for populating the slides: [LINK]
For next time:
using a script to pull tags/fandoms/pairings will make the gifter/giftee pairing process sooooo much smoother (so far it's been very manual!! Bless @aetherseer's tenacity!)
I need to figure out image imports/population of the slides... ignoring any aspect ratio/size issues
learned how to use Google apps scripts to send people emails when they fill in a Google form and I feel like a god now. this power knows no bounds. now I wanna make a bot that u can email and it'll play noughts and crosses with u or something. text adventure would be fun but could take a long time to come up with plot stuff possibly... figuring out how best to keep track of data would be interesting (and probably involve a spreadsheet). the long and short of it is I now feel a whole world of possibilities are now open to me and nothing can stop me :))
URL shortening is a technique where a long URL is shortened to a shorter, more manageable URL. This can be useful in various situations where a shorter URL is more convenient to use or share.In this video we will show you how to create a bulk URL shortener in Google Sheets using Bitly API and Apps Script.
We asked ChatGPT to write a script that can extract the input from an HTML form and then save it into Google Sheets in a proper format. This is will help you...
Using Google Apps Script, you can create a web form that allows users to input data and then have that data automatically transferred to a Google Sheet.
In this post, I will explain to you how to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets. Using Google Sheets APIs...
In this post, I will explain to you how to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets. Using Google Sheets APIs...
Create a CRON Job with Google Apps Spreadsheet Script
Create a CRON Job with Google Apps Spreadsheet Script
To perform repetitive task we used to create a CRON job from our CPANEL, so that it will be executed after particular interval or at particular time of the day, everyday. But have you ever thought of using Google Apps Script to create your CRON job ?
Also There are so many online services are also available to create CRON job, some are free and some are paid.
