Generating a PowerPoint presentation from all Looks in a Space

[jonathan_walls]

A nice example of how you can use Looker’s APIs to expand the capabilities of the data platform is to generate new kinds of content. Even data-driven businesses sometimes want to put things into a PowerPoint presentation:

With just a little bit of scripting, you can automatically generate a slide deck by downloading PNG images via the API.

1. Install the required Python modules.
This code uses the python-pptx module. There is good documentation online, including a lot more you can do with PowerPoint slides: http://python-pptx.readthedocs.io/.

While there isn’t an official Python Looker SDK client, you can install my build (from using Swagger Codegen) directly from GitHub: https://github.com/ContrastingSounds/looker_sdk_30/.

Instructions for building your own client can be found here: Generating Client SDKs for the Looker API.

Note that for Python 3 users there is a slight glitch in Swagger Codegen 2, which doesn’t handle APIs that return different content types very well. See the change history on the GItHub example above to see the necessary changes.

pip install python-pptx
pip install git+https://github.com/ContrastingSounds/looker_sdk_30

2. Generate your Looker API key
See the instructions under API3 Keys on the Users Admin documentation.

3. Prepare an empty PowerPoint slidedeck to act as a template
When we’re producing a presentation for others, we usually want to start with some standard branding and colour palettes. You can re-use an existing slide deck by just creating a copy and deleting all the slides – the deck will be empty, but all the slide layouts will be available. You’ll need the index number of the layout to use for the generated slides.

from pptx import Presentation
from pptx.util import Cm

pptx = Presentation(powerpoint_template)
for idx, layout in enumerate(pptx.slide_layouts):
    print(idx, layout.name)

In a default PowerPoint file, the available layouts will be as listed below. It’s probable that the exact contents of your own slide deck are different, but you’ll usually be able to find a layout still called something like “Title Only”.

0 Title Slide
1 Title and Content
2 Section Header
3 Two Content
4 Comparison
5 Title Only
6 Blank
7 Content with Caption
8 Picture with Caption
9 Title and Vertical Text
10 Vertical Title and Text

4. Set the number of the title layout

title_only_layout = pptx.slide_layouts[5]

5. Set the parameters and authenticate the client
Note that you need the number of the Space you want to convert to a PowerPoint. This can be found at the end of its URL:
“https://your-company.looker.com/spaces/351”

import looker_client_30 as looker_client

looker_instance = 'your-company.looker.com'
target_space = 351 # 'Period over Period' Space on the Looker instance
powerpoint_template = 'looker_template.pptx'

client_id = 'xxxxxxxx'
client_secret = 'xxxxxxxx'

# instantiate Auth API
unauthenticated_client = looker_client.ApiClient(configuration=None)
unauthenticated_client.configuration.host = f'https://{looker_instance}:19999/api/3.0/'
unauthenticated_authApi = looker_client.ApiAuthApi(unauthenticated_client)

# authenticate client
token = unauthenticated_authApi.login(client_id=client_id, client_secret=client_secret)
client = looker_client.ApiClient(header_name='Authorization', header_value='token ' + token.access_token)
client.configuration.host = f'https://{looker_instance}:19999/api/3.0/'

6. Get the details of the Space and the Looks it contains

space = looker_client.SpaceApi(client).space(target_space)
looks = looker_client.SpaceApi(client).space_looks(target_space)

7. Generate the PowerPoint

The PowerPoint itself is fairly simple to generate. Note that you might want to remove the print() statements or replace them with proper logging.

The loop below will:

1. Generate an API request for each Look, saving the PNG image generated
2. Add a new slide, give it the same title as the Look
3. Set the slide’s title with the name of the Look
4. Add a hyperlink back to the Look itself on your Looker instance

The presentation will then be saved to disk, with the same name as the original Space.

for idx, look in enumerate(looks):
    print(idx, look.id, look.title)
    
    look_request = {
        "look_id": look.id, 
        "result_format": 'png', 
        "image_width": 960, 
        "image_height": 540
    }
    try:
        image = looker_client.LookApi(client).run_look(**look_request)
        image_file = ''.join([str(look.id), '.png'])
        shutil.move(image, image_file)
    except:
        print(f'Look failed {look.id}: {look.title}')
        image_file = None
        
    pptx.slides.add_slide(title_only_layout)
    pptx.slides[idx].shapes.title.text = f'{look.title} – Look #{look.id}'
    
    try:
        pptx.slides[idx].shapes.add_picture(image_file, Cm(5.4), Cm(4.95), width=Cm(23)) # image, left, top, width
    except:
        print('Failed to add image to slide')
        
    text_box = pptx.slides[idx].shapes.add_textbox(Cm(1.0), Cm(17.6), width=Cm(23.0), height=Cm(1.0))

    p = text_box.text_frame.paragraphs[0]
    run = p.add_run()
    run.text = ''.join([f'https://{looker_instance}/looks/', str(look.id)])
    run.hyperlink.address = run.text

pptx.save(space.name + '.pptx')