Discussions

Hello team,

I'm trying to pull data of our truck fleet Odometer data on a real-time basis, into a column of a google sheet i have created in place and i'm trying to pull out data and update the google sheet using a code in the Appscript in google sheets.

Unfortunately, its not fetching me the Odometer data into the Google sheets.

Code being used:

function updateMotiveOdometers() {
var apiKey = "input my motive API key here";

var sheetNamesToProcess = [
"Long Haul", "SF Trucks", "GTA", "CAMBRIDGE", "Montreal", "CSX + Local"
];

var motiveData = {};
var page = 1;
var hasMore = true;

console.log("--- STARTING FETCH (V1 VEHICLES) ---");

while (hasMore) {
var url = "https://api.keeptruckin.com/v1/vehicles?per_page=100&page_no=" + page;
var options = {
"method": "GET",
"headers": { "X-Api-Key": apiKey },
"muteHttpExceptions": true
};

var response = UrlFetchApp.fetch(url, options);
var json = JSON.parse(response.getContentText());
var vehicles = json.vehicles;

if (!vehicles || vehicles.length === 0) {
  hasMore = false;
} else {
  // DEBUG: Log the first truck of the first page to see exactly what data looks like
  if (page === 1 && vehicles[0]) {
    console.log("Data Sample for Truck " + vehicles[0].vehicle.number + ": " + JSON.stringify(vehicles[0].vehicle));
  }

  vehicles.forEach(function(v) {
    var truckNum = String(v.vehicle.number).trim().toUpperCase();
    
    // Try all possible locations for the odometer in V1
    var rawOdo = v.vehicle.odometer || 
                 (v.vehicle.current_location ? v.vehicle.current_location.odometer : null) ||
                 v.vehicle.last_known_odometer; 

    if (rawOdo && rawOdo > 0) {
      var miles = (rawOdo * 0.000621371).toFixed(0);
      motiveData[truckNum] = miles;
    }
  });
  page++;
}
if (page > 10) hasMore = false;

}

console.log("Total trucks found with mileage: " + Object.keys(motiveData).length);

var ss = SpreadsheetApp.getActiveSpreadsheet();

sheetNamesToProcess.forEach(function(name) {
var sheet = ss.getSheetByName(name);
if (!sheet) return;

var lastRow = sheet.getLastRow();
if (lastRow < 2) return;

// Get Truck Numbers from Column C
var truckValues = sheet.getRange(2, 3, lastRow - 1, 1).getValues();
var odoUpdates = [];

for (var i = 0; i < truckValues.length; i++) {
  var truckInSheet = String(truckValues[i][0]).trim().toUpperCase();
  
  if (motiveData[truckInSheet]) {
    odoUpdates.push([motiveData[truckInSheet]]);
  } else {
    // Keep current cell value in Column D if no match
    odoUpdates.push([sheet.getRange(i + 2, 4).getValue()]);
  }
}

// Write ONLY to Column D to avoid dropdown errors
sheet.getRange(2, 4, odoUpdates.length, 1).setValues(odoUpdates);
console.log("Updated " + name);

});
}

But when i run the code, the code executed successfully, but my google sheets are NOT updated with any Odometer data.

One of the lines in the Excecution log shows:

10:06:36 AM Info Total trucks found with mileage: 0

I need help here ASAP!