Set Up Dialogflow
- Navigate to the Dialogflow Console and create a new agent or choose an existing one.
- In your agent's settings, go to the "General" tab and locate your agent's Project ID. This ID will be used later for authentication and accessing the agent programmatically.
- Generate a Service Account JSON key for your Dialogflow agent. Go to the Google Cloud Console, navigate to "IAM & Admin" > "Service Accounts", and create a new key for your project. Download this key in JSON format.
Create API Access in Excel
- Open Microsoft Excel and enable the "Developer" tab if it's not already available. To do this, go to "File" > "Options" > "Customize Ribbon" and check "Developer".
- In the "Developer" tab, select "Visual Basic" to open the VBA Editor.
- In the VBA Editor, go to "Tools" > "References" and check "Microsoft Scripting Runtime" and "Microsoft WinHTTP Services" for required libraries.
- Insert a new module by right-clicking on any existing module in the "Project Explorer" window and selecting "Insert" > "Module".
Write the VBA Script
- Create a function to authenticate and send a query to your Dialogflow agent using the API. Use the following VBA code template:
' Function to query Dialogflow
Function QueryDialogflow(query As String, credentials As String) As String
Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim url As String
url = "https://dialogflow.googleapis.com/v2/projects/<YOUR_PROJECT_ID>/agent/sessions/<CONSULTAION_ID>:detectIntent"
Dim payload As String
payload = "{""queryInput"":{""text"":{""text"":""" & query & """,""languageCode"":""en""}}}"
http.Open "POST", url, False
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Authorization", "Bearer " & GetAuthToken(credentials)
http.send payload
QueryDialogflow = http.responseText
End Function
' Function to get authorization token
Function GetAuthToken(credentials As String) As String
' Add code to authenticate using the JSON credentials and return an OAuth token
End Function
- Replace <YOUR_PROJECT_ID> and <CONSULTAION\_ID> with your Dialogflow’s Project ID and a session ID (could be any unique string).
- Implement the `GetAuthToken` function to handle authentication. This function should read the Service Account JSON file, use Google's OAuth 2.0 endpoint to generate an access token, and return the token.
Use the Function in Excel
- Go back to your Excel sheet and use the `QueryDialogflow` function like any other Excel function. Enter a cell reference or a string in double quotes as the `query` parameter and the path to your credentials JSON file as the `credentials` parameter.
- Check the output cell for the JSON response from Dialogflow, which includes the intent and response text.
Handle JSON Response in VBA
- To process the JSON response from Dialogflow, use a library to parse JSON in VBA. Consider using a JSON parser like "VBA-JSON".
- Download the VBA-JSON parser and include it in your VBA project via "File" > "Import File".
- Use the parser to extract specific fields from the JSON response, such as the resolved query and fulfillment text.
' Example to parse JSON response
Dim json As Object
Set json = JsonConverter.ParseJson(http.responseText)
Dim fulfillmentText As String
fulfillmentText = json("queryResult")("fulfillmentText")
- Display the parsed data back in Excel cells for further analysis or presentation.
