Jaguar InControl API for Google Sheets - Part 2

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.
30.03.2020:
- Added support for selecting the correct Jaguar if you have more than 1 car.
- Improved effeciency of the script that is fetching trips. Script now uses less time in fetching trips and writing trips to Google Sheet.

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 Name	JS Variable	Comment
Trip ID	tripId	Trip ID
Odometer End Orig	endOdometerOrig	Orginal Odometer End data from the API. Value is in meters.
Eco Score Speed	speedEcoScore	Speed ECO Score
Eco Score Speed Status	speedEcoscoreStatus	Speed ECO Score Status
Avg. Consumption Orig	averageEnergyConsumptionOrig	Orginal Avg. Energy Consumption data delivered from the API. The value is km/kWh.
Distance Orig	distanceOrig	Original Distance data. Value is meter.
Average Fuel Consumption	averageFuelConsumption	Fossil data. Returns null for I-Pace
Eco Score Throttle	throttleEcoScore	Throttle ECO Score
Eco Score Throttle Status	throttleEcoScoreStatus	Throttle ECO Score Status
Avg. Speed Orig	averageSpeedOrg	Original Average Speed from the API. Value is in Km/h.
Electrical Regeneration	electricalRegeneration	Electrical Regeneration data seems to be null
Fuel Consumption	fuelConsumption	Fossil car consumption (I guess). Value is null for I-Pace
Start Country	startPositionCountry	Country Start Position
Start Address	startPositionAddress	Address Start Position
Start City	startPositionCity	City Start Position. Value seems to be null most of the time
Start Latitude	startPositionLatitude	Latitude Start Position. Code is forcing Google Sheet to treat startPositionLatitude as string
Start Postal Code	startPositionPostalCode	Postal Code Start Position. Code is forcing Google Sheet to treat startPositionPostalCode as string
Start Region	startPositionRegion	Region Start Position. Code is forcing Google Sheet to treat startPositionRegion as string
Start Longitude	startPositionLongitude	Longitude Start Positiion. Code is forcing Google Sheet to treat startPositionLongitude as string
Start Coordinates	startCoordinates	Creating latitude,longitude data
Eco Score Braking	brakeEcoScore	Brake ECO Score
Eco Score Braking Status	brakeEcoScoreStatus	Brake ECO Score Status
Odometer Start Orig	startOdometerOrig	Orginal Odometer End data delivered from the API. Value is in meters
Electrical Consumption	electricalConsumption	Electrical Consumption value seems to be null
End Country	endPositionCountry	Country End Position
End Address	endPositionAddress	Address End Position
End City	endPositionCity	City End Position. Value seems to be null most of the time
End Latitude	endPositionLatitude	Latitude End Position. Code is forcing Google Sheet to treat endPositionLatitude as string
End Postal Code	endPositionPostalCode	Postal Code End Position. Code is forcing Google Sheet to treat endPositionPostalCode as string
End Region	endPositionRegion	Region End Position.
End Longitude	endPositionLongitude	Longitude End Position. Code is forcing Google Sheet to treat endPositionLongitude as string
End Coordinates	endCoordinates	Creating latitude,longitude data
Avg. PHEV Fuel Consumption	averagePHEVFuelConsumption	Plug-in hybrid electric vehicle (PHEV) consumption.
EV Distance	evDistance	EV Distance value. Returns null for I-Pace
Eco Score Total	totalEcoScore	Total ECO Score
Eco Score Total Status	totalEcoScoreStatus	Total ECO Score Status
Start Time	startTime	Trip Start Time
End Time	endTime	Trip End Time
Energy Regenerated	energyRegenerated	Energy Regenerated
Name	name	Name. Value seems to be null, and is not sure what name this is
Route Longitude Max	routeDetailsMaxLongitude
Route Latitude Min	routeDetailsMinLatitude
Route Longitude Min	routeDetailsMinLongitude
Route Latitude Max	routeDetailsMaxLatitude
Waypoints Total	totalWaypoints	Totalt Number of Waypoints
Category	category	Category value seems to be null. Don't know what Category is
Duration Seconds	durationInSeconds	Duration in Seconds. Google Data Studio needs duration in seconds
Duration HH:MM:SS	durationInHHMMSS	Duration in format HH:MM:SS.
Odometer End	Sheet Calculation	Data from the API is in meters, so we recalculate Odometer End based on user settings.
Avg. Consumption	Sheet Calculation	Avg. 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.
Distance	Sheet Calculation	Data from the API is in meters, so we have to recalculate Distance into Km or Miles based on user settings
Energy Consumption	Sheet Calculation	Energy Consumption for the Trip. Calculation: Distance/Avg. Consumption Orig
Avg. Speed	Sheet Calculation	Data 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 Start	Sheet Calculation	Data from the API is in meters, so we recalculate Odometer Start based on user settings.
Energy Regenerated	Sheet Calculation	Calculation of Energy Regenerated based on user settings.
Consumption Calculation Value	Sheet Calculation	Extraction 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.

/******************** INFORMATION ********************/
/* This Google Apps Script is built upon the Jaguar InControl API:
https://documenter.getpostman.com/view/6250319/RznBMzqo

Coded for fun by Eivind Savio in February 2019
Updated March 2020
*/

/* GLOBAL VARIABLES */
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Settings'); // Settings Sheet

// Save info to PropertiesService
var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('username',sheet.getRange('Username').getValue());
  scriptProperties.setProperty('password',sheet.getRange('Password').getValue());
  scriptProperties.setProperty('deviceId',sheet.getRange('DeviceId').getValue());
  scriptProperties.setProperty('tripCount',sheet.getRange('tripCount').getValue().toString());
  scriptProperties.setProperty('overwriteData',sheet.getRange('OverwriteData').getValue());
  scriptProperties.setProperty('carNumber',sheet.getRange('CarNumber').getValue());
  scriptProperties.setProperty('sheetId',ss.getId());

  
