# How to Decode a List of VINs in Google Sheets

> A copy-paste Apps Script that turns a column of VINs into year, make, model and trim, with a menu for long lists. Plus what it costs and Google's limits.

2026-10-21 · Auto.dev Team · 3 min read

## In short

Paste a short Apps Script into your sheet, save your Auto.dev API key as a script property, and type =VIN(A2) to get the year, make, model and trim. For long lists, use the Auto.dev menu instead: it paces itself, writes plain values and picks up where it stopped. Each VIN is one API call.

If you work with vehicles, you probably have a spreadsheet with a column of VINs and not much else. This script fills in the rest: a `=VIN()` formula for a few rows, and a menu for hundreds.

## What you need

- A Google Sheet with VINs in one column.
- An Auto.dev API key. Create one in your [dashboard](https://www.auto.dev/dashboard/api-keys) under API Keys.

## How to decode VINs in Google Sheets

1. In your sheet, open Extensions, then Apps Script.
2. Replace everything in `Code.gs` with the script below, and save.
3. Open Project Settings (the gear icon), scroll to Script Properties, and add a property named `AUTODEV_API_KEY` with your key as its value.
4. Back in the sheet, type `=VIN(A2)` next to your first VIN. The year, make, model and trim fill that cell and the three to its right. Drag it down for more rows.
5. For a long list, reload the sheet, select the VINs, and choose Auto.dev, then Decode selected VINs. Google asks you to authorize the script the first time.

The menu writes into the four columns to the right of your selection, so leave those empty.

## The script

```js title="Code.gs"
// Auto.dev VIN decoding for Google Sheets.
// =VIN(A2) decodes one VIN. For long lists, use the Auto.dev menu.

// The columns to fill, in order. Add any field from the VIN Decode response,
// like "engine", "drive", "body" or "origin".
const COLUMNS = ["year", "make", "model", "trim"]

function VIN(vin) {
  if (!vin) return [COLUMNS.map(() => "")]
  return [decode_(vin)]
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Auto.dev")
    .addItem("Decode selected VINs", "decodeSelection")
    .addToUi()
}

// Writes results into the columns to the right of the selected VINs, as plain
// values. Skips rows that are already filled, so you can run it again.
function decodeSelection() {
  const sheet = SpreadsheetApp.getActiveSheet()
  const range = sheet.getActiveRange()
  const vins = range.getValues()
  const firstRow = range.getRow()
  const outCol = range.getLastColumn() + 1
  const filled = sheet.getRange(firstRow, outCol, vins.length, 1).getValues()
  const start = Date.now()
  let done = 0
  for (let i = 0; i < vins.length; i++) {
    if (!vins[i][0] || filled[i][0] !== "") continue
    if (Date.now() - start > 5 * 60 * 1000) break // Google stops scripts at 6 minutes
    sheet.getRange(firstRow + i, outCol, 1, COLUMNS.length).setValues([decode_(vins[i][0])])
    done++
    Utilities.sleep(250) // stays under 5 requests a second
  }
  const left = vins.filter((v, i) => v[0] && filled[i][0] === "").length - done
  const note = left > 0 ? `Decoded ${done}. Run it again for the other ${left}.` : done ? `Decoded ${done}.` : "Nothing to decode: those rows are already filled."
  SpreadsheetApp.getActive().toast(note)
}

function decode_(vin) {
  const key = PropertiesService.getScriptProperties().getProperty("AUTODEV_API_KEY")
  if (!key) return error_("Add AUTODEV_API_KEY in Project Settings")
  const url = "https://api.auto.dev/vin/" + encodeURIComponent(String(vin).trim())
  let res
  for (let tries = 0; tries < 3; tries++) {
    res = UrlFetchApp.fetch(url, { headers: { Authorization: "Bearer " + key }, muteHttpExceptions: true })
    if (res.getResponseCode() !== 429) break
    Utilities.sleep(1000 * (tries + 1)) // rate limited: wait and try again
  }
  const code = res.getResponseCode()
  let data = {}
  try {
    data = JSON.parse(res.getContentText())
  } catch (e) {}
  if (code === 200) return COLUMNS.map((c) => value_(data, c))
  if (code === 207) return partial_(data)
  const err = data.error && (data.error.message || data.error)
  return error_("Error " + code + ": " + (err || "no details"))
}

// A field from a 200 response. The year lives under vehicle; an ambiguous VIN
// has no single trim, so show its candidates.
function value_(data, col) {
  const v = data.vehicle || {}
  if (col === "year") return v.year || data.year || ""
  if (col === "trim" && !data.trim && data.trims) return "One of: " + data.trims.join(", ")
  const x = data[col] !== undefined && data[col] !== null ? data[col] : v[col]
  return x === undefined || x === null ? "" : x
}

// A trailer, motorcycle or heavy truck gets a partial answer: what the public
// decoders named, and why there's no more, in the last column.
function partial_(data) {
  const v = data.vehicle || {}
  const row = COLUMNS.map((c) => (c === "make" ? v.make || v.manufacturer : v[c]) || "")
  row[row.length - 1] = "Partial: " + data.reason
  return row
}

function error_(message) {
  return COLUMNS.map((c, i) => (i === 0 ? message : ""))
}
```

For a 2022 Chevrolet Silverado, `3GCUDHEL3NG668790`, a row comes out as `2022 | Chevrolet | Silverado 1500 | ZR2`.

Some rows come out differently on purpose. When several trims share a VIN pattern, the decoder doesn't guess, so a 2026 Cybertruck's trim column reads "One of: Dual Motor, Premium, Base". Trailers, motorcycles and heavy trucks get a partial answer, with the reason in the last column. A mistyped VIN or a missing key shows the API's message in the first column; to retry a row, clear its cells and run the menu again.

## What it costs

Each VIN is one API call, from the formula or the menu. VIN decoding is on every plan. The Free plan stops at 1,000 calls a month and paid plans bill each call, so check [pricing](https://www.auto.dev/pricing) before a big run. Partial answers aren't billed.

A formula only runs again when you edit its VIN or the formula itself, so an open sheet doesn't keep calling the API. The menu writes plain values, which never call it again.

## Limits to know

- Google gives a formula 30 seconds and a menu run 6 minutes. The menu stops itself at 5 minutes and tells you how many are left; run it again and it skips the rows already filled.
- A personal Google account can make 20,000 URL fetches a day, and a Workspace account 100,000.
- The Free plan allows 5 requests a second, so the menu waits a quarter of a second between VINs. Drag `=VIN()` down hundreds of rows and Sheets runs them in parallel, so some may hit the limit. The script retries those, but the menu is the better tool for long lists.

## Using Excel or a CSV instead?

Excel doesn't run Apps Script, but if you save the column as a CSV, the command line does the same job:

```bash
npm install -g @auto.dev/sdk
tail -n +2 vins.csv | cut -d, -f1 | while read -r vin; do
  auto decode "$vin" --json --api-key "$AUTODEV_API_KEY"
done
```

That prints one JSON result per VIN.

## Questions

### Can I decode VINs in Google Sheets without an API key?

Yes, with NHTSA's vPIC API, which needs no key. It returns 154 fields per VIN, most of them empty, with makes in capital letters, and takes up to 50 VINs per batch request. We tested it in NHTSA's VIN decoder: what it returns and where it stops.

### Why does a cell say "Exceeded maximum execution time"?

Google gives a formula 30 seconds. A slow or rate-limited request can run past that. Use the Auto.dev menu for long lists, since it has 6 minutes per run.

### How do I add columns like engine or drivetrain?

Add the field names to COLUMNS at the top of the script, for example "engine", "drive", "body" or "origin". The VIN Decode docs list every field.

### Is my API key safe in the script?

Script properties keep the key out of the sheet's cells, but anyone who can edit the script can read it. Only share edit access with people you'd trust with the key.