Google Sheets - Custom Function (Legacy)

This tutorial will show you how to create a custom function that will be able to pull data from a Google sheet and return that info to the user inside the conversation.

How to Create a New Custom Function:

1. Use ChatGPT to generate the code needed

2. Create a new Google Cloud Project to connect with generated code

3. Test to make sure code is working

4. Create AI agent with custom function

The Dunder Mifflin HR Example

In this example we are building an AI chatbot for Dunder Mifflin HR. This will allow a user to say the name of an employee and the AI agent will go to the Google Sheet with all employee data, find the person, gather all of that person's information and send it back to the user in the chat.

Step 1 - Starting Prompt Template (Example)

Write the python code (flask app) for a new custom function that will be able to get data from a google sheet and send back to the user. Provide the complete code for the Flask app and the JSON parameters for the AI custom function. walk me through the process of setting this up in replit.

User flow:

The AI agent will get a name from the user, the system will find the name in the google sheet and return all of the data.

Google Sheet categories = Name, Email, Location, Favorite Color, Date Joined, Phone Number

example input: “Michael” example output: Name: Michael Scott Email: [e-mail]

Location: Scranton, PA Favorite Color: Blue

Date Joined: 1998-04-01

Phone Number: [phone]

Step 2 - Set Up Access to Google Sheets

Step 1: Set Up a Google Cloud Project

1. Create a Google Cloud Account:

   • If you don’t already have a Google Cloud account, go to Google Cloud and sign up.

2. Create a New Project:

   • Once you are logged in, go to the Google Cloud Console.

   • Click on the project dropdown in the top left corner and select "New Project."

   • Name your project and click "Create."

Step 2: Enable the Google Sheets API

1. Navigate to the API Library:

   • In the Google Cloud Console, use the left-hand menu to navigate to "APIs & Services" > "Library."

2. Search for Google Sheets API:

   • In the search bar, type "Google Sheets API" and select it.

   • Click "Enable" to enable the API for your project.

3. Enable Google Drive API (Optional):

   • If your app also needs to interact with Google Drive (e.g., creating or sharing sheets), you can enable the Google Drive API the same way you enabled the Google Sheets API.

Step 3: Create Service Account Credentials

1. Create a Service Account:

   • In the Google Cloud Console, go to "APIs & Services" > "Credentials."

   • Click on "Create Credentials" and choose "Service Account."

2. Service Account Details:

   • Enter a name and description for your service account and click "Create and Continue."

   • Assign a role to the service account. For accessing Google Sheets, you can select "Editor" or "Viewer" under the "Project" roles, depending on your needs.

   • Click "Done."

3. Generate a JSON Key:

   • After creating the service account, click on it in the list to open its details.

   • Go to the "Keys" tab and click "Add Key" > "Create New Key."

   • Choose "JSON" as the key type, and a JSON file will be downloaded to your computer. This file contains your service account credentials.

4. Rename the JSON File:

   • Rename this file to credentials.json and keep it secure. This file will be uploaded to your Replit project.

Step 4: Share the Google Sheet with the Service Account

1. Open Your Google Sheet:

   • Go to Google Sheets and open the sheet you want your Flask app to access.

2. Share the Sheet:

   • Click on the "Share" button in the top right corner.

   • In the "Share with people and groups" field, enter the email address of the service account. This email address can be found in the credentials.json file under the client_email key.

   • Set the permissions (Viewer or Editor) and click "Send."

Step 5: Deploy the Flask App in Replit

1. Upload the credentials.json File:

   • In your Replit project, upload the credentials.json file to the root directory.

2. Update the Flask App Code:

   • Ensure the code in your Flask app uses the credentials.json file to authenticate with Google Sheets. This code has already been provided in the previous step.

3. Run the Flask App:

   • Click "Run" in Replit to start your Flask app. The app should now be able to access the Google Sheet using the credentials you’ve set up.

Step 6: Test the Integration

1. Send a Test Request:

   • Use a tool like Postman, or curl in the terminal, to send a POST request to your Flask app with a JSON body containing the name you want to look up.

2. Check the Output:

   • If everything is set up correctly, the Flask app will return the user data from the Google Sheet.

Step 3 - Deploy Code in Replit

This is the code used in the example to take the input of a name and return all available data from the Google sheet.

from flask import Flask, request, jsonify
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import difflib

app = Flask(__name__)

# Function to authenticate and get the Google Sheet
def get_google_sheet():
    scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
    creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
    client = gspread.authorize(creds)
    sheet = client.open("Add your google sheet name here").sheet1  # Replace with your Google Sheet name
    return sheet

# Route to handle getting user data by name
@app.route('/get_user_data', methods=['POST'])
def get_user_data():
    name = request.json.get('name')

    if not name:
        return jsonify({"error": "Name is required"}), 400

    sheet = get_google_sheet()
    data = sheet.get_all_records()

    # Extract all names from the sheet and convert them to lower case
    names = [record['Name'].lower() for record in data]

    # Find the closest match to the provided name (case-insensitive)
    closest_match = difflib.get_close_matches(name.lower(), names, n=1, cutoff=0.3)

    if closest_match:
        matched_name = closest_match[0]
        for record in data:
            if record['Name'].lower() == matched_name:
                return jsonify(record), 200

    return jsonify({"error": "Name not found"}), 404

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)

Step 4 - Test Function with ReqBin

1. Open up https://reqbin.com/

2. Paste in the URL from Replit deployment

3. Add in the test data

4. Check that the results are correct from your document

Step 5 - Build Custom Function in Stammer.ai

Create the custom function in Stammer by filling out all of the fields.

Alias

Input: get_user_data

• Explanation: This is the name of the function that will be referenced in the AI agent's base prompt.

Value

Input: http://localhost:5000/get_user_data (or the actual URL of your deployed Flask app)

• Explanation: This is the URL of the Flask app’s endpoint that retrieves user data based on the name. Replace http://localhost:5000 with your actual URL if you're deploying it somewhere other than localhost.

Description

Input: Retrieve user data by name from a Google Sheet. Returns details including Name, Email, Location, Favorite Color, Date Joined, and Phone Number.

• Explanation: This is a brief description of what the function does.

Parameters

Input:

{
  "parameters": {
    "type": "object",
    "properties": {
      "name": {
        "type": "string",
        "description": "The name of the user to look up in the Google Sheet."
      }
    },
    "required": ["name"]
  }
}

• Explanation: This JSON defines the parameters the function requires. In this case, the only required parameter is the name, which is a string.