var username = scriptProperties.getProperty('username');
var password = scriptProperties.getProperty('password');
var deviceId = scriptProperties.getProperty('deviceId');
var tripCount = scriptProperties.getProperty('tripCount');
var overwriteData = scriptProperties.getProperty('overwriteData');
var sheetId = scriptProperties.getProperty('sheetId');
var carNumber = scriptProperties.getProperty('carNumber')-1;

  if(!deviceId) {
    deviceId = Utilities.getUuid(); // X-Device-Id Fallback
  }
  if(!tripCount) {
    tripCount = 100; // Trip Count Fallback
  }
  if(!carNumber) {
    carNumber = 0; // Car Number Fallback
  }

/******************** USER AUTHENTICATION ********************/
/* AUTH */
// https://documenter.getpostman.com/view/6250319/RznBMzqo#dc5d321e-26fc-4636-b440-8167584ada4d

function AuthJaguarInControlAPI() { 
if (username && password && deviceId) {
  
  var url = 'https://ifas.prod-row.jlrmotor.com/ifas/jlr/tokens';
  var data = JSON.stringify({
    'grant_type': 'password',
    'password': password,
    'username': username
  })

  var settings = {
    'muteHttpExceptions': true,
    'method' : 'post',
    'headers': {
      'Content-Type': 'application/json',
      'Authorization': 'Basic YXM6YXNwYXNz',
      'X-Device-Id': deviceId,
      'Connection': 'close'
      },
    'payload': data
  };
  
  try {
   // Call the Jaguar InControl API
   var response = UrlFetchApp.fetch(url,settings);
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);

    var access_token = json['access_token'];
    var authorization_token = json['authorization_token'];
    var expires_in = json['expires_in'];
    var refresh_token = json['refresh_token'];

// Call function deviceRegistration
if (access_token && authorization_token && expires_in && refresh_token) {
  deviceRegistration(access_token,authorization_token,expires_in,refresh_token);
}    

  } catch (error) {
    // deal with any errors
    Logger.log('AUTH: ' +error);
  };
}
}

/* DEVICE REGISTRATION */
// https://documenter.getpostman.com/view/6250319/RznBMzqo#ff8b8a31-a711-4264-8bd8-2d45f05ed888

function deviceRegistration(access_token,authorization_token,expires_in,refresh_token) {
  
var access_token = access_token;
var authorization_token = authorization_token;
var expires_in = expires_in;
var refresh_token = refresh_token;

if (access_token && authorization_token && expires_in && refresh_token) {  
  var url = 'https://ifop.prod-row.jlrmotor.com/ifop/jlr/users/'+username+'/clients';
  var data = JSON.stringify({
    'access_token': access_token,
    'authorization_token': authorization_token,
    'expires_in': expires_in,
    'deviceID': deviceId
  })

  var settings = {
    'muteHttpExceptions': true,
    'method' : 'post',
    'timeout': 0,
    'headers': {
      'Content-Type': 'application/json',
      'X-Device-Id': deviceId,
      'Connection': 'close',
      'Authorization': 'Bearer ' + access_token
      },
    'payload': data
  };
  
  try {
   // Call the Jaguar InControl API
   var response = UrlFetchApp.fetch(url,settings);
    var responseData = response.getResponseCode().toString();
    
    // If HTTP status code is 204, call function loginUser
    if (responseData.indexOf('204') > -1) {
      logInUser(access_token)
    }

  } catch (error) {
    // deal with any errors
    Logger.log('DEVICE REGISTRATION: '+error);
  };
}
}

/* LOGIN USER */
// https://documenter.getpostman.com/view/6250319/RznBMzqo#65b89dbe-e104-4aa3-9237-d6bec2171d2b

function logInUser(access_token) {

var access_token = access_token;
  
if(access_token && username) {
  var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/users?loginName='+username;
  var settings = {
    'muteHttpExceptions': true,
    'method' : 'get',
    'timeout': 0,
    'headers': {
      'Accept': 'application/vnd.wirelesscar.ngtp.if9.User-v3+json',
      'Content-Type': 'application/json',
      'X-Device-Id': deviceId,
      'Connection': 'close',
      'Authorization': 'Bearer ' + access_token
    }
  };
  
  try {
   // Call the Jaguar InControl API   
   var response = UrlFetchApp.fetch(url,settings);
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    
    var dateFormat = json['contact']['userPreferences']['dateFormat'];
    var userId = json['userId'];
    var unitsOfMeasurement = json['contact']['userPreferences']['unitsOfMeasurement'];
    
    if (dateFormat && userId && unitsOfMeasurement) {      
      var ss = SpreadsheetApp.openById(sheetId);
      var sheet = ss.getSheetByName('Settings'); // Settings Sheet
      sheet.getRange('DateFormat').setValue(dateFormat); // Update DateFormat to the "Settings" sheet
      sheet.getRange('UserId').setValue(userId); // Update UserId to the "Settings" sheet
      sheet.getRange('MeasurementUnits').setValue(unitsOfMeasurement); // Update MeasurementUnits to the "Settings" sheet
    }
    
    // if data is returned succesfully, call function getVehiclesForUserId
    if (access_token && userId) {
      getVehiclesForUserId(access_token,userId);
    }
    
  } catch (error) {
    // deal with any errors
    Logger.log('LOGIN USER: '+error);
  };
}
}

/******************** USER INFORMATION ********************/
/* GET VEHICLES FOR USERID */
// https://documenter.getpostman.com/view/6250319/RznBMzqo#4ad8fc5f-8b4e-41e1-a9cc-868cd2530b3f

