Part 1: The basics

Part 1 of the tutorial introduces you to scripting in Robots and using the HighBond API to partially automate a process.

What will I learn?

You'll learn how to:

  • set up, create, and test a Python/HCL script in a HighBond robot

  • use the HighBond API to interact with a Diligent One object

  • use HCL to import and transform some data, and save the output to Results

Scenario: save output results to the Results app in Diligent One

Let's say you need to perform some recurring data analysis in Robots. After performing the analysis, you want to save the output results to the Results app for issue remediation or data visualization.

You want to schedule a Robots script containing the data analysis to run unattended on a nightly basis. You don't want to worry about whether a table exists in Results to receive the nightly output results, or have to manually retrieve a unique table ID. You want the script to automatically find the correct table, based on name, or create a new table if the named table does not exist.

In Part 1 of the tutorial we'll partially automate this end-to-end process. In Part 2 we'll fully automate it.

Note

We are going to use a table in Results to demonstrate how the HighBond API works. However, you can use the API to interact with many different types of Diligent One objects across multiple different Diligent One apps.

To learn more, scan the Core Resources in the HighBond API Reference to see which objects you can access.

Sign in to Diligent One and do some set-up tasks

The first thing you need to do is sign in to your organization's Diligent One instance and do some set-up tasks. The tasks are quick and easy to complete.

Just a reminder: you need to be a Launchpad System Admin, and have access to the Robots and Results apps, in order to perform all the tasks in this tutorial.

Sign in to Diligent One

  1. Go to www.highbond.com.
  2. Enter your Diligent One credentials (email and password).
  3. Click Sign in.
  4. If your company uses more than one instance in Diligent One, make sure the appropriate instance is active.

Create a collection and an analysis in the Results app

Create a collection and an analysis in Results to store the table that you are going to create. A collection is the top-level container in Results, and an analysis is the second-level container.

Note

In Part 2 of the tutorial, you will extend the script to automate this portion of the set-up so that you do not have to perform it manually.

Create a collection

  1. Click Switch Apps Switch Apps icon > Results to go to the Results app.
  2. From the Results home page, click Add collection.
  3. On the New collection page, name the collection Test collection 1 <your initials>.

    For example: Test collection 1 LM

    Important

    Make sure the collection name is unique. Results does not prevent identical collection names. Identical collection names will cause problems for the script you're going to create.

    Adding your initials to the end of the collection name should be sufficient to make it unique.

  4. Click Create collection.

    Result The new collection page opens and you see the list of members who have access to the collection.

Create an analysis

  1. Select the Tables tab and click Add analysis.
  2. On the New analysis page, name the analysis Test analysis 1, and click Create analysis.

    Result The new analysis opens and you see the message Your Analysis is Empty.

  3. In the analysis, click the settings button .
  4. In the browser address bar, copy the unique ID for the analysis and paste it in a text file for temporary storage.

Create a robot in the Robots app

Create a robot to contain the script that you are going to create.

  1. Open the Robots app in a separate tab:

    1. Click Switch Apps Switch apps icon.

    2. Right-click Robots and select Open link in new tab.

    Tip

    Having Results and Robots open in separate tabs is helpful later in the tutorial.

  2. Switch to the browser tab where Robots is open.

  3. Select HighBond Robots and click Create a HighBond robot.

    Note

    If you are a System Admin with a Professional subscription, you also have the option of creating a Workflow robot. For the purposes of the tutorial, you can create either a HighBond robot or a Workflow robot.

    Do not create an ACL robot. ACL robots are for use with scripts written with ACLScript and uploaded from Analytics.

  4. Enter a name for the robot such as Tutorial robot <your initials> and click Create a HighBond robot.

    For example: Tutorial robot LM

    Result The Robots script editor in the new robot begins the start-up process. You cannot run a script, or a script cell, until the start-up process is complete.

Create a HighBond API token in Launchpad

