Part 2: Increase your knowledge

In Part 2 of the tutorial you use scripting in Robots and the HighBond API to fully automate the process that you only partially automated in Part 1.

Note

You must have created the robot and the script in Part 1 before you can do Part 2.

What will I learn?

You'll learn how to:

  • use the HighBond API to interact with multiple related Diligent One objects

  • create a task and schedule a script for full process automation

Extend the script to do more

Recall that in Part 1 of the tutorial you had to manually create a collection and an analysis in Results, retrieve the unique ID of the analysis, and paste it into the code in a script editor cell.

What if you want a separate analysis for each month, and a separate collection for each year? Using the manual method, you would need to remember, in advance, to create a new analysis each month, and update the script with the new analysis ID. And once a year, you would need to remember to create a new collection.

We're going to extend the script to use the HighBond API to automatically create a new analysis each month, and a new collection each year, so that you don't need to rely on an unwieldy, error-prone manual process. We're also going to create a scheduled task to run the script automatically.

Retrieve your HighBond API token and update the password variable

Sign in to Diligent One

If you are currently signed out, 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.

Retrieve your HighBond API 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. For more information, see Create a HighBond API token in Launchpad.

    Tip

    You may want to keep the token easily accessible. You need to provide it later in the tutorial when you create a scheduled task to run the script automatically.

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

Update the password variable

  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.

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

  3. Open the robot that you created in Part 1 of the tutorial.

  4. In the upper-right corner of the robot, click Development to switch to development mode.

  5. In the Script versions tab, select the most recent script version, and in the Version details panel click Edit script.

    The Robots script editor begins the start-up process.

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

  7. In the Variables window, paste your HighBond API token into the Value field for the v_hb_token variable.

  8. Click Save and close.

    Result The variable is updated 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

You're going to use the same method that you used in Part 1 to copy and paste code blocks into script editor cells. If it's been a while since you did Part 1, review the Guidelines.

Note

You're going to reuse the script and the cells that you already created in Part 1, and insert two additional cells. The content of some cells does not need to be updated.

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

    To insert a cell between two existing cells, select the first cell and click Add cell below on the script editor tool bar.

    Tip

    The most reliable way to update code in an existing cell is to click into the cell and press Ctrl+A to select all the content, and then either delete the content or paste over it.

  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.

Cell descriptions and code blocks

Note

Did you retrieve your HighBond API token and update the password variable?

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

Cell 1 remains unchanged from Part 1 of the tutorial and you don't need to update it.

  1. Do not change anything.

  2. 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

Update the code in cell 2 to add additional variables required to extend the script.

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

    Note

    Make sure you completely replace the existing code in cell 2.

  2. In the pasted code block, add your initials to the end of the collection name (line 18).

    For example:

    v_collection_name = f"Test collection 2 LM ({current_year})"

    Important

    The Results app does not prevent identical collection names. Identical collection names will cause problems for the script.

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

  3. If you want the script to output a daily table instead of an accumulating monthly table, update the table name variable (line 16) to use current_date rather than current_month :

    v_table_name = f"Test table 2 ({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, month, and year 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")
current_year = today.strftime("%Y")

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

# Specify table, analysis, and collection names that already exist in Results, or that you want to create
v_table_name = f"Test table 2 ({current_month})"
v_analysis_name = f"Test analysis 2 ({current_month})"
v_collection_name = f"Test collection 2 ({current_year})"

# Create variables for Diligent One table, analysis, and collection ID numbers
v_table_id = None
v_analysis_id = None
v_collection_id = None

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

Example of cell output

date = 29-Sep-2021
month = Sep
year = 2021
table name = Test table 2 (Sep)
analysis name = Test analysis 2 (Sep)
collection name = Test collection 2 LM (2021)
current Diligent One platform datetime = 2021-09-29 00:43:27.418796
Cell 3

Create a collection in Results

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

Note

This code block represents the first key step in fully automating the process. You use Python, HCL, and the HighBond API to find or create a collection in the Results app programmatically. You have no need to open the app or interact with it manually.

  1. Insert an empty cell after cell 2:

    1. Select cell 2.

    2. Click Add cell below on the script editor tool bar.

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

# CELL 3

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

# Get a list of all collections in the Diligent One instance
collections = hcl.api_get("collections")

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

# Loop through the collections in the collections dictionary to search for v_collection_name
# If the name exists, assign its unique ID to v_collection_id
for collection in collections_dict["data"]:
    if collection['attributes']['name'] == v_collection_name:
        v_collection_id = collection['id']
        print(f"The ID of the existing collection is {v_collection_id}")

# If v_collection_name does not exist, create a collection with that name in Results
# Refresh the collections dictionary and assign the unique ID of the newly created collection to v_collection_id
if v_collection_id == None:
    hcl.api_post("collections", {"data": {"type": "collections", "attributes": {"name": v_collection_name, "description": f"Description of {v_collection_name}", "type": "RegularProject"}}})
    collections = hcl.api_get("collections")
    collections_dict = collections.json()
    for collection in collections_dict["data"]:
        if collection['attributes']['name'] == v_collection_name:
            v_collection_id = collection['id']
            print(f"The ID of the newly created collection is {v_collection_id}")

