Code for maintaining vehicle values automatically

Spreadsheets
,
1

Hi all, allow me to share a Google Sheet for maintaining your vehicle values automatically:

To use this code, you need to create a manual account for each car as follows:

  • Account name - enter a nickname for your car such as “My Corollita”
  • Account number: enter “vin=” followed by the VIN of your car, e.g. vin=3GTUUDED4RG154891. This is essential because it will allow the script to detect vehicle-related accounts.
  • Institution: No Institution
  • Class: Asset
  • Type: Other
  • Date: acquisition date of your vehicle
  • Balance: the price you paid for the vehicle

Once you entered your vehicle(s), you’re good to go. All you need to do is copy the google script code to your sheet. In the sheet above select Extensions → Apps Script, then select all code and copy it to the clipboard, then go to your sheet, select once again Extensions → Apps Script and paste the code into your script page.

Refresh your sheet and you’ll see a new bar menu at the top called “Actions”. Chose Actions → Update Vehicle Values and a new line will be added to Balance History for each of your vehicles.

To automate tracking, you need to create a trigger that calls function “updateVehicleValues” every week or so.

Have fun!

3 Likes
2

Just noting that the Apps Script does make assumptions on the Balance History sheet column order and content, so people are aware.

  // Use 13 instead of sheet.getLastColumn() because last column is reserved and should not be copied
  const rowData = sheet.getRange(old_value_row, 1, 1, 13/*sheet.getLastColumn()*/).getValues();

  rowData[0][1] = Utilities.formatDate(now, tz, "yyyy-MM-dd");
  rowData[0][2] = Utilities.formatDate(now, tz, "HH:mm:ss");
  rowData[0][8] = current_value;
  rowData[0][9] = firstOfMonth;
  rowData[0][10] = sunday;
  rowData[0][14] = Utilities.formatDate(now, tz, "yyyy-MM-dd HH:mm:ss");

And in case people are curious, here is where vehicle values are coming from:

  const url = `https://marketvalues.vinaudit.com/getmarketvalue.php?key=VA_DEMO_KEY&format=json&${vin}`;
2 Likes
3

@Mark.S Thanks. I wonder what sort of guarantees for compatibility can be counted on. Should I have code for searching column titles instead of hardcoded indices?

1 Like
4

Yes, something like this:

rowData[0]["Date"]

I have some code along these lines for something unrelated to Balance History, if it helps you to leverage something, although I’m sure there are a few ways to accomplish this.

And remove the special last column code - maybe only write the required, standard columns, instead of the entire row? :thinking:

1 Like
5

@Mark.S Cool, thanks. I updated the code per your advice. Appreciated.

1 Like
6

Nice, thanks!

I did notice some other hard-coded columns:

  const sh = SpreadsheetApp.getActive().getSheetByName("Accounts");
  const values = sh.getRange("K2:K" + sh.getLastRow()).getValues();

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Balance History");
  Logger.log(`Got sheet.`);
  const textFinder = sheet.getRange("E2:E").createTextFinder(vin);

  const old_value = sheet.getRange(old_value_row, 9).getValue(); // Column I (index 9)
1 Like
7

Somehow I missed this when you first announced it. I just tried it and I’m getting a script error (I replaced my VIN with ‘xxxx’ in the output). Note that when I go to the website https://www.vinaudit.com/vin-search?vin=xxxx it does show basic info about my car:

11:34:03 AM	Notice	Execution started
11:34:04 AM	Info	[
  "vin=xxxx"
]
11:34:04 AM	Info	Starting.
11:34:04 AM	Info	Got sheet.
11:34:05 AM	Info	Got old car value for VIN vin=xxxx on row 2: 10000.
11:34:05 AM	Info	Content: {"vehicle": null, "success": false, "error": "invalid_key", "vin": "xxxx"}
11:34:05 AM	Error	
TypeError: Cannot read properties of undefined (reading 'average')
updateVehicleValue	@ Code.gs:239
updateVehicleValues	@ Code.gs:213

Note line 239 is const current_value = data.prices.average;

Line 213 is updateVehicleValue(vin);

My interpretation of the error is that the key “VA_DEMO_KEY” is no longer valid.

1 Like
8

Thanks. Indeed I looked at the logs and it seems the company disabled the facility a couple of months ago. There is still a way by scraping the web response. Not ideal, but hopefully they won’t change that too often. I’ve updated the sheet.

1 Like
9

Thanks, now it’s working, very nice!