Google Directory API ?

[MarkBriscoe]

Hi everyone, random question - was some level of Google Directory API integration included recently in AppSheet?

I see the Chat Bot is integrated now which is great; but I swear I read something about being able to poll Google Directory and thought, "that will help me with a new offboarding project" but cannot find any references.

The more I type, I suspect not, but let me know if I am wrong! 😄 otherwise ill going back to plan B with an App Script

Thanks 
M

[Ratatosk]

I don't think so, at least not for Core users.

I use apps script, and it works flawlessly. Much more customizable also.

[MarkBriscoe]

Thanks Ratatosk,

I think this is what I originally read and mistook this for some full directory api: https://support.google.com/appsheet/answer/13604441

Might be handy for someone to know if they stumble across this thread 👍

[Ratatosk]

Huh! This is news to me! Thanks!

[Ratatosk]

Here's my script if it's any help. It also includes thumbnails (and a replacement photo if user has not added any), which directory does not support.

Do notice that I have custom fields added in admin to each user as well. So those needs to be removed from the arrays.

EDIT: Updated script

const DEFAULT_PHOTO = "REPLACE WITH BASE64 IMAGE (REMEMBER MAX CELL DATA SIZE)"; // Please replace with actual base64 string
const BASE_URL = "https://admin.googleapis.com/admin/directory/v1/users/";
const DOMAIN = 'MYCOMPANY.COM';

function listAllUsers() {
    const ss = SpreadsheetApp.getActive();
    let pageToken;
    let listArray = createHeaderRow();

    do {
        const page = fetchUserPage(DOMAIN, pageToken);
        const users = page.users;

        if (users) {
            users.forEach(user => listArray.push(processUser(user)));
        }

        pageToken = page.nextPageToken;
    } while (pageToken);

    writeUsersToSheet(ss, listArray);
}

function createHeaderRow() {
    return [['Full Name', 'First name', 'Last name', 'E-mail', 'Phone', 'Title', 'Department', 'Employeenumber', 'Photo']];
}

function fetchUserPage(domain, pageToken) {
    return AdminDirectory.Users.list({
        domain: domain,
        orderBy: 'givenName',
        pageToken: pageToken
    });
}

function processUser(user) {
    const photoData = fetchUserPhoto(user.primaryEmail);
    const photo = photoData ? `data:image/jpeg;base64,${photoData.replace(/_/g,'/').replace(/-/g,'+')}` : DEFAULT_PHOTO;

    const department = getUserProperty(user, 'department') || "";
    const title = getUserProperty(user, 'title') || "";
    const userID = getUserProperty(user, 'externalIds') || "";

    return [
        user.name ? user.name.fullName : "", 
        user.name ? user.name.givenName : "", 
        user.name ? user.name.familyName : "", 
        user.primaryEmail || "", 
        generatePhonesCell(user.phones || []), 
        title, 
        department, 
        userID, 
        photo
    ];
}

function fetchUserPhoto(primaryEmail) {
    const url = `${BASE_URL}${primaryEmail}/photos/thumbnail`;
    const headers = {"Authorization": "Bearer " + ScriptApp.getOAuthToken()};
    const parameters = {
        'method': 'GET',
        'muteHttpExceptions': true,
        'headers': headers
    };

    const response = UrlFetchApp.fetch(url, parameters);
    const resultData = JSON.parse(response);

    return resultData.photoData;
}

function getUserProperty(user, property) {
    try {
        if (property === 'externalIds') {
            return (user.externalIds && user.externalIds.length > 0) ? user.externalIds[0].value : "";
        } else {
            return (user.organizations && user.organizations.length > 0) ? user.organizations[0][property] : "";
        }
    } catch (e) {
        console.log(e.message); // Log error for debugging
        return ""; // Return an empty string or a custom error message
    }
}

function generatePhonesCell(phones) {
    return phones.map(phone => phone.value).join('\n');
}

function writeUsersToSheet(ss, listArray) {
    let outputSheet;
    try {
        outputSheet = ss.getSheetByName('googleUsers');
    } catch (err) {
        outputSheet = ss.insertSheet('googleUsers', 2);
    }
    
    outputSheet.clear();
    outputSheet.getRange(1, 1, listArray.length, listArray[0].length).setValues(listArray);
    deleteEmptyRows(outputSheet); // Delete empty rows after writing data
}

function deleteEmptyRows(ss) {
    const maxRows = ss.getMaxRows(); 
    const lastRow = ss.getLastRow(); 
    if (maxRows > lastRow) {
        ss.deleteRows(lastRow + 1, maxRows - lastRow);
    }
}

[Aurelien]

@Ratatosk neat! 🤓