Google Sheet "Front End" for Robot

Share your robot!
1

We wanted to call a Robocorp process based on a google sheet (which extracted reports from a 3rd party system).

The sheet contained a list of clients and the administrator could then select which client they wanted to run the automation for.

What we built:

  • A custom menu on google sheet
  • A sidebar which contained a drop down list (based on values in sheet)
  • A robot which accepted a work item in the control room
  • An API call to the robot from Google Sheet

Below if the extract of the function from Google App Script:

function getRobocorpData(client, end, start, period, sheet) {
    // Submit the data to Robocorp
    var url = "https://api.eu1.robocorp.com/process-v1/workspaces/XXXXXXXX/processes/XXXXXX/runs?";
    var data = { 'client': client, 'end': end, 'start': start, 'period': period, 'sheet_id': sheet };
    payload = JSON.stringify(data);

    // Set up the call and allow exceptions so we can interrogate and give feedback to sheet user
    var options = {
        method: "post",
        headers: {
            contentType: "application/json",
            "Authorization": "RC-WSKEY XXXXXXXXXXXXX"
        },
        payload: payload,
        muteHttpExceptions: true
    };

    // Call Robocorp API
    var response = UrlFetchApp.fetch(url, options);

    if (response.getResponseCode() != 200) {
        console.log(response.getContentText());
        SpreadsheetApp.getActive().toast("Error calling the Robocorp API.", "⚠️ Error");
        return;
    } else {
        console.log(response.getContentText());
        SpreadsheetApp.getActive().toast("Report generation started.", "đź‘Ť Success");
    };

};

Hope this helps others that are looking to integrate Google Apps from a “front end” perspective.

We chose this approach as then there is nothing for the end users to “install” on their workstation.

Regards,
Warren

3 Likes