Adapt VBA for Notion API

2024-02-01 19:35 UTC gpt-4 Open in ChatGPT ↗

I want to push some excel data to a Notion db via VBA (http request). I want to use this code, for mac compatibility. How to adapth it for the Notion API?

Option Explicit

’ execShell() function courtesy of Robert Knight via StackOverflow ’ https://stackoverflow.com/questions/6136798/vba-shell-function-in-office-2011-for-mac

Private Declare Function popen Lib “libc.dylib” (ByVal command As String, ByVal mode As String) As Long Private Declare Function pclose Lib “libc.dylib” (ByVal file As Long) As Long Private Declare Function fread Lib “libc.dylib” (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long Private Declare Function feof Lib “libc.dylib” (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String Dim file As Long file = popen(command, “r”)

If file = 0 Then
    Exit Function
End If

While feof(file) = 0
    Dim chunk As String
    Dim read As Long
    chunk = Space(50)
    read = fread(chunk, 1, Len(chunk) - 1, file)
    If read > 0 Then
        chunk = Left$(chunk, read)
        execShell = execShell & chunk
    End If
Wend

exitCode = pclose(file)

End Function

Function HTTPGet(sUrl As String, sQuery As String) As String

Dim sCmd As String
Dim sResult As String
Dim lExitCode As Long

sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
sResult = execShell(sCmd, lExitCode)

' ToDo check lExitCode

HTTPGet = sResult

End Function

To adapt your VBA code for use with the Notion API on a Mac, you’ll need to consider a few important changes, especially because Notion API requires more than just a simple GET request for most operations. You’ll typically need to use POST requests, include headers for authentication, and send data as JSON. The Notion API also uses specific endpoints for different types of operations, such as adding or updating database entries.

Here’s an adapted version of your code focusing on a generic POST request which you can further customize for your specific Notion API needs:

Option Explicit

Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As Long
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Function HTTPPost(sUrl As String, sData As String, sAuthToken As String) As String

    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long

    ' Construct the curl command with necessary headers and data for POST request
    sCmd = "curl -X POST " & sUrl & " " & _
           "-H ""Content-Type: application/json"" " & _
           "-H ""Notion-Version: 2021-05-13"" " & _
           "-H ""Authorization: Bearer " & sAuthToken & """ " & _
           "--data-binary @- <<< '" & sData & "'"

    sResult = execShell(sCmd, lExitCode)

    ' ToDo: Check lExitCode to handle errors

    HTTPPost = sResult

End Function

In this adapted code, HTTPPost is a new function for sending POST requests. You’ll need to provide the URL for your Notion API request, the data in JSON format, and your Notion integration token (sAuthToken).

Important Considerations:

Notion Integration: Ensure you’ve set up a Notion integration and obtained the necessary access token.
Permissions: The integration must have permissions to access the database or page you’re trying to modify.
Notion API Version: This example uses "Notion-Version: 2021-05-13". Check the Notion API documentation for the latest version or any changes.
Data Formatting: The sData parameter must be a properly formatted JSON string that matches the Notion API’s requirements for the endpoint you’re using.
Error Handling: The lExitCode can be used to check if the curl command executed successfully. You might want to add more sophisticated error handling based on the response received from the Notion API.

Remember, the Notion API’s functionality is quite broad, so you’ll need to refer to the API documentation for specific endpoints and data formats depending on whether you’re adding a new page, updating a database item, querying a database, etc.

To achieve your goal of tracking how long a user has an Excel file open and storing this information along with the user’s username in a Notion database, you’ll need to:

Track Open and Close Times: Use VBA to record the time when the Excel file is opened and the time when it is closed.
Calculate Duration: Calculate the duration the file was open by subtracting the open time from the close time.
Identify the User: Get the username of the current user.
Send Data to Notion: Use an HTTP POST request to send this data to a Notion database.

Here’s a simplified VBA script to accomplish this. This script includes the necessary procedures to track the file’s open/close duration, obtain the username, and send the data to Notion. Note that this script assumes you have set up a Notion integration and have the necessary permissions to add items to your database.

Option Explicit

' Declarations for Mac compatibility
#If Mac Then
    Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
    Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
    Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
    Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
#End If

Dim openTime As Double

' ThisWorkbook module
Private Sub Workbook_Open()
    openTime = Timer ' Record time when the workbook is opened
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim closeTime As Double
    Dim duration As Double
    Dim username As String
    Dim jsonData As String

    closeTime = Timer ' Record time when the workbook is about to be closed
    duration = closeTime - openTime ' Calculate duration in seconds

    ' Get the username of the current user
    username = Environ$("USERNAME")

    ' Format the data as JSON
    jsonData = "{""parent"": {""database_id"": ""Your_Database_ID""}, " & _
               """properties"": { " & _
               """Username"": {""title"": [{""text"": {""content"": """ & username & """}}]}, " & _
               """Duration"": {""number"": " & duration & "}}}"

    ' Send the data to Notion
    Call SendDataToNotion(jsonData)
End Sub

' General module
Function SendDataToNotion(jsonData As String)
    Dim notionUrl As String
    Dim authToken As String
    Dim curlCommand As String
    Dim result As String
    Dim exitCode As Long

    notionUrl = "https://api.notion.com/v1/pages"
    authToken = "Bearer Your_Secret_API_Token"

    ' Construct the curl command
    curlCommand = "curl -X POST " & notionUrl & " " & _
                  "-H ""Authorization: " & authToken & """ " & _
                  "-H ""Notion-Version: 2021-05-13"" " & _
                  "-H ""Content-Type: application/json"" " & _
                  "--data '" & jsonData & "'"

    ' Execute the curl command and capture the result
    result = execShell(curlCommand, exitCode)

    ' Check the exit code to see if the curl command was successful
    If exitCode <> 0 Then
        MsgBox "Failed to send data to Notion. Exit Code: " & exitCode
    End If
End Function

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    #If Mac Then
        Dim file As Long
        file = popen(command, "r")

        If file = 0 Then
            Exit Function
        End If

        While feof(file) = 0
            Dim chunk As String
            Dim read As Long
            chunk = Space(50)
            read = fread(chunk, 1, Len(chunk) - 1, file)
            If read > 0 Then
                chunk = Left$(chunk, read)
                execShell = execShell & chunk
            End If
        Wend

        exitCode = pclose(file)
    #Else
        ' For Windows, you might need a different approach to execute shell commands
        ' or use an alternative method to send HTTP requests, such as XMLHTTP or WinHttp.WinHttpRequest.5.1
    #End If
End Function

Key Points:

Replace "Your_Database_ID" with the ID of your Notion database.
Replace "Your_Secret_API_Token" with your Notion integration’s secret API token.
The Workbook_Open and Workbook_BeforeClose procedures must be placed

To directly edit a Notion database without going through pages, you would typically use the Notion API to update database entries directly. This involves making a PATCH request to the /v1/pages/{page_id} endpoint, where {page_id} is the ID of the database entry you want to update. However, to create or update database entries directly, you still work with “pages” since each entry in a Notion database is technically a page under the hood.

If you want to add a new entry to a database (which might be what you’re asking for when you mention directly editing a database), you would use the /v1/pages endpoint with a POST request, providing the necessary properties according to your database schema.

Here’s an example of how to adapt your VBA code to add a new entry to a Notion database, assuming you have a database with columns for “Username” and “Duration”:

Option Explicit

' Declarations for Mac compatibility
#If Mac Then
    Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
    Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
    Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
    Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
#End If

Dim openTime As Double

' ThisWorkbook module
Private Sub Workbook_Open()
    openTime = Timer ' Record time when the workbook is opened
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim closeTime As Double
    Dim duration As Double
    Dim username As String
    Dim jsonData As String

    closeTime = Timer ' Record time when the workbook is about to be closed
    duration = closeTime - openTime ' Calculate duration in seconds

    ' Get the username of the current user
    username = Environ$("USERNAME")

    ' Format the data as JSON for adding to the database
    jsonData = "{""parent"": {""database_id"": ""Your_Database_ID""}, " & _
               """properties"": { " & _
               """Username"": {""title"": [{""text"": {""content"": """ & username & """}}]}, " & _
               """Duration"": {""number"": " & duration & "}}}"

    ' Send the data to Notion to add a new database entry
    Call AddDataToNotionDatabase(jsonData)
