API's, Data Pipelines & External Reporting

Here is one that I have been working on. This one is a map that shows clock in locations and number of miles away from the branch. Hoping it will be useful to spot employees that are continually clocking in away from the branch. 

@vladryabyy That is SUPER cool!

I haven’t done that yet. We still have some guys with location services turned off. (lol idk why) I was working on one to track if they were using mobile time at all and it was shockingly challenging. The closest I got was if all the [Device Name] fields were blank, that was a good indication...

We have been looking into using PowerBI, but have some hesitation on the API set up. I love the reports you guys have shared. 

@Kbaird If you are going to ignite you should check out Christina and my’s presentation on API and Data Pipelines.

I go over a few different options, from coding it yourself, to companies that can help you do it. 

Or feel free to reach out to me, and I can help you get started over zoom or something!

@LandscapeNerd I attended the API session that you and Christina gave at the Ignite Conference - super inspiring stuff! I’m very interested in getting into this but am a total newbie to API, although I did a little bit of python coding awhile back. The possibilities seem endless with the types of reports we could produce - my colleagues and I were totally geeking out over it after the presentation. 🤓

Did I hear correctly during the presentation that you have an AP process with data flowing back in to Aspire? If so, would love to hear more on what that looks like! 

Hi @mwheeler !

Thanks for attending that session! If you have done some python coding before, then this will be a breeze for you!

Right, I have mainly been working on reporting, but just recently on automation type things. I have successfully taken a PDF Invoice on my desktop and attached it to a purchase receipt via API, but I haven’t figured out exactly where in our workflow to use that. 

In the swagger documentation here:

Swagger UI (youraspire.com)

any of the endpoints that have PUT and POST options - you can use to send information to Aspire!

Happy to help any way I can!

Thanks!
AC

Hey @mwheeler!

We put together an example of how we sync Aspire API data with Google Big Query in Python. Check it out! https://github.com/brent-lemieux/capsa-connectors

Here’s an interesting one. This shows Opportunity win rate on the y-axis by price per estimated hour on the x axis for fert opportunities in 2024.
 

I wanted to Create an API Alert.  Currently, if your Aspire>Administration>Configuration, has the Restrict Editing of Employees checked (enabled), then the PUT Contact API (REST API) will not be updating your Employee Type contacts.  This is a bug.  Currently, Aspire has a solution in Testing (Sandbox) which is scheduled to role out to Production in early October.  

The workaround is to both Uncheck the Restrict Editing of Employees AND to pull a new Token after this change has been saved as the token is carrying this setting permission within it.  

POST Contact is working correctly.

PUT USERS is working correctly.

POST/PUT PAYRATES - working correctly

POST/PUT PayRateOverridePayCodes - working correctly
 

 @CapsaAnalytics very cool! Thanks for the example, and that’s a super interesting graph. Haven’t used Google Big Query before but I’ll be looking into it this week! 

I am trying to see about using the API as an audience host for Meta platforms.  Basically, I want to write my query (say all won opportunities over x hours) and get to a list of FNAME, LNAME, EMAIL, ZIP from that opportunity query.  The way I’ve gotten there has required an initial call to the API to fetch all the opportunities, and then a loop through the data from that response to request each propertycontact with based on the propertyid from the opportunity. It seems like a very roundabout way of getting what I need, but I haven’t been able to determine a direct way to get this same list with fewer calls.  Has anyone had experience with list building via the api? 

Just wanted to share a problem I have been having with the API and a temporary workaround just in case anyone else is experiencing this!

Currently the Aspire API for Opportunities does not pull Opportunity Tags and it pulls all Opportunities even ones that have been deleted. 

I created a list in Aspire that just pulls the Opportunity number and the tags. I then run this python code and it pulls that list. If you do not have the over 2500 records button just erase step 6! 

You now have data that you can add to a to filter your Aspire Opportunities pulled via the API.

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.support import expected_conditions as EC
import os
import time

# Variables for sensitive information
chrome_binary_location = r"C:\Path\To\Your\Chrome.exe"  # Replace with your Chrome executable path
chrome_user_data_dir = r'C:\Path\To\Your\User\Data'  # Replace with your user data directory path
chrome_profile_directory = 'YourProfile'  # Replace with your profile name (e.g., 'Default')
download_folder = r"C:\Path\To\Download\Folder"  # Replace with your desired download folder path
aspire_url = 'https://cloud.youraspire.com/login?returnUrl=%2Fapp'  # Replace with the Aspire login URL
pin_code = 'XXXX'  # Replace with the PIN code
search_term = 'Number'  # Replace with your search term
output_file_name = 'Opportunity Number.xlsx'  # Replace with the desired output file name

