Skip to main content

Connecting elvex to your Google Sheets

How-to guide for connecting your Google Sheets to an elvex assistant

Updated yesterday

You can call the elvex API directly from Google Sheets using a Google Apps Script. This integration enables you to seamlessly run elvex within Google Sheets, leveraging the power of custom AI applications to enhance your workflow.

Setting up your API key

To get started, create an API key. Go to Settings > API Keys and click Create an API Key. Provide it a name, and copy the key somewhere safe. We will use this key later.

Configuring your assistant

Next, create or find an assistant that you have access to edit. In the assistant configuration, under Security & Permissions, click the Assistant Users dropdown, and select your API key.

After selecting your key, you will see your it in the list of users.

Under Advanced, copy the Assistant ID and Latest Version values. We will use this later.

Important Note: Every time you update your assistant's rules or tools, elvex creates a new assistant version. The Latest Version number will change, and you'll need to update this value in your Google Sheets script (explained below).


At the bottom, click Save & Publish to save your changes.

Setting up the Google Sheets script

Now, go into your Google Sheets page. In the menu bar, click Extensions > Apps Script.

This will open the Apps Scripts Editor. Name this app whatever you want, I call mine elvex Integration. In the file Code.gs there will be a pre-loaded function. Delete this, so you have an empty file.

Copy and paste the following text into the Code.gs file:

Code.gs

const documentProperties = PropertiesService.getDocumentProperties();
const ELVEX_API_KEY_SETTING = "ELVEX_API_KEY";
const ELVEX_APP_ID_SETTING = "ELVEX_APP_ID";
const ELVEX_APP_VERSION_SETTING = "ELVEX_APP_VERSION";

/**
 * Returns the currently configured elvex API key.
 * @return the currently configured elvex API key or nothing if not configured.
 */
function getElvexAPIKey() {
  return documentProperties.getProperty(ELVEX_API_KEY_SETTING);
}

/**
 * Builds a request to pass to UrlFetchApp.fetch
 * @param {string} apiKey the bearer token to access the elvex API
 * @param {string} appId the app ID
 * @param {string} appVersion the app version
 * @param {string} the prompt
 * @return An object containing { url, params } that can be used with UrlFetchApp.fetch
 */
function buildElvexAppRequest(apiKey, appId, appVersion, prompt) {
  const request = {
    url: `https://api.elvex.ai/v0/apps/${appId}/versions/${appVersion}/text/generate`,
    params: {
      method: "POST",
      contentType: "application/json",
      headers: {
        "Authorization": `Bearer ${apiKey}`,
      },
      payload: JSON.stringify({ prompt }),
      muteHttpExceptions: true,
    }
  }
  return request;
}

/**
 * Generates a response to a prompt using a specific elvex app and version.
 * @param {string} appId The elvex app ID which can be either hard coded or derived from a cell.
 * @param {string} appVersion The version of the elvex app ID to use which can be either hard coded or derived from a cell.
 * @param {string} prompt The prompt to send to the elvex app. You can use formulas to build more complex prompts from different cells / inputs.
 * @return The response from the elvex app 
 * @customfunction
 */
function elvexGenerate(appId, appVersion, prompt) {
  const apiKey = getElvexAPIKey();
  if (!apiKey) {
    Logger.log("elvex API key not set: %s", apiKey);
    throw "Error: elvex API key is not yet set. Please set the API key by navigating to elvex > Set API Key in your toolbar."
  }

  appId = appId.toString().trim();
  appVersion = appVersion.toString().trim();
  if (appId === "") {
    throw "appId is required";
  }

  const appVersionNum = Number(appVersion);
  if (!Number.isInteger(appVersionNum) || appVersionNum < 1) {
    throw "appVersion must be an integer that is greater than 1";
  }

  prompt = prompt.toString();
  if (prompt.trim() === "") {
    // Avoid calling the API for blank prompts
    return "";
  }

  const request = buildElvexAppRequest(apiKey, appId, appVersion, prompt);
  const response = UrlFetchApp.fetch(request.url, request.params);
  const code = response.getResponseCode()
  const text = response.getContentText()
  if (code !== 200) {
    Logger.log("elvex API error (%s): %s", code, text)
    throw `elvex API Error: ${text}`;
  }
  const deserialized = JSON.parse(response.getContentText());
  Logger.log(deserialized);
  return deserialized.data.response;
}

/**
 * Validates that an API key is valid and can be used to access the elvex API.
 */
function isElvexAPIKeyValid(apiKey) {
  const response = UrlFetchApp.fetch("https://api.elvex.ai/ping", {
      method: "GET",
      contentType: "application/json",
      headers: {
        "Authorization": `Bearer ${apiKey}`,
      },
      muteHttpExceptions: true,
    }
  )
  return response.getResponseCode() == 200;
}

