the barcode scanner keeps adding an I to the beginning when scanning a vin barcode on a vehicle? it is doing this using the default scanner and the scandit 3rd party service.
The validation rule constraining the result to be 17 characters (exact character count of a VIN on a vehicle) allows the tech to delete the I but when you are on strict time constraints and scanning 15 to 20 cars a day it is annoying.
Why do you think the barcode itself doesn’t include the “I”? Where is the barcode coming from? Maybe a mistake was made in the generation of the barcode?
because it has happened on 6 cars now. it is scanned from the sticker on the inside of the car door or other various locations on the vehicle with the vin number displayed with it and it is always an I at the beginning.
the validation rule is how we were able to catch the scan error.
A quick search comes up with the below. It appears the “I” is a valid part of some VIN barcodes. You will need to research this, its usages and understand how to handle it in your app.
is there a way to have the i removed before adding the value to the spreadsheet when synced? the api connection to NHTSA does not recognize it as a valid vin with the it and it performs the call as soon as the value is added to the spreadsheet.
Now that you have this additional specification, it is obvious the barcode and VIN are not the same thing. I would suggest introducing a separate column for the VIN and keep the current column as the VIN Barcode. I would NOT modify the barcode. It may be vital for something later.
When the barcode is scanned, use an expression in the new VIN column to extract the 17-digit VIN. Then use this new column to submit the NHTSC service.
CORRECTION:
Please see willows reply for easiest most efficient solution concerning the import digit handling when scanning a vin barcode on a vehicle. 
Why don’t follow @WillowMobileSys 's suggestion which is way simpler? Just add another column and leave the barcode one just for input purpose
After trying this out it is a much better solution. Its efficient, simple, and although ive never run into a situation in 8 years as a Nissan technician needing the 18 Bit especially since I is character that typically is unused in a vin since it looks like a 1, i imagine if i ever needed to create a slice or filter import cars from domestic it would make for a perfect identifier for automation.
i hadn’t seen his post until after i made my previous solution which worked for all purposes but was more expensive processing and sync wise and a lot more leg work to integrate.
Did you ever figure out a solution? The solution I see posted as solved here does not seem to answer or solve your issue.
yes it is indicating it as an import. you need to have a column to insert
the data the scanner extracts the a second column that formats the vin
using the LEFT() and RIGHT() functions. you should also be aware that chevy
includes over 50 word values in their vin codes describing the vehicle
equipment like engine size etc and will need to incorporate into your
formula.
This is the formula for the column that formats the data extracted from the VIN Barcode using the scanner
I used this on Save Form
SUBSTITUTE([VIN],“I”,“”)
that only covers imports. what about chevrolet who stores all the vehicle properties in the vin barcode along with the vin.
example return
3GKALXEG7PL125234,2023,TXN26,09,22,X ,BXRP33, ADT AG1 AR9 AXP AY0 CJ2 CNI C3U DD8 DWK EF7 EPM FE9 FJW F48 GNA GND GXD HFS IOU J23 J60 LSD MAH M3U NTB N57 RJA RR0 RT7 SJF SPM TC2 UHS UQA U2K V8D WMX ZP5 4AA 5SA 6BM 7BM 8X2 9X2,130H,
Created By:
jdtyler
Documentation:
Formats the VIN number when using the barcode scanner to remove additional vehicle properties stored in the barcode and cause it not conform to the 17 characters and will cause error if automating vin decoding.
Imports typically include an I in front of the VIN when scanned, GM will include all vehicle properties in the vin and can exceed 100 characters +.
This is proven and tested in production enterprise application currently deploy for my company and is used 50-60 times a day every day.
Dependencies
you must have 2 columns in your table
[VINBarcode]
is the column the raw scan results goes to and has the scanning
option enabled
[VIN]
should be where the formatting should be performed using an app
formula.
IFS( LEFT([VINBarcode],1) = "I",
RIGHT([VINBarcode], 17),
CONTAINS([VINBarcode], ","),
LEFT([VINBarcode], 17),
CONTAINS([VINBarcode], " "),
LEFT([VINBarcode], 17),
RIGHT([VINBarcode],18) = "G",
LEFT([VINBarcode],17),
NOT(
CONTAINS([VINBarcode], "I")),
[_THISROW].[VINBarcode],
NOT(
CONTAINS([VINBarcode],",")),
[_THISROW].[VINBarcode],
NOT(
CONTAINS([VINBarcode]," ")),
[_THISROW].[VINBarcode],
NOT(
CONTAINS([VINBarcode],"G")),
[_THISROW].[VINBarcode]
)
Expressions Used:
[_THIS]
{constant} (Number)
{value_1} = {value_2}
CONTAINS()
IF(condition-to-check, value-if-true, value-if-false)
IFS(condition1, value1, [condition2, value2, …])
IN()
LEFT()
NOT()
RIGHT()
VINLog!
You will need a seperate sheet for the RETURN data then referencing it by
using the OUTPUT table and adding a new row to VINLog!.
Then dereference the updated row to pull it into wherever else your use
case needs the decoded vin data
NOTE!
You can use App Script to write a function to make the api call to the
NHTSA then using the “call a script” BOT and using the [VIN] as a variable to
populate in app script when the bot is triggered it will automate decoding
the vin.
You will also need actions to take the OUTPUT data from the
decoded vin and place where needed in your sheets to accommodate
your use case.
APP SCRIPT CODE
function getVehicleInfo(vin) {
var vinResult = UrlFetchApp.fetch('https://vpic.nhtsa.dot.gov/api/vehicles/decodevinvalues/' + vin + '?format=json');
var modelyear = JSON.parse(vinResult).Results[0].ModelYear;
var make = JSON.parse(vinResult).Results[0].Make;
var model = JSON.parse(vinResult).Results[0].Model;
var abs = JSON.parse(vinResult).Results[0].ABS;
var activesafetysysnote = JSON.parse(vinResult).Results[0].ActiveSafetySysNote;
var adaptivecruisecontrol = JSON.parse(vinResult).Results[0].AdaptiveCruiseControl;
var adaptivedrivingbeam = JSON.parse(vinResult).Results[0].AdaptiveDrivingBeam;
var adaptiveheadlights = JSON.parse(vinResult).Results[0].AdaptiveHeadlights;
var additionalerrortext = JSON.parse(vinResult).Results[0].AdditionalErrorText;
var airbagloccurtain = JSON.parse(vinResult).Results[0].AirBagLocCurtain;
var airbaglocfront = JSON.parse(vinResult).Results[0].AirBagLocFront;
var airbaglocknee = JSON.parse(vinResult).Results[0].AirBagLocKnee;
var airbaglocseatcushion = JSON.parse(vinResult).Results[0].AirBagLocSeatCushion;
var airbaglocside = JSON.parse(vinResult).Results[0].AirBagLocSide;
var automaticpedestrianalertingsound = JSON.parse(vinResult).Results[0].AutomaticPedestrianAlertingSound;
var autoreversesystem = JSON.parse(vinResult).Results[0].AutoReverseSystem;
var axleconfiguration = JSON.parse(vinResult).Results[0].AxleConfiguration;
var axles = JSON.parse(vinResult).Results[0].Axles;
var batterya = JSON.parse(vinResult).Results[0].BatteryA;
var batterya_to = JSON.parse(vinResult).Results[0].BatteryA_to;
var batterycells = JSON.parse(vinResult).Results[0].BatteryCells;
var batteryinfo = JSON.parse(vinResult).Results[0].BatteryInfo;
var batterykwh = JSON.parse(vinResult).Results[0].BatteryKWh;
var batterykwh_to = JSON.parse(vinResult).Results[0].BatteryKWh_to;
var batterymodules = JSON.parse(vinResult).Results[0].BatteryModules;
var batterypacks = JSON.parse(vinResult).Results[0].BatteryPacks;
var batterytype = JSON.parse(vinResult).Results[0].BatteryType;
var batteryv = JSON.parse(vinResult).Results[0].BatteryV;
var batteryv_to = JSON.parse(vinResult).Results[0].BatteryV_to;
var bedlengthin = JSON.parse(vinResult).Results[0].BedLengthIN;
var bedtype = JSON.parse(vinResult).Results[0].BedType;
var blindspotintervention = JSON.parse(vinResult).Results[0].BlindSpotIntervention;
var blindspotmon = JSON.parse(vinResult).Results[0].BlindSpotMon;
var bodycabtype = JSON.parse(vinResult).Results[0].BodyCabType;
var bodyclass = JSON.parse(vinResult).Results[0].BodyClass;
var brakesystemdesc = JSON.parse(vinResult).Results[0].BrakeSystemDesc;
var brakesystemtype = JSON.parse(vinResult).Results[0].BrakeSystemType;
var can_aacn = JSON.parse(vinResult).Results[0].CAN_AACN;
var chargerlevel = JSON.parse(vinResult).Results[0].ChargerLevel;
var chargerpowerkw = JSON.parse(vinResult).Results[0].ChargerPowerKW;
var cib = JSON.parse(vinResult).Results[0].CIB;
var coolingtype = JSON.parse(vinResult).Results[0].CoolingType;
var curbweightlb = JSON.parse(vinResult).Results[0].CurbWeightLB;
var daytimerunninglight = JSON.parse(vinResult).Results[0].DaytimeRunningLight;
var displacementcc = JSON.parse(vinResult).Results[0].DisplacementCC;
var displacementci = JSON.parse(vinResult).Results[0].DisplacementCI;
var displacementl = JSON.parse(vinResult).Results[0].DisplacementL;
var doors = JSON.parse(vinResult).Results[0].Doors;
var driverassist = JSON.parse(vinResult).Results[0].DriverAssist;
var drivetype = JSON.parse(vinResult).Results[0].DriveType;
var dynamicbrakesupport = JSON.parse(vinResult).Results[0].DynamicBrakeSupport;
var edr = JSON.parse(vinResult).Results[0].EDR;
var electrificationlevel = JSON.parse(vinResult).Results[0].ElectrificationLevel;
var engineconfiguration = JSON.parse(vinResult).Results[0].EngineConfiguration;
var enginecycles = JSON.parse(vinResult).Results[0].EngineCycles;
var enginecylinders = JSON.parse(vinResult).Results[0].EngineCylinders;
var enginehp = JSON.parse(vinResult).Results[0].EngineHP;
var enginehp_to = JSON.parse(vinResult).Results[0].EngineHP_to;
var enginekw = JSON.parse(vinResult).Results[0].EngineKW;
var enginemanufacturer = JSON.parse(vinResult).Results[0].EngineManufacturer;
var entertainmentsystem = JSON.parse(vinResult).Results[0].EntertainmentSystem;
var errorcode = JSON.parse(vinResult).Results[0].ErrorCode;
var errortext = JSON.parse(vinResult).Results[0].ErrorText;
var esc = JSON.parse(vinResult).Results[0].ESC;
var evdriveunit = JSON.parse(vinResult).Results[0].EVDriveUnit;
var forwardcollisionwarning = JSON.parse(vinResult).Results[0].ForwardCollisionWarning;
var fuelinjectiontype = JSON.parse(vinResult).Results[0].FuelInjectionType;
var fueltypeprimary = JSON.parse(vinResult).Results[0].FuelTypePrimary;
var fueltypesecondary = JSON.parse(vinResult).Results[0].FuelTypeSecondary;
var keylessignition = JSON.parse(vinResult).Results[0].KeylessIgnition;
var lanecenteringassistance = JSON.parse(vinResult).Results[0].LaneCenteringAssistance;
var lanedeparturewarning = JSON.parse(vinResult).Results[0].LaneDepartureWarning;
var lanekeepsystem = JSON.parse(vinResult).Results[0].LaneKeepSystem;
var lowerbeamheadlamplightsource = JSON.parse(vinResult).Results[0].LowerBeamHeadlampLightSource;
var makeid = JSON.parse(vinResult).Results[0].MakeID;
var manufacturerid = JSON.parse(vinResult).Results[0].ManufacturerId;
var modelid = JSON.parse(vinResult).Results[0].ModelID;
var ncsabodytype = JSON.parse(vinResult).Results[0].NCSABodyType;
var ncsamake = JSON.parse(vinResult).Results[0].NCSAMake;
var ncsamapexcapprovedby = JSON.parse(vinResult).Results[0].NCSAMapExcApprovedBy;
var ncsamapexcapprovedon = JSON.parse(vinResult).Results[0].NCSAMapExcApprovedOn;
var ncsamappingexception = JSON.parse(vinResult).Results[0].NCSAMappingException;
var ncsamodel = JSON.parse(vinResult).Results[0].NCSAModel;
var ncsanote = JSON.parse(vinResult).Results[0].NCSANote;
var nonlanduse = JSON.parse(vinResult).Results[0].NonLandUse;
var parkassist = JSON.parse(vinResult).Results[0].ParkAssist;
var pedestrianautomaticemergencybraking = JSON.parse(vinResult).Results[0].PedestrianAutomaticEmergencyBraking;
var possiblevalues = JSON.parse(vinResult).Results[0].PossibleValues;
var pretensioner = JSON.parse(vinResult).Results[0].Pretensioner;
var rearautomaticemergencybraking = JSON.parse(vinResult).Results[0].RearAutomaticEmergencyBraking;
var rearcrosstrafficalert = JSON.parse(vinResult).Results[0].RearCrossTrafficAlert;
var rearvisibilitysystem = JSON.parse(vinResult).Results[0].RearVisibilitySystem;
var saeautomationlevel = JSON.parse(vinResult).Results[0].SAEAutomationLevel;
var saeautomationlevel_to = JSON.parse(vinResult).Results[0].SAEAutomationLevel_to;
var seatbeltsall = JSON.parse(vinResult).Results[0].SeatBeltsAll;
var seatrows = JSON.parse(vinResult).Results[0].SeatRows;
var seats = JSON.parse(vinResult).Results[0].Seats;
var semiautomaticheadlampbeamswitching = JSON.parse(vinResult).Results[0].SemiautomaticHeadlampBeamSwitching;
var series = JSON.parse(vinResult).Results[0].Series;
var series2 = JSON.parse(vinResult).Results[0].Series2;
var steeringlocation = JSON.parse(vinResult).Results[0].SteeringLocation;
var suggestedvin = JSON.parse(vinResult).Results[0].SuggestedVIN;
var topspeedmph = JSON.parse(vinResult).Results[0].TopSpeedMPH;
var tpms = JSON.parse(vinResult).Results[0].TPMS;
var trackwidth = JSON.parse(vinResult).Results[0].TrackWidth;
var tractioncontrol = JSON.parse(vinResult).Results[0].TractionControl;
var transmissionspeeds = JSON.parse(vinResult).Results[0].TransmissionSpeeds;
var transmissionstyle = JSON.parse(vinResult).Results[0].TransmissionStyle;
var trim = JSON.parse(vinResult).Results[0].Trim;
var trim2 = JSON.parse(vinResult).Results[0].Trim2;
var turbo = JSON.parse(vinResult).Results[0].Turbo;
var valvetraindesign = JSON.parse(vinResult).Results[0].ValveTrainDesign;
var vehicledescriptor = JSON.parse(vinResult).Results[0].VehicleDescriptor;
var vehicletype = JSON.parse(vinResult).Results[0].VehicleType;
var wheelbaselong = JSON.parse(vinResult).Results[0].WheelBaseLong;
var wheelbaseshort = JSON.parse(vinResult).Results[0].WheelBaseShort;
var wheelbasetype = JSON.parse(vinResult).Results[0].WheelBaseType;
var wheels = JSON.parse(vinResult).Results[0].Wheels;
var wheelsizefront = JSON.parse(vinResult).Results[0].WheelSizeFront;
var wheelsizerear = JSON.parse(vinResult).Results[0].WheelSizeRear;
var windows = JSON.parse(vinResult).Results[0].Windows;
const decode = [modelyear, make, model, makeid, modelid, manufacturerid, batteryinfo, batterytype, bedtype, bodycabtype, bodyclass, enginecylinders, displacementcc, displacementci, displacementl, doors, drivetype, driverassist, enginecycles, enginekw, entertainmentsystem, fueltypeprimary, seats, series, steeringlocation, transmissionstyle, trim, vehicletype, windows, axles, brakesystemtype, batterycells, bedlengthin, brakesystemdesc, curbweightlb, airbagloccurtain, airbaglocseatcushion, batterya, batteryv, batterykwh, wheelbasetype, seatrows, valvetraindesign, transmissionspeeds, engineconfiguration, airbaglocfront, fueltypesecondary, fuelinjectiontype, airbaglocknee, enginehp, evdriveunit, pretensioner, seatbeltsall, adaptivecruisecontrol, adaptiveheadlights, abs, cib, blindspotmon, ncsabodytype, ncsamake, ncsamodel, esc, tractioncontrol, forwardcollisionwarning, lanedeparturewarning, lanekeepsystem, rearvisibilitysystem, parkassist, airbaglocside, trim2, series2, wheelbaseshort, wheelbaselong, wheels, wheelsizefront, wheelsizerear, coolingtype, enginehp_to, electrificationlevel, chargerlevel, chargerpowerkw, batterya_to, batteryv_to, batterykwh_to, turbo, batterymodules, batterypacks, topspeedmph, suggestedvin, errorcode, possiblevalues, axleconfiguration, enginemanufacturer, additionalerrortext, trackwidth, tpms, activesafetysysnote, dynamicbrakesupport, pedestrianautomaticemergencybraking, autoreversesystem, automaticpedestrianalertingsound, can_aacn, edr, keylessignition, daytimerunninglight, lowerbeamheadlamplightsource, semiautomaticheadlampbeamswitching, adaptivedrivingbeam, saeautomationlevel, saeautomationlevel_to, rearcrosstrafficalert, ncsanote, ncsamappingexception, ncsamapexcapprovedon, ncsamapexcapprovedby, errortext, rearautomaticemergencybraking, blindspotintervention, lanecenteringassistance, nonlanduse, vehicledescriptor];
Logger.log('API Response is: ' + decode)
const Output = {
modelyear: decode[0],
make: decode[1],
model: decode[2],
makeid: decode[3],
modelid: decode[4],
manufacturerid: decode[5],
batteryinfo: decode[6],
batterytype: decode[7],
bedtype: decode[8],
bodycabtype: decode[9],
bodyclass: decode[10],
enginecylinders: decode[11],
displacementcc: decode[12],
displacementci: decode[13],
displacementl: decode[14],
doors: decode[15],
drivetype: decode[16],
driverassist: decode[17],
enginecycles: decode[18],
enginekw: decode[19],
entertainmentsystem: decode[20],
fueltypeprimary: decode[21],
seats: decode[22],
series: decode[23],
steeringlocation: decode[24],
transmissionstyle: decode[25],
trim: decode[26],
vehicletype: decode[27],
windows: decode[28],
axles: decode[29],
brakesystemtype: decode[30],
batterycells: decode[31],
bedlengthin: decode[32],
brakesystemdesc: decode[33],
curbweightlb: decode[34],
airbagloccurtain: decode[35],
airbaglocseatcushion: decode[36],
batterya: decode[37],
batteryv: decode[38],
batterykwh: decode[39],
wheelbasetype: decode[40],
seatrows: decode[41],
valvetraindesign: decode[42],
transmissionspeeds: decode[43],
engineconfiguration: decode[44],
airbaglocfront: decode[45],
fueltypesecondary: decode[46],
fuelinjectiontype: decode[47],
airbaglocknee: decode[48],
enginehp: decode[49],
evdriveunit: decode[50],
pretensioner: decode[57],
seatbeltsall: decode[52],
adaptivecruisecontrol: decode[53],
adaptiveheadlights: decode[54],
abs: decode[55],
cib: decode[56],
blindspotmon: decode[57],
ncsabodytype: decode[58],
ncsamake: decode[59],
ncsamodel: decode[60],
esc: decode[61],
tractioncontrol: decode[62],
forwardcollisionwarning: decode[63],
lanedeparturewarning: decode[64],
lanekeepsystem: decode[65],
rearvisibilitysystem: decode[66],
parkassist: decode[67],
airbaglocside: decode[68],
trim2: decode[69],
series2: decode[70],
wheelbaseshort: decode[71],
wheelbaselong: decode[72],
wheels: decode[73],
wheelsizefront: decode[74],
wheelsizerear: decode[75],
coolingtype: decode[76],
enginehp_to: decode[77],
electrificationlevel: decode[78],
chargerlevel: decode[79],
chargerpowerkw: decode[80],
batterya_to: decode[81],
batteryv_to: decode[82],
batterykwh_to: decode[83],
turbo: decode[84],
batterymodules: decode[85],
batterypacks: decode[86],
topspeedmph: decode[87],
suggestedvin: decode[88],
errorcode: decode[89],
possiblevalues: decode[90],
axleconfiguration: decode[91],
enginemanufacturer: decode[92],
additionalerrortext: decode[93],
trackwidth: decode[94],
tpms: decode[95],
activesafetysysnote: decode[96],
dynamicbrakesupport: decode[97],
pedestrianautomaticemergencybraking: decode[98],
autoreversesystem: decode[99],
automaticpedestrianalertingsound: decode[100],
can_aacn: decode[101],
edr: decode[102],
keylessignition: decode[103],
daytimerunninglight: decode[104],
lowerbeamheadlamplightsource: decode[105],
semiautomaticheadlampbeamswitching: decode[106],
adaptivedrivingbeam: decode[107],
saeautomationlevel: decode[108],
saeautomationlevel_to: decode[109],
rearcrosstrafficalert: decode[110],
ncsanote: decode[111],
ncsamappingexception: decode[112],
ncsamapexcapprovedon: decode[113],
ncsamapexcapprovedby: decode[114],
errortext: decode[115],
rearautomaticemergencybraking: decode[116],
blindspotintervention: decode[117],
lanecenteringassistance: decode[118],
nonlanduse: decode[119],
vehicledescriptor: [120]
};
Logger.log(Output)
return Output;
}​