Populating presentation (power point / google slides) with data

I needed to automate generation of a presentation deck with data (same deck being used repeatedly and tons of time spent on populating the excel sheet backing it). I realized that if the deck is built in Google Slides, it can use reports and tables from Google Sheet, which we have writer to, so...

The source in this case was Hubspot (but it can be anything, of course). I used the Hubspot to CRM scaffold to save time on the initial transformations. Then added transformation of my own that generated the tables as I needed them. From that, populated google sheet, created reports (pro-tip - always good to keep the tabs that are being updated from Keboola separate and use "filter" function to take them into a separate tab that then contains the reports). Then inserting the reports into GoogleSlides, keeping them linked to the source. Now the customer can either use the GoogleSlides deck, or download as a static PowerPoint file to add notes etc.

Then I thought that it would be nice to be able to refresh data on demand, so I modified the GoogleSheet orchestration-triggering script we already had (see original article for details) to work in GoogleSlides. This whole setup took under 2 hours (!), with defining and creating the reports taking most of it. And because there's not much data and the whole orchestration takes about 10 minutes, it actually runs in the PAYG/Freemium plan completely for free for the customer (they don't need fresh data every day, but even if they did, still would fit under the 5 credits per month).

I suppose similar thing could be achieved with Excel / Sharepoint or OneDrive / PowerPoint combo. Maybe someone will try and publish here as well :).

Here's the Google Script (Slides version):
 

/*

* @OnlyCurrentDoc

*/

function runKeboola(skip_wait) {

try {

var ui = SlidesApp.getUi();

var properties = PropertiesService.getDocumentProperties()


 

var orch_url = properties.getProperty("orch_url")

if (orch_url === null) {

var orch_url = ui.prompt("Enter the whole orchestration URL from Keboola Connection").getResponseText();

if (orch_url === "") return;

properties.setProperty("orch_url", orch_url);

 

var reg_exp1 = new RegExp("(?<=orchestrations\/)\\d*");

var id = reg_exp1.exec(orch_url);

ui.alert("Orchestration ID check: " + id);

properties.setProperty("Parsed Orchestration ID", id);

 

var reg_exp2 = new RegExp("(?<=connection.).*keboola.com");

var region = reg_exp2.exec(orch_url);

ui.alert("Parsed Base URL check: " + region);

properties.setProperty("region", region);


 

 

} else {

orch_url = properties.getProperty("orch_url");


 

var reg_exp1 = new RegExp("(?<=orchestrations\/)\\d*");

var id = reg_exp1.exec(orch_url);

properties.setProperty("Parsed Orchestration ID", id);

 

var reg_exp2 = new RegExp("(?<=connection.).*keboola.com");

var region = reg_exp2.exec(orch_url);


 

}

var token = properties.getProperty("token")

if (token === null) {

var token = ui.prompt("Enter API token").getResponseText();

if (token === "") return;

properties.setProperty("token", token);

} else {

token = properties.getProperty("token");

}

var isFinished = false;

var res = '';

var url = Utilities.formatString("https://syrup.%s/orchestrator/orchestrations/%s/jobs", region, id);

var params = {

method: "POST",

contentType: "application/json",

headers: {

"x-storageapi-token": token

}

}

Logger.log(params);

var res = JSON.parse(UrlFetchApp.fetch(url, params).getContentText());

var jobID = res.id;

while (isFinished == false){

var url = Utilities.formatString("https://syrup.%s/orchestrator/jobs/" + jobID, region);

var params = {

method: "GET",

contentType: "application/json",

headers: {

"x-storageapi-token": token

}

}

var res = JSON.parse(UrlFetchApp.fetch(url, params).getContentText());

isFinished = Boolean(res.isFinished);

var htmlOutput = buildStatusModal(res.status.toUpperCase());

if (prevStatus != res.status.toUpperCase()) {

var prevStatus = res.status.toUpperCase();

SlidesApp.getUi().showModelessDialog(htmlOutput, 'Keboola Orchestration');

}

;

if (skip_wait && res.status.toUpperCase()==="PROCESSING") {

isFinished = true;

Utilities.sleep(1000);

ui.alert("Orchestration started successfuly");

}

else {

Utilities.sleep(3000);

}

}

} catch (e) {

if (e.message.indexOf("has 1 waiting job")>-1) {

ui.alert("Another orchestration already waiting");

}

else {

ui.alert("Error: "+ e.message);

}

}

}


 

function buildStatusModal(stage) {

var perc = 0;

switch (stage) {

case "WAITING":

perc = 25;

break;

case "PROCESSING":

perc = 75;

break;

case "SUCCESS":

perc = 100;

break;

default:

perc = 0;

}

var html =

'<html>' +

'<head><link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous"></head>' +

'<body>' +

'<div class="progress">' +

'<div class="progress-bar progress-bar-striped progress-bar-animated" role="progressbar" aria-valuenow="' + perc + '" aria-valuemin="0" aria-valuemax="100" style="width: ' + perc + '%">' + stage + '</div>' +

'</div>' +

' <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>' +

'<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>' +

'</body>';

var htmlOutput = HtmlService

.createHtmlOutput(html)

.setWidth(300)

.setHeight(50);

return htmlOutput

}



 

function reset() {

var properties = PropertiesService.getDocumentProperties();

var ui = SlidesApp.getUi();

properties.deleteAllProperties();

ui.alert("Properties reset");

}



 

function runAndWait() {

runKeboola(false);

}


 

function runAndForget() {

runKeboola(true);

}


 

function parse_url() {

try {

var ui = SlidesApp.getUi();

var properties = PropertiesService.getDocumentProperties();

var my_url = ui.prompt("Enter orchestration url").getResponseText();

ui.alert("Check: " + my_url);

var reg_exp1 = new RegExp("(?<=orchestrations\/)\\d*");

var tmp_id = reg_exp1.exec(my_url);

ui.alert("ID: " + tmp_id);

var reg_exp2 = new RegExp("(?<=connection.).*keboola.com");

var tmp_url = reg_exp2.exec(my_url);

ui.alert("Base URL: " + tmp_url);

} catch (e) {

ui.alert("Error: "+ e.message);

}

}


 

function onOpen() {

SlidesApp.getUi()

.createMenu("Keboola")

.addItem("Run Orchestration", "runAndWait")

.addItem("Reset Settings", "reset")

// .addItem("URL Parse Test", "parse_url")

.addToUi()

}  

Enjoy!

2
1 reply