Hello everyone,
Hope all is well.
Today I wanted to share with you how to teach your Apollo to lookup product info based on code using a custom OpenAPI 3.0 schema.
Part 1: Google Sheet
First you will need to bring your product list to Google Sheets
Then you need to add a script to this sheet:
In the Apps Script interface, add the following script:
function doPost(params) {
const query = params.parameter.query
const {
entries,
total
} = findInSheet({
searchColumnIndex : 1,
query,
limit : 10,
hasHeaderRow : true,
fullMatch : false,
caseSensitive : false,
columnKeys : {
'code' : 1,
'name' : 2,
'description' : 3
}
})
const result = {
total,
entries : entries
}
return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}
/**
* Finds values in sheet and formats them
* @param {Object} params
* @param {number} params.searchColumnIndex Number of column to search (column numbering starts from 1)
* @param {string} params.query Query to search for
* @param {string} params.limit Number of entries to return.
* @param {boolean} params.headerRow Whether data includes header row
* @param {boolean} params.fullMatch Should a full match be performed.
* @param {boolean} params.caseSensitive Whether to match in sensitive manner,
* @param {Record<string, number>} params.columnKeys Object containing column keys in `title : column` format. Column numbers start from 1 for the first column
*/
function findInSheet(params) {
const {
searchColumnIndex,
query,
hasHeaderRow,
fullMatch,
caseSensitive,
columnKeys,
limit
} = params;
// Get the active spreadsheet and the active sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the range of the specified column
var columnRange = sheet.getRange(hasHeaderRow ? 1 : 2, searchColumnIndex, sheet.getLastRow(), 1);
var finder = columnRange.createTextFinder(query);
// Do a full match (set to `false` to do partial matches)
finder.matchEntireCell(fullMatch);
// Do a case-insensitive matchin
finder.matchCase(caseSensitive);
// Get the values of the specified column
var matches = finder.findAll();
const entries = []
// Find range we need to find
const dataColumnLenght = Math.max(...Object.values(columnKeys));
const columnDefs = Array.from(Object.entries(columnKeys))
const total = matches.length;
for (const foundValue of matches) {
var rowRange = sheet.getRange(foundValue.getRow(), 1, 1, dataColumnLenght);
// Get the values of the specified row
var rowValues = rowRange.getValues();
const rowObject = {};
for (const [key, column] of columnDefs) {
rowObject[key] = rowValues[0][column - 1];
}
entries.push(rowObject);
if (entries.length >= limit) {
break
}
}
return {
total : total,
entries: entries
};
}
Depending on what labels your columns have, update this part accordingly:
columnKeys : {
'code' : 1,
'name' : 2,
'description' : 3
Once the script is done you need to Deploy it:
Deploy it as Web app
Make sure that Access is given to Anyone so AI can use it:
Once you have authorized the deployment you will get a link to your web app:
Part 2: OpenAPI 3.0 Schema:
For this, you will need to host your schema .json or .yaml file. For this demo, I used Gist from Github:
These are the instructions I used:
openapi: "3.0.0"
info:
version: 1.0.0
title: Google Apps Script API
description: >
This API retrieves product descriptions based on code
license:
name: MIT
servers:
- URL: < YOUR SCRIPT WEB APP LINK GOES HERE >
paths:
/exec:
post:
summary: Retrieve product details based on product code
operationId: findProductsByCode
parameters:
- in: query
name: query
schema:
type: string
minLength : 3
required: true
description: Code to look up products. Performs a partial match. Minimum of 3 characters
responses:
'200':
description: Found products
content:
application/json:
schema:
type: object
properties :
total:
type: number
description : Total number of that match the provided code
entries:
type: array
items:
type: object
properties:
code:
type: string
example: "A123"
name:
type: string
example: "Example Name"
description:
type: string
example : "Product A123 is very awesome"
'400':
description: Missing parameters
content:
application/json:
schema:
type: object
properties:
status:
type: string
message:
type: string
example:
status: error
message: Missing parameters
AI Assistant would use the description here to know when to launch the function.
Add your web app link to this part of the schema:
servers:
- URL: < YOUR SCRIPT WEB APP LINK GOES HERE >
It is important to remove ‘’/exec’’ from the end of the link when placing it in the schema
Lastly, save your schema as a public .json or .yaml file.
Part 3: Giving Apollo the tool
Now that you have your schema saved, go to tawk.to dashboard ->Add-ons → AI Assist → Integration/API and Add Tool.
Note that this requires a paid subscription to AI Assist addon.
Now post the link to your schema and save the tool:
Once the tool is enabled, AI will look for the scenario that fits the description and ask the user for the required details accordingly:
A few points of note:
- This setup will only query for the first code column and fetch the relevant row. For a more sophisticated lookup based on the contents of other columns, the code would have to be expanded accordingly by your developers.
- Depending on the use case, you can adjust the parameters in the Google Apps script to look for exact matches and case sensitivity:
fullMatch : false,
caseSensitive : false,
- Depending on the number of columns you have you might need to limit the number of matches it includes in response
} = findInSheet({
searchColumnIndex : 1,
query,
limit : 10,
- Modern problems require modern solutions so if you need help adjusting either the Apps Script or OpenAPI schema, you can ask GPT to explain what each row does and help with changes.
As always, if you need any help with your AI Assistant, feel free to reach out to me directly at ralfs@tawk.to
Best regards,