You need a HighBond API token to be able to use the HighBond API. You can create a new token or use an existing one. Safeguard the token like a password and avoid sharing it with others.

  1. Go to Launchpad:

    1. In the upper-left corner of the screen, right-click the name of your Diligent One instance.

    2. Select Open link in new tab.

    Tip

    Having Launchpad and Robots open in separate tabs is helpful for this part of the tutorial.

  2. Switch to the browser tab where Launchpad is open.

  3. In Launchpad, select Profile menu icon > Profile.
  4. In the Application tokens section, click Manage.

    The Manage API tokens page opens.

  5. Create a new token, or use an existing token:

    Create a new token

    1. Click Add token and select HighBond API.
    2. In the New HighBond API token panel, enter the following information:

      • Description Enter Token for Robots scripting tutorial

      • Token expiry Leave enabled

      • Expires in Enter 30

        Enter a greater number of days if you want the automation that you build in the tutorial to remain functional for a longer period of time.

      • Password Enter your Diligent One password

    3. Click Generate token.

      If the token is successfully generated, a confirmation appears with the token.

    4. Click Copy to copy the token.
    5. Either now or later, click the close button in the upper-right corner of the New HighBond API token panel.

      Important

      Save your token before you close the New HighBond API token panel. As a security best practice, you only have access to tokens when they are first created for the purpose of saving them in a secure place for future use. Once the panel is closed, tokens cannot be accessed again from Diligent One. If a token is lost or no longer available, a new one needs to be generated.

      Tip

      You may want to keep the token easily accessible until you have successfully created the password variable in the next section of the tutorial.

      Keep in mind that the token is like a revealed password and you should not leave it exposed longer than necessary.

    Use an existing token

    1. To use an existing token, access the token you saved in a secure place for future use.
    2. Optional. You can also create a new token if the token you previously used is no longer available. See Creating and managing HighBond access tokens.

Create and test the script in interactive mode

You create and test scripts in the Robots script editor. Manually running scripts or individual script cells in the script editor is considered interactive mode. You need to manually interact with the script in order to run it.

In Part 2 of the tutorial you'll schedule the script so that it runs unattended.

Note

If you are inactive in the script editor for more than a few minutes, the script editor process expires. If the script editor becomes unresponsive or hangs when you try to run a cell, save and commit the script, and exit and reenter the script editor. For more information, see Save the script and exit the script editor.

Create a password variable for the HighBond API token

Before you begin building the script, create a password variable for the HighBond API token.

  1. Switch to the browser tab where Robots is open.

  2. From the Robots script editor, click Manage variables .

  3. In the Variables window, specify the following values to create the password variable:

    Field/option Value
    Name

    Specify v_hb_token. You must use this exact name.
    Type

    Select Password.
    Value

    Paste your HighBond API token from the clipboard into the field.
    Task input Automatically enabled when you select Password.
    Task input label

    Specify Enter your HighBond API token:

    In Part 2 of the tutorial, you will see where this input label is used.
    User input required Leave selected.
    Task input description Optional. You can leave the description blank.
  4. Click Save and close.

    Result The variable is saved and you are returned to the script editor.

    Note

    As a security measure, any time you exit or refresh the script editor your HighBond API token is automatically deleted from the password variable. You must re-paste a valid token into the variable each time you reopen or refresh the script editor if you want to run a script that uses the HighBond API.

    If you forget this step, you get an error message when you run the script:

    HTTPError: 401 Client Error: Unauthorized for url: <url>

Copy and paste code blocks into script editor cells

  1. From the table below, copy and paste each code block into a separate cell in the script editor.

    To add a new cell in the script editor, click Add cell below on the script editor tool bar. You can add four additional empty cells right now, if you like.

  2. After you paste the code for a cell, and perform any specified updates in the code, click Run selected cell to see the output.

Learn more

You could put all the code in a single cell. However, organizing the code into separate cells imposes a visual order on the script, and makes the relation between the different logical blocks easier for you to see and understand. For additional benefits, see Key characteristics of cell-based script authoring.

Guidelines

  • Keep the same order Make sure the code in the script editor cells follows the same order as the code in the table below. The script will fail if any of the cells are out of order.

    Note

    A number appears beside each script editor cell when you run the cell. For example: [7]. The number is not a cell number. It's a dynamically updated sequence indicator that shows the order in which the cell has been run during a scripting session.

  • Use the Copy button In the code blocks below, hover over the block and click the Copy button to copy all the code in the block.

  • Copy and paste carefully When copying and pasting code, make sure you get all the code. Python is unforgiving about any missing items such as opening and closing parentheses ( ) .

    Copy and paste only the code. Do not include the examples of cell output that appear after the code blocks below.

    Careful copying and pasting helps you avoid runtime errors.

  • Work cell-by-cell Seeing the output cell-by-cell helps you understand the structure of the script logic. Even though the first cell has no visible output, you must run it before any of the other cells can run.

  • Read the comments in the code In addition to the commentary in the table below, read the comments in the code to understand what the code is doing.

    # Comment text

