How to Integrate Google's Gemini AI into Excel

This post covers various ways to integrate Google's Gemini AI Model into MS Excel. Google released a public API for Gemini which can be used to run it inside Excel.

Table of Contents

---

How to Get Gemini API

To get an API Key for the Gemini AI model, all you need to do is visit this link - makersuite.google.com and sign up using your Google account.

The Gemini API is free as of now but it will most likely have charges in the future.

Method 1 : Excel Plugin

You can download the Excel plugin for Gemini by clicking on Download button below.

Note - This Gemini plugin is compatible with both Windows and macOS operating systems.

How to Install Add-in

When you download add-in or macro file from internet, Microsoft blocks them from running and shows the source of this file is untrusted. You need to follow the steps below to make it working.

1. Go to the folder where the downloaded add-in file is located.
2. Right-click the file and choose Properties from the menu.
3. At the bottom of the General Tab, select the Unblock checkbox under security option and then click OK.

Refer the following steps to install Gemini add-in in MS Excel.

1. Open Excel and click on the File tab in the ribbon.
2. Click on Options and then select Add-ins from the left-hand menu.
3. In the Manage drop-down menu at the bottom of the screen, select Excel Add-ins and click on the Go button.
4. Click on the Browse button and locate the add-in file that you downloaded.
5. Select the add-in file and click on the OK button.
6. You should see the name of the add-in file in the Add-Ins dialog box. Check the box next to the add-in name to activate it.
7. Once you are done with the above steps, a new tab called Gemini should be visible in your Excel workbook.

How to Use Gemini's Excel Add-in

Follow the steps below to use Gemini add-in for MS Excel.

1. Open a new or existing MS Excel Workbook
2. Enter text you want to ask Gemini in any cell
3. Click on Gemini Tab > Talk to Gemini.
4. Enter your API Key.
5. Select the cell in which you entered text in step 2.
6. Result will be generated and become visible after processing.
7. To run on multiple cells, you can use the Gemini() function. Refer to the animated image below.

Functions of Gemini's Excel Add-in

This add-in has the following functions.

1. Gemini(text, [word_count]) : Returns output for the question you asked.
   - text : Question you want to ask
   - word_count : Optional. Specify the maximum number of words for the output generated by Gemini.
2. Gemini_Chat(text, [reset]) : Converse like Bard chatbot which remembers prior conversations.
   - text : Ask the question you want to know.
   - reset : Optional. Start/Reset chat session.
3. Gemini_FillData(rng_existingdata, rng_fill) : Fill missing data by training Gemini on existing data.
   - rng_existingdata : Range of training data
   - rng_fill : Specify cell that needs to be filled in.
4. Gemini_Extractor(prompt, keyword) : Extract key data from the text you provided. Key Data can be Name, Place, Organization details etc.
   - prompt : Specify the cell which contains text from which you want to extract key data.
   - keyword : keyword can be name, place, organisation etc.
5. Gemini_Translator(text, language) : It translates text from one language to another.
   - text : Specify cell that contains text that you want to translate.
   - language : Name of language. E.g. Spanish, French etc.
6. Gemini_Explain(cell_formula, [detail]) : It helps you understand in detail any Excel formula you want to know.
   - cell_formula : Cell that contains an Excel formula that you want Gemini to explain to you.
   - detail : Optional. Default value is TRUE. Set FALSE if you want less verbose reply.
7. Gemini_Insights(rng_data, [prompt]) : It generates data insights from the provided dataset.
   - rng_data : Specify cells containing data (including the header).
   - prompt : Optional. Provide specific information you want about the data.
8. Gemini_QnA(query, passage) : It returns an answer to a question based on the passage you provided.
   - query : Specify the cell containing a question.
   - passage : Specify the cell containing a large text.

Chat like Bard

By default when we send API requests to Google, it does not recall prior conversations. It considers every API request as a new chat. It means when it answers your current question, it does not remember your previous question.

To improve Gemini's ability to remember prior conversations, we can use the function Gemini_Chat(text, [reset]). Set second argument reset as TRUE to start/reset chat.

Fill Missing Data