function getVehiclesForUserId(access_token,userId) {

var access_token = access_token;
var userId = userId;

if (userId && access_token) {
  var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/users/'+userId+'/vehicles?primaryOnly=true';

  var settings = {
    'muteHttpExceptions': true,
    'method' : 'get',
    'headers': {
      'Content-Type': 'application/json',
      'X-Device-Id': deviceId,
      'Connection': 'close',
      'Authorization': 'Bearer ' + access_token
    }
  };
  
  try {
   // Call the Jaguar InControl API   
   var response = UrlFetchApp.fetch(url,settings);
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    
    var vin = json['vehicles'][carNumber]['vin']; // VIN (Vehicle Identification Number)
    
    if (vin) {
      var ss = SpreadsheetApp.openById(sheetId);
      var sheet = ss.getSheetByName('Settings'); // Settings Sheet
      sheet.getRange('VIN').setValue(vin); // Update VIN to the "Settings" sheet
    }
    
    // if data is returned succesfully, call function getVehicleAttributes & getVehicleTrips
    if (access_token && vin) {
      getVehicleAttributes(access_token,vin);
      getVehicleTrips(access_token,vin);
    }
    
  } catch (error) {
    // deal with any errors
    Logger.log('LOGIN USER: '+error);
  };
}
}

/******************** VEHICLE INFORMATION ********************/
/* GET VEHICLE ATTRIBUTES */
// https://documenter.getpostman.com/view/6250319/RznBMzqo#11dc9de8-7c47-4bf3-9fc5-c3a3a55dbcca

function getVehicleAttributes(access_token,vin) {

var access_token = access_token;
var vin = vin;

if (vin && access_token) {
  var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/vehicles/'+vin+'/attributes';
  var settings = {
    'muteHttpExceptions': true,
    'method' : 'get',
    'headers': {
      'Accept': 'application/vnd.ngtp.org.VehicleAttributes-v3+json',
      'Content-Type': 'application/json',
      'X-Device-Id': deviceId,
      'Connection': 'close',
      'Authorization': 'Bearer ' + access_token
    }
  };
  
  try {
   // Call the Jaguar InControl API   
   var response = UrlFetchApp.fetch(url,settings);
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    
    var nickname = json['nickname']; // Your name of the car
    var registrationNumber = json['registrationNumber']; // Registration Number
    
    if (nickname) {
      var ss = SpreadsheetApp.openById(sheetId);
      var sheet = ss.getSheetByName('Settings'); // Settings Sheet
      sheet.getRange('Nickname').setValue(nickname); // Update Nickname to the "Settings" sheet
      sheet.getRange('RegistrationNumber').setValue(registrationNumber); // Update RegistrationNumber to the "Settings" sheet
    }
       
  } catch (error) {
    // deal with any errors
    Logger.log('GET VEHICLE ATTRIBUTES: '+error);
  };
}
}


/* GET VEHICLE STATUS */
// https://documenter.getpostman.com/view/6250319/RznBMzqo?version=latest#d3b57f40-6888-4571-aef9-8759dbbdc3cd

function getVehicleStatus(access_token,vin) {

var access_token = access_token;
var vin = vin;

if (vin && access_token) {
  var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/vehicles/'+vin+'/status';
  
  var settings = {
    'muteHttpExceptions': true,
    'method' : 'get',
    'headers': {
      'Accept': 'application/vnd.ngtp.org.if9.healthstatus-v2+json',
      'Content-Type': 'application/json',
      'X-Device-Id': deviceId,
      'Connection': 'close',
      'Authorization': 'Bearer ' + access_token
    }
  };
  
  try {
   // Call the Jaguar InControl API   
   var response = UrlFetchApp.fetch(url,settings);
    var responseData = response.getContentText();
    var json = JSON.parse(responseData);
    
    Logger.log('GET VEHICLE STATUS: '+JSON.stringify(json));
       
  } catch (error) {
    // deal with any errors
    Logger.log('GET VEHICLE STATUS: '+error);
  };
}
}

/* GET VEHICLE TRIPS */
// https://documenter.getpostman.com/view/6250319/RznBMzqo#af76c5d6-3ed1-483b-bf82-6ef92e6c8271

