• License Notifier is a Google Apps script based utility to track Google Workspace licenses
  
  
• It also sends you alerts when you reach (configurable) defined license threshold
  
  
• Simply copy the Google sheet (includes script) below, watch video, done

You can copy my google sheet with the script here

‍

If you can organization has disabled anyone with the link files for incoming sharing, you can copy/paste following script :

----------------------------------------------------

// get all license assignments
function getLicenses() {

   // get access to the google sheet where we'll write the output
   const sheet = SpreadsheetApp.getActive().getSheetByName("License Notifier")
   const domainName = Session.getActiveUser().getEmail().replace(/.*@/, "") // or "yourdomain.com"
   const fileArray = [
       ["User Email", "Assigned License"]
   ]

   /*
   productIds are required to get license assignment list, following product ids are taken from Google ent license
    api https://developers.google.com/admin-sdk/licensing/v1/how-tos/products
    */

   const productIds = ["Google-Apps", "101031", "Google-Drive-storage", "Google-Vault", "101001", "101005", "101033"]
   productIds.forEach(product => {
       /*  call enterprise license api to get list of license assignment in our domain looping for all google productsIds above
       make sure to add Admin license manager service
        */

       var pageToken;

       do {
           let optionalArgs = {
               maxResults: 100,
               pageToken: pageToken
           }

           const page = AdminLicenseManager.LicenseAssignments.listForProduct(product, domainName, optionalArgs)
           const licenses = page.items
           pageToken = page.nextPageToken;
           licenses.forEach(prod => {
               fileArray.push([prod.userId, prod.skuName])

           })
       } while (pageToken)
   })

   // write the licenses assignment outback back to our google sheet
   sheet.getRange(1, 1, fileArray.length, 2).setValues(fileArray)

   // Notification to Admins
   const threshold = sheet.getRange("J2").getValue(); // change the column/range as /if required
   const emailToRemind = sheet.getRange("i2").getValue(); // change the column/range as /if required
   const getRows = sheet.getRange("H:H").getValues().filter(String).length - 1; // change the column/range as /if required
   const licensesLeftArray = sheet.getRange(2, 8, getRows, 1).getValues(); // change the column/range as /if required

   // change the email subject and/or body as required
   const emailSubject = "Time to purchase Google Workspace licenses"
   const emailBody = `Hey Google Workspace Admin/s,
We just realized that our Google Workspace Licenses are almost over, we need to purchase more licenses, please check the folloing sheet for details:
https://docs.google.com/spreadsheets/d/11mga9ySf5JX_znrVZ1Ip15hRaMK7TVforT7Hd7J8Zp4/edit#gid=0

Regards,
Goldy Arora
Google Workspace Certified
www.goldyarora.com`

   // send email if licenses left for any sku are equal or below threshold defined in google sheet

   if (licensesLeftArray.some(licensesLeft => licensesLeft <= threshold)) {
       MailApp.sendEmail(emailToRemind, emailSubject, emailBody)
   }

}

----------------------------------------------------

‍