Example of cell output

The ID of the newly created collection is 124661
Cell 4

Create an analysis in Results

The code in cell 4 uses the HighBond API to find or create the Results analysis with the name that you specified in cell 2 (the value of v_analysis_name ).

Note

This code block represents the next key step in fully automating the process. You use Python, HCL, and the HighBond API to find or create an analysis in the Results app programmatically. You have no need to open the app or interact with it manually.

The code locates the analysis in the collection you found or created in the previous cell.

  1. Insert an empty cell after cell 3.

    1. Select cell 3.

      Note

      Select the cell 3 that you just created, not the previous # CELL 3 .

    2. Click Add cell below on the script editor tool bar.

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

# CELL 4

# Find an analysis with the name specified in v_analysis_name. If the analysis doesn't exist, create it.

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

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

# Loop through the analyses in the analyses dictionary to search for v_analysis_name
# If the name exists, assign its unique ID to v_analysis_id
for analysis in analyses_dict["data"]:
    if analysis['attributes']['name'] == v_analysis_name:
        v_analysis_id = analysis['id']
        print(f"The ID of the existing analysis is {v_analysis_id}")

# If v_analysis_name does not exist, create an analysis with that name in Results
# Refresh the analyses dictionary and assign the unique ID of the newly created analysis to v_analysis_id
if v_analysis_id == None:
    hcl.api_post(f"collections/{v_collection_id}/analyses", {"data": {"type": "analyses", "attributes": {"name": v_analysis_name, "description": f"Description of {v_analysis_name}"}}})
    analyses = hcl.api_get(f"collections/{v_collection_id}/analyses")
    analyses_dict = analyses.json()
    for analysis in analyses_dict["data"]:
        if analysis['attributes']['name'] == v_analysis_name:
            v_analysis_id = analysis['id']
            print(f"The ID of the newly created analysis is {v_analysis_id}")

Example of cell output

The ID of the newly created analysis is 93672
Cell 5

Create a table in Results

If you have maintained the correct order of the cells, the code for finding or creating a table in Results is now in cell 5.

The code remains unchanged from Part 1 of the tutorial and you don't need to update it, except for updating the cell identifier in line 1.

  1. In line 1, update # CELL 3 to # CELL 5.

  2. Run the cell.

Note

This code block represents the final key step in fully automating the process. You use Python, HCL, and the HighBond API to find or create a table in the Results app programmatically. You have no need to open the app or interact with it manually.

The code locates the table in the analysis that you found or created in the previous cell.

 
# CELL 5

# 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 209719
Cell 6

Perform some data analysis

If you have maintained the correct order of the cells, the code for performing some sample data analysis is now in cell 6.

The code remains unchanged from Part 1 of the tutorial and you don't need to update it, except for updating the cell identifier in line 1.

  1. In line 1, update # CELL 4 to # CELL 6.

  2. Run the cell.

 
# CELL 6

# 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 7

Save the output of the data analysis to the Results table

If you have maintained the correct order of the cells, the code for saving the output to Results is now in cell 7.

The code remains unchanged from Part 1 of the tutorial and you don't need to update it, except for updating the cell identifier in line 1.

  1. In line 1, update # CELL 5 to # CELL 7.

  2. Run the cell.

 
# CELL 7

# 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 collection, analysis, and table

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

  1. Open the Results app in a separate tab:

    1. Click Switch Apps Switch Apps icon.

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

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

  3. Open the collection that you just created using the Robots script and the HighBond API.

    If you don't see the collection, refresh the page.

    Result The table that you just created using the HighBond API is present, nested inside the analysis that you just created. The table 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.

  4. 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.

  5. Click the table name to open the table.

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

    In the browser address bar, the URL contains the unique IDs created and displayed when you ran the script. In order, they are:

    • the collection ID

    • the analysis ID

    • the table ID

  6. 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 the cells that create a collection, an analysis, and a table now display existing rather than newly created. For example:

    The ID of the existing table is table_ID.

    When you re-run the script, the code now finds a collection, a nested analysis, and a nested table with names that match the names you specified in the variables. So there is no need to create new objects.

  7. 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.

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 Updated script to automatically create a Results collection, analysis, and table.

  4. Select Save script output to the task run log file.

    When you run a script using a Robots task, this option saves all the script output to a log file. The saved output can be useful to review when you are developing and troubleshooting scripts.

  5. Click Commit to save and commit the script.

    The message Your script has been committed successfully appears.

  6. 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.

Schedule a Robots task to run the script

So, you now have a script that fully automates a process. The script automatically finds or creates the hierarchy of objects in Results required to contain the output of the process. It also automatically creates additional hierarchy objects, as required, based on date.

