Hey Looker Community,
A few times in recent history, I’ve had to come up with a way to automatically create users in Looker, whether it was so that a form-based signup for an event didn’t have to be manual, to automate specific types of users being created, or just cause I was lazy. I figured the world at large might benefit from my work.
Bear in mind that I’m just a curious Community Manager, not an API wizard— So if you see a mistake, or can think of a way better way to do this, please share it in a reply! This is a starting point, not the end-all-be-all. That said, here’s my workflow!
I also threw the full script up on github.
This kinda has to be step one, for obvious reasons. I’m sure there’s a zillion ways to do this, but for a rolling signup, I think the simplest is a google form that dumps emails/names/user attributes into a google sheet.
At Looker, we often get requests to access our learning environment, and because we don’t necessarily want everyone to automatically be granted access, we actually just manually enter their names into a google sheet (pulling back the curtain there!). You might also have some kind of security considerations that would make you not want to just let anyone use a form, but I leave that puzzling up to you. (You could also use the google forms feature to automatically collect email addresses, and only allow addresses from your organization to proceed).
This is the main part of the process I’ll let you figure out yourself, but for the rest of this walkthrough, I’ll assume that email addresses for your new users will be input into a google sheet on a rolling basis.
There’s also a couple options here (two good ones, I guess).
In this lazier project, I used Zapier to yank emails out of a google sheet and pipe them to my script.
This flow looks like:
If you don’t want to use Zapier, you can also do this in python/the language of your choice directly.
There’s a python quickstart here: https://developers.google.com/sheets/api/quickstart/python.
And a package that’s not half bad here: https://github.com/burnash/gspread.
You could set your script to run every 5 or 10 minutes, and check for any values in column A each time, moving them to column B afterwards.
Example using the gspread package and python:
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def get_new_users():
scope = ['https://spreadsheets.google.com/feeds']
creds = --generate creds by manner of your choice: https://gspread.readthedocs.io/en/latest/oauth2.html--
client = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
sheet = client.open_by_url('docs.google.com/spreadsheets/d/spreadsheeturl')
worksheet = sheet.get_worksheet(0)
#Grab all values in column 1
new_users = worksheet.col_values(1)
return new_users
The only practical difference between these two methods would be that if you use zapier, your microservice (this is a big word, and I’m not sure what it means) purely needs to be reactive and accept POST requests. If you end up using gspread or similar to check for results, then you need to have it run on a schedule actively.
My examples rely on a POST from Zapier. I told you I was lazy 😄!
Now we have to make a script that creates new Looker users and properly provisions them. There’s a bunch of steps here. I’m going to be referencing the python SDK, but the endpoints will be the same regardless of if you’re using cURL or Ruby.
Let’s walk through the full flow first, and then figure out what each function does. I set my script up as a flask app. Here’s the full script
#Housekeeping
import lookerapi as looker
from flask import Flask, request, abort
import requests
import json
import os
import re
import time
from lookerapi.rest import ApiException
from pprint import pprint
from provisioning_utils import create_user, apply_role, get_email_setup, send_mail
#instantiate flask app
app = Flask(__name__)
# I named the URL to be "usr_gen", you can call it whatever you want.
# You'll probably want to restrict the methods to POST, like here
# You'll also probably want to add some basic secret-based authentication, at the very least.
@app.route('/usr_gen', methods=['POST'])
def usr_gen():
if request.method == 'POST':
data = request.get_json()
# I have zapier parse out the fields I want and send them pre-named
firstname = data['name'].split(' ',1)[0]
lastname = data['name'].split(' ',1)[1]
email = data['email']
# API creation process
base_url = 'https://your.looker.com:19999/api/3.0/'
#secrets are stored in environment variables
client_id = os.environ['apikey']
client_secret = os.environ['apisecret']
unauthenticated_client = looker.ApiClient(base_url)
unauthenticated_authApi = looker.ApiAuthApi(unauthenticated_client)
token = unauthenticated_authApi.login(client_id=client_id, client_secret=client_secret)
client = looker.ApiClient(base_url, 'Authorization', 'token ' + token.access_token)
#User Creation Process
user_id = create_user(client,firstname,lastname,email)
apply_role(client,user_id,role_id)
# If you want to add user attributes, add the user to groups, etc
# you'll probably want to add another function here
reset_url = get_email_setup(client,user_id)
send_mail(reset_url,email)
#Email send
print('User Created')
return '', 200
else:
abort(400)
Let’s break those functions out.
def create_user(client,firstname,lastname,email):
#Instantiate UserAPI
userApi = looker.UserApi(client)
newuser = {
"first_name": "{}".format(firstname),
"last_name": "{}".format(lastname),
"email": "{}".format(email)}
try:
# Create User
api_response = userApi.create_user(body=newuser)
userid = api_response.id
pprint('successfully created user {}'.format(userid))
except ApiException as e:
print("Exception when calling UserApi->create_user: %s\n" % e)
#Create credentials
try:
body = looker.CredentialsEmail(email = email)
api_response = userApi.create_user_credentials_email(userid, body=body)
pprint('successfully created user credentials')
except ApiException as e:
print("Exception when calling UserApi->create_user_credentials_email: %s\n" % e)
return userid
def apply_role(client,userid,roleId):
userApi = looker.UserApi(client)
body =[roleId]
try:
# Set User Roles
api_response = userApi.set_user_roles(userid, body)
pprint('successfully set role')
except ApiException as e:
print("Exception when calling userApi->set_user_roles: %s\n" % e)
2.5: If you want to set user_attributes here, you’ll want to create another function that hits the set_user_attribute_user_value endpoint: https://github.com/llooker/python_sdk/blob/master/docs/UserApi.md#set_user_attribute_user_value
def get_email_setup(client,userid):
userApi = looker.UserApi(client)
user_id = userid
try:
# Create Token
api_response = userApi.create_user_credentials_email_password_reset(user_id)
url = api_response.password_reset_url
pprint('successfully generated reset url')
#edit URL to be account setup
url = url.replace('password/reset','account/setup')
print('RESET URL = {}'.format(url))
return url
except ApiException as e:
print("Exception when calling UserApi->create_user_credentials_email_password_reset: %s\n" % e)
#You gotta import sendgrid— In the actual script, I do this all at the beginning.
import sendgrid
from sendgrid.helpers.mail import *
def send_mail(url,email):
#you need a sendgrid account + API keys to send stuff. I guess that's kind of a 'duh' thing.
sg = sendgrid.SendGridAPIClient(apikey=os.environ.get('SENDGRID_API_KEY'))
from_email = Email("theemailtobesentfrom")
to_email = Email(email)
subject = "Welcome to Looker "
content = Content("text/plain", "Whatever you want your email to say. Click this link to get set up {}".format(url))
mail = Mail(from_email, subject, to_email, content)
response = sg.client.mail.send.post(request_body=mail.get())
pprint('successfully sent reset email')
And hey presto, you’ve automatically configured a user/hundreds of users!
To run the whole shebang, you can just do
export FLASK_APP=app.py
flask run
or if you prefer gunicorn, gunicorn app:app
As I said in the intro, I’m sure there’s better and more efficient ways to do this, and I’m super eager to hear them 🙂 If someone has anything more streamlined, share it! Hopefully this helps out as a framework if you’re looking to build something similar. It’s worked well for me in the past.
As someone who’s not totally familiar with how Zapier works, I’m a bit confused how it fits into the example.
Does Zapier host your flask app? If so, do you have a link to some documentation??
Thanks!!!
I could have definitely made that more clear! I’ll edit the original post later today.
Zapier does not host my flask app— It just grabs the google form response and POSTs the data from it to the actual flask app that catches it, which I deployed on heroku.
Zapier has a POST action that lets you feed in values from the spreadsheet of responses:
Does that help? The major downside of doing it with Zapier in this way is that then you have 2 separate moving parts, but it makes authenticating into google sheets and grabbing the proper rows a lot easier.
You could probably also do it 100% in Zapier with python blocks, but I feel like once you start doing more complex things in Zapier, it actually gets more confusing and hard to troubleshoot than doing it yourself.
Got it… you have a flask app on heroku that zapier posts data to. Pretty cool! You could also use AWS Lambda or Google Cloud Functions for this. Pretty sweet!