Call Vertex AI via Apps Script

[derekco]

Generative AI

This use case integrates with the PaLM 2 for Text model in Vertex AI via an API connection. Vertex AI has a slew of pre-trained models that can be used out of the box or tuned for specific tasks. The connection details are similar across models. So once you get this app up and running, you can apply the same design to integrate with other models too. Detailed connection information below.

Process Overview

Check out this recorded demo of the user experience

https://youtu.be/dMtE27dW4rg

This example uses a Payroll and HR team. These roles can be generalized to “Client” and “Support Desk”. The Payroll team (or client) represents a group of people who occasionally need help with particular issues. The HR team (or support desk) represent a group of people who must prioritize, organize, and respond to requests.

Other examples of this are:

• Office workers with IT issues
• Employees with maintenance requests
• Nurses with equipment requests
• Manufacturing teams with supply requests

Sticking with the Payroll and HR team example, there are two primary roles, each with a few requirements:

• Payroll team - sends requests via email for help with issues at their branch
• Easy interaction via email
• No additional websites, apps, or tools should be required
  
  
• HR team - manages a prioritized list of requests in a dashboard
• Central dashboard to view tickets
• Generative AI assistance to reduce response time per ticket
• Side by side access to a knowledge base with intelligent search
• SLA tracking
• Clear visual formatting to indicate high priority or past due tickets
• Responses generated in the dashboard must be automatically sent as inline email responses

The diagram below depicts the process flow:

Steps to Deploy

This app uses Apps Script to monitor an inbox for new messages. It is easiest for the account that receives incoming emails to also own the AppSheet app and Apps Script project. This isn’t strictly necessary, but it is a good starting point.

Resources:

• Public app template (read only, so make a copy)
• Apps Script template (placeholders for connection details)

Deploy the AppSheet App

1. Open the public app template using the AppSheet account based on the same email that will receive incoming service requests.
2. Make a copy of the template (Manage > Collaborate & Publish > Copy App)
3. Open the Apps Script template using the same account that now has a copy of the app
4. Make a copy (Overview > copy button)
5. Return to the app editor and click on Automations (robot icon on far left)
6. Click on Messages > Send Response > Send via Apps Script
1. Click on ‘Apps Script Project’ and select your copy of the Apps Script project
2. Save the app definition
3. Click ‘Authorize’ 
4. Set ‘Function Name’ to ‘respondToThread (threadId, name, body)’
5. Save the app definition
7. Click on Prompts > Fetch AI Response > Fetch AI Response
1. Click on ‘Apps Script Project’ and select your copy of the Apps Script project
2. Set ‘Function Name’ to ‘callTextAI (prompt)’
3. Save the app definition
8. Enable the AppSheet API for your app
1. Open Settings > Integrations > IN: from cloud services to your app
2. Click the toggle switch to Enable
3. Click ‘Create Application Access Key’
4. Save the app
5. You’ll need the App ID and Access Key when configuring the Apps Script project

Create a GCP Project

Follow these steps to configure a GCP Project with access to Vertex AI. See this page for pricing. The cost for an experimental version of this app will be very low. Ballpark estimate is about $5 per 1000 iterations of the full process.

1. Open the GCP Console
2. Create a new project or open one to which you have access
3. Create a new service account with the ‘Vertex AI User’ role (IAM > Service Accounts). You’ll need the service account email address later when setting up the Apps Script project
4. Create a JSON security key for your service account. You’ll need this when setting up the Apps Script project.
5. Enable the Vertex AI API

Update the Apps Script Project

Complete these steps to customize the Apps Script project:

1. Open the project in Apps Script and open the constants.gs file
2. Update the GCP connection details
1. PROJECT_ID
2. SERVICE_ACCOUNT_EMAIL
3. SERVICE_ACCOUNT_PRIVATE_KEY
3. Update the AppSheet API settings
1. APP_ID
2. ACCESS_KEY
3. TIMEZONE (Note: the script sets a static timezone, but this could be configured per-user)
4. Save changes
4. Create a Trigger
1. Open Triggers
2. Click ‘Add Trigger’ (bottom right)
3. Set function to ‘monitorInbox’
4. Set type to ‘Minutes timer’
5. Set interval to ‘Every minute’
6. Save changes

The Apps Script has a couple dependencies. Both will copy over if you copy the Apps Script project into your account. So this is just FYI:

• OAuth2 Library: This is used to generate an Auth token for calling the Vertex AI API
• Gmail API: This is a service used for working with gmail.

Test it out

Follow these steps to make sure it’s working:

1. Send an email to the account that owns the app
2. Wait for the trigger to run, or run the ‘monitorInbox’ function manually
3. Resync the app and you should see the email in the Ticket Dashboard
4. Open the associated message
5. Click ‘Prompt response’
6. Use the boiler plate prompt or modify
7. Wait for AI to generate draft
8. Click ‘Use draft’
9. Make any necessary changes
10. Save the form
11. Check the email thread to see the inline response

App Design Overview

I kept the app relatively simple. There are always lots of bells and whistles that can be added. Watch the ‘how it’s made video’ for a walkthrough of the nifty design patterns in this app.

[mateo]

Hi @derekco ,
First of all, thanks for this post. I am currently diving deeper into Vertex AI <--> AppSheet integration and it helped me a lot. For now, I am wondering if it is possible to make this applicable to more than just one Gmail Inbox. I am not sure how to accomplish that because when AppSheet is deployed under account X, it has access to GAS projects deployed on that same account and GmailApp Class gets access to the project owner's Google account. This is something tricky for me. I am not sure how to approach this. Maybe I need to authorize to get emails and threads from another account (within the organization)? Maybe you know how to do this? 😊
Cheers!

[derekco]

Hi Mateo,

The inbox monitoring part could be deployed on multiple accounts. Each user would need to copy the Apps Script template into their account. Then create a trigger to run the function on a schedule. Emails from each inbox would be saved in the data source connected to the AppSheet app and appear in the AppSheet dashboard.

There are a lot of cool use cases for Apps Script + AppSheet. However, the Apps Script side is outside of the no-code envelope. Google Partners are the best source for implementation support if you want to dive deeper into Apps Script. 

https://cloud.google.com/find-a-partner/?search=Apps%20Script

[mateo]

Thanks, @derekcoI will consider the multi-layer deployment.

[Aurelien]

Amazing! Thank you for sharing this tip @derekco , I can't wait to give it a try!

[scott192]

This is really useful @derekco 
Thanks for taking the time to write this up!