Google Apps Script onEdit触发NHTSA VIN解码API并更新对应行单元格需求及代码优化求助

Let's tweak your script so it automatically triggers whenever any cell in column E gets a new VIN, and writes the decoded Year/Make/Model to the corresponding B/C/D columns in that row. Here's the breakdown of what was missing in your original code, plus the revised version:

Issues with the Original Script #

• It hardcodes row 1 (getRange(1,5)), so only E1 works
• Doesn't use the onEdit event object (e) to detect which cell was actually edited
• No checks to ensure we only run when a VIN is entered in column E (right now it runs on any edit)
• Lacks error handling for invalid VINs or API failures

Revised Working Code #

function onEdit(e) {
  // Make sure we have a valid event object (prevents errors when running manually)
  if (!e) {
    SpreadsheetApp.getUi().alert("Please run this script via an edit trigger, not manually.");
    return;
  }

  const sheet = e.source.getSheetByName('VINDECODEAPI');
  // Only proceed if the edit happened on our target sheet, in column E, and the cell isn't empty
  if (!sheet || e.range.getColumn() !== 5 || e.range.getValue() === "") {
    return;
  }

  const row = e.range.getRow();
  const vin = e.range.getValue().trim(); // Clean up any extra spaces in the VIN

  try {
    // Call NHTSA API with the VIN
    const response = UrlFetchApp.fetch(`https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/${vin}?format=json`);
    const data = JSON.parse(response.getContentText());
    
    // Extract decoded values (handle cases where data might be missing)
    const year = data.Results[0]?.ModelYear || "N/A";
    const make = data.Results[0]?.Make || "N/A";
    const model = data.Results[0]?.Model || "N/A";

    // Write values to the corresponding B, C, D columns in the same row
    sheet.getRange(row, 2).setValue(year);
    sheet.getRange(row, 3).setValue(make);
    sheet.getRange(row, 4).setValue(model);
  } catch (error) {
    // Handle API errors or invalid VINs
    sheet.getRange(row, 2).setValue("Error");
    sheet.getRange(row, 3).setValue("Failed to decode");
    sheet.getRange(row, 4).setValue(error.message);
    Logger.log(`VIN Decode Error: ${error.message}`);
  }
}

Key Improvements Explained #

1. Event Object Usage: We use e.range to get the exact cell that was edited, e.range.getColumn() to check if it's column E (5), and e.range.getRow() to target the correct row for writing results.
2. Sheet & Validation Checks: The script only runs if the edit is on your VINDECODEAPI sheet, in column E, and the cell has a value (avoids running on empty edits or wrong sheets).
3. Error Handling: The try/catch block catches API failures or invalid VINs, so your sheet won't break and you'll get a clear error message instead.
4. Dynamic Row Targeting: Instead of hardcoding row 1, we use the edited row number to write results to the same row's B/C/D columns.
5. VIN Cleanup: trim() removes extra spaces from the VIN to avoid API errors from malformed inputs.

How to Test #

1. Replace your existing script with this version in the Google Apps Script editor
2. Go back to your spreadsheet, enter a valid VIN in any cell in column E (like E2, E3, etc.)
3. The corresponding B/C/D cells should populate with decoded data (or an error message if something goes wrong)

内容的提问来源于stack exchange，提问作者FIRELAND CDJR