Cell descriptions and code blocks

Note

If you get a Python error (a red error block) at any point, see Troubleshooting.

Cell # Description and code block
Cell 1

Import required components

The code in cell 1 imports some additional code components.

  • Importing in Python To extend Python's core functionality, you often need to import additional code components such as libraries, modules, or classes. If you're just getting started with Python, don't worry too much about the names of these components, or the distinction between them.

  • import hcl Whenever you open the Robots script editor, the HCL library is imported by default and doesn't need to be explicitly imported. You can omit import hcl , but we're explicitly specifying it here so that you understand what's happening.

Copy and paste this code block into cell 1 in the script editor, and then run the cell.

There is no visible output when you run this cell.

# CELL 1

# Import the HighBond Command Language (HCL) custom Python library
import hcl

# Import the date, datetime, and timedelta classes from the Python datetime module
from datetime import date, datetime, timedelta
Cell 2

Define the variables used in the script

The code in cell 2 defines several variables. Variables are required because certain values used by the script can change:

  • date variables the name of the Results table created by the script includes a suffix with either the current date or the current month

  • table name variable allows you to specify any table name that you want

  • ID variables contain the system-generated unique IDs for the table and the analysis that contains the table

    Note

    To save output results from Robots to Results, you need the unique ID of the target table in Results. In cell 3, you retrieve the ID programmatically.

The print() function allows you to see the current values of some of the variables

  1. Copy and paste the code block below into cell 2 in the script editor.

  2. In the pasted code block, update v_analysis_id = "XXXXXX" (line 19) with the actual Results analysis ID that you saved in the temporary text file.

    For example: v_analysis_id = "93671" .

  3. Optional. Update the string portion of v_table_name from Test table 1 to anything you want (line 15).

    Change only the table name. Do not change anything else. For example:

    v_table_name = f"My table name ({current_date})"

  4. Run the cell.

# CELL 2

# Define the variables used in the script

# Assign the current date to a variable, and break out date and month into separate variables
# The timedelta() method gives you the option of adjusting the date for a script that runs after midnight (Diligent One platform time)
today = date.today() # - timedelta(days = 1)
current_date = today.strftime("%d-%b-%Y")
current_month = today.strftime("%b")

# Assign the current Diligent One server timestamp to a variable
highbond_datetime = datetime.now()

# Specify a table name that already exists in Results, or that you want to create
v_table_name = f"Test table 1 ({current_date})"

# Create variables for Diligent One table and analysis ID numbers
v_table_id = None
v_analysis_id = "XXXXXX"

# Display the variable values
print("date =", current_date)
print("month =", current_month)
print("table name =", v_table_name)
print("current Diligent One platform datetime =", highbond_datetime)

Example of cell output

date = 29-Sep-2021
month = Sep
table name = Test table 1 (29-Sep-2021)
current Diligent One platform datetime = 2021-09-29 11:03:45.988875
Cell 3

Create a table in Results

The code in cell 3 uses the HighBond API to find or create the Results table with the name that you specified in the previous cell (the value of v_table_name ).

Two HCL methods make the API requests:

  • hcl.api_get – gets a list of all tables in the analysis

  • hcl.api_post – if the table name you specified does not exist in the analysis, creates a table with that name

Note

This code block represents the core value of the script. By using the HighBond API, you can interact programmatically with the Results app with no need to manually open the app.

This ability means you can achieve your goal of running the script unattended without worrying if a table exists in Results to receive the output results.

In Part 2 of the tutorial, we'll extend the script to use this same API-based approach to automatically find or create a collection and an analysis.

Copy and paste this code block into cell 3 in the script editor, and then run the cell.

# CELL 3

# Find a table with the name specified in v_table_name. If the table doesn't exist, create it.

# Get a list of all tables in v_analysis_id
tables = hcl.api_get(f"analyses/{v_analysis_id}/tables")

# Convert the list from a JSON string to a Python dictionary
tables_dict = tables.json()

# Loop through the tables in the tables dictionary to search for v_table_name
# If the name exists, assign its unique ID to v_table_id
for table in tables_dict["data"]:
    if table['attributes']['name'] == v_table_name:
        v_table_id = table['id']
        print(f"The ID of the existing table is {v_table_id}")

