Jaguar InControl API for Google Sheets – Part 2

Jaguar InControl API for Google Sheets using Google Apps Script cover image

This is a follow up to the blog post  How to Download your Jaguar InControl Trips to Google Sheets.  In this blog post I explain what information you get about your trips, which part of the Jaguar InControl API I use, and I also share the Google Apps Script code used in the Google Sheets.

Do also check out the blog post Analyze your Jaguar I-Pace Trips in Google Data Studio.

Updates

  • 26.02.2019:
    • Moved calculations from the Google Apps Script to a seperate Sheet.
    • Improved “removeDuplicates” from the Google Apps Script.
    • Added possibility to calculate a more correct Average Consumption.

Jaguar InControl Google Sheets Column Names & Variables

The table below shows the Jaguar InControl Trip Data that are returned from the API and the Google Apps Script. Some of the data are from the API, some of the data are created in the Google Apps Script.

Sheet Column NameJS VariableComment
Trip IDtripIdTrip ID
Odometer End OrigendOdometerOrigOrginal Odometer End data from the API. Value is in meters.
Eco Score SpeedspeedEcoScoreSpeed ECO Score
Eco Score Speed StatusspeedEcoscoreStatusSpeed ECO Score Status
Avg. Consumption OrigaverageEnergyConsumptionOrigOrginal Avg. Energy Consumption data delivered from the API. The value is km/kWh.
Distance OrigdistanceOrigOriginal Distance data. Value is meter.
Average Fuel ConsumptionaverageFuelConsumptionFossil data. Returns null for I-Pace
Eco Score ThrottlethrottleEcoScoreThrottle ECO Score
Eco Score Throttle StatusthrottleEcoScoreStatusThrottle ECO Score Status
Avg. Speed OrigaverageSpeedOrgOriginal Average Speed from the API. Value is in Km/h.
Electrical RegenerationelectricalRegenerationElectrical Regeneration data seems to be null
Fuel ConsumptionfuelConsumptionFossil car consumption (I guess). Value is null for I-Pace
Start CountrystartPositionCountryCountry Start Position
Start AddressstartPositionAddressAddress Start Position
Start CitystartPositionCityCity Start Position. Value seems to be null most of the time
Start LatitudestartPositionLatitudeLatitude Start Position. Code is forcing Google Sheet to treat startPositionLatitude as string
Start Postal CodestartPositionPostalCodePostal Code Start Position. Code is forcing Google Sheet to treat startPositionPostalCode as string
Start RegionstartPositionRegionRegion Start Position. Code is forcing Google Sheet to treat startPositionRegion as string
Start LongitudestartPositionLongitudeLongitude Start Positiion. Code is forcing Google Sheet to treat startPositionLongitude as string
Start CoordinatesstartCoordinatesCreating latitude,longitude data
Eco Score BrakingbrakeEcoScoreBrake ECO Score
Eco Score Braking StatusbrakeEcoScoreStatusBrake ECO Score Status
Odometer Start OrigstartOdometerOrigOrginal Odometer End data delivered from the API. Value is in meters
Electrical ConsumptionelectricalConsumptionElectrical Consumption value seems to be null
End CountryendPositionCountryCountry End Position
End AddressendPositionAddressAddress End Position
End CityendPositionCityCity End Position. Value seems to be null most of the time
End LatitudeendPositionLatitudeLatitude End Position. Code is forcing Google Sheet to treat endPositionLatitude as string
End Postal CodeendPositionPostalCodePostal Code End Position. Code is forcing Google Sheet to treat endPositionPostalCode as string
End RegionendPositionRegionRegion End Position.
End LongitudeendPositionLongitudeLongitude End Position. Code is forcing Google Sheet to treat endPositionLongitude as string
End CoordinatesendCoordinatesCreating latitude,longitude data
Avg. PHEV Fuel ConsumptionaveragePHEVFuelConsumptionPlug-in hybrid electric vehicle (PHEV) consumption.
EV DistanceevDistanceEV Distance value. Returns null for I-Pace
Eco Score TotaltotalEcoScoreTotal ECO Score
Eco Score Total StatustotalEcoScoreStatusTotal ECO Score Status
Start TimestartTimeTrip Start Time
End TimeendTimeTrip End Time
Energy RegeneratedenergyRegeneratedEnergy Regenerated
NamenameName. Value seems to be null, and is not sure what name this is
Route Longitude MaxrouteDetailsMaxLongitude
Route Latitude MinrouteDetailsMinLatitude
Route Longitude MinrouteDetailsMinLongitude
Route Latitude MaxrouteDetailsMaxLatitude
Waypoints TotaltotalWaypointsTotalt Number of Waypoints
CategorycategoryCategory value seems to be null. Don't know what Category is
Duration SecondsdurationInSecondsDuration in Seconds. Google Data Studio needs duration in seconds
Duration HH:MM:SSdurationInHHMMSSDuration in format HH:MM:SS.
Odometer EndSheet CalculationData from the API is in meters, so we recalculate Odometer End based on user settings.
Avg. ConsumptionSheet CalculationAvg. Energy Consumption returned from the API is in "km/kWh", so we have to recalculate this based on user settings. Do not use this value alone to calculate Average Consumption across all your trips.
DistanceSheet CalculationData from the API is in meters, so we have to recalculate Distance into Km or Miles based on user settings
Energy ConsumptionSheet CalculationEnergy Consumption for the Trip. Calculation: Distance/Avg. Consumption Orig
Avg. SpeedSheet CalculationData from the API is in Km/h, so we have to recalculate Average Speed based on user settings if the user have chosen Miles.
Odometer StartSheet CalculationData from the API is in meters, so we recalculate Odometer Start based on user settings.
Energy RegeneratedSheet CalculationCalculation of Energy Regenerated based on user settings.
Consumption Calculation ValueSheet CalculationExtraction of the "Consumption Calculation Value" in the "Settings" sheet. Used for calculation in Google Data Studio.

Jaguar InControl API, which part of the API are used

The following parts of the Jaguar InControl API are used in the Google Apps Script.

  • user authentication
    • auth
      • Initial user authentication.
    • device registration
      • After a succesful user authentication it is neccessary to register a device.
    • login user
      • After successful user authentication and device id registration, we need to log in the user.
  • user information
    • get vehicles for user id
      • Lists the vehicles associated with the specified user id. Seems to limit the result to whatever is the primary vehicle by default.
  • vehicle information
    • get vehicle attributes
      • Getting attributes for a specific vehicle (VIN).
    • get vehicle trips
      • Get the last N trips associated with the specified vehicle.

Google Apps Script for Jaguar InControl API

This is the Google Apps Script that is used in this solution.

Some Closing Words

This solution is in no way a product of Jaguar or related to Jaguar. I’m just a happy Jaguar I-Pace owner that happens to work with data, and thought it was great fun to write the Google Apps Script and create the Google Sheet.

Happy driving and analyzing your Jaguar car!

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.