Make an api call to geoTab to get current location of the vehicle in VBA. Tried few codes but nothing seem to work. Any help is appreciated.

[Pardeep Singh]

I am trying to make an API call to Geotab website in VBA. Geotab provides a DeviceStatusInfo Object that can be used to get the current Latitude and Longitude. (MyGeotab API Reference) I have been trying for a while in VBA but having no success. Hoping someone can help me out here.

Used following code in VBA but no luck

Sub GetVehicleStatus()
 
    Dim objHTTP As Object
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
 
    'API URL to get the vehicle status
    Dim credentials As String
    credentials = EncodeBase64("Username" & ":" & "PasswordY")
    objHTTP.Open "POST", "https://my792.geotab.com/apiv1/Get", False
 
    objHTTP.setRequestHeader "Content-Type", "application/json"
    objHTTP.setRequestHeader "Accept", "application/json"
    objHTTP.setRequestHeader "Authorization", "Basic " & credentials
 
    'API request body
    Dim requestBody As String
    requestBody = "{ ""method"": ""GetDeviceStatusInfo"", ""params"": { ""database"": ""Database"", ""deviceSearch"": { ""id"": ""Truck ID"" }, ""deviceStatusInfo"": [ ""BatteryVoltage"", ""IgnitionState"", ""Speed"", ""Location"", ""FuelLevel"" ] } }"
 
    objHTTP.send requestBody
 
    'Check the status of the API call
    If objHTTP.Status = 200 Then
        Dim result As Object
        Set result = JsonConverter.ParseJson(objHTTP.responseText)
        Debug.Print "Battery Voltage: " & result("result")(0)("BatteryVoltage")
        Debug.Print "Ignition State: " & result("result")(0)("IgnitionState")
        Debug.Print "Speed: " & result("result")(0)("Speed")
        Debug.Print "Location: " & result("result")(0)("Location")(0)("Latitude") & "," & result("result")(0)("Location")(0)("Longitude")
        Debug.Print "Fuel Level: " & result("result")(0)("FuelLevel")
    Else
        Debug.Print "API Call Failed with error code: " & objHTTP.Status
    End If
 
End Sub
 
'Function to encode the credentials in base64 format
Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement

  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = Replace(objNode.text, vbLf, "")

  Set objNode = Nothing
  Set objXML = Nothing
End Function

---------------------------------------------------------------------------------------------------------------------------------------

I am using the following code in Google Appscript and it works. hoping this might help to understand what I am trying to do.

function call(method, params) {
  // Construct an object that conforms to JSON-RCP2 spec
  var jsonRpcPayload = {
    method : method,
    params: params
    },
   options = {
          "method" : "post",
          "contentType" : "application/json",
          "payload" : JSON.stringify(jsonRpcPayload)
        },
  result = UrlFetchApp.fetch("https://" + "my792.geotab.com" + "/apiv1", options);
        
  if (result.getResponseCode() == 200) {
    return JSON.parse(result.getContentText());
        }
    throw "Could not execute JSON-RPC";
}



function authenticate() {
  var rpcResult,
      server = "my792.geotab.com",
            
  // Construct parameters that Authenticate expects
   credentials = {
    userName : '********',
    password : '*******',
    database : '*******'
        };
        
   // Authenticate always destroys the previous session and creates a new one. Set the server. Call depends on it.
   session = {
    server : server
        }
          
        rpcResult = call("Authenticate", credentials);
        
        if (rpcResult.result) {
          rpcResult = rpcResult.result;
          if (rpcResult.path.toUpperCase() !== "THISSERVER") {
            // We're not on the right server - update sessino.
            session.server = rpcResult.path;
          }
          session.credentials = rpcResult.credentials;
          return session;
        } else if (rpcResult.error) {
              var response = rpcResult.error;
              if (response.errors && response.errors.length >= 1) {
                var errorMessage = response.errors[0].name.toUpperCase();
                if (errorMessage === "INVALIDUSEREXCEPTION" || errorMessage === "DBUNAVAILABLEEXCEPTION") {
                  // TODO: should we be doing this for DBUNAVAILABLE?
                  throw "Authentication failed";
                }
              }
              else throw rpcResult.error;
        }
        throw "Invalid response received";
}




function getdeviceInfo(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Test')
  credential = authenticate()
  var param = {
    'credentials': credential.credentials,
    'typeName': "DeviceStatusInfo",
    'search':{
      'deviceSearch':{'id':'b174'}
    }
    
    
  }
  var result = call('Get', param)
  Logger.log(result['result'][0]['longitude'])
  var results = Object.entries(result)
  var arrayLength = results[0][1].length
  var vehicleData = []
    for (var i = 0;i<arrayLength;i++){
      var id = result['result'][0]['longitude']
      var geoName = result['result'][0]['latitude']
      value = [id,geoName]
      vehicleData.push(value)
      
  }
  var rowHeaders = [['id','Device']]
  if(sheet.getLastRow()>0){
    sheet.getRange(1,1,sheet.getLastRow(),rowHeaders[0].length).clearContent()
  }
  sheet.getRange(1,1,1,rowHeaders[0].length).setValues(rowHeaders)
  sheet.getRange(2,1,vehicleData.length,rowHeaders[0].length).setValues(vehicleData)
  Logger.log(vehicleData.length)
 
}