Track the Value of Your Vehicle with a vinAudit Script

Google Sheets Show & Tell
, , ,
21

@jnjustice no, it doesn’t change often. Believe I just set it up like that originally and never changed it.

22

Thanks! This was great. I only wish we could run it 6/12 months at a time via the trigger. Oh well… monthly it is

25

I have two cars and am successfully pulling estimates for both and see them appear in balances. If I wanted to update my mileage for each to presumably get a more precise estimate how do I do that?

26

This was my experience also. I added toString() in line 28 and the the script runs without error. Unfortunately, I don’t see anything added to my Balance History sheet.

27

My good friend ChatGPT just helped me write the below. It’s a slightly different approach as compared to the above. If you use this, you’ll need to edit the script to:

  1. Add your vin do the apiURL in row 3
  1. Add your sheetId in row 16

Good luck!

-Eric

function getDataAndInsertToSheet() {
// API URL with VIN and API key
var apiUrl = “https://marketvalue.vinaudit.com/getmarketvalue.php?vin=*****************&key=VA_DEMO_KEY&format=json”;

// Make API request
var response = UrlFetchApp.fetch(apiUrl);
var data = response.getContentText();
var jsonData = JSON.parse(data);

// Extracting values from JSON
var vin = jsonData.vin;
var vehicle = jsonData.vehicle;
var averagePrice = jsonData.mean;

// Inserting data into Google Spreadsheet
var sheetId = “********************”;
var sheetName = “Balance History”; // Correct sheet name

var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);

// Get current date and time
var currentDate = Utilities.formatDate(new Date(), “GMT”, “M/d/YYYY”);
var currentTime = Utilities.formatDate(new Date(response.getHeaders()[“Date”]), “GMT”, “h:mm a”);

// Get the first day of the current month
var firstDayOfMonth = Utilities.formatDate(new Date(), “GMT”, “MM/01/YYYY”);

// Get the date of the Sunday of the current week
var today = new Date();
var sundayDate = new Date(today.setDate(today.getDate() - today.getDay()));

// Inserting data into the sheet at the top
sheet.insertRowBefore(2);
sheet.getRange(2, 2, 1, 14).setValues([
[currentDate, currentTime, vehicle, vin, null, null, “https://marketvalue.vinaudit.com/”, averagePrice, firstDayOfMonth, Utilities.formatDate(sundayDate, “GMT”, “MM/dd/YYYY”), “vehicle value”, “Asset”, “Active”, currentDate]
]);

Logger.log(“Data inserted successfully!”);
}