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:

  1. Navigate to Extensions -> Apps script
  2. Insert the entire code into the Code.gs (replace the existing content)
    1. Find the script attached on the post.
  3. Save
  4. Refresh or reopen the sheet.
  5. New tab named Keboola will appear.
  6. Hit reset settings.
  7. Once you click Run orchestration you will be asked to provide url to your orchestration + Storage token (that will be saved for next run)
  8. 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()
}