# Helper function for checkpoints
def wait_for_element(driver, locator, timeout=60):
    """Wait for an element to be present and visible for up to `timeout` seconds."""
    # Adding a 5-second buffer before starting the wait
    time.sleep(5)
    try:
        return WebDriverWait(driver, timeout, poll_frequency=1).until(
            EC.presence_of_element_located(locator)
        )
    except Exception as e:
        raise Exception(f"Element {locator} not found within {timeout} seconds.")

# Function to get the latest Excel file in the directory
def get_latest_excel_file(directory):
    """Return the latest .xlsx file in the given directory."""
    files = [os.path.join(directory, f) for f in os.listdir(directory) if f.endswith('.xlsx')]
    return max(files, key=os.path.getctime) if files else None

# Set up Chrome options
chrome_options = webdriver.ChromeOptions()
chrome_options.binary_location = chrome_binary_location
chrome_options.add_argument(f'--user-data-dir={chrome_user_data_dir}')
chrome_options.add_argument(f'--profile-directory={chrome_profile_directory}')

# Set up default download location
prefs = {"download.default_directory": download_folder}
chrome_options.add_experimental_option("prefs", prefs)

# Initialize the WebDriver with the specified options
driver = webdriver.Chrome(options=chrome_options)

# Open the Aspire login page
driver.get(aspire_url)

try:
    # Step 1: Enter the PIN
    pin_field = wait_for_element(driver, (By.ID, 'pin'))
    pin_field.send_keys(pin_code)  # Enter the PIN

    # Step 2: Locate the login button and click it
    login_button = wait_for_element(driver, (By.XPATH, '//button[@type="submit"]'))
    login_button.click()

    # Step 3: Click the button with the class 'aspire-icon icon-opportunity'
    opportunity_button = wait_for_element(driver, (By.CLASS_NAME, 'aspire-icon.icon-opportunity'))
    opportunity_button.click()

    # Step 4: Click the search input field and type the search term
    search_input = wait_for_element(driver, (By.CLASS_NAME, 'p-autocomplete-input'))
    search_input.click()
    search_input.send_keys(search_term)

    # Step 5: Wait for the dropdown to appear and select the correct option
    dropdown_option = wait_for_element(driver, (By.XPATH, f"//span[contains(@class, 'ng-tns') and text()='{search_term}']"))
    dropdown_option.click()

    # Step 6: Click the button with the specific title using XPath
    more_records_button = wait_for_element(driver, (By.XPATH, "//button[@title='More than 2500 records available. Click to return all records.']"))
    driver.execute_script("arguments[0].scrollIntoView(true);", more_records_button)  # Scroll the element into view
    time.sleep(1)  # Small delay to ensure the element is ready
    more_records_button.click()

    # Step 7: Click the "More" button with class 'p-element action-button'
    more_button = wait_for_element(driver, (By.XPATH, "//button[contains(@class, 'action-button') and @title='More']"))
    more_button.click()

    # Step 8: Click the 'Export to Excel (Current View)' option
    export_button = wait_for_element(driver, (By.XPATH, "//span[text()='Export to Excel (Current View)']"))
    export_button.click()

    # Step 9: Wait for 10 seconds and rename the newest Excel file
    time.sleep(10)  # Wait for the file download to complete

    # Define the target file name
    new_file_name = os.path.join(download_folder, output_file_name)

    # Delete the existing file if it exists
    if os.path.exists(new_file_name):
        os.remove(new_file_name)

    # Find the latest Excel file in the download folder
    downloaded_file = get_latest_excel_file(download_folder)

    if not downloaded_file:
        raise Exception(f"No Excel file found in the directory: {download_folder}")

    # Rename the file
    os.rename(downloaded_file, new_file_name)

finally:
    # Close the browser
    driver.quit()

@Kbaird If you are going to ignite you should check out Christina and my’s presentation on API and Data Pipelines.

I go over a few different options, from coding it yourself, to companies that can help you do it. 

Or feel free to reach out to me, and I can help you get started over zoom or something!

 

This looks great! I couldn’t find it in the shared folder from Ignite 24 or 23. Do you happen to have a link somewhere?