Integrate ChatGPT (OpenAI API) into your research project (Part 2): Excel
In the realm of research, the integration of cutting-edge tools can dramatically streamline processes, enhance data analysis, and unlock insights with unprecedented efficiency. One such revolutionary tool that has been making waves across various fields is ChatGPT, powered by OpenAI's formidable API.
This article aims to guide researchers, data analysts, and enthusiasts on how to leverage ChatGPT within Microsoft Excel, transforming it from a mere spreadsheet tool into a powerful platform for generating insights and automating tasks through natural language processing.
Whether you're aiming to automate data analysis, generate content, or simply explore the potential of AI in research, this article serves as a comprehensive guide to getting started.
By the end of this tutorial, you'll have a fully functional setup that allows you to interact with ChatGPT directly from your Excel workbook, opening up a world of possibilities for enhancing your research projects. Let's embark on this exciting journey to merge the analytical prowess of Excel with the innovative capabilities of ChatGPT.
Read more in this series:
1. On the File tab in Excel, go to Options > Customize Ribbon. Under Customize the Ribbon and under Main Tabs, select the Developer check box.
2. On the Developer tab, select Visual Basic (VB)
3. In the VB window, select insert > Module. This creates an empty module for adding code later.
4. Open a GitHub page https://github.com/VBA-tools/VBA-JSON, select Code > Download ZIP. Extract the downloaded ZIP file.
5. In the VB window, select File > Import File, and select the file “JsonConverter.bas” in the extracted folder.
领英推荐
6. For window user, Select Tools > References. For Mac user, download and import VBA-Dictionary (https://github.com/VBA-tools/VBA-Dictionary) instead.
7. Select Microsoft Scripting Runtime in the References window, click OK.
8. Paste the following code into the module window. Replace “YOUR_API_KEY” with your actual OpenAI API key.
Sub OpenAI()
Dim ws As Worksheet
Dim activeRow As Long
Dim activeColumn As Long
Dim http As Object
Dim url As String
Dim apiKey As String
Dim model As String
Dim response As String
Dim jsonBody As String
Dim json As Object
Dim ChatGPT As String
' Replace YOUR_API_KEY with your actual OpenAI API key YOUR_API_KEY
apiKey = "Bearer YOUR_API_KEY"
' Your POST request URL
url = "https://api.openai.com/v1/chat/completions"
' Set the model
model = "gpt-3.5-turbo"
' Initialize the HTTP request object
Set http = CreateObject("MSXML2.XMLHTTP")
' Set the worksheet
Set ws = ActiveSheet
activeRow = ActiveCell.Row
activeColumn = ActiveCell.Column
' Check if the two cells to the left of the active column are not empty
If ws.Cells(activeRow, activeColumn - 2).Value <> "" And ws.Cells(activeRow, activeColumn - 1).Value <> "" Then
' Prepare your JSON body for the POST request
jsonBody = "{""model"":""" & model & """, ""messages"":[{""role"":""system"", ""content"":""" & ws.Cells(activeRow, activeColumn - 2).Value & """},{""role"": ""user"", ""content"":""" & ws.Cells(activeRow, activeColumn - 1).Value & """}]}"
' Open the POST request
http.Open "POST", url, False
' Set the necessary headers.
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Authorization", apiKey
' Send the request with the JSON body
http.Send (jsonBody)
' Get the response text
response = http.responseText
' Debug information
Debug.Print jsonBody
Debug.Print response
' Parse the JSON response
Set json = JsonConverter.ParseJson(response)
' Extract the desired data
If json.Exists("error") Then
ChatGPT = "ERROR: " & json("error")("message")
Else
ChatGPT = json("choices")(1)("message")("content")
End If
' Fill in the response
ws.Cells(activeRow, activeColumn).Value = ChatGPT
End If
End Sub
9. Switch to Excel, create three columns. First for system messages, second for user prompts. Place these columns anywhere and highlight the cell in the third column
10. Select Macros in Developer page, our module will be shown in the popup, click Run.
11. It will put the API answer into the cell after some seconds.
12. If you forget to replace the API key, it will show the error message below. Other error messages will also appear here.
With this setup, your Excel can now chat with ChatGPT, making your spreadsheet smarter than ever. This powerful combo can automate tasks, analyze data, and even answer complex questions, all within Excel. Dive in, play around with different prompts, and see how it transforms your research work. Who knew Excel could get this exciting? Keep experimenting, and enjoy your new, intelligent research assistant!
Happy researching!