Automating Excel-Heavy Workflows with Excel Automate

Technical TLDR:

• Excel Automate: Excel Automate feature allows you to create automation scripts in TypeScript to extract and manipulate data without leaving Excel.

• Excel Automate + API Integration: Connecting Excel to external APIs allows you to centralize business logic while keeping users in their familiar Excel workflow.

• Well Planning with Excel Automate: Using Excel Automate and APIs to capture inputs across multiple teams, enabling a more structured, transparent, and efficient well planning workflow without disrupting existing processes.

Critical Workflows in Excel

There's no denying that Excel is the workspace of the O&G industry. Broadly, business workflows that depend on Excel fall into 2 buckets:

1. A single large Excel sheet is used to manage a workflow. More often than not, this acts as a makeshift database, serving as the “master” source of information for a team or group.

2. Multiple individuals work in their own Excel sheets, each formatted to their preference or work style. These sheets then feed into a larger process, requiring manual merges to consolidate all the inputs.

Excel is a great choice for simple workflows or one-off tasks. Its flexibility and low barrier to entry make it the go to tool. But rarely does it stay that way. As these workflows grow, they take on more responsibility within the organization, eventually becoming core to the business.

At some scale, teams start getting frustrated because the process feels too complex and slow. Managers get frustrated because it takes 1-2 weeks just to piece together all the inputs before they can even see a draft of the results. A classic O&G example is well planning. It's a process that involves multiple teams, each contributing different inputs, often in Excel, even when the company uses some level of enterprise planning software. Iterations on a single planning cycle can take anywhere from 1 to 4 weeks.

So how do you improve a process that spans multiple teams, each with its own way of structuring data, and make it faster?

The first instinct is usually: “Let’s build an app for it” (PowerApps, custom software, etc.). Sometimes this works, especially when inputs are well-defined and the process is relatively static. But these solutions tend to break as formats change, processes evolve, and teams face the challenge of building, onboarding, and training on an entirely new system.

So what's the alternative?

Excel Automate

Excel Automate became generally available in 2021, with full desktop rollout in 2023. It appears as a tab on your ribbon, located to the right of "View". The purpose of this feature is to allow users to record their repetitive actions into a script that can then be run in that Excel sheet at any time. Think of tasks like pivoting the same data, applying the same filters, formatting, etc.

However, the real power of Automate lies in the ability to write your own scripts, interacting with Excel in an object-oriented format.

Below is a basic example provided by Microsoft when you click "New Script" in the Automate ribbon. The language used is TypeScript (strongly-typed JavaScript), which allows you to interact with the entire contents of an Excel file in an object-based format.

In this simple example, we get the active cell in the current sheet and change its background color to yellow:

Excel Automate as an API Wrapper

So, how does this relate to Excel workflows specifically the second category where multiple individuals work in separate sheets? Instead of embedding business logic directly into Excel Automate, we can leverage these scripts to capture the necessary inputs for the workflow.

Since Automate gives us access to the user’s selected area and its contents, we can create a simple API wrapper that extracts those inputs and sends them to a centralized API. This API is responsible for handling the business logic (processing, validating, etc.) without locking critical logic inside Excel scripts.

We highly encourage centralizing business logic, regardless of the programming language you choose. This approach ensures:

• Transparency into how the logic works

• Testability, so you can verify the logic behaves as expected

• Version control through GitHub, keeping everything organized and trackable

Here’s an example of what a script like this could look like. We take the selected data range and the author of the workbook for reference. Then, we send this data to our API in JSON format.

We can choose to wait for the response before proceeding with any additional actions. If results are returned, we can process them accordingly.

const endpoint = 'https://your-api-here'

interface ResponseData {
    message: string
    data: {}
    status_code: number
    success: boolean
    error: boolean
}

async function main(workbook: ExcelScript.Workbook) {
    // Get the currently selected range
    const selectedRange = workbook.getSelectedRange()

    // Get the values from the selected range
    const values = selectedRange.getValues()

    const requestData = {
        data: values,
        user: workbook.getProperties().getLastAuthor(),
    }

    try {
        const response = await fetch(endpoint, {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
            },
            body: JSON.stringify(requestData),
        })

        const data: ResponseData = await response.json()

        if (data.status_code !== 200) {
            throw new Error(data.message)
        }
    } catch (error) {
        console.log('Error sending data:', error)
        throw error
    }
}

⚠️ A word of caution: We do not recommend building core business logic in these scripts. Currently, there’s no robust way to version control this code, build tests, or manage it effectively. If you’re using it for personal automation, go for it. But in a business workflow setting, these scripts can quickly become difficult to maintain leading to the same issues as managing multiple Excel sheets, except now with custom scripts instead.

Oil & Gas Well Planning Use Case

We leveraged this approach to streamline a highly Excel dependent well planning workflow that spanned multiple teams and systems. The goal was to establish a structured process quickly without the need to build full-fledged apps or custom software.

After setting up the necessary database and backend API, we developed custom Excel Automate scripts for core users. These scripts allowed them to send their inputs to the API whenever they were ready. This way, users could continue working in Excel with minimal disruption to their existing workflows, while still benefiting from a larger, integrated system.

On the backend, everything was version-controlled, ensuring visibility into when inputs were saved, what assumptions were made, and how data was processed. This added a new level of transparency to the planning workflow and significantly reduced the time required for a single iteration.

Important Notes on Excel Automate

There are a few critical limitations of Excel Automate that you need to be aware of. We’re sharing these to help you avoid common pitfalls and leverage lessons learned from our experience.

If you have any questions about these points, or the workflow in general, feel free to reach out at info@aai.agency.

Key Limitations:

• Limited Library Support: The libraries available in Automate scripts are extremely restricted, typically limited to basic Node packages (if any). You won’t be able to import external packages, which is another reason we strongly advocate for centralizing business logic elsewhere.

• No Version Control: There is no built-in version control system like GitHub. Each save will override the previous script, making it difficult to track changes or revert to previous versions.

• Script Sharing & Security: You can share scripts by saving them to SharePoint, but for users to run them, they have to save them to their own OneDrive at a specific location. Be mindful of who is using the scripts and consider tracking user requests in your API to maintain visibility and support future updates.

• Error Handling Matters: Implement proper error handling on your backend. If errors aren’t handled correctly, generic error messages will appear in the script console, making debugging difficult.