Embedding GPT in Excel Applications: A Guide for Beginners

In the ever-evolving landscape of technology, the integration of advanced AI tools like OpenAI’s Generative Pre-trained Transformer (GPT) into everyday applications has opened new frontiers for automation and efficiency. One such practical application is embedding GPT into Excel plugins, enabling users to harness the power of AI directly within their spreadsheets. This article will guide beginners through understanding how to embed GPT into an Excel plugin, using a basic plugin as an example, and discuss the potential benefits of this approach.

Creating an Excel plugin, also known as an Excel Add-in, using JavaScript or TypeScript involves several steps, from setting up your development environment to writing the code and testing your Add-in within Excel. This guide will walk you through the process, focusing on a simple example to get you started.

Step 1: Prerequisites

Microsoft Office: You need Excel installed on your computer or access to Excel on the web.
Node.js: Install Node.js, which includes npm (node package manager), to run the development server and manage your project’s dependencies.
Yeoman and Yeoman Office Generator: Yeoman helps scaffold new projects, and the Yeoman Office Generator (generator-office) provides templates for creating Office Add-ins.

Step 2: Setting Up Your Project

Install Yeoman and the Yeoman Office Generator:

Open a terminal or command prompt and run the following command:
```
npm install -g yo generator-office
```
Scaffold Your Excel Add-in Project:

Run the Yeoman generator for Office Add-ins:
```
yo office
```
Follow the prompts to set up your project. Choose:
- Project Type: Office Add-in Task Pane project
- Script Type: JavaScript or TypeScript (according to your preference)
- Office Application: Excel
This will create a new directory with your project’s name and set up a basic project structure.

Step 3: Understanding the Project Structure

Your project directory will contain several files and folders:

src: Contains the source code for your project.
- taskpane.html: The HTML file for the task pane UI.
- taskpane.js (or taskpane.ts for TypeScript): The JavaScript or TypeScript file that contains the logic for your Add-in.
package.json: Manages your project’s dependencies and scripts.
manifest.xml: The XML file that describes your Add-in to Excel.

Step 4: Developing Your Add-in

Open the taskpane.html and taskpane.js (or .ts) files in your preferred code editor to start adding your custom code. For example, you might want to fetch data from a web API and display it in Excel.

Sample JavaScript Code

If you’re using JavaScript, your taskpane.js might look something like this to write data to an Excel sheet:

Office.onReady((info) => {
  if (info.host === Office.HostType.Excel) {
    document.getElementById("run").onclick = run;
  }
});

async function run() {
  try {
    await Excel.run(async (context) => {
      const sheet = context.workbook.worksheets.getActiveWorksheet();
      const range = sheet.getRange("A1");
      range.values = [["Hello, Excel!"]];
      await context.sync();
    });
  } catch (error) {
    console.error(error);
  }
}

Sample TypeScript Code

For TypeScript, you might have a taskpane.ts with similar functionality:

Office.onReady((info: Office.InitializationReason) => {
  if (info.host === Office.HostType.Excel) {
    const runButton: HTMLButtonElement = document.getElementById("run") as HTMLButtonElement;
    runButton.onclick = run;
  }
});

async function run(): Promise<void> {
  try {
    await Excel.run(async (context: Excel.RequestContext) => {
      const sheet: Excel.Worksheet = context.workbook.worksheets.getActiveWorksheet();
      const range: Excel.Range = sheet.getRange("A1");
      range.values = [["Hello, Excel!"]];
      await context.sync();
    });
  } catch (error) {
    console.error(error);
  }
}

Step 5: Testing Your Add-in

Start the Local Web Server:

Run the following command in your project directory:
```
npm start
```
This command starts a local web server and opens your default web browser to the Office Add-in loading page.
Load Your Add-in in Excel:
- For Excel on the desktop, use the Upload My Add-in button on the Office Add-in loading page to upload the manifest file.
- For Excel on the web, go to Insert > Office Add-ins > Upload My Add-in and upload your manifest file.

Step 6: Publish Your Add-in

To share your Add-in with others, you need to publish it to an Add-in catalog or to the Office Store. The specifics of this process depend on your target audience and distribution needs.

Introduction to the Stock Excel Plugin

The example provided demonstrates a basic Excel plugin designed to showcase how developers can integrate AI functionalities into Excel. The plugin utilizes HTML for the user interface and JavaScript for backend logic, incorporating OpenAI’s GPT and the AlphaVantage API to offer investment advice and stock information directly within Excel.

Key Components of the Plugin

HTML File (User Interface): This file defines the visual layout and elements of the plugin, including the welcome message, input field, and search button. It also includes references to external resources like Fluent UI for styling and Office.js for integrating with Excel.
JavaScript File (Logic and API Calls): This file contains the logic for the plugin, handling events (e.g., clicking the search button), making API calls to OpenAI for generating stock recommendations based on user input, and fetching stock details from AlphaVantage.
Environment Variables: The OPENAI_KEY and ALPHAVANTAGE_KEY are used to authenticate requests to the respective APIs. Note that the provided OpenAI key is expired and for demonstration purposes only.