End Sub

' General module
Function AddDataToNotionDatabase(jsonData As String)
    Dim notionUrl As String
    Dim authToken As String
    Dim curlCommand As String
    Dim result As String
    Dim exitCode As Long

    notionUrl = "https://api.notion.com/v1/pages"
    authToken = "Bearer Your_Secret_API_Token"

    ' Construct the curl command for a POST request to add a new database entry
    curlCommand = "curl -X POST " & notionUrl & " " & _
                  "-H ""Authorization: " & authToken & """ " & _
                  "-H ""Notion-Version: 2021-05-13"" " & _
                  "-H ""Content-Type: application/json"" " & _
                  "--data '" & jsonData & "'"

    ' Execute the curl command and capture the result
    result = execShell(curlCommand, exitCode)

    ' Check the exit code to see if the curl command was successful
    If exitCode <> 0 Then
        MsgBox "Failed to add data to Notion database. Exit Code: " & exitCode
    End If
End Function

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    #If Mac Then
        Dim file As Long
        file = popen(command, "r")

        If file = 0 Then
            Exit Function
        End If

        While feof(file) = 0
            Dim chunk As String
            Dim read As Long
            chunk = Space(50)
            read = fread(chunk, 1, Len(chunk) - 1, file)
            If read > 0 Then
                chunk = Left$(chunk, read)
                execShell = execShell & chunk
            End If
        Wend

        exitCode = pclose(file)
    #Else
        ' For Windows, you might need a different approach to execute shell commands
        ' or use an alternative method to send HTTP requests, such as XMLHTTP or WinHttp.WinHttpRequest.5.1
    #End If
End Function

In this script:

Replace "Your_Database_ID" with the ID of your Notion database.
Replace "Your_Secret_API_Token" with your Notion integration’s secret API token.
The

Using the https://api.notion.com/v1/databases/ endpoint in the Notion API is not bad practice, but its usage depends on what you intend to do with the database. This endpoint is primarily used for retrieving information about a database or querying a database to get a list of pages that match certain filter criteria. Specifically:

GET https://api.notion.com/v1/databases/{database_id}: This retrieves metadata about the database with the given {database_id}. It’s useful for understanding the structure of your database, such as what properties it contains, but it does not modify the database or its entries.
POST https://api.notion.com/v1/databases/{database_id}/query: This queries a database for pages that match the specified filter criteria. It’s used to retrieve pages from the database based on specific conditions but, again, does not modify the database or its entries.

If you want to add a new entry to a Notion database, you should use the https://api.notion.com/v1/pages endpoint with a POST request, as each new entry in a database is technically a new page with properties conforming to the database schema.

If you want to update an existing entry in a Notion database, you would use the https://api.notion.com/v1/pages/{page_id} endpoint with a PATCH request, where {page_id} is the ID of the page (database entry) you want to update.

In summary, the choice of endpoint depends on your specific needs:

Use /v1/databases/{database_id} to get information about the database or to query it.
Use /v1/pages with POST to add new entries to the database.
Use /v1/pages/{page_id} with PATCH to update existing entries in the database.

Choosing the correct endpoint based on your requirements is crucial for interacting with the Notion API effectively and is not considered bad practice as long as it aligns with the intended use case of the endpoint.

For modern VBA development, especially in environments like Office for Mac 2016 and later, where traditional Windows API calls (such as Declare Function) are not supported, you would typically avoid using external libraries for tasks like HTTP requests. Instead, you can use built-in VBA capabilities or leverage external tools through shell commands in a way that is compatible with both Windows and Mac environments.

Given the limitations on Mac, one common approach is to use Shell to execute command-line tools like curl for making HTTP requests. This method works on both Windows and Mac, but on Windows, you typically have access to more sophisticated methods like using WinHttp.WinHttpRequest or MSXML2.XMLHTTP for HTTP requests.

Here’s an updated version of the code that should be more compatible with modern VBA environments, including Mac:

Option Explicit

Sub TestNotionAPI()
    Dim openTime As Double
    Dim closeTime As Double
    Dim duration As Double
    Dim username As String
    Dim jsonData As String
    Dim notionUrl As String
    Dim authToken As String

    ' Initialize variables
    openTime = Timer ' Simulate workbook open time
    closeTime = Timer + 60 ' Simulate workbook close time after 1 minute
    duration = closeTime - openTime ' Calculate duration in seconds
    username = Environ$("USERNAME") ' Get the username of the current user

    ' Prepare JSON data for Notion API
    jsonData = "{""parent"": {""database_id"": ""Your_Database_ID""}, " & _
               """properties"": { " & _
               """Username"": {""title"": [{""text"": {""content"": """ & username & """}}]}, " & _
               """Duration"": {""number"": " & duration & "}}}"

    ' Notion API details
    notionUrl = "https://api.notion.com/v1/pages"
    authToken = "Bearer Your_Secret_API_Token"

    ' Send data to Notion
    Call SendDataToNotion(notionUrl, authToken, jsonData)
