[Excel] Gebruik van Google API in VBA

[stimpyMGS]

Hallo,

Ik wil weten hoe ik een Google API key toevoeg aan een Excel bestand.

Als ZZP'er reken ik op het einde van het jaar altijd mijn aantal gereden kilometers uit middels een handig Excel bestand dat ik ooit van het net heb geplukt. Ik vulde daar mijn 'van' en 'naar' postcodes in, en dat resulteerde in een 'afstand' in km's. De afstanden werden via een Google API berekend. Sinds 2018 is daar echter een API key voor nodig.
Ik heb die gegenereerd, maar ik heb onvoldoende kennis van Excel om te weten waar ik deze key nu moet invoegen. Onderstaand zien jullie de visual basic code. Zoals gezegd heeft deze altijd gewerkt, totdat Google dit jaar hun beleid heeft aangepast.

Ik vind online diverse stukken code maar die zijn allen gebaseerd op andere sheets die niet passen bij mijn wens. Het bestand dat ik heb is eenvoudig en voor mij als leek daardoor prettig.

Alvast bedankt!

''''''''''''''''''''''''''''''''''
' Reistijd berekenen
'
' start = startlocatie
' eind = eindlocatie
' vervoer = manier waarop te reizen
' eenheid = manier van tijdspresentatie
'
''''''''''''''''''''''''''''''''''

Public Function G_REISTIJD(start As String, eind As String, Optional vervoer As Variant, Optional eenheid As Variant) As Variant

Dim Verv As String
Dim Eenh As String
Dim Link As String
Dim Bestemming As String
Dim Mode As String
Dim Taal As String
Dim Min As Integer
Dim Uur As Integer
Dim Sec As Integer

''' Link opbouw '''
Link = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
Bestemming = "&destinations="
Mode = "&mode="
Taal = "&language=nl"

''' Controleren op waarde in vervoer '''
' Openbaar vervoer is een registratienummer voor nodig bij google '
If IsMissing(vervoer) = True Or IsEmpty(vervoer) = True Then
Verv = "driving"
Else
If vervoer > 2 Then
Verv = "driving"
Else
Select Case vervoer
Case 0: Verv = "driving"
Case 1: Verv = "walking"
Case 2: Verv = "bicycling"
End Select
End If
End If

''' Controleren op waarde in eenheid '''
If IsMissing(eenheid) = True Or IsEmpty(eenheid) = True Then
Eenh = 0
Else
If eenheid > 3 Then
Eenh = 0
Else
Eenh = eenheid
End If
End If

''' Oproepen informatie '''
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = Link & Replace(start, " ", "+") & Bestemming & Replace(eind, " ", "+") & Mode & Verv & Taal
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

''' Als POST tekst niet klopt '''
If InStr(objHTTP.responseText, """duration"" : {") = 0 Then GoTo Error

''' Tijd opzoeken '''
tijd = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStrRev(objHTTP.responseText, """value"" : ") - 9)

''' Tijd naar seconden, minuten en uren '''
Sec = CDbl(Replace(Split(tijd)(0), ".", ","))
Min = Int((Sec - (Int(Sec / 3600) * 3600)) / 60)
Minn = Int(Round((Sec - (Int(Sec / 3600) * 3600)) / 60))
Uur = Int(Sec / 3600)

''' Als eenheid in 00:00 '''
Kort = Format(Uur, "00") & ":" & Format(Min, "00")

''' Als eenheid in 00:00:00 '''
Lang = Format(Uur, "00") & ":" & Format(Min, "00") & ":" & Format(((Sec Mod 60)), "00")

''' Als eenheid in tekst '''
If Uur > 0 Then
Tekst = Uur & " u. " & Minn & " min."
Else
Tekst = Minn & " min."
End If

''' Eindresultaat maken '''
Select Case Eenh
Case 0: G_REISTIJD = Kort
Case 1: G_REISTIJD = Lang
Case 2: G_REISTIJD = Sec
Case 3: G_REISTIJD = Tekst
End Select
Exit Function

''' Error uitgang '''
Error:
G_REISTIJD = CVErr(xlErrNA)

End Function

[nescafe]

quote: https://developers.google...stance-matrix/get-api-key

When loading the Distance Matrix API, substitute YOUR_API_KEY in the code below with the API key you got from the previous step.

https://maps.googleapis.com/maps/api/distancematrix/json?origins=Seattle&destinations=San+Francisco&key=YOUR_API_KEY

Zoek bijv. eens op de URL van Google in je code om te zien waar je deze key kunt plaatsen.

[stimpyMGS]

nescafe schreef op zondag 25 november 2018 @ 14:48:
[...]


Zoek bijv. eens op de URL van Google in je code om te zien waar je deze key kunt plaatsen.

thx, dat ben ik al tegengekomen tijdens mijn zoektocht, maar in het voorbeeld dat Google geeft zijn 'origins' en 'destinations' al ingevuld. In het bestand dat ik heb zijn deze leeg en variabel.

[nescafe]

Google gaat ervanuit dat je weet hoe je een link aan kunt passen. Voor jou betekent dit iets als:

code:

Link = "https://maps.googleapis.com/maps/api/distancematrix/json?key=YOUR_API_KEY&origins="

[dafloor71]

Kun je niet het volgende proberen:
Onder de regel:
Taal = "&language=nl"
Deze regel toevoegen:
Key = "&key=YOUR_API_KEY"
En dan deze regel:
URL = Link & Replace(start, " ", "+") & Bestemming & Replace(eind, " ", "+") & Mode & Verv & Taal
vervangen door:
URL = Link & Replace(start, " ", "+") & Bestemming & Replace(eind, " ", "+") & Mode & Verv & Taal & Key

[F_J_K]

afstandsberekening

Je vraag heeft niets van doen met afstandsberekening, maar met gebruik van de Google API. De topictitel pas ik dus aan.

Inhoudelijk: als ik het zo zie dan geeft zoeken bij google naar 'how to use maps.googleapis.com in excel' al antwoord. Inderdaad 'gewoon' de key toevoegen aan de URL.

offtopic:
Wees erg voorzichtig met overnemen van code als je geen idee hebt wat het doet Los van beveiliging, malware, etc. is het heel makkelijk om fouten te maken. En dan mag je later uitleggen waarom de berekende afstanden nergens op slaan

[stimpyMGS]

F_J_K schreef op zondag 25 november 2018 @ 16:26:
[...]
Je vraag heeft niets van doen met afstandsberekening, maar met gebruik van de Google API. De topictitel pas ik dus aan.

Inhoudelijk: als ik het zo zie dan geeft zoeken bij google naar 'how to use maps.googleapis.com in excel' al antwoord. Inderdaad 'gewoon' de key toevoegen aan de URL.

offtopic:
Wees erg voorzichtig met overnemen van code als je geen idee hebt wat het doet Los van beveiliging, malware, etc. is het heel makkelijk om fouten te maken. En dan mag je later uitleggen waarom de berekende afstanden nergens op slaan

thx heren voor de uitleg. Ik ken niks van VBA en had dus ook geen idee dat ik "gewoon" de key kon toevoegen aan de URL regel. Ik ben net een paar weken bezig met JSON in home-assistant, mijn eerste ervaring met programmeren, en dat schijnt nog een van de makkelijkere talen te zijn VBA is voor mij van een ander niveau.

Mijn vraag is iig beantwoord!