/**
 * Handler for when this add-on is initially installed.
 * @param {Event} e The onInstlal event
 * @see https://developers.google.com/apps-script/guides/triggers#oninstalle
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Handler for when a spreadsheet is initally opened.
 * @param {Event} e The onOpen event.
 * @see  https://developers.google.com/apps-script/guides/triggers#onopene
 */
function onOpen(e) {
  const ui = SpreadsheetApp.getUi();  // Or DocumentApp, SlidesApp, or FormApp.
  ui.createMenu("elvex")
  .addItem("Set API Key", "setAPIKey")
  .addItem("Generate from range", "showGenerateSidebar")
  .addToUi();
}

/**
 * 
 */
function showGenerateSidebar() {
  const template = HtmlService.createTemplateFromFile("Sidebar");
  template.data = {
    "appId": documentProperties.getProperty(ELVEX_APP_ID_SETTING) ?? "",
    "appVersion": documentProperties.getProperty(ELVEX_APP_VERSION_SETTING) ?? "",
  }
  const html = template.evaluate().setTitle("elvex");
  SpreadsheetApp.getUi().showSidebar(html);
}

async function generateResponsesForRange(appId, appVersion, promptRange, outputRange) {
  documentProperties.setProperties({
    [ELVEX_APP_ID_SETTING]: appId,
    [ELVEX_APP_VERSION_SETTING]: appVersion
  });
  // Validate input and output ranges
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var promptRangeObj = sheet.getRange(promptRange);
  var numInputRows = promptRangeObj.getNumRows();
  var numInputCols = promptRangeObj.getNumColumns();
  var outputRangeObj = sheet.getRange(outputRange);
  var numOutputRows = outputRangeObj.getNumRows();
  var numOutputCols = outputRangeObj.getNumColumns();

  if (numInputRows != numOutputRows || numInputCols != numOutputCols) {
    throw new Error("Input and output ranges must have the same dimensions");
  }

  if (numInputCols != 1 && numOutputCols != 1) {
    throw new Error("Input range must be one dimensional");
  }

  if (numOutputCols != 1 && numOutputCols != 1) {
    throw new Error("Output range must be one dimensional");
  }

  // var inputValues = promptRangeObj.getValues();
  // const outputValues = await Promise.all(inputValues.map(async (prompt) => elvexGenerate(appId, appVersion, prompt)));
  // outputRange.setValues(outputValues);
  const inputValues = promptRangeObj.getValues().flat();
  const outputValues = [];

  // Generate promises in batches of 25
  for (let i = 0; i < inputValues.length; i += 25) {
    const batch = inputValues.slice(i, i + 25);
    const batchPromises = batch.map(async (prompt) => elvexGenerate(appId, appVersion, prompt));
    const batchResults = await Promise.allSettled(batchPromises);
    for (const batchResult of batchResults) {
      if (batchResult.status == "rejected") {
        outputValues.push(`Error: ${batchResult.reason}`);
      } else {
        outputValues.push(batchResult.value);
      }
    }
  }

  outputRangeObj.setValues(outputValues.map((value) => [value]));
}

/**
 * Handler for when someone clicks "Set API Key" in the UI.
 * Will prompt the user to enter their elvex API key and then set it as a user property.
 */
function setAPIKey() {
  const currentApiKey = getElvexAPIKey();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    "Configure your elvex API key",
    `Provide your elvex API Key.` + 
    typeof currentApiKey === "string" && currentApiKey != "" ? `\nCurrent API key: ${currentApiKey.slice(0, 10)}...` : "" +
    "\nNote: This API key will be saved and used for all users of this document.", 
    ui.ButtonSet.OK_CANCEL
  );
  if (response.getSelectedButton() != ui.Button.OK) {
    return;
  }
  const newApiKey = response.getResponseText().trim();
  if (!isElvexAPIKeyValid(newApiKey)) {
    throw "The provided API key does not appear to be valid, please try again.";
  }
  
  documentProperties.setProperty(
    ELVEX_API_KEY_SETTING, 
    newApiKey,
  );
}

Create a new HTML file and call it Sidebar.

This will create an HTML file with a preloaded HTML script. Delete this, so you have an empty file.

Copy and paste the following text into the Sidebar.html file:

Sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://cdn.tailwindcss.com"></script>
    <style type="text/tailwindcss">
      @layer components {
        input[type="text"] {
          @apply block min-w-0 grow py-1.5 pl-1 pr-3 text-base text-gray-900 placeholder:text-gray-400 focus:outline focus:outline-0 sm:text-sm/6 border;
        }

        label {
          @apply block text-sm/6 font-medium text-gray-900;
        }

        .btn-primary {
          @apply py-2 px-5 bg-violet-500 text-white font-semibold rounded-full shadow-md hover:bg-violet-700 focus:outline-none focus:ring focus:ring-violet-400 focus:ring-opacity-75;
        }

        #status {
          @apply p-2 border rounded-lg;
        }
    }
    </style>
  </head>
  <body class="p-2">
    <form id="form" onsubmit="generateOutput(event)">
      <div class="mb-2">
        <label for="appId">Assistant ID</label>
        <input type="text" id="appId" required>
      </div>
      <div class="mb-2">
        <label for="appVersion">Assistant Version</label>
        <input type="text" id="appVersion" required>
      </div>
      <div class="block form-group">
        <label for="inputRange">Input Range</label>
        <input type="text" id="inputRange" required>
      </div>
      <div class="block form-group">
        <label for="outputRange">Output Range</label>
        <input type="text" id="outputRange" required>
      </div>
      <div class="mt-2 block">
        <button class="btn-primary" type="submit">Generate</button>
      </div>
      <div id="status"></div>
    </form>
    <script>
      var data = JSON.parse("<?=JSON.stringify(data)?>");
      if (data.appId) {
        document.getElementById("appId").value = data.appId;
      }
      if (data.appVersion) {
        document.getElementById("appVersion").value = data.appVersion;
      }

      function generateOutput(evt) {
        evt.preventDefault();

        var appId = document.getElementById("appId").value;
        var appVersion = document.getElementById("appVersion").value;
        var inputRange = document.getElementById("inputRange").value;
        var outputRange = document.getElementById("outputRange").value;
        if (!appId || !appVersion || !inputRange || !outputRange) {
          status.innerText = "Enter values for Assistant ID, Assistant Version"
        }
        var status = document.getElementById("status");

        status.innerText = "Generating...";
        google.script.run.withSuccessHandler(function onSuccess(output) {
          status.innerText = "Completed.";
        }).withFailureHandler(function onFailure(err) {
          status.innerText = `Failure: ${err}`;
        }).generateResponsesForRange(appId, appVersion, inputRange, outputRange);
      }
    </script>
  </body>
</html>

Go to the Code.gs file. Click the Save button, then the Run button.


This will prompt you to allow the script permissions to your sheets file. Give the script access.
​

Using the integration​

Go back to your Google Sheet and refresh the page. You should see an elvex button in the menu bar. Click elvex > Set API Key. Paste the API key you created.

This will save the API key for this sheet. Click elvex > Generate from range. This will open a sidebar that we can enter information for this sheet.

Paste in the Assistant ID and the Assistant Version.

Important Note: Every time you make a change to your assistant configuration by updating the rules or tools, elvex will save that as a new assistant version and the value in the Latest Version bubble will change under the Advanced tab in the assistant configuration. You will need to copy each new version into this input box.

For the Input Range, you can provide a single cell reference (example - A1) or a range reference (example - A1:A5). This will be the input data to the elvex assistant we are connecting to. If you provide a range of a single cell, it will make a single API call to the assistant. If you provide a range of cells, it will make multiple API calls to the assistant in parallel.

For the Output Range, you can provide a single cell reference (example - B1) or a range reference (example - B1:B5).This will be where the assistant outputs the data from the elvex assistant.

Tip: for testing, start by setting the input range to only a single cell, or a small number of cells (less than 5). The script only outputs data when all calls are done, so having a large input range can take some time for the data to return.

Testing and Using the Integration

Run your first test

Click the "Generate" button to run the script. A loading indicator will appear while the API calls are processing. Once complete, check your specified output range for results from elvex.

Optimizing your usage

For initial testing, use a small input range (1-5 cells) to ensure quick response times. The script waits for all API calls to complete before displaying results, so larger ranges will take longer. Consider organizing your sheet with input data in one section and output data in another for clarity.

Troubleshooting common issues

If you see "API Key Invalid" errors, verify your API key is correct and has permission to access the assistant. If outputs show "App Version Not Found," check that you've entered the latest version number from your assistant configuration. For "Rate Limit Exceeded" messages, try processing smaller batches of data or implementing a delay between calls.

Best practices
Update your Assistant Version number whenever you modify your elvex assistant. Save your sheet after successful runs to preserve the generated outputs. For complex workflows, consider adding a timestamp column to track when data was processed.

Next steps
Once your basic integration is working, you can enhance it by adding triggers to automatically run the script on a schedule, creating custom buttons or menus for different elvex assistants, or setting up conditional formatting to highlight important insights from elvex.

Did this answer your question?