Count von Count — a Google Workspace Add-on built using Apps Script

A global counter that summarises accessible stats about different Workspace services.

Count von Count — a Google Workspace Add-on built using Apps Script
Count von Count — a Google Workspace Add-on built using Apps Script
you can install the add-on either by visiting the workspace marketplace or from inside gmail, calendar, drive, sheets, docs or slides.

If you don't already know this - Count von Count is a character from sesame street and in case you missed it - I'd previously built Workbook Statistics - a Google Sheets Editor Add-on but as part of 2021, I'd also wanted to publish my first Google Workspace Add-on, which is what turned out to be this yet-another-useless-app that does nothing but shows you different counts 😛

This post would mostly talk about the learnings i've had during the process of publishing a Workspace Add-on as it is relatively new given all the advancements that the Google Workspace Marketplace team along with the Apps Script team have been working on over the past year (Card Services, new marketplace listing, improved documentation, new processes etc.).

Lets break this down to 3 steps -

  1. coding the add-on
  2. OAuth approval process
  3. add-on listing approval process

Coding the add-on

With the new Apps Script IDE in place, I found the following to be the major differentiators vs. the old one -

  • the execution log (obviously) – just like coding any other script, it was much more easier to understand what output was being delivered by the core pieces of code that was running
  • the deployment process – I got to experience working with the 'test deployment' feature that allowed me to seamlessly install it as a Workspace Add-on (this was detected and picked-up using the manifest specifications)
test deployment as a workspace add-on
test deployment as a workspace add-on

I also happen to be making use of Apps Script Advanced Service and so it was important that the manifest was set up accurately as well + the Add-on config was no joke either and had to be meticulously tailored to accommodate all key-value pairs that were needed – enter, the manifests for Google Workspace Add-ons documentation.

Card services

It was an interesting learning curve while building card-based interfaces as i didn't want to rely on the same old html-css bundle (not that i'm complaining 😉) – there's plenty to unpack here with their widgets and action elements that can be configured in a multitude of ways.

pro tip: while you will find yourself constrained by the pre-made ui interfaces, it is worth noting that the card services are designed to behave in the most optimal way while extending add-ons across all of google's services (hopefully, to all mobile apps in the future as well 🤞🏾) and by extension, worth getting used to!

I'm making use of the DecoratedText class (an information widget) which does have terribly minimum html formatting available.
Full disclosure: this was originally inspired by the KeyValue class which has unfortunately been deprecated.

This is what the bare-bones of the common homepage looks like -

function commonHome() {
  const text = CardService.newDecoratedText()
    .setText("This is the common homepageTrigger");

  const footer = CardService.newFixedFooter()
    .setPrimaryButton(CardService.newTextButton()
      .setText('SHARE')
      .setBackgroundColor("#7c4dff")
      .setOpenLink(CardService.newOpenLink()
        .setUrl('https://twitter.com/schoraria911')))
    .setSecondaryButton(CardService.newTextButton()
      .setText('READ MORE')
      .setOpenLink(CardService.newOpenLink()
        .setUrl('https://script.gs/count-von-count/')));

  const section = CardService.newCardSection()
    .addWidget(text);
    
  const card = CardService.newCardBuilder()
    .addSection(section)
    .setFixedFooter(footer)
    .build();

  return card;
}

Here are 2 more things that took me longer to understand purely from a design / UX point of view -

  • handling navigation – when to update existing card vs. return a new card instead and everything that goes in-between. I particularly obsessed over whether or not to have the app show a "back button" when refreshing the app and I was mostly inclined towards not showing it because I'm not making use of multiple cards and so it was important for me to ensure that the cards that already existed, just "updated" in their existing space post refresh
  • handling notifications (toast) – thanks to the feedback from Adam Morris (@clssrmtechtools) & Martin Hawksey (@mhawksey), I got to include this in order to indicate what was happening after the app was done refreshing (and re-fetching data), based off of a click on a card

OAuth approval process

My documentation discovery journey for this process, in context of publishing an Add-on went on as follows -

  1. Google Workspace Developers > Add-ons > publish your add-on
  2. Google Workspace Developers > Google Workspace Marketplace > Overview: publish an app (step 2) > configure OAuth > submit for OAuth verification
  3. Google Cloud Platform console help > OAuth API verification faqs* > steps to submit your app

