Workbook Statistics - a Google Sheets Add-on built using Apps Script

you can install the add-on either via sheets directly or by visiting the g suite marketplace

in this post, more than listing down all the points to follow in order to publish an add-on - that have oh-so-amazingly been documented (already) - i've rather chronicled my journey and experience through it + some interesting learnings from the ride.

backstory

as part of something that i've been wanting to do for quite some time now, towards the aspect that i wanted to explore within google apps script and accomplish in 2020 was that to publish an add-on.

lo and behold, i stumbled upon something on twitter that i thought could translate to a pretty cool add-on - though, not really solving any real-world problem (or not that anyone asked for it either 😅)

prior reading

• publishing an editor add-on
• oauth client verification > requesting verification
• editor add-on authorization > authorization model > the complete lifecycle

...but basically everything that surrounds the very first bullet point!

this was my very first time trying to attempt at publishing an add-on and by going through the required documentations, i was quickly made aware that almost at every step to make something public, it had to be approved by folks at google. this seemed a bit daunting/intimidating at first (given that i hold no expertise either with coding, publishing add-ons or requesting approvals) but didn't turn out to be that difficult after all.

pro tip: follow the documentation to the teeth and view every obstacle from it's lenses - even official objections/rejections

setup

usage

known limitations

some of the things that i wish were available in contrast with what already exists in microsoft excel:

comment stats

ms excel's workbook statistics also throws comment stats - which is why i wanted to incorporate that too within the add-on that i was building but after a bit of quick googling, turned out that fetching comments was not a function supported by SpreadsheetApp already but instead, had to be done via google drive apis instead (i.e. using advanced google services within google apps script).

the implementation would've been that of a single line -

var commentsWB = Drive.Comments.list(ss.getId()).items.length;

however, i did not want to add additional scopes with my first cut and so thought to let it be, for now.

as discussed on -

• stack overflow
• google's issue tracker

updating custom menu automatically

this may seem a little too generic given that it's applicable to things that are beyond the scope of this project but i felt it was important to highlight just the same as this add-on makes use of custom menus as a form of output/delivery, as opposed to any other html element.

so, the thing that i thought would make more sense was that the menu should've automatically changed/updated, as and when i navigated between different sheets; turns out, there are no triggers currently available (like we have for onEdit) that does that.

as discussed on -

• stack overflow
• google's issue tracker

learnings

right off the bat, i must say that i enjoyed working with toasts for the first time -

approval process

as i'm sure you'd eventually get around to this point, the oauth client verification ought to happen before you publish the add-on (dm me in case you want specific quotes from official documentation); however, when i tried doing so, the first official, non-automated response from google's 'api oauth dev verification' team was as below -

but since i'd been through the documentations thoroughly, i made my case to the team (politely) who saw reason in my appeal and also helped me out with the shortcomings of my submission in the first place (i'd accidentally missed out on adding the right scopes in my original request 😅 though i knew that had to be done).

no notification anxiety

what followed after client verification was to actually publish the add-on itself.
funny story: unlike with the aforementioned process, i received no notification/acknowledgement of my request to publish the add-on and so i grew anxious by the hour. all i did day-in & day-out was refresh my console page to see if the status had changed and by some miracle, it eventually did (~ after 4-5 days).

codebase

you can access the entire script on my github repository here or make a copy of the original script here.

here's a preview for one of the stats that i'm gathering -

...
ss.toast(" 🔥 Fetching: Workbook > Pivot Tables...", title, -1);
var pivotTablesWB = allSheets.filter(function(sheet) {
    return ss.getSheetByName(sheet.getName()).getType() == 'GRID';
}).map(function(sheet) {
    return ss.getSheetByName(sheet.getName()).getPivotTables().length;
}).reduce(function(a,b){
    return a + b;
}, 0);
Logger.log("Workbook > Pivot Tables " + pivotTablesWB);
...

open source

please feel free to share your feedback, feature requests or contributions for improvements either via email (code@script.gs), twitter, github or by any other form of communication.