Stammer.ai Docs
  • Welcome
    • πŸ‘‹Welcome to Stammer.ai
    • πŸ“‘About Stammer.ai
    • πŸ†•New/ Latest Updates
  • START HERE
    • ❓What is Stammer.ai?
    • 🫑Agency Quick Start Guide
    • πŸ“šGlossary
    • πŸ€‘Our Community
  • AI Features
    • πŸ€–AI Agents (Chatbots)
      • How AI Agents Work
      • How to Build an AI Agent
    • πŸ”ƒAI Agent Dashboard
      • (White Label) Marketplace
      • Default Chatbot
      • Share Chatbot
      • Clone Chatbot
      • Chatbot UUID
      • Delete Chatbot
      • Summary (Analytics)
    • πŸ’¬Conversations
      • Review Past Conversations
      • Conversation Links
      • Training Better Responses
      • Live Chat
        • Human Handoff Automation (for Live Chat)
    • πŸ“šKnowledge Base Explained
      • πŸ’½Data Storage (Characters)
        • Storage (Characters) Example
      • Copy & Paste Text
      • Upload Documents
      • Scrape Websites
        • 24-hour Auto-Scraping
        • Scraping a Google Doc
        • Google Docs
      • Question & Answer Pairs
      • Data & Content Security
      • Citations/ Sources
      • HTML Rendering (Embed Images & Videos)
    • ✍️Prompting
      • What is a Base System Prompt?
      • Writing a Base System Prompt
      • Prompt Examples (Marketplace)
        • Base System Prompt Template (General)
    • πŸ”§AI Agent Settings
    • 🎨AI Chat Widget
      • Customize Chat Widget Apperance
      • Chat Widget Pop-Up
      • Initial Messages Pop-Up
      • Chat Widget Voice
    • βš™οΈAdvanced Settings for AI Agent
      • Model Version
      • Prioritize Question/Answer Results
      • Chatbot Visibility
      • Temperature
      • Message Template
      • Support Email
      • Domains
      • Show Data Sources in Chat Widget
      • Debug Mode
    • Leads 2.0
    • πŸ“ˆLeads (Legacy)
      • Overview of Lead Generation
      • Lead Collection Form Fields
      • Lead Collection Webhook URL
      • Lead Collection Email Notification
      • In Chat Lead Collection Base System Prompt
      • Sending Lead Data to Zapier
      • Sending Lead Data to GHL
      • Sending Lead Data to Zoho CRM
    • πŸ“…Scheduling
      • AI Scheduling - Simplified
      • AI Scheduling - Experimental (Beta)
        • What is Scheduling?
        • Calendar Integration
          • Google Calendar
        • Appointment Scheduling Activation
        • Appointment Scheduling Webhook URL
        • Appointment Scheduling Email Notification
        • Appointment Scheduling Base System Prompt
        • The Appointment Scheduling Functions & Customizing the Appointment Scheduling Prompt
    • 🧊Integrations
      • Embed Chatbot on a Website
      • Zapier App
      • GoHighLevel (GHL)
      • Instagram DM
      • Facebook Messenger
      • WhatsApp
      • ElevenLabs Voice
      • Discord
    • πŸ€–Troubleshooting
      • How to Respond in Any Language
      • Responses are Too Long
      • Debug Mode
      • Clickable Links in Responses
    • 🌌Functions (Labs)
      • Custom Functions
        • How Custom Functions Work
        • Code Generation Prompt for Custom Functions
        • Stock Market Data - Custom Function
        • Shopify - Custom Function
        • Airtable - Custom Function
        • Google Sheets - Custom Function
    • πŸ”„Vault
      • OpenAI API Key (ChatGPT)
      • Anthropic API Key (Claude)
      • xAI API Key (Grok)
      • Eleven Labs API Key
  • πŸ‘‘MasterChat
  • White Label
    • πŸ€‘What is White Labeling?
    • πŸ‘€Client Sub-Accounts
      • Create Sub-Account
      • Sub-Account Wallet
      • Sub-Account Storage Balance
      • Sub-Account Extra AI Agents
      • How to clone a chatbot to a sub-account
    • πŸ”—Custom Domain
      • Troubleshooting Custom Domain Deployment DNS Issues
    • πŸ—οΈThe SaaS Configurator
      • Create Subscription Package
        • Custom Packages + Settings
      • Add-Ons
        • Storage (Characters) - Add Ons
        • AI Agent Rebilling - Add Ons
        • MasterChat - Add Ons
        • Advanced Scraper - Add Ons
    • πŸ‘White Label API
      • API Documentation
      • AI Agent API
        • Message AI Agent
        • Create AI Agent
        • Retrieve AI Agent
        • Update AI Agent
        • Delete AI Agent
      • Knowledge Base API
        • Add AI Agent Q/A
        • Add URLs to Scrape
        • Add Files (Pdf, Doc etc.)
      • Conversations
        • AI Agent Conversations
        • Retrieve Conversation
        • Delete A Conversation
      • Sub-Accounts
        • Create Sub-Account
        • Retrieve Sub-Account
        • Update Sub-Account
        • Delete Sub-Account
      • User
        • Retrieve User's Data
      • Examples
        • Create New Sub-Account (Zapier Example)
        • White Label API x Zapier X Slack Example
        • AI Chatbot Chrome Extension
    • πŸ‘ΎWhite Label (Your) Marketplace
    • πŸ˜‡White Label Free Trials
      • Free Trial Settings (No CC Req.)
      • Free Trial Settings (CC Req.)
    • 🎨Custom Dashboard Styling
  • Agency Settings
    • β›³Agency Dashboard
    • πŸ‘₯User Permissions
    • 😎Agency Profile
    • πŸ‘₯User Access
    • πŸ’³Connect Stripe
    • πŸ”—Custom Menu Links
    • 🀘Default Prompts
  • Account Management
    • βš™οΈBilling Usage & Settings
    • πŸ’²Agency Billing
      • Subscription Plans
      • Change Subscription Plan
      • Update Credit Card
      • Download Invoice
      • Refunds
      • Cancel Subscription
    • πŸ’±Agency Wallet
      • The Agency Wallet System
      • Agency vs Sub-Account Wallet
      • Auto Billing
      • Add Funds to Wallet
      • AI Messages Costs
      • AI Agents Costs
      • Storage (Characters) Costs
    • 🀲Admin Account Profile
      • Change Password
      • Google Auth Login
  • Support
    • 🀝Need Help?
    • πŸ”“Security & Compliance
      • Data Privacy & Security Report
      • GDPR
      • Terms & Conditions
      • Privacy Policy
      • Data Processing Agreement
      • AI Model Data Usage
      • Service Level Agreement
    • ⚑Discord Community
    • πŸ“ΉWeekly Office Hours
    • πŸ™ŒFAQ
      • General
      • Localization
      • Data/Security
      • Set Up
      • Password
      • Technical Questions
      • Sales/Onboarding
      • Marketing/Pricing
      • Where can I find...?
      • Other
      • Alternatives to Stripe Connect
    • πŸ“°Changelog/ Roadmap
    • 🍭Resources
      • Sales Deck
      • πŸ’°AI Agency 101
        • How to Price Your AI Chatbots
        • How to Get More Clients
        • The Value Proposition of AI Agents
        • AI SaaS Funnel Explained
    • πŸ€‘Affiliate Program
Powered by GitBook
On this page
  • The Dunder Mifflin HR Example
  • Step 1 - Starting Prompt Template (Example)
  • Step 2 - Set Up Access to Google Sheets
  • Step 3 - Deploy Code in Replit
  • Step 4 - Test Function with ReqBin
  • Step 5 - Build Custom Function in Stammer.ai
  1. AI Features
  2. Functions (Labs)
  3. Custom Functions

Google Sheets - Custom Function

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.

PreviousAirtable - Custom FunctionNextVault

Last updated 8 months ago

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

Location: Scranton, PA Favorite Color: Blue

Date Joined: 1998-04-01

Phone Number: (570) 555-1234

Step 2 - Set Up Access to Google Sheets

Step 1: Set Up a Google Cloud Project

  1. Create a Google Cloud Account:

  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. Paste in the URL from Replit deployment

  2. Add in the test data

  3. 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.

example input: β€œMichael” example output: Name: Michael Scott Email:

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

Open up

🌌
michael.scott@dundermifflin.com
Google Cloud
https://reqbin.com/