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!

1 Like
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?

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:

5

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

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)