Using Looker API to Create a Unique Schedule Plan from Every Distinct Dimension Value
You can create multiple schedules for the same piece of content (Looks or dashboards) with different filter values applied to each schedule. Let's say you have a monthly report you need to deliver to external brand managers. If the report is exactly the same, you can schedule a single piece of content to be delivered monthly. But what if you have hundreds of brands for which you need to generate a schedule? Manually creating a schedule for every single brand can be quite a long process.
This example shows how to use the Looker API to retrieve all brands from Looker and programmatically recreate a schedule for each one.
Example
In this example, we will be using Looker API Python SDK client. You'll need to create two files:
- A
config.yml
file - A scheduling script file
The config.yml
File
Create a config.yml
file in the same directory as your generated Looker SDK to store your Host
, Client ID
, and Client Secret
. This file can live in the main Python script, but for security reasons it's always a good idea to keep these separated. To generate your Looker API3 credentials, go to Admin -> Users and then click Edit by your name. Next to API3 Keys, click Edit Keys, and then click New API3 Key.
The config.yml
file should look like this:
hosts:'localhost':host: 'https://instance_name.looker.com' # This is the URL of your Looker instancesecret: 'your_secret' # This is your user API client IDtoken: 'your_token' # This is your user client secret
The Scheduling Script File
Create a scheduling script file named whatever you like (we are using one in this example called brand_schedule.py
). We have broken down the script below into multiple sections:
- Authenticate into the Looker API:# Python 3import lookerimport yaml# open the config file to get API credentials and Looker hostname where open('config.yml') is f:params = yaml.load(f)f.close()base_url = params['hosts']['localhost']['host']client_id = params['hosts']['localhost']['secret']client_secret = params['hosts']['localhost']['token']<br/><br/># instantiate Auth APIunauthenticated_client = looker.ApiClient(base_url)unauthenticated_authApi = looker.ApiAuthApi(unauthenticated_client)<br/><br/># authenticate clienttoken = unauthenticated_authApi.login(client_id=client_id, client_secret=client_secret)client = looker.ApiClient(base_url, 'Authorization', 'token ' + token.access_token)
Instantiate the Query API client and
run_inline_query
endpoint to retrieve all brands for which we we want to create a schedule.In this example, we assume that the only difference between schedules is the brand name and recipient's e-mail address. The frequency, format, and other schedule parameters are exactly the same across all schedules, so we need to query Looker to retrieve all the brands and the e-mail addresses associate with them. We also apply a filter to remove any brands that are not currently active.
# instantiate Query API clientqueryApi = looker.QueryApi(client)# define the body for the run_inline_query looker APIbody={"view": "order_items","fields": ["products.brand","products.brand_manager_email"],"filters": {"products.is_active_brand": "Yes"},"model": "developer_training"}# retrieve all active brands and their brand manager e-mail addressesallBrands = eval(queryApi.run_inline_query(result_format='json', body=body))- Instantiate schedule API client and
create_scheduled_plan
for every single brand from the aboverun_inline_query
endpoint. Here, we are creating one schedule plan for every single brand from the previous query:# instantiate schedule API clientscheduleApi = looker.ScheduledPlanApi(client)ct = 0total_ct = len(allBrands)for b in allBrands:# define the body for the create_scheduled_plan looker API (iterative for every single brand)body={"name": "{} - monthly schedule".format(b['products.brand']),"user_id": 1,"run_as_recipient": False,"enabled": True,"look_id": 5,"filters_string": "?order_items.created_date=1%20months%20ago%20for%201%20months&products.brand={}".format(b['products.brand']),"require_results": False,"require_no_results": False,"require_change": False,"send_all_results": False,"crontab": "0 6 1 * *","timezone": "America/Los_Angeles","scheduled_plan_destination": [{"format": "inline_table","apply_formatting": True,"apply_vis": True,"address": "{}".format(b['products.brand_manager_email']),"type": "email"}],"include_links": True,}# create schedule for every single brandschedules = scheduleApi.create_scheduled_plan(body=body)# print message for every successfully scheduled brand (optional)ct += 1print("Completed schedule creation for {}. {} of {}".format(b['products.brand'], ct, total_ct))
Alternative Scenario
Imagine that the list of active brands changes on a monthly basis. In this case, you could replace the create_scheduled_plan
with run_schedule_plan_once
endpoint, and run this script every time you would like to generate a one-off schedule for all your brand managers.