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 instance secret: 'your_secret' # This is your user API client ID token: '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 3 import looker import 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']
# instantiate Auth API unauthenticated_client = looker.ApiClient(base_url) unauthenticated_authApi = looker.ApiAuthApi(unauthenticated_client)
# authenticate client token = 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 client queryApi = looker.QueryApi(client)
# define the body for the run_inline_query looker API body={ "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 addresses allBrands = eval(queryApi.run_inline_query(result_format='json', body=body)) - Instantiate schedule API client and
create_scheduled_plan for every single brand from the above
run_inline_query
endpoint. Here, we are creating one schedule plan for every single brand from the previous query:# instantiate schedule API client scheduleApi = looker.ScheduledPlanApi(client)
ct = 0 total_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 brand schedules = scheduleApi.create_scheduled_plan(body=body)
# print message for every successfully scheduled brand (optional) ct += 1 print("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.