function getVehicleTrips(access_token,vin) {

var access_token = access_token;
var vin = vin;

if (vin && tripCount && access_token) {
  var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/vehicles/'+vin+'/trips?count='+tripCount;
  var settings = {
    'muteHttpExceptions': true,
    'method' : 'get',
    'headers': {
      'Accept': 'application/vnd.ngtp.org.triplist-v2+json',
      'Content-Type': 'application/json',
      'X-Device-Id': deviceId,
      'Connection': 'close',
      'Authorization': 'Bearer ' + access_token
    }
  };
  
  try {
   // Call the Jaguar InControl API   
     var response = UrlFetchApp.fetch(url,settings);
     var responseData = response.getContentText();
     var json = JSON.parse(responseData);
    
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName("Jaguar InControl Trips API Data");
var settings = ss.getSheetByName('Settings'); // Settings Sheet
    
    // Overwrite Trip Data or not
    if (overwriteData == "Yes") {
      sheet.clear(); // Clear Tripdata in Spreadsheet "Jaguar InControl Trips API Data"

      // Append Column Headers  
      sheet.appendRow(["Trip ID",
                     "Odometer End Orig",
                     "Eco Score Speed",
                     "Eco Score Speed Status",
                     "Avg. Consumption Orig",
                     "Distance Orig", 
                     "Avg. Fuel Consumption",
                     "Eco Score Throttle",
                     "Eco Score Throttle Status",
                     "Avg. Speed Orig",
                     "Electrical Regeneration",
                     "Fuel Consumption",
                     "Start Country",
                     "Start Address",
                     "Start City",
                     "Start Latitude",
                     "Start Postal Code",
                     "Start Region",
                     "Start Longitude",
                     "Start Coordinates",
                     "Eco Score Braking",
                     "Eco Score Braking Status",                     
                     "Odometer Start Orig",
                     "Electrical Consumption",
                     "End Country",
                     "End Address",
                     "End City",
                     "End Latitude",
                     "End Postal Code",
                     "End Region",
                     "End Longitude",
                     "End Coordinates",
                     "Avg. PHEV Fuel Consumption",
                     "EV Distance",
                     "Eco Score Total",
                     "Eco Score Total Status",
                     "Start Time", 
                     "End Time",
                     "Energy Regenerated Orig",
                     "Name",
                     "Route Longitude Max",
                     "Route Latitude Min",
                     "Route Longitude Min",
                     "Route Latitude Max",
                     "Waypoints Total",
                     "Category",
                     "Duration Seconds",
                     "Duration HH:MM:SS"]);
      }
    // End Overwrite Trip Data or not
    
var tripData = json['trips'];
var report = [];
// Loop through Trip Data    
for (i=0; i< tripData.length; i++){
  var a = tripData[i];
  var b = a.tripDetails;

  var tripId = a.id; // Trip ID
  var endOdometerOrig = b.endOdometer; // Orginal Odometer End data from the API. Value is in meters.
  var speedEcoScore = b.speedEcoScore.score; // Speed ECO Score
  var speedEcoscoreStatus = b.speedEcoScore.scoreStatus; // Speed ECO Score Status
  var averageEnergyConsumptionOrig = b.averageEnergyConsumption; // Orginal Avg. Energy Consumption data delivered from the API. The value is km/kWh.
  var distanceOrig = b.distance; // Original Distance data. Value is meter.
  var averageFuelConsumption = b.averageFuelConsumption; // Fossil data. Returns null for I-Pace
  var throttleEcoScore = b.throttleEcoScore.score; // Throttle ECO Score
  var throttleEcoScoreStatus = b.throttleEcoScore.scoreStatus; // Throttle ECO Score Status
  var averageSpeedOrig = b.averageSpeed; // Original Average Speed from the API. Value is in Km/h.
  var electricalRegeneration = b.electricalRegeneration; // Electrical Regeneration data seems to be null
  var fuelConsumption = b.fuelConsumption; // Fossil car consumption
  var startPositionCountry = b.startPosition.country; // Country Start Position
  var startPositionAddress = b.startPosition.address; // Address Start Position
  var startPositionCity = b.startPosition.city; // City Start Position. Value seems to be null most of the time
  var startPositionLatitude = "'"+b.startPosition.latitude; // Latitude Start Position. Code is forcing Google Sheet to treat startPositionLatitude as string
  var startPositionPostalCode = "'"+b.startPosition.postalCode; // Postal Code Start Position. Code is forcing Google Sheet to treat startPositionPostalCode as string
  var startPositionRegion = b.startPosition.region; // Region Start Position. Code is forcing Google Sheet to treat startPositionRegion as string
  var startPositionLongitude = "'"+b.startPosition.longitude; // Longitude Start Positiion. Code is forcing Google Sheet to treat startPositionLongitude as string
  var startCoordinates = b.startPosition.latitude +','+ b.startPosition.longitude; // Creating latitude,longitude data
  var brakeEcoScore = b.brakeEcoScore.score; // Brake ECO Score
  var brakeEcoScoreStatus = b.brakeEcoScore.scoreStatus; // Brake ECO Score Status 
  var startOdometerOrig = b.startOdometer; // Orginal Odometer End data delivered from the API. Value is in meters
  var electricalConsumption = b.electricalConsumption; // Electrical Consumption value seems to be null
  var endPositionCountry = b.endPosition.country; // Country End Position
  var endPositionAddress = b.endPosition.address; // Address End Position
  var endPositionCity = b.endPosition.city; // City End Position.  Value seems to be null most of the time
  var endPositionLatitude = "'"+b.endPosition.latitude; // Latitude End Position. Code is forcing Google Sheet to treat endPositionLatitude as string
  var endPositionPostalCode = "'"+b.endPosition.postalCode; // Postal Code End Position. Code is forcing Google Sheet to treat endPositionPostalCode as string
  var endPositionRegion = b.endPosition.region; // Region End Position. 
  var endPositionLongitude = "'"+b.endPosition.longitude; // Longitude End Position. Code is forcing Google Sheet to treat endPositionLongitude as string
  var endCoordinates = b.endPosition.latitude +','+ b.endPosition.longitude; // Creating latitude,longitude data
  var averagePHEVFuelConsumption = b.averagePHEVFuelConsumption; // Plug-in hybrid electric vehicle (PHEV) consumption.
  var evDistance = b.evDistance; // EV Distance value seems to be null
  var totalEcoScore = b.totalEcoScore.score; // Total ECO Score
  var totalEcoScoreStatus = b.totalEcoScore.scoreStatus; // Total ECO Score Status
  var startTime = new Date(b.startTime); // Trip Start Time
  var endTime = new Date(b.endTime); // Trip End Time
  var energyRegeneratedOrig = b.energyRegenerated; // Energy Regenerated
  var name = a.name; // Name. Value seems to be null, and is not sure what name this is
  var routeDetailsMaxLongitude = "'"+a.routeDetails.boundingBox.maxLongitude; // Just forcing Google Sheet to treat routeDetailsMaxLongitude as string
  var routeDetailsMinLatitude = "'"+a.routeDetails.boundingBox.minLatitude; // Just forcing Google Sheet to treat routeDetailsMinLatitude as string 
  var routeDetailsMinLongitude = "'"+a.routeDetails.boundingBox.minLongitude; // Just forcing Google Sheet to treat routeDetailsMinLongitude as string
  var routeDetailsMaxLatitude = "'"+a.routeDetails.boundingBox.maxLatitude; // Just forcing Google Sheet to treat routeDetailsMaxLatitude as string
  var totalWaypoints = a.routeDetails.totalWaypoints; // Totalt Number of Waypoints
  var category = a.category; // Category value seems to be null. Don't know what Category is
  var durationInSeconds = (Math.abs(endTime.getTime())-Math.abs(startTime.getTime()))/1000; // Duration in Seconds. Google Data Studio needs duration in seconds
  var durationInHHMMSS = new Date(durationInSeconds * 1000).toISOString().substr(11, 8); // Duration in format HH:MM:SS. 
  
// Report the data. The order of the values must match Column Headers
  report.push([tripId,
                endOdometerOrig,
                speedEcoScore,
                speedEcoscoreStatus,
                averageEnergyConsumptionOrig,
                distanceOrig,
                averageFuelConsumption,
                throttleEcoScore,
                throttleEcoScoreStatus,
                averageSpeedOrig,
                electricalRegeneration,
                fuelConsumption,
                startPositionCountry,
                startPositionAddress,
                startPositionCity,
                startPositionLatitude,
                startPositionPostalCode,
                startPositionRegion,
                startPositionLongitude,
                startCoordinates,
                brakeEcoScore,
                brakeEcoScoreStatus,
                startOdometerOrig,
                electricalConsumption,
                endPositionCountry,
                endPositionAddress,
                endPositionCity,
                endPositionLatitude,
                endPositionPostalCode,
                endPositionRegion,
                endPositionLongitude,
                endCoordinates,
                averagePHEVFuelConsumption,
                evDistance,
                totalEcoScore,
                totalEcoScoreStatus,
                startTime,
                endTime,
                energyRegeneratedOrig,
                name,
                routeDetailsMaxLongitude,
                routeDetailsMinLatitude,
                routeDetailsMinLongitude,
                routeDetailsMaxLatitude,
                totalWaypoints,
                category,
                durationInSeconds,
                durationInHHMMSS]);

}
// Add data to sheet
sheet.getRange(sheet.getLastRow()+1, 1, report.length, report[0].length).setValues(report);   
  } catch (error) {
    // deal with any errors
    Logger.log('GET VEHICLE TRIPS: '+error);
  };
}
}