End Sub

Sub SendDataToNotion(notionUrl As String, authToken As String, jsonData As String)
    Dim script As String
    Dim tempFilePath As String
    Dim fileNo As Integer

    ' Create a temporary file to store JSON data
    tempFilePath = Environ$("TEMP") & "\tempData.json"
    fileNo = FreeFile
    Open tempFilePath For Output As #fileNo
    Print #fileNo, jsonData
    Close #fileNo

    ' Construct the curl command
    script = "curl -X POST """ & notionUrl & """ -H ""Authorization: " & authToken & _
             """ -H ""Notion-Version: 2021-05-13"" -H ""Content-Type: application/json"" --data @" & _
             Replace(tempFilePath, "\", "/")

    ' Execute the curl command using Shell
    Shell script, vbHide

    ' Clean up: Delete the temporary file
    Kill tempFilePath
End Sub

This code defines a TestNotionAPI subroutine that prepares the data and calls SendDataToNotion to send the data to the Notion API using a curl command executed via Shell. The JSON data is temporarily stored in a file because curl might have issues with complex inline JSON data on some systems.

Important Notes:

Replace "Your_Database_ID" and "Your_Secret_API_Token" with your actual Notion database ID and API token.
This approach uses a temporary file to store JSON data, which is then passed to curl using the --data @filename syntax. Ensure that the path to the temporary file is accessible and writable.
Error handling and response processing from curl are not covered here and should be implemented as needed.
The Shell function’s behavior might vary between Windows and Mac, and the path to curl might need to be adjusted based on the system’s configuration.
Directly executing shell commands from VBA involves security risks, especially when incorporating user-generated content or external data. Always validate and sanitize inputs to avoid injection attacks.

This approach should offer a more modern and cross-platform way to interact with external APIs from VBA, albeit with some limitations and considerations, especially regarding security and error handling.

For a Windows environment, you can leverage the WinHttp.WinHttpRequest.5.1 object to make HTTP requests directly from VBA without relying on external tools like curl. This method is more integrated and secure, allowing for better error handling and response processing within VBA.

Here’s how you can adapt the previous example to use WinHttp.WinHttpRequest.5.1 for sending data to the Notion API on Windows:

Option Explicit

Sub TestNotionAPI()
    Dim openTime As Double
    Dim closeTime As Double
    Dim duration As Double
    Dim username As String
    Dim jsonData As String
    Dim notionUrl As String
    Dim authToken As String

    ' Initialize variables
    openTime = Timer ' Simulate workbook open time
    closeTime = Timer + 60 ' Simulate workbook close time after 1 minute
    duration = closeTime - openTime ' Calculate duration in seconds
    username = Environ$("USERNAME") ' Get the username of the current user

    ' Prepare JSON data for Notion API
    jsonData = "{""parent"": {""database_id"": ""Your_Database_ID""}, " & _
               """properties"": { " & _
               """Username"": {""title"": [{""text"": {""content"": """ & username & """}}]}, " & _
               """Duration"": {""number"": " & duration & "}}}"

    ' Notion API details
    notionUrl = "https://api.notion.com/v1/pages"
    authToken = "Bearer Your_Secret_API_Token"

    ' Send data to Notion
    Call SendDataToNotion(notionUrl, authToken, jsonData)
End Sub

Sub SendDataToNotion(notionUrl As String, authToken As String, jsonData As String)
    Dim httpRequest As Object
    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")

    With httpRequest
        .Open "POST", notionUrl, False
        .SetRequestHeader "Authorization", authToken
        .SetRequestHeader "Notion-Version", "2021-05-13"
        .SetRequestHeader "Content-Type", "application/json"
        .Send jsonData

        ' Check the status code of the response
        If .Status = 200 Then
            ' Successfully sent data to Notion
            MsgBox "Data successfully sent to Notion!", vbInformation
        Else
            ' Handle HTTP errors
            MsgBox "Failed to send data to Notion. Status: " & .Status & " - " & .StatusText, vbCritical
        End If
    End With

    Set httpRequest = Nothing
End Sub

This VBA script defines two subroutines:

TestNotionAPI: This subroutine simulates opening and closing a workbook, calculates the duration, and prepares the JSON data to be sent to Notion.
SendDataToNotion: This subroutine makes the actual HTTP POST request to the Notion API using the WinHttp.WinHttpRequest.5.1 object. It sets necessary headers and sends the JSON data.

Important Notes:

Replace "Your_Database_ID" and "Your_Secret_API_Token" with your actual Notion database ID and API token.
The WinHttp.WinHttpRequest.5.1 object is synchronous in this example (False parameter in the .Open method), meaning the code execution will wait until the HTTP request is completed. This might cause Excel to become unresponsive for a short duration during the request. Consider using asynchronous requests (True parameter) and handling the OnResponse event for more complex scenarios.
This code includes basic error handling by checking the HTTP response status. You might want to expand this based on your specific requirements, especially for handling different types of HTTP errors or parsing JSON responses.
Ensure that your VBA project has access to the necessary COM libraries if required. Usually, CreateObject doesn’t need additional references, but in case of issues, check your VBA editor’s references section.

This approach is more native to Windows and doesn’t rely on external command-line utilities, making it more secure and reliable for VBA applications running in Windows environments.

To measure total clicks on cells in an Excel workbook and store this information along with the duration and username in a Notion database, you can use the Worksheet_SelectionChange event in VBA. This event is triggered every time a different cell or range of cells is selected in the workbook.

Below is an extended version of the previous VBA code that includes a counter for cell clicks. This counter is incremented every time a cell selection changes, and the total count is sent to the Notion database along with the duration and username when the workbook is closed.

Option Explicit

' Global variable to count cell clicks
Dim cellClicks As Long

Sub Workbook_Open()
    ' Initialize cell click count when the workbook is opened
    cellClicks = 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim duration As Double
    Dim username As String
    Dim jsonData As String

    ' Calculate the duration the workbook was open (in seconds)
    duration = (Now - openTime) * 86400 ' Convert time difference to seconds

    ' Get the username of the current user
    username = Environ$("USERNAME")

    ' Prepare JSON data for Notion API, including cell clicks
    jsonData = "{""parent"": {""database_id"": ""Your_Database_ID""}, " & _
               """properties"": { " & _
               """Username"": {""title"": [{""text"": {""content"": """ & username & """}}]}, " & _
               """Duration"": {""number"": " & duration & "}, " & _
               """Clicks"": {""number"": " & cellClicks & "}}}"

    ' Send data to Notion
    Call SendDataToNotion("https://api.notion.com/v1/pages", "Bearer Your_Secret_API_Token", jsonData)
End Sub

' This event handler increments the cellClicks counter every time a different cell or range is selected
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    cellClicks = cellClicks + 1
End Sub

Sub SendDataToNotion(notionUrl As String, authToken As String, jsonData As String)
    Dim httpRequest As Object
    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")

    With httpRequest
        .Open "POST", notionUrl, False
        .SetRequestHeader "Authorization", authToken
        .SetRequestHeader "Notion-Version", "2021-05-13"
        .SetRequestHeader "Content-Type", "application/json"
        .Send jsonData

        If .Status = 200 Then
            MsgBox "Data successfully sent to Notion!", vbInformation
        Else
            MsgBox "Failed to send data to Notion. Status: " & .Status & " - " & .StatusText, vbCritical
        End If
    End With

    Set httpRequest = Nothing
End Sub

Key Points:

Global Variable for Clicks: The cellClicks variable is used to track the total number of cell selection changes (clicks).
Workbook_SheetSelectionChange Event: This event is fired every time the selection changes in any worksheet of the workbook. It increments the cellClicks variable. Note that if you have multiple worksheets and you want to track clicks across all of them, you might need to add this handler to each worksheet’s code module, or use a class module to handle events for all worksheets.
Storing Clicks in Notion: The click count is included in the JSON payload sent to Notion in the Workbook_BeforeClose subroutine. The property name "Clicks" should match the name of the column in your Notion database designed to store the click count.

Customization:

Replace "Your_Database_ID" and "Bearer Your_Secret_API_Token" with your actual Notion database ID and API token.
Ensure the Notion database has a Number property named "Clicks" to store the click count.
The Workbook_SheetSelectionChange subroutine needs to be added to the ThisWorkbook object in the VBA editor to ensure it tracks selection changes across all sheets. If you wish to track selections on a specific sheet only, move this subroutine to the respective sheet’s code module in the VBA editor.

This approach provides a simple way to track user interactions with the Excel workbook and store this data in Notion, offering insights into how the workbook is being used.