# If v_table_name does not exist, create a table with that name in Results
# Refresh the tables dictionary and assign the unique ID of the newly created table to v_table_id
if v_table_id == None:
    hcl.api_post(f"analyses/{v_analysis_id}/tables", {"data": {"type": "tables", "attributes": {"name": v_table_name, "description": f"Description of {v_table_name}", "table_type": "data_analytic"}}})
    tables = hcl.api_get(f"analyses/{v_analysis_id}/tables")
    tables_dict = tables.json()
    for table in tables_dict["data"]:
        if table['attributes']['name'] == v_table_name:
            v_table_id = table['id']
            print(f"The ID of the newly created table is {v_table_id}")

Example of cell output

The ID of the newly created table is 209717
Cell 4

Perform some data analysis

The code in cell 4 performs some simple data analysis that demonstrates a couple of HCL methods:

  • hcl.read_csv() – import two CSV files to HCL dataframes

  • join() – join the dataframes on a common field

Dataframes are table-like structures that organize data in rows and columns.

Note

You could substitute any data analysis that outputs an HCL dataframe that you then save to the Results app.

Copy and paste this code block into cell 4 in the script editor, and then run the cell.

# CELL 4

# Perform data analysis to identify prohibited transactions made with corporate purchase cards 

# Import CSV files to HCL dataframes
pcard_transactions = hcl.read_csv("https://help.highbond.com/helpdocs/analytics/tutorial_data/pcard_trans.csv")
prohibited_merchant_codes = hcl.read_csv("https://help.highbond.com/helpdocs/analytics/tutorial_data/prohib_codes.csv")

# Join HCL dataframes on the common MerchCode/MerchantCode columns
prohibited_transactions = pcard_transactions.join(other = prohibited_merchant_codes, lkey = ["MerchCode"], lcolumns = ["AccountNumber", "TransDate", "VendorName", "Description", "UnitCost", "Quantity", "Amount"], rkey = ["MerchantCode"], rcolumns = ["MerchantType"], join_type='inner')

# Display the joined dataframe
prohibited_transactions

Example of cell output (an HCL dataframe)
Cell 5

Save the output of the data analysis to the Results table

The code in cell 5 saves the output of the data analysis to the Results table that was previously found or created by the script. The HCL method to_hb_results() saves an HCL dataframe to Results.

Copy and paste this code block into cell 5 in the script editor, and then run the cell.

# CELL 5

# Save the results to the existing or newly created Results table

# Save the HCL dataframe to Results
prohibited_transactions.to_hb_results(table_id = v_table_id, overwrite=False)

Example of cell output

202 is the HTTP status code for "Accepted".

202

Check Results for the new table

Let's check the Results app to confirm that the new table was created, and that the table contains the output results from the sample data analysis.

  1. Switch to the browser tab where Results is open and navigate back to the collection page (Test collection 1 <your initials>).

    Result The table that you just created using the HighBond API is present and it contains the output results that you just saved.

    Note

    Depending on the current usage levels of Results there may be a delay in the output results populating the table.

  2. Enable Show table IDs.

    The table ID displays to the left of the table name. It should be the same ID that was created and displayed when you ran the script cell to create the table.

  3. Click the table name to open the table.

    The output results that you saved from Robots to Results are displayed.

  4. Switch back to the browser tab where Robots is open and click Run all cells sequentially on the script editor toolbar.

    Result The script runs from the first cell to the last. Notice that the output of cell 3 now displays:

    The ID of the existing table is table_ID.

    When you re-run the script, the code in cell 3 now finds a table with a name that matches v_table_name. So there is no need to create a new table.

  5. Switch back to the browser tab where Results is open and refresh the page.

    Result The table updates to display the additional set of output results that you just saved from Robots to Results.

    Note

    Depending on the current usage levels of Results there may be a delay in the output results populating the table.

Learn more

Feel free to experiment with the script and try out some different options for creating Results tables.

Different options for creating Results tables

The script creates a daily table, identified by the date the script runs. You can modify the script to use the previous day's date in the table name, which is useful if the script runs after midnight. Or you can create a single accumulating monthly table rather than individual daily tables.

Note

After updating the script, click Run all cells sequentially to ensure that all updates and any prerequisites or dependencies are run in the correct order.

Tip

