Hi all, some of you are already using the Google Apps script that runs in Google sheet and creates option for the user to trigger a Keboola Orchestration directly from the sheet. We have created and updated version of that which supports the new queue, e.g. Orchestrations v2 or Flows.
Here's a guide on how to deploy it in you sheet:
- Navigate to Extensions -> Apps script
- Insert the entire code into the
Code.gs
(replace the existing content)- Find the script attached on the post.
- Save
- Refresh or reopen the sheet.
- New tab named
Keboola
will appear. - Hit reset settings.
- Once you click
Run orchestration
you will be asked to provide url to your orchestration + Storage token (that will be saved for next run) - Now you can trigger your orchestration to collect the data directly from the sheet


/*
* @OnlyCurrentDoc
*/
function runKeboola(skip_wait) {
try {
var ui = SpreadsheetApp.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-v2|flows)\/)(\\d*)");
var id = reg_exp1.exec(orch_url)[3];
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-v2|flows)\/)(\\d*)");
var id = reg_exp1.exec(orch_url)[3];
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://queue.%s/jobs", region);
var data = {
"component": "keboola.orchestrator",
"mode": "run",
"config": id
}
var params = {
method: "POST",
'contentType': 'application/json',
// Convert the JavaScript object to a JSON string.
'payload' : JSON.stringify(data),
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://queue.%s/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();
SpreadsheetApp.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 = SpreadsheetApp.getUi();
properties.deleteAllProperties();
ui.alert("Properties reset");
}
function runAndWait() {
runKeboola(false);
}
function runAndForget() {
runKeboola(true);
}
function parse_url() {
try {
var ui = SpreadsheetApp.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-v2\/)\\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() {
SpreadsheetApp.getUi()
.createMenu("Keboola")
.addItem("Run Orchestration", "runAndWait")
.addItem("Reset Settings", "reset")
// .addItem("URL Parse Test", "parse_url")
.addToUi()
}
It is not working with flows, Davide.