# Google Sheets - Custom Function (Legacy) {% embed url="" %} 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: 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**: * If you don’t already have a Google Cloud account, go to [Google Cloud](https://cloud.google.com/) 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. ```python 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 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**: ```json { "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. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.stammer.ai/stammer.ai-docs/chat-ai-agents/functions-labs/custom-functions-legacy/google-sheets-custom-function-legacy.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.