If you accidentally introduce an error into the script and can't solve the issue, just re-paste the code for the affected cell.

  • Daily table If you schedule the script to run nightly, it creates a new Results table every time it runs, with the current date as a table name suffix.

  • Subtract a day If the script runs after midnight and you want the date suffix to reflect the previous day's date, you can uncomment the timedelta() method (cell 2, line 7), which subtracts 1 day from the current date of your Diligent One instance:

    today = date.today() - timedelta(days = 1)

    Note

    The current date of your Diligent One instance is dictated by the datetime of the cloud infrastructure where your Diligent One instance is located. There may be an offset between the cloud infrastructure time zone and your local time zone.

  • Monthly table What if you want a monthly table rather than daily tables? In that case, change the variable in the table name suffix (cell 2, line 15):

    v_table_name = f"Test table 1 ({current_month})"

    Now the daily output results from the data analysis in Robots are appended into a single monthly table. If the script runs after midnight, use the timedelta() method to ensure that the final day of the month is included in the correct table.

  • Test the changeover of monthly tables You can verify that the script automatically creates a new monthly table at the start of each month. Use the timedelta() method (cell 2, line 7) to add an appropriate number of days to the current date so that it occurs in the following month. For example:

    today = date.today() + timedelta(days = 12)

Important

Once you have finished experimenting, revert the code to its original values:

today = date.today() # - timedelta(days = 1)

Save the script and exit the script editor

  1. Switch back to the browser tab where Robots is open.

  2. On the script editor tool bar, click Save and commit.

  3. Enter a commit message such as Script to automatically create a Results table.

  4. Click Commit to save and commit the script.

    The message Your script has been committed successfully appears.

  5. On the page header, click the robot name (Tutorial robot <your initials>).

    Result You are returned to the Script versions tab in the robot. Every time that you commit and save a script, the saved version is added to this tab.

  6. To reenter the script editor, do the following:

    1. In the Script versions tab, select the script version that you want to open (typically, the most recent version).

    2. On the Version details side panel that opens, click Edit script.

      Result The script version opens in the script editor and the start-up process begins again.

      Note

      Remember to re-paste your HighBond API token in the Variables window.

Robots development mode versus production mode

Development mode

When you exit the Robots script editor take a moment to orient yourself. You are in Robots development mode. Development mode is distinguished by a header with a dark background, and the Development button is selected.

You must come back to development mode whenever you want to edit a script or run it interactively, as you have just done. You cannot edit or run scripts interactively in Robots production mode.

Production mode

Use the Development and Production buttons to switch between development mode and production mode.

Production mode is distinguished by a header with a light background, and the Production button is selected. Whenever you reenter a robot, you are initially in production mode.

Learn more

For more information about the two modes, see Development mode and production mode in Robots.

What you learned

Congratulations! You now have a basic understanding of using native scripting in Robots with the HighBond API.

Specifically, you have learned how to:

  • create and test a Python/HCL script in a HighBond robot

  • use the HighBond API to interact with a Diligent One object

  • use HCL to import and transform some data, and save the output to Results

What's next?

In Part 2 of the tutorial, you'll fully automate the process you've been working with:

  • extend the script so that you don't have to manually create collections or analyses in the Results app

  • create a scheduled task so that you can run the script unattended

Go to >  Part 2: Increase your knowledge

 

Learn more

Learn more about HCL and scripting in Robots:

Troubleshooting

If you get a Python error (a red error block) when running a cell or the entire script, perform the troubleshooting procedure below.

  1. In the script editor, click Variables and double-check that the password variable v_hb_token exists and that you have supplied a token in the Value field.

    The token must be a HighBond API token, not an Analytics token. To make sure, re-copy an appropriate token from the Manage API tokens screen in Launchpad and paste it into the Value field.

    Tip

    Remember, if you exit or refresh the script editor, the value of any password variable is automatically deleted and must be re-specified.

  2. If a password variable is completely absent, complete all required aspects of the password variable definition.

    For more information, see Create a password variable for the HighBond API token.

  3. Click Save and close.

    Result All variables are saved and you are returned to the script editor.

  4. On the script editor toolbar, click Run all cells sequentially .

    Result If no errors remain, the script runs from the first cell to the last.

    Note

    When you save and close the Variables window, the script editor session memory is cleared. Re-running all the cells sequentially ensures that all required Python libraries are imported, and that all prerequisite operations required by subsequent cells are performed.