However, you are running the script interactively, meaning that you have to manually run it from the script editor. The final stage in fully automating the process is to create a Robots task that automatically runs the script based on whatever schedule you specify.

What a Robots task does

A Robots task runs the script or scripts that you specify. A task provides two things critical for building automation:

  • Storage of script input The task allows you, or another user, to provide the inputs required to run the script. You provide the inputs in advance, the task stores the inputs, and then uses them each time the task runs.

  • Scheduled execution The task allows you to specify what time of day and how frequently the task will run.

Create a task

Create and schedule a task to run the script that you just created. The task requires only a single input: your HighBond API token. However, tasks can accommodate as many inputs as your script requires.

  1. In Robots, select the Tasks tab.

  2. Click Create task, enter a name for the task such as Tutorial task, and click Save.

    Result The Task Designer opens and you can begin configuring the task settings.

  3. Click Select all to select the script that you just created.

    Note

    Unlike ACL robots, HighBond robots currently support only one script.

  4. Open the Parameters dropdown list, paste your HighBond API token into the field, and click Continue.

    Note

    The label that you created in the Variables window in the script editor is displayed here: Enter your HighBond API token:

  5. Select Put your task on a schedule, specify the following values, or values of your own choosing, and click Continue:

    Option Value
    Frequency Daily
    Run every 1 day(s)
    Starting at 11:00 pm
    Beginning on the current date

  6. Select Send notifications on failure, select yourself as the email recipient, and click Continue.

  7. Review the task configuration and click Confirm and create task.

    Result The task is created and will now run the script on the schedule that you specified.

Run the task ad hoc

Even though the task is scheduled, we're going to run it ad hoc to make sure that it works.

  1. On the Tasks tab, select the task that you just created and click > Run now.

    Result the task starts and you can monitor its progress in either the Tasks tab or the Task runs tab.

    If the task runs without a problem, it moves through this sequence of statuses:

    • Queued

    • In progress

    • Success

    Note

    Depending on the current operating state of Robots the task may remain queued for a minute or so.

  2. When the task shows a status of Success, switch to the browser tab where Results is open and refresh the page.

    If necessary, navigate back to the table where you're saving the output results.

    Result The table updates to display the additional set of output results that you just saved by running the task ad hoc.

Check the task run log file

Download and take a look at the task run log file. Because you selected Save script output to the task run log file when you committed the script, the log file contains all the script output. This record of script output can provide useful information as you start to develop script-based automation in Robots.

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

  2. On the Task runs tab, select the task run.

    The Task run details side panel opens. The task run log file appears in the Output section, and is named Tutorial task.json if you used the suggested name for the task.

  3. Click Download beside the file name.

    Result The task run log file is downloaded to your computer.

  4. Open the file in a text editor such as Notepad++, or another editor that supports automatic formatting of JSON syntax.

  5. Format the JSON syntax and review the file.

    You should see the same script output that you saw when you ran the script interactively in the script editor.

Check the task output over successive days

If you scheduled the task to run once a day, periodically check the output results in the Results app to make sure the task is working correctly. If the task does not run for any reason, you should get a notification sent to your email address.

Theoretically, the task will now run indefinitely, creating a new table once a day or once a month, a new analysis once a month, and a new collection once a year.

If you are using a HighBond API token with an expiry date, then the task will stop running when the token expires. You can update the task with a new, unexpired token at any time.

Disable or delete the task

Once you are satisfied that the task is running correctly based on the schedule, you can disable or delete the task.

Disable the task

Disabling the task is reversible and does not delete anything. The task is flagged as Disabled and does not run at the scheduled time, and cannot be run ad hoc.

  1. In Robots, on the Tasks tab, select the task and click > Disable.

    Result The Disabled flag appears beside the task name and the task will not run.

  2. To re-enable the task, click > Enable.

Delete the task

Deleting the task deletes only the task. It does not delete any of the associated information in the Task runs tab, and it does not delete the script.

  1. In Robots, on the Tasks tab, select the task and click > Delete.

  2. In the prompt, click Delete to confirm that you want to permanently delete the task.

What you learned

Congratulations! You've increased your understanding of using native scripting in Robots with the HighBond API.

Specifically, you have learned how to:

  • use the HighBond API to interact with multiple related Diligent One objects

  • create a task and schedule a script for full process automation

What's next?

In Part 3 of the tutorial, you will use scripting in Robots with the Python Requests library to connect to the HighBond API and to a third-party API.

Go to >  Part 3: Extend your reach

Practice interacting with Diligent One

Why not practice your new skills by creating additional robots to automate interaction with other types of Diligent One objects? Scan the Core Resources in the HighBond API Reference to see which objects you can access.

Caution

Using the API GET method ( hcl.api_get ) is safe. You are only reading data. Be careful if you use API methods to update or delete data in your organization's production Diligent One instance. Make sure that you are only interacting with test or sample data!

Without the user interface to guide you, it's easier to update or delete the wrong object.

 

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.