How It Works

When the user enters a description of a company in the input field and clicks the “Search” button, the plugin executes the following steps:

Capture User Input: The plugin retrieves the user’s input from the text field.
OpenAI Chat Function: It then calls the openAiChat function, sending the input to OpenAI’s API to receive a stock ticker recommendation based on the input.
AlphaVantage Function: With the recommended stock ticker, the plugin calls the alphaVantage function to fetch detailed information about the stock from the AlphaVantage API.
Display Information: Finally, the plugin displays the fetched stock information in the plugin interface.

Understanding the Code Structure

The integration is made up of two main components: an HTML file that serves as the user interface and a JavaScript file that contains the logic and API calls to OpenAI and AlphaVantage.

HTML File: User Interface

This file (index.html) sets up the UI of the Excel plugin. It includes references to external libraries and stylesheets, such as Office.js (for Excel integration) and Fluent UI (for styling). The body of the HTML file contains elements like headers, sections, and inputs that form the user interface of the plugin.

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Contoso Task Pane Add-in</title>
    <script src="https://appsforoffice.microsoft.com/lib/1.1/hosted/office.js"></script>
    <link rel="stylesheet" href="https://static2.sharepointonline.com/files/fabric/office-ui-fabric-core/11.0.0/css/fabric.min.css"/>
    <link href="taskpane.css" rel="stylesheet" type="text/css" />
</head>
<body class="ms-font-m ms-welcome ms-Fabric">
    <header class="ms-welcome__header ms-bgColor-neutralLighter">
        <img width="90" height="90" src="../../assets/logo-filled.png" alt="Contoso" title="Contoso" />
        <h1 class="ms-font-su">Welcome</h1>
    </header>
    <section id="sideload-msg" class="ms-welcome__main">
        <h2 class="ms-font-xl">Please <a href="https://learn.microsoft.com/">sideload</a> your add-in to see app body.</h2>
    </section>
    <main id="app-body" class="ms-welcome__main" style="display: none;">
        <p class="ms-font-l">Input a description of a company and hit <b>Search</b>.</p>
        <input type="textarea" id="prompt" placeholder="enter prompt here"></input>
        <div role="button" id="run" class="ms-welcome__action ms-Button">
            <span class="ms-Button-label">Search</span>
        </div>
        <div id="description"></div>
    </main>
</body>
</html>

JavaScript File: Logic and API Calls

This file (commands.js) is responsible for the plugin’s functionality. It uses the Office JavaScript API to interact with Excel and fetches from OpenAI and AlphaVantage to get stock recommendations and details.

import fetch from "cross-fetch";
import { OPENAI_KEY, ALPHAVANTAGE_KEY } from "./env";

Office.onReady((info) => {
  if (info.host === Office.HostType.Excel) {
    document.getElementById("sideload-msg").style.display = "none";
    document.getElementById("app-body").style.display = "flex";
    document.getElementById("run").onclick = run;
  }
});

async function openAiChat(prompt) {
  const apiKey = OPENAI_KEY;
  const response = await fetch("https://api.openai.com/v1/chat/completions", {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${apiKey}`,
    },
    body: JSON.stringify({
      model: "gpt-3.5-turbo",
      messages: [{ role: "system", content: "Investment advice prompt" }, { role: "user", content: prompt }],
      temperature: 0.7,
    }),
  });

  const data = await response.json();
  return data.choices[0].message.content;
}

async function alphaVantage(symbol) {
  const url = `https://www.alphavantage.co/query?function=OVERVIEW&symbol=${symbol}&apikey=${ALPHAVANTAGE_KEY}`;
  const response = await fetch(url);
  const data = await response.json();
  return data;
}

async function run() {
  try {
    await Excel.run(async (context) => {
      const inputValue = document.getElementById("prompt").value;
      await context.sync();
      const stockResponse = await openAiChat(inputValue);
      const tickerData = JSON.parse(stockResponse);
      const results = await alphaVantage(tickerData.ticker);
      document.getElementById("description").innerHTML = results.Description;
    });
  } catch (error) {
    console.error(error);
  }
}



// Note: OPENAI_KEY and ALPHAVANTAGE_KEY are placeholders and should be replaced with valid API keys.

Benefits of Embedding GPT in Excel

Embedding GPT into Excel applications like this plugin enables several powerful capabilities:

Automation: Reduces manual data entry and analysis, saving users valuable time and effort.
Intelligence: Leverages AI to provide insightful recommendations and data, enhancing decision-making.
Customization: Allows for tailored functionalities to meet specific user needs, making Excel a more versatile tool.
Accessibility: Brings advanced AI capabilities into a familiar environment, making it more accessible to a broader audience.

Conclusion

Integrating GPT into Excel plugins represents a significant leap forward in making AI accessible and usable in everyday applications. This guide and the provided example serve as a starting point for beginners to explore the integration of AI into Excel, highlighting the steps involved and the benefits of such an approach. As you become more comfortable with these concepts, you can expand upon this foundation to create even more sophisticated and customized Excel plugins, leveraging the full power of AI to transform how we work with data.