The Gemini_FillData function can be used to fill missing data. All you need is some existing data to train on and then populate the missing data accordingly. It can be beneficial for tasks such as labeling customer reviews and classifying companies into industries.

How to Develop Question and Answering System

The Gemini_QnA function helps create a question and answering system. You give it a paragraph or a text document, ask a question and it finds the right answer in that text. It's useful for getting information from big chunks of text without having to search manually. Check out the example below.

=Gemini_QnA(query, passage)

Method 2 : Excel Macro

The following macro does the same what the Gemini() function of add-in does. It returns response from Gemini AI Model using API based on prompt.

Note - This macro only works on Windows OS. To run Gemini API Model in MS Excel on Mac OS, please use the add-in shown in the previous section of the article.

In the macro below, it assumes prompt or your search query in cell B2.

Sub gemini()
  
  Dim request As Object
  Dim response As String
  Dim text, API, api_key, DisplayText, error_result As String
  Dim status_code As Long
  Dim cellr As Range
  Dim prompt, rng As Range
  
  'Update API Key and Cell containing prompt
  api_key = "XXXXXXXXXXXXXXXXXX"
  Set prompt = Range("B2")

  'API Info
  API = "https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=" & api_key

  If api_key = "" Then
        MsgBox "Error: API key is blank!"
        Exit Sub
    End If
    
  Set cellr = prompt.Offset(1, 0)
  Set rng = Range(prompt.Offset(1, 0), prompt.Offset(2000, 0))
  rng.Clear
    
  'Input Text
    If prompt.Value = "" Then
        MsgBox "Error: Cell " & prompt.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is blank!"
        Exit Sub
    End If

  text = Replace(prompt.Value, Chr(34), Chr(39))
  text = Replace(text, vbLf, " ")

  'Create an HTTP request object
  Set request = CreateObject("MSXML2.XMLHTTP")
  With request
     .Open "POST", API, False
     .setRequestHeader "Content-Type", "application/json"
     .send "{""contents"":{""parts"":[{""text"":""" & text & """}]},""generationConfig"":{""temperature"":0.5}}"
   status_code = .Status
   response = .responseText
  End With

  'Extract content
  If status_code = 200 Then
    DisplayText = ExtractContent(response)
  Else
    DisplayText = "Error : " & ExtractError(response)
  End If
    
  'Put response
  cellr.Value = DisplayText
  
  'Split to multiple rows
  Call SplitTextToMultipleRows(cellr)
  rng.WrapText = True
    
  'Clean up the object
  Set request = Nothing

End Sub

Sub SplitTextToMultipleRows(cell As Range)
    Dim splitArr() As String
    Dim delimiter As String
    delimiter = "\n"
    
    splitArr = Split(cell.Value, delimiter)
    For i = LBound(splitArr) To UBound(splitArr)
        x = splitArr(i)
        If Left(Trim(x), 1) = "=" Then
            x = "'" & x
        End If
        cell.Offset(i, 0).Value = Replace(x, "\""", Chr(34))
    Next i
    
End Sub

Function ExtractContent(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
    startPos = InStr(jsonString, """text"": """) + Len("""text"": """)
    endPos = InStr(startPos, jsonString, """") ' Find the position of the next double quote character
    TextValue = Mid(jsonString, startPos, endPos - startPos)
    Content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas as response
    If Left(Trim(Content), 1) = "=" Then
      Content = "'" & Content
    End If
    
    Content = Replace(Content, vbCrLf, "")
    Content = Replace(Content, vbLf, "")
    Content = Replace(Content, vbCr, "")
    
    If Right(Content, 1) = """" Then
      Content = Left(Content, Len(Content) - 1)
    End If
    
    ExtractContent = Content
End Function

Function ExtractError(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
    startPos = InStr(jsonString, """message"": """) + Len("""message"": """)
    endPos = InStr(startPos, jsonString, """") ' Find the position of the next double quote character
    TextValue = Mid(jsonString, startPos, endPos - startPos)
    Content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas as response
    If Left(Trim(Content), 1) = "=" Then
      Content = "'" & Content
    End If
    
    Content = Replace(Content, vbCrLf, "")
    Content = Replace(Content, vbLf, "")
    Content = Replace(Content, vbCr, "")
    
    If Right(Content, 1) = """" Then
      Content = Left(Content, Len(Content) - 1)
    End If
    
    ExtractError = Content
End Function

How to use VBA code

1. Press Alt+F11 to open the VBA editor.
2. Click Insert > Module to create a new module.
3. In the module, paste the VBA code shown above.
4. Update API key in the code highlighted in yellow above in the variable api_key. Once done, close the VBA editor.
5. Run the macro by pressing Alt+F8 or by going to the Developer tab > Macros and select the macro gemini and hit Run button.

Method 3 : User Defined Function

Incase you want to run Gemini AI model as an excel function instead of macro, you can use the code below. It is a custom user-defined function which allows you to run Gemini AI model over multiple cells by just dragging the formula down.

The syntax of function is as follows -

Gemini(text, [word_count])

text : prompt. Question you want to ask.
word_count : optional. Maximum number of words in the result.

Function Gemini(text As String, Optional word_count As Long = 0) As String
  Dim request As Object
  Dim response As String
  Dim API, api_key, DisplayText, error_result As String
  Dim status_code As Long
  
  'Update API Key
 api_key = "XXXXXXXXXXXXXXXXXX"
 
    
  'API Info
  API = "https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=" & api_key

    If api_key = "" Then
        MsgBox "Error: API key is blank!"
        Exit Function
    End If
       
   
   'Input Text
   If word_count > 0 Then
    text = text & ". Provide response in maximum " & word_count & " words"
   End If

  text = Replace(text, Chr(34), Chr(39))
  text = Replace(text, vbLf, " ")

  'Create an HTTP request object
  Set request = CreateObject("MSXML2.XMLHTTP")
  With request
     .Open "POST", API, False
     .setRequestHeader "Content-Type", "application/json"
     .send "{""contents"":{""parts"":[{""text"":""" & text & """}]},""generationConfig"":{""temperature"":0.5}}"
   status_code = .Status
   response = .responseText
  End With

  'Extract content
  If status_code = 200 Then
    DisplayText = ExtractContent(response)
  Else
    DisplayText = "Error : " & ExtractError(response)
  End If
    
  'Clean up the object
  Set request = Nothing
  
  'Return result
  Gemini = DisplayText

End Function


Function ExtractContent(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
    startPos = InStr(jsonString, """text"": """) + Len("""text"": """)
    endPos = InStr(startPos, jsonString, """") ' Find the position of the next double quote character
    TextValue = Mid(jsonString, startPos, endPos - startPos)
    Content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas as response
    If Left(Trim(Content), 1) = "=" Then
      Content = "'" & Content
    End If
    
    Content = Replace(Content, vbCrLf, "")
    Content = Replace(Content, vbLf, "")
    Content = Replace(Content, vbCr, "")
    Content = Replace(Content, "\n", vbNewLine)
    
    If Right(Content, 1) = """" Then
      Content = Left(Content, Len(Content) - 1)
    End If
    
    ExtractContent = Content
End Function

Function ExtractError(jsonString As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim TextValue As String
    
    startPos = InStr(jsonString, """message"": """) + Len("""message"": """)
    endPos = InStr(startPos, jsonString, """") ' Find the position of the next double quote character
    TextValue = Mid(jsonString, startPos, endPos - startPos)
    Content = Trim(Replace(TextValue, "\""", Chr(34)))
    
    'Fix for excel formulas as response
    If Left(Trim(Content), 1) = "=" Then
      Content = "'" & Content
    End If
    
    Content = Replace(Content, vbCrLf, "")
    Content = Replace(Content, vbLf, "")
    Content = Replace(Content, vbCr, "")
    
    If Right(Content, 1) = """" Then
      Content = Left(Content, Len(Content) - 1)
    End If
    
    ExtractError = Content
End Function

Make sure you enter your API key highlighted in yellow above.

Related Posts

Excel Tutorials : Top 100 Excel Tutorials

Spread the Word!
Share Share Tweet

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn