Introduction
In this blog, we’ll dive into the fascinating world of building an appointment bot using Langchain and integrating it with Google Sheets using Python. Imagine having an automated system that updates your Google Sheets for efficient record-keeping.
Make an API in Google Sheet
Before creating the AI chatbot that helps to create an appointment. We need to create one API for Google Sheets that will take the required arguments and insert the details in the particular Google Sheet.
Many services like Zapier provide the ready-to-implement template to do the desired thing, But the only downside of these services is that they cost us money.
Luckily Google Sheets provides one tool called Apps Script to implement a custom JavaScript code for API creation. So let’s use it.
Follow the below steps to create an API endpoint for Google Sheets.
- Create a new Google Sheet and add the columns’ names you want.
Let’s take the three columns for our Appointment Sheet called Name, Time, and Reason.
- Go to Extensions and click on Apps Script
- Now you have to create one JavaScript function
This JS function will help to insert the fetched information into Google Sheets.
Don’t worry if you do not have any knowledge of JavaScript(Like me). I have made one example function below that will help you with this.
function doGet(e) {
Sheets.Spreadsheets.Values.append({"majorDimension":"ROWS","values": [[e.parameter.name,e.parameter.time,e.parameter.query]]},
SpreadsheetApp.getActiveSpreadsheet().getId(),
SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getSheetName() + "!A:A",
{valueInputOption:"USER_ENTERED"});
return ContentService.createTextOutput("thanks for the update");
}
3. Click on Services from the left panel and Add Google Sheets API.
4. Click on Deploy.
select the description type Web App and add the Description on the app. Make sure that the access is set to Anyone to be able to call the API endpoint from any script without authorization.
And that is it. You will be able to see the URL after the deployment, that is your API endpoint. We will use that in our Python Script.
Python implementation using appointment bot using the Langchain agent
Now let’s come to the Python script part. The libraries that we are going to use are as follows:
from langchain.chains import create_extraction_chain
from langchain.agents import tool
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain.agents import AgentExecutor
from langchain.agents.format_scratchpad.openai_tools import (
format_to_openai_tool_messages,
)
from langchain.agents.output_parsers.openai_tools import OpenAIToolsAgentOutputParser
Here we are going to create a tool using the Langchain that will be used by the Agent to perform our task.
As you can remember, the Google Sheets API that we have made above is a GET method that accepts values of name, time, and reason. So Before making a tool for the Agent, we need a way to extract the name, time, and reason from the user’s string.
Luckily there is a special type of chain called create_extraction_chain in Langchain that helps to extract the information from the string. So let’s create one function for it.
But first, let’s add all the required libraries in Python:
from langchain.chains import create_extraction_chain
from langchain.agents import tool
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain.agents import AgentExecutor
from langchain.agents.format_scratchpad.openai_tools import (
format_to_openai_tool_messages,
)
from langchain.agents.output_parsers.openai_tools import OpenAIToolsAgentOutputParser
llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
def extract_info_for_appt(text):
schema = {
"properties": {
"name": {"type": "string"},
"time": {"type": "string"},
"reason": {"type": "string"},
}}
chain = create_extraction_chain(schema, llm)
return chain.run(text)
Now Let’s create the Langchain tool that will be used by an agent to book an appointment. We can convert any Python function into a custom tool by using a decorator.
Here the function helps to call the Google Sheet’s API endpoint to insert the record in the sheet.
@tool
def update_sheet(text:str):
"""Books the Appointment in the Sheet via API"""
#Extract the name, time and reason of appointment
extraction_chain = create_extraction_chain(schema, llm)
result=extract_info_for_appt(text)[0]
name=result.get('name',"null")
time=result.get('time',"null")
reason=result.get('reason',"null")
#call the API endpoint
URL="<<Google Sheet's API URL>>"
PARAMS = {'name':name,'time':time,"query":reason}
# sending get request and saving the response as response object
r = requests.get(url = URL, params = PARAMS)
tools=[update_sheet]
llm_with_tools = llm.bind_tools(tools)
Here in the llm_bind_tools we have only added the update_sheet function as a tool. But you can add more tools according to your use case and also can use ready-made tools that are available on Langchain.
Our tool is also ready. Now we need to use this in an Agent. So let’s make an agent and add the tool to it.
prompt = ChatPromptTemplate.from_messages(
[
(
"system",
"You are very powerful assistant, but don't know current events. When you receive date convert it into DD/MM/YYY format.",
),
("user", "{input}"),
MessagesPlaceholder(variable_name="agent_scratchpad"),
]
)
agent = (
{
"input": lambda x: x["input"],
"agent_scratchpad": lambda x: format_to_openai_tool_messages(
x["intermediate_steps"]
),
}
| prompt
| llm_with_tools
| OpenAIToolsAgentOutputParser()
)
agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=True)
Now our agent has been created with our tool to add the details in the script.
So let’s test it out:
response=agent_executor.invoke({"input": "Hi my name is Kathan and I want to book an appointment for 27 April. I just want to ask about available purchase plans for my Camaro GT."})
Let’s see in the Google Sheet Also:
And Here you go. You have just made the Appointment bot.
Also read: How to Create A Chat GPT-3 Web app with Streamlit in Python