/**
 * Removes duplicate rows from the current sheet.
 * Use this function if you have chosen to NOT overwrite data.
 * You need to activate a seperate trigger for this function, and run it at least 6 minutes after the "AuthJaguarInControlAPI" function.
 */
function removeDuplicates() {
try {
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName("Jaguar InControl Trips API Data");

  var data = sheet.getDataRange().getValues();
  var newData = [];
  for (i in data) {
    var row = data[i];
    var duplicate = false;
    for (j in newData) {
      if(row[0] == newData[j][0]){
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
} catch (error) {
    // deal with any errors
    Logger.log('REMOVE DUPLICATES: '+error);
  };
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

/******************** INFORMATION ********************/

/* This Google Apps Script is built upon the Jaguar InControl API:

https://documenter.getpostman.com/view/6250319/RznBMzqo

Coded for fun by Eivind Savio in February 2019

Updated March 2020

/* GLOBAL VARIABLES */

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName('Settings'); // Settings Sheet

// Save info to PropertiesService

var scriptProperties = PropertiesService.getScriptProperties();

scriptProperties.setProperty('username',sheet.getRange('Username').getValue());

scriptProperties.setProperty('password',sheet.getRange('Password').getValue());

scriptProperties.setProperty('deviceId',sheet.getRange('DeviceId').getValue());

scriptProperties.setProperty('tripCount',sheet.getRange('tripCount').getValue().toString());

scriptProperties.setProperty('overwriteData',sheet.getRange('OverwriteData').getValue());

scriptProperties.setProperty('carNumber',sheet.getRange('CarNumber').getValue());

scriptProperties.setProperty('sheetId',ss.getId());

var username = scriptProperties.getProperty('username');

var password = scriptProperties.getProperty('password');

var deviceId = scriptProperties.getProperty('deviceId');

var tripCount = scriptProperties.getProperty('tripCount');

var overwriteData = scriptProperties.getProperty('overwriteData');

var sheetId = scriptProperties.getProperty('sheetId');

var carNumber = scriptProperties.getProperty('carNumber')-1;

if(!deviceId) {

deviceId = Utilities.getUuid(); // X-Device-Id Fallback

}

if(!tripCount) {

tripCount = 100; // Trip Count Fallback

}

if(!carNumber) {

carNumber = 0; // Car Number Fallback

}

/******************** USER AUTHENTICATION ********************/

/* AUTH */

// https://documenter.getpostman.com/view/6250319/RznBMzqo#dc5d321e-26fc-4636-b440-8167584ada4d

function AuthJaguarInControlAPI() {

if (username && password && deviceId) {

var url = 'https://ifas.prod-row.jlrmotor.com/ifas/jlr/tokens';

var data = JSON.stringify({

'grant_type': 'password',

'password': password,

'username': username

})

var settings = {

'muteHttpExceptions': true,

'method' : 'post',

'headers': {

'Content-Type': 'application/json',

'Authorization': 'Basic YXM6YXNwYXNz',

'X-Device-Id': deviceId,

'Connection': 'close'

'payload': data

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getContentText();

var json = JSON.parse(responseData);

var access_token = json['access_token'];

var authorization_token = json['authorization_token'];

var expires_in = json['expires_in'];

var refresh_token = json['refresh_token'];

// Call function deviceRegistration

if (access_token && authorization_token && expires_in && refresh_token) {

deviceRegistration(access_token,authorization_token,expires_in,refresh_token);

}

} catch (error) {

// deal with any errors

Logger.log('AUTH: ' +error);

};

}

/* DEVICE REGISTRATION */

// https://documenter.getpostman.com/view/6250319/RznBMzqo#ff8b8a31-a711-4264-8bd8-2d45f05ed888

function deviceRegistration(access_token,authorization_token,expires_in,refresh_token) {

var access_token = access_token;

var authorization_token = authorization_token;

var expires_in = expires_in;

var refresh_token = refresh_token;

if (access_token && authorization_token && expires_in && refresh_token) {

var url = 'https://ifop.prod-row.jlrmotor.com/ifop/jlr/users/'+username+'/clients';

var data = JSON.stringify({

'access_token': access_token,

'authorization_token': authorization_token,

'expires_in': expires_in,

'deviceID': deviceId

})

var settings = {

'muteHttpExceptions': true,

'method' : 'post',

'timeout': 0,

'headers': {

'Content-Type': 'application/json',

'X-Device-Id': deviceId,

'Connection': 'close',

'Authorization': 'Bearer ' + access_token

'payload': data

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getResponseCode().toString();

// If HTTP status code is 204, call function loginUser

if (responseData.indexOf('204') > -1) {

logInUser(access_token)

}

} catch (error) {

// deal with any errors

Logger.log('DEVICE REGISTRATION: '+error);

};

}

/* LOGIN USER */

// https://documenter.getpostman.com/view/6250319/RznBMzqo#65b89dbe-e104-4aa3-9237-d6bec2171d2b

function logInUser(access_token) {

var access_token = access_token;

if(access_token && username) {

var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/users?loginName='+username;

var settings = {

'muteHttpExceptions': true,

'method' : 'get',

'timeout': 0,

'headers': {

'Accept': 'application/vnd.wirelesscar.ngtp.if9.User-v3+json',

'Content-Type': 'application/json',

'X-Device-Id': deviceId,

'Connection': 'close',

'Authorization': 'Bearer ' + access_token

}

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getContentText();

var json = JSON.parse(responseData);

var dateFormat = json['contact']['userPreferences']['dateFormat'];

var userId = json['userId'];

var unitsOfMeasurement = json['contact']['userPreferences']['unitsOfMeasurement'];

if (dateFormat && userId && unitsOfMeasurement) {

var ss = SpreadsheetApp.openById(sheetId);

var sheet = ss.getSheetByName('Settings'); // Settings Sheet

sheet.getRange('DateFormat').setValue(dateFormat); // Update DateFormat to the "Settings" sheet

sheet.getRange('UserId').setValue(userId); // Update UserId to the "Settings" sheet

sheet.getRange('MeasurementUnits').setValue(unitsOfMeasurement); // Update MeasurementUnits to the "Settings" sheet

}

// if data is returned succesfully, call function getVehiclesForUserId

if (access_token && userId) {

getVehiclesForUserId(access_token,userId);

}

} catch (error) {

// deal with any errors

Logger.log('LOGIN USER: '+error);

};

}

/******************** USER INFORMATION ********************/

/* GET VEHICLES FOR USERID */

// https://documenter.getpostman.com/view/6250319/RznBMzqo#4ad8fc5f-8b4e-41e1-a9cc-868cd2530b3f

function getVehiclesForUserId(access_token,userId) {

var access_token = access_token;

var userId = userId;

if (userId && access_token) {

var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/users/'+userId+'/vehicles?primaryOnly=true';

var settings = {

'muteHttpExceptions': true,

'method' : 'get',

'headers': {

'Content-Type': 'application/json',

'X-Device-Id': deviceId,

'Connection': 'close',

'Authorization': 'Bearer ' + access_token

}

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getContentText();

var json = JSON.parse(responseData);

var vin = json['vehicles'][carNumber]['vin']; // VIN (Vehicle Identification Number)

if (vin) {

var ss = SpreadsheetApp.openById(sheetId);

var sheet = ss.getSheetByName('Settings'); // Settings Sheet

sheet.getRange('VIN').setValue(vin); // Update VIN to the "Settings" sheet

}

// if data is returned succesfully, call function getVehicleAttributes & getVehicleTrips

if (access_token && vin) {

getVehicleAttributes(access_token,vin);

getVehicleTrips(access_token,vin);

}

} catch (error) {

// deal with any errors

Logger.log('LOGIN USER: '+error);

};

}

/******************** VEHICLE INFORMATION ********************/

/* GET VEHICLE ATTRIBUTES */

// https://documenter.getpostman.com/view/6250319/RznBMzqo#11dc9de8-7c47-4bf3-9fc5-c3a3a55dbcca

function getVehicleAttributes(access_token,vin) {

var access_token = access_token;

var vin = vin;

if (vin && access_token) {

var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/vehicles/'+vin+'/attributes';

var settings = {

'muteHttpExceptions': true,

'method' : 'get',

'headers': {

'Accept': 'application/vnd.ngtp.org.VehicleAttributes-v3+json',

'Content-Type': 'application/json',

'X-Device-Id': deviceId,

'Connection': 'close',

'Authorization': 'Bearer ' + access_token

}

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getContentText();

var json = JSON.parse(responseData);

var nickname = json['nickname']; // Your name of the car

var registrationNumber = json['registrationNumber']; // Registration Number

if (nickname) {

var ss = SpreadsheetApp.openById(sheetId);

var sheet = ss.getSheetByName('Settings'); // Settings Sheet

sheet.getRange('Nickname').setValue(nickname); // Update Nickname to the "Settings" sheet

sheet.getRange('RegistrationNumber').setValue(registrationNumber); // Update RegistrationNumber to the "Settings" sheet

}

} catch (error) {

// deal with any errors

Logger.log('GET VEHICLE ATTRIBUTES: '+error);

};

}

/* GET VEHICLE STATUS */

// https://documenter.getpostman.com/view/6250319/RznBMzqo?version=latest#d3b57f40-6888-4571-aef9-8759dbbdc3cd

function getVehicleStatus(access_token,vin) {

var access_token = access_token;

var vin = vin;

if (vin && access_token) {

var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/vehicles/'+vin+'/status';

var settings = {

'muteHttpExceptions': true,

'method' : 'get',

'headers': {

'Accept': 'application/vnd.ngtp.org.if9.healthstatus-v2+json',

'Content-Type': 'application/json',

'X-Device-Id': deviceId,

'Connection': 'close',

'Authorization': 'Bearer ' + access_token

}

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getContentText();

var json = JSON.parse(responseData);

Logger.log('GET VEHICLE STATUS: '+JSON.stringify(json));

} catch (error) {

// deal with any errors

Logger.log('GET VEHICLE STATUS: '+error);

};

}

/* GET VEHICLE TRIPS */

// https://documenter.getpostman.com/view/6250319/RznBMzqo#af76c5d6-3ed1-483b-bf82-6ef92e6c8271

function getVehicleTrips(access_token,vin) {

var access_token = access_token;

var vin = vin;

if (vin && tripCount && access_token) {

var url = 'https://if9.prod-row.jlrmotor.com/if9/jlr/vehicles/'+vin+'/trips?count='+tripCount;

var settings = {

'muteHttpExceptions': true,

'method' : 'get',

'headers': {

'Accept': 'application/vnd.ngtp.org.triplist-v2+json',

'Content-Type': 'application/json',

'X-Device-Id': deviceId,

'Connection': 'close',

'Authorization': 'Bearer ' + access_token

}

};

try {

// Call the Jaguar InControl API

var response = UrlFetchApp.fetch(url,settings);

var responseData = response.getContentText();

var json = JSON.parse(responseData);

var ss = SpreadsheetApp.openById(sheetId);

var sheet = ss.getSheetByName("Jaguar InControl Trips API Data");

var settings = ss.getSheetByName('Settings'); // Settings Sheet

// Overwrite Trip Data or not

if (overwriteData == "Yes") {

sheet.clear(); // Clear Tripdata in Spreadsheet "Jaguar InControl Trips API Data"

// Append Column Headers

sheet.appendRow(["Trip ID",

"Odometer End Orig",

"Eco Score Speed",

"Eco Score Speed Status",

"Avg. Consumption Orig",

"Distance Orig",

"Avg. Fuel Consumption",

"Eco Score Throttle",

"Eco Score Throttle Status",

"Avg. Speed Orig",

"Electrical Regeneration",

"Fuel Consumption",

"Start Country",

"Start Address",

"Start City",

"Start Latitude",

"Start Postal Code",

"Start Region",

"Start Longitude",

"Start Coordinates",

"Eco Score Braking",

"Eco Score Braking Status",

"Odometer Start Orig",

"Electrical Consumption",

"End Country",

"End Address",

"End City",

"End Latitude",

"End Postal Code",

"End Region",

"End Longitude",

"End Coordinates",

"Avg. PHEV Fuel Consumption",

"EV Distance",

"Eco Score Total",

"Eco Score Total Status",

"Start Time",

"End Time",

"Energy Regenerated Orig",

"Name",

"Route Longitude Max",

"Route Latitude Min",

"Route Longitude Min",

"Route Latitude Max",

"Waypoints Total",

"Category",

"Duration Seconds",

"Duration HH:MM:SS"]);

}

// End Overwrite Trip Data or not

var tripData = json['trips'];

var report = [];

// Loop through Trip Data

for (i=0; i< tripData.length; i++){

var a = tripData[i];

var b = a.tripDetails;

var tripId = a.id; // Trip ID

var endOdometerOrig = b.endOdometer; // Orginal Odometer End data from the API. Value is in meters.

var speedEcoScore = b.speedEcoScore.score; // Speed ECO Score

var speedEcoscoreStatus = b.speedEcoScore.scoreStatus; // Speed ECO Score Status

var averageEnergyConsumptionOrig = b.averageEnergyConsumption; // Orginal Avg. Energy Consumption data delivered from the API. The value is km/kWh.

var distanceOrig = b.distance; // Original Distance data. Value is meter.

var averageFuelConsumption = b.averageFuelConsumption; // Fossil data. Returns null for I-Pace

var throttleEcoScore = b.throttleEcoScore.score; // Throttle ECO Score

var throttleEcoScoreStatus = b.throttleEcoScore.scoreStatus; // Throttle ECO Score Status

var averageSpeedOrig = b.averageSpeed; // Original Average Speed from the API. Value is in Km/h.

var electricalRegeneration = b.electricalRegeneration; // Electrical Regeneration data seems to be null

var fuelConsumption = b.fuelConsumption; // Fossil car consumption

var startPositionCountry = b.startPosition.country; // Country Start Position

var startPositionAddress = b.startPosition.address; // Address Start Position

var startPositionCity = b.startPosition.city; // City Start Position. Value seems to be null most of the time

var startPositionLatitude = "'"+b.startPosition.latitude; // Latitude Start Position. Code is forcing Google Sheet to treat startPositionLatitude as string

var startPositionPostalCode = "'"+b.startPosition.postalCode; // Postal Code Start Position. Code is forcing Google Sheet to treat startPositionPostalCode as string

var startPositionRegion = b.startPosition.region; // Region Start Position. Code is forcing Google Sheet to treat startPositionRegion as string

var startPositionLongitude = "'"+b.startPosition.longitude; // Longitude Start Positiion. Code is forcing Google Sheet to treat startPositionLongitude as string

var startCoordinates = b.startPosition.latitude +','+ b.startPosition.longitude; // Creating latitude,longitude data

var brakeEcoScore = b.brakeEcoScore.score; // Brake ECO Score

var brakeEcoScoreStatus = b.brakeEcoScore.scoreStatus; // Brake ECO Score Status

var startOdometerOrig = b.startOdometer; // Orginal Odometer End data delivered from the API. Value is in meters

var electricalConsumption = b.electricalConsumption; // Electrical Consumption value seems to be null

var endPositionCountry = b.endPosition.country; // Country End Position

var endPositionAddress = b.endPosition.address; // Address End Position

var endPositionCity = b.endPosition.city; // City End Position. Value seems to be null most of the time

var endPositionLatitude = "'"+b.endPosition.latitude; // Latitude End Position. Code is forcing Google Sheet to treat endPositionLatitude as string

var endPositionPostalCode = "'"+b.endPosition.postalCode; // Postal Code End Position. Code is forcing Google Sheet to treat endPositionPostalCode as string

var endPositionRegion = b.endPosition.region; // Region End Position.

var endPositionLongitude = "'"+b.endPosition.longitude; // Longitude End Position. Code is forcing Google Sheet to treat endPositionLongitude as string

var endCoordinates = b.endPosition.latitude +','+ b.endPosition.longitude; // Creating latitude,longitude data

var averagePHEVFuelConsumption = b.averagePHEVFuelConsumption; // Plug-in hybrid electric vehicle (PHEV) consumption.

var evDistance = b.evDistance; // EV Distance value seems to be null

var totalEcoScore = b.totalEcoScore.score; // Total ECO Score

var totalEcoScoreStatus = b.totalEcoScore.scoreStatus; // Total ECO Score Status

var startTime = new Date(b.startTime); // Trip Start Time

var endTime = new Date(b.endTime); // Trip End Time

var energyRegeneratedOrig = b.energyRegenerated; // Energy Regenerated

var name = a.name; // Name. Value seems to be null, and is not sure what name this is

var routeDetailsMaxLongitude = "'"+a.routeDetails.boundingBox.maxLongitude; // Just forcing Google Sheet to treat routeDetailsMaxLongitude as string

var routeDetailsMinLatitude = "'"+a.routeDetails.boundingBox.minLatitude; // Just forcing Google Sheet to treat routeDetailsMinLatitude as string

var routeDetailsMinLongitude = "'"+a.routeDetails.boundingBox.minLongitude; // Just forcing Google Sheet to treat routeDetailsMinLongitude as string

var routeDetailsMaxLatitude = "'"+a.routeDetails.boundingBox.maxLatitude; // Just forcing Google Sheet to treat routeDetailsMaxLatitude as string

var totalWaypoints = a.routeDetails.totalWaypoints; // Totalt Number of Waypoints

var category = a.category; // Category value seems to be null. Don't know what Category is

var durationInSeconds = (Math.abs(endTime.getTime())-Math.abs(startTime.getTime()))/1000; // Duration in Seconds. Google Data Studio needs duration in seconds

var durationInHHMMSS = new Date(durationInSeconds * 1000).toISOString().substr(11, 8); // Duration in format HH:MM:SS.

// Report the data. The order of the values must match Column Headers

report.push([tripId,

endOdometerOrig,

speedEcoScore,

speedEcoscoreStatus,

averageEnergyConsumptionOrig,

distanceOrig,

averageFuelConsumption,

throttleEcoScore,

throttleEcoScoreStatus,

averageSpeedOrig,

electricalRegeneration,

fuelConsumption,

startPositionCountry,

startPositionAddress,

startPositionCity,

startPositionLatitude,

startPositionPostalCode,

startPositionRegion,

startPositionLongitude,

startCoordinates,

brakeEcoScore,

brakeEcoScoreStatus,

startOdometerOrig,

electricalConsumption,

endPositionCountry,

endPositionAddress,

endPositionCity,

endPositionLatitude,

endPositionPostalCode,

endPositionRegion,

endPositionLongitude,

endCoordinates,

averagePHEVFuelConsumption,

evDistance,

totalEcoScore,

totalEcoScoreStatus,

startTime,

endTime,

energyRegeneratedOrig,

name,

routeDetailsMaxLongitude,

routeDetailsMinLatitude,

routeDetailsMinLongitude,

routeDetailsMaxLatitude,

totalWaypoints,

category,

durationInSeconds,

durationInHHMMSS]);

}

// Add data to sheet

sheet.getRange(sheet.getLastRow()+1, 1, report.length, report[0].length).setValues(report);

} catch (error) {

// deal with any errors

Logger.log('GET VEHICLE TRIPS: '+error);

};

}

/**

* Removes duplicate rows from the current sheet.

* Use this function if you have chosen to NOT overwrite data.

* You need to activate a seperate trigger for this function, and run it at least 6 minutes after the "AuthJaguarInControlAPI" function.

function removeDuplicates() {

try {

var ss = SpreadsheetApp.openById(sheetId);

var sheet = ss.getSheetByName("Jaguar InControl Trips API Data");

var data = sheet.getDataRange().getValues();

var newData = [];

for (i in data) {

var row = data[i];

var duplicate = false;

for (j in newData) {

if(row[0] == newData[j][0]){

duplicate = true;

}

if (!duplicate) {

newData.push(row);

}

sheet.clearContents();

sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

} catch (error) {

// deal with any errors

Logger.log('REMOVE DUPLICATES: '+error);

};

}

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!

Savio.no

Digital Analytics & Internet Marketing

Jaguar InControl API for Google Sheets – Part 2

Updates

Jaguar InControl Google Sheets Column Names & Variables

Jaguar InControl API, which part of the API are used

Google Apps Script for Jaguar InControl API

Some Closing Words

Related Posts:

2 Trackbacks & Pingbacks

Leave a comment Cancel reply