License Notifier

				
					// 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)
    }


}