Automate Excel with Real AI

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video. Source code will be presented in the order of the sections within the video.

For the below to code to work, you will need to create a constant for the API Key.

' Add your API key here
Public Const API_key As String = ""

' Main Sub
Sub SummarizeFeedbackWithGemini()

    Dim http As Object
    Dim apiKey As String
    Dim apiUrl As String
    Dim prompt As String
    Dim response As String
    Dim lastRow As Long
    Dim i As Long
    Dim cellText As String
    Dim promptText As String
    Dim jsonPayload As String
    Dim summary As String
    Dim ws As Worksheet
    
    apiKey = API_key
    apiUrl = "https://generativelanguage.googleapis.com/v1/models/gemini-2.5-flash:generateContent?key=" & apiKey
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set ws = ThisWorkbook.Sheets("FeedbackSummary")
    ws.Range("B2:B" & ws.Rows.Count).ClearContents
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    promptText = ws.Range("D2").Value
    
    For i = 2 To lastRow
        cellText = ws.Cells(i, 1).Value
        If Trim(cellText) <> "" Then
            prompt = promptText & " >>> " & cellText
            jsonPayload = "{""contents"":[{""parts"":[{""text"":""" & prompt & """}]}]}"
            http.Open "POST", apiUrl, False
            http.SetRequestHeader "Content-Type", "application/json"
            http.send jsonPayload
            response = http.responseText
            summary = ParseSummary(response)
            ws.Cells(i, 2).Value = summary
        End If
    Next i
    
    MsgBox "Summarization Complete!"

End Sub

Function ParseSummary(jsonText As String) As String
        Dim startToken As String
        startToken = """text"": """
        Dim endToken As String
        endToken = """"
        
        Dim startIndex As Integer
        startIndex = InStr(1, jsonText, startToken)
        Dim endIndex As Integer
        
        If startIndex > 0 Then
            startIndex = startIndex + Len(startToken)
            endIndex = InStr(startIndex, jsonText, endToken)
            If endIndex > 0 Then
                    ParseSummary = Mid(jsonText, startIndex, endIndex - startIndex)
                    ParseSummary = Replace(ParseSummary, "\\n", vbNewLine)
                    ParseSummary = Replace(ParseSummary, "\\""", """")
                Else
                    ParseSummary = "Error: Could not find the end of the text in the response."
            End If
            Else
                ParseSummary = "Error: Could not find 'text' in the API response."
        End If
        
End Function