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.
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.
Next, create or find an app that you have access to edit. In the app configuration, under Security & Permissions, click the App 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 App ID and Latest Version values. We will use this later.
Important Note: Every time you make a change to your app configuration by updating the rules or tools, elvex will save that as a new app version and the value in the Latest Version bubble will change. You will need to copy each new version number into the Google Sheets script, described below.
At the bottom, click Save & Publish to save your changes.
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
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
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">App ID</label>
<input type="text" id="appId" required>
</div>
<div class="mb-2">
<label for="appVersion">App 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 App ID, App 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.
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 App ID and the App Version.
Important Note: Every time you make a change to your app configuration by updating the rules or tools, elvex will save that as a new app version and the value in the Latest Version bubble will change under the Advanced tab in the app 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 app we are connecting to. If you provide a range of a single cell, it will make a single API call to the app. If you provide a range of cells, it will make multiple API calls to the app 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 app outputs the data from the elvex app.
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.
Click Generate to run the script. All values will be sent via API to elvex to run for the given App ID and App Version.