Creating new users through Google Sheets using Looker API

As discussed in this article about custom Google Sheets functions, there’s a ton of useful things you can do from Google Sheets that integrate with Looker’s API. One of these is to create new Looker users straight from Sheets, and for example integrate this with a mail merge workflow, so you can send a customised welcome email. The script below shows how to create new users in Looker.

Getting started

• If you don’t already have API credentials for your Looker, get them first through Admin > Users or reach out to a Looker admin in your organisation.


• Similar to this article, we’re going to create a custom formula we can use in Sheets, using =CREATE_LOOKER_USER(email, roles). So open up your spreadsheet and navigate to Tools > Script editor. Copy paste in the script below in the editor, you can remove the dummy function myFunction().


• At the top, change the base URL to your company domain for Looker, and add in your API credentials (client ID & secret).


• Hit save and give the project a name.


• Navigate back to your spreadsheet.

It’s easy to reuse this flow in a customised script, and integrate it. Here’s the logic:

Flow

1. Check if the user already exists


2. If not, create a new empty user (POST /api/3.0/users)


3. Add an email address to that new user (POST /api/3.0/users/{user_id}/credentials_email)


4. Add roles to the user (optional, PUT /api/3.0/users/{user_id}/roles)


5. Get the password reset URL and extract the token (POST/api/3.0/users/{user_id}/credentials_email/password_reset)


6. Construct the “setup” URL so the new user can set up their account (https://customer.looker.com/account/setup/' + reset_token)

Demo

Script

var BASE_URL = 'XXX'; // e.g. https://customer.looker.com:19999/api/3.0

var CLIENT_ID = 'XXX';

var CLIENT_SECRET = 'XXX';



/**

 * Creates a new user in Looker

 *

 * @param {string} email The user email address

 * @param {string} roles Comma-separated roles (e.g. "1,2,3")

 * @return The user setup link

 * @customfunction

 */

function CREATE_LOOKER_USER(email, roles) {

    if (email.match(/^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$/g) == null) {

        return "Not a valid email address.";

    } else {

        var existing_user = checkExistingUser(email);

        if (existing_user.length == 0) {

            var user_id = createNewUser();

            addEmail(email, user_id);

            if (roles != null || roles != "") {addRoles(user_id, roles)};

            var reset_token = getPasswordResetToken(user_id);

            var setup_url = BASE_URL.split(/(:19999)/)[0] + '/account/setup/' + reset_token;

            return setup_url;

        } else {

            return "No new user created, user " + email + " already exists!";

        }

    }

}



// checks if a user with the same email_address already exists

function checkExistingUser(email_address) {

    var options = {'method': 'get','headers': {'Authorization': 'token ' + login()}};

    var existing_user = UrlFetchApp.fetch(BASE_URL + "/users/search?email=" + encodeURIComponent(email_address), options);

    existing_user = JSON.parse(existing_user.getContentText());

    return existing_user;

}



// creates new empty Looker user

function createNewUser() {

    var options = {'method': 'post','headers': {'Authorization': 'token ' + login()},'payload': JSON.stringify({})};

    var new_user = UrlFetchApp.fetch(BASE_URL + "/users", options);

    var user_id = parseInt(JSON.parse(new_user.getContentText()).id);

    return user_id;

}



// adds an email address to an existing user

function addEmail(email, user_id) {

    var options = {'method': 'post','headers': {'Authorization': 'token ' + login()},'payload': JSON.stringify({'email': email})};

    var response = UrlFetchApp.fetch(BASE_URL + "/users/" + user_id +"/credentials_email", options);

}



// adds role(s) to an existing user

function addRoles(user_id, roles) {

    if(typeof roles !== "undefined") {

        var roles_array = roles.toString().split(",").map(function(role) {return role.trim()});

        var options = {'method': 'put','headers': {'Authorization': 'token ' + login()}};

        options.payload = JSON.stringify(roles_array)

        var response = UrlFetchApp.fetch(BASE_URL + "/users/" + user_id + "/roles", options);

    } else {

        Logger.log("No roles added.");

    }

}



// get a password reset URL, the token can be reused for setup

function getPasswordResetToken(user_id) {

    var options = {'method': 'post','headers': {'Authorization': 'token ' + login()}};

    var response = UrlFetchApp.fetch(BASE_URL + "/users/" + user_id +"/credentials_email/password_reset", options);

    var reset_url = JSON.parse(response.getContentText()).password_reset_url;

    var reset_token = reset_url.split('/').pop(); // get the reset token only

    return reset_token;

}



// API login call

function login() {

    var post = {'method': 'post'};

    var response = UrlFetchApp.fetch(BASE_URL + "/login?client_id=" + CLIENT_ID + "&client_secret=" + CLIENT_SECRET, post);

    return JSON.parse(response.getContentText()).access_token;

}