License Notifier

  • 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
Google Workspace License Notifier
Play Video about Google Workspace License Notifier

Click on the copy button on script block below to copy the 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)
    }


}
				
			
Scroll to Top