*it is important that one reads, understand and executes this doc in its entirety (regardless of what "type" of app you're building, "purpose", or "scopes" being used).

steps-to-submit-your-app
steps-to-submit-your-app

What I wish someone had told me about or had it been documented was that I'd have to look at a page and press buttons that looked like these (I was not mentally / emotionally prepared for it 😅):

  1. once you're done "preparing" for verification, you'd actually need to press a PUBLISH APP button -

2. "confirm" that you're pushing something to "production" -

3. ensure that you've actually prepared for verification (which isn't all that bad to do, to be honest) -

4. and finally, rest while the verification is in progress – until, of course, there are some iterations you may have to do that the api oauth dev verification team asks you to -

And that's when the fun really begins 😬 in my case, I had to address the following objections -

  1. make the privacy policy page for this add-on a lot better -

While my add-on does make use of 2 sensitive scopes, it doesn't make use of any restricted scopes but I went on a limb here (by reading between the lines) and did everything that I had to assuming that I was indeed making use of restricted scopes instead. Here are some of the changes that I made after this email -

  • added a limited use disclosure on the privacy policy page, as described here
  • on the add-on homepage, added a blockquote with the link to the privacy policy page (prior to that, the privacy policy link was the very end of the page – which in retrospect, was my bad)
  • made my words more explicit about data not being stored anywhere except being displayed on the add-on interface + it not being accessible by anyone except the user
  • while individual explanation to scope already existed in the images displayed on homepage, i also added text based explanation against every scope on the privacy policy page too (which i think make a lot more sense as well)

2. create a new demo video

to which i created a new video and ensured that my response captured everything (point-by-point) as was requested in the email -

you may struggle trying to get the client id being shown in the url of the oauth consent screen but just expand the entire window to its' full size limit and navigate to the very end of the url by double-clicking it (the cursor wont show-up as the url, in that pop-up, would not be an editable one)

Overall, this process mostly felt like an art which may (at times) require you do a little more than what's documented. I can only imagine the trouble folks who may use restricted scopes go through but try and keep you cool and continue engaging the review team as simply as you can 😇

Genuinely happy moment amidst all this -

add-on listing approval process

unfortunately, there still isn't an email notification that's triggered at the moment you submit your add-on for review and so, it starts with waiting (mostly) -

In about 3.5 days post submission, i received an email + an invite to comment on a Google Doc towards the review (which isn't exactly how I'd expected it would be but that was cool).

I don't suppose there was much to do here except to share an explanation towards the behavior of the Add-on while it was being executed on Slides and Docs but in the meantime, I suspect my Add-on was marked as rejected 😭 (i can see from the review teams' POV, it was with good reasons).

Amidst all the panic, I forgot to take a snap of what a rejected Workspace Marketplace listing status looks like but here's some context on what happened -

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 (Gmail, my favourite) -

function gmailData() {
  const mailCount = {};

  const labels = Gmail.Users.Labels.list(`me`).labels
    .filter(label => label.type == 'system')
    .forEach(function (label) {
      const labelName = label.name;
      const labelMeta = Gmail.Users.Labels.get(`me`, labelName);

      mailCount[labelName] = {
        "messagesTotal": labelMeta.messagesTotal,
        "messagesUnread": labelMeta.messagesUnread,
        "threadsTotal": labelMeta.threadsTotal,
        "threadsUnread": labelMeta.threadsUnread,
      };

    });

  console.log(mailCount);
  return mailCount;
}
gmail.gs

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.

Known limitations

  • takes too long – while this should not be the case with Gmail, almost all the other services' (viz. Calendar, Drive, Sheets, Docs & Slides) data need to be retrieved by paginating across the entirety of an account's lifecycle. What this means is if you've had a Gmail / Workspace (G Suite) account for about a decade, then the APIs for all these services would try and fetch each and every resource (in bulk) but for that very time period, which could...take a while 😅
  • spinner keeps spinning (and nothing happens) – even during my Workspace listing process, the odd experience that I was getting to face was that the Add-on under Slides and Docs services did not actually load during first-run or post-install and to solve that, I had to simply refresh those docs at once and when I ran the Add-on again, it worked just as well as it did for the other services. I've absolutely no idea why as there are no errors thrown and there's absolutely no difference in which the add-on has been coded for these services (in comparison to that of Drive or Sheets)