Using Looker API to Create a Unique Schedule Plan from Every Distinct Dimension Value

[aleks1]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

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.ymlfile 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:

1. 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)

2. 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))    

3. 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.