And I say, take Excel and call

Voximplant, , API, , , . , , , API.

API – HTTP-, , . SDK : Python, PHP, C# . , , . - ?



. - 15-30 – . , , MS Excel 100 . ? , , , Excel, . email , : « Excel!». ( ?) .



– - , , ActiveX-, , , , – SDK API. Excel VBA, SDK.

API-, :

1. URL POST-.

   
   
   
   
   

2. .

   
   
   
   
   

3. .

   
   
   
   
   

4. ( JSON).

   
   
   
   
   

URL POST-

, , : URL- . VBA URL- ( , ). , , .

Public Function URL_Encode(ByRef txt As String) As String
    Dim buffer As String, i As Long, c As Long, n As Long
    buffer = String$(Len(txt) * 12, "%")
 
    For i = 1 To Len(txt)
        c = AscW(Mid$(txt, i, 1)) And 65535
 
        Select Case c
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95  ' Unescaped 0-9A-Za-z-._ '
                n = n + 1
                Mid$(buffer, n) = ChrW(c)
            Case Is <= 127            ' Escaped UTF-8 1 bytes U+0000 to U+007F '
                n = n + 3
                Mid$(buffer, n - 1) = Right$(Hex$(256 + c), 2)
            Case Is <= 2047           ' Escaped UTF-8 2 bytes U+0080 to U+07FF '
                n = n + 6
                Mid$(buffer, n - 4) = Hex$(192 + (c \ 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
            Case 55296 To 57343       ' Escaped UTF-8 4 bytes U+010000 to U+10FFFF '
                i = i + 1
                c = 65536 + (c Mod 1024) * 1024 + (AscW(Mid$(txt, i, 1)) And 1023)
                n = n + 12
                Mid$(buffer, n - 10) = Hex$(240 + (c \ 262144))
                Mid$(buffer, n - 7) = Hex$(128 + ((c \ 4096) Mod 64))
                Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
            Case Else                 ' Escaped UTF-8 3 bytes U+0800 to U+FFFF '
                n = n + 9
                Mid$(buffer, n - 7) = Hex$(224 + (c \ 4096))
                Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
        End Select
    Next
    URL_Encode = Left$(buffer, n)
End Function
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

– . API Voximplant UTC YYYY-MM-DD hh:mm:ss. Excel ( , ). / UTC. , 100+ Excel , UTC, .



, VBA , , . : “yyyy-mm-dd hh:mm:ss”. “mm” – , , : “hh” “yyyy” ( , MSDN ). , - , .

. Voximplant JWT, , , , , SDK . JWT .

VBA? , JWT- . , VBA .NET 4.x, RSA.ImportPkcs8PrivateKey, PKCS8, .NET 5. , .NET- .

API-. , , - . . - SDK.

  – . HTTP VBA ( , URL-, ?). 

, , – MSXML 6.0 Microsoft Scripting Runtime , COM MSXML. !

Function makeRequest(name As String, params As Dictionary, accountId As Integer, apiKey As String) As Object

    Dim objHTTP As New MSXML2.XMLHTTP60
    Dim jsonData As String
    Dim parsedJson As Object
    Dim postString As String

    postString = ""
    
    Dim iterKey As Variant
    
    For Each iterKey In params.Keys
        postString = postString & "&" & iterKey & "=" & URL_Encode(params(iterKey))
    Next

    Url = "https://api.voximplant.com/platform_api/" + name
    objHTTP.Open "POST", Url, False
    objHTTP.send "account_id=" & accountId & "&api_key=" & apiKey & postString
    jsonData = objHTTP.responseText
    Set parsedJson = JsonConverter.ParseJson(jsonData)
    Set makeRequest = parsedJson

End Function
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

JSON

, , JSON. , JSON - VBA. , 2021 , GitHub, - JSON- VBA. .

JSON- Dictionary. , !

SDK ( ), VBA. SDK, GitHub.



SDK Class Module, Excel . , , SDK API ). , , , Excel ( API). 

- SDK, , . , , - .



:

Function getTotalCallCost(FromDate, ToDate, Username) As Double

    Dim totalCost As Double
    Dim lastCount As Integer
    Dim offset As Integer
    Dim res As Dictionary
    Dim RecordsPerRequest As Integer
    Dim api As New VoximplantAPI
    totalCost = 0
    lastCount = 1
    offset = 0
    RecordsPerRequest = 100
    
    'Pass Voximplant account id and API key
    api.SetCredentials 100, "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
    
    Do While lastCount > 0
        Set res = api.GetCallHistory(FromDate, ToDate, remote_number:=Username, with_calls:=True, with_records:=True, with_other_resources:=True, offset:=offset, count:=RecordsPerRequest)
        
        Dim session As Variant
        Dim item As Variant
        
        For Each session In res("result")
            For Each item In session("calls")
                totalCost = totalCost + item("cost")
            Next
            For Each item In session("records")
                totalCost = totalCost + item("cost")
            Next
            For Each item In session("other_resource_usage")
                totalCost = totalCost + item("cost")
            Next
        Next
        
        lastCount = res("count")
        offset = offset + RecordsPerRequest
    Loop
    
    getTotalCallCost = totalCost
End Function
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

, , .

:

VBA - SDK. . , 1 ! :D