Tags
Now that I have a better idea on the BoM creation workflow I use, I decided to automate the BoM creation process.
The (Google Script – which is Java Script) code is extremely crude. I see it as a working prototype. While there is much (much) room for improvement, the current effort provides a framework to the direction I am heading.
Open Source
Here is an example Google Spreadsheet – BoM Parts Example. The script I used is pasted near the bottom of this post.
An Ask
- I don’t program in Java Script. Also, I have no formal training or work experience in programming. I’d appreciate any advice on script improvements.
- I would be very grateful for a Google Script/Javascript snippet that given a part number within a Google Spreadsheet cell, can go to the distributor’s web page and get the 1, 100, 1000 piece price. Right now I record the link to the distributor’s page, which is usually a page on Digikey.com. I then record the 1, 100, 1000 piece price. I would like to improve the BoM workflow process by getting the price information from the distributor (Digikey, Mouser, etc.) at the time of BoM worksheet creation. Because I am not familiar with the code necessary to Get the price information given a part number from a given (or more generically – across) distributor, I have a learning curve that would absorb many hours. I would then evolve the snippet to work within the workflow and publish on GitHub. I would make sure to give you credit within any documentation as well as within the GitHub repository.
The BoM Workflow
Here is an image of my BoM workflow:
I use Kicad to design and layout a PCB. The EeSchema tool is used to create the schematic. EeSchema includes a BoM generation utility that creates a CSV file. I import the CSV file into a Google Spreadsheet. The Google Spreadsheet includes a script that parses through the BoM CSV and match the rows within the BoM CSV with parts in the Parts Inventory Worksheet. The script goes on to create the Completed BoM worksheet with rows that look like:
Type | Name | MFG | Part | Quantity | 1 Piece Price | 1 Piece SKU | 100 Piece Price | 100 Piece SKU | 1000 Piece Price | 1000 Piece SKU | |
CONNECTOR | LB_COIN_CELL | Link Technologies | BAT-HLD-001 | 1 | 0.28 | 0.28 | 0.233 | 0.233 | 0.22875 | 0.22875 | http://www.digikey.com/product-detail/en/BAT-HLD-001/BAT-HLD-001-ND/1577235 |
CAPACITOR | 1u | Taiyo Yuden | TMK212BJ105KG-T | 3 | 0.16 | 0.48 | 0.0523 | 0.1569 | 0.03025 | 0.09075 | http://www.digikey.com/product-detail/en/TMK212BJ105KG-T/587-1291-1-ND/931068 |
TEST POINT | TESTPOINT | N/A | N/A | 10 | 0 | 0 | 0 | 0 | 0 | 0 | |
CAPACITOR | 47n | Kemet | C0805C473K5RACTU | 1 | 0.1 | 0.1 | 0.0269 | 0.0269 | 0.0144 | 0.0144 | http://www.digikey.com/product-detail/en/C0805C473K5RACTU/399-1166-1-ND/411441 |
CAPACITOR | .1u | Kemet | C0805C104K5RACTU | 12 | 0.1 | 1.2 | 0.02 | 0.24 | 0.012 | 0.144 | http://www.digikey.com/product-detail/en/C0805C104K5RACTU/399-1170-1-ND/411445 |
LED | LED | Stanley Electric Co. | PG1101W-TR | 1 | 0.47 | 0.47 | 0.2169 | 0.2169 | 0.11832 | 0.11832 | http://www.digikey.com/product-detail/en/PG1101W-TR/404-1046-1-ND/428848 |
DIODE | DIODE | Micro Commercial Co | 1N4448W-TP | 4 | 0.14 | 0.56 | 0.0816 | 0.3264 | 0.02719 | 0.10876 | http://www.digikey.com/product-detail/en/1N4448W-TP/1N4448WTPMSCT-ND/789337 |
INDUCTOR | 15nH | Bourns Inc. | CE201210-15NJ | 2 | 0.1 | 0.2 | 0.056 | 0.112 | 0.0416 | 0.0832 | http://www.digikey.com/product-detail/en/CE201210-15NJ/CE201210-15NJCT-ND/3741724 |
INDUCTOR | 10uH | Taiyo Yuden | LBR2012T100K | 2 | 0.1 | 0.2 | 0.0665 | 0.133 | 0.0494 | 0.0988 | http://www.digikey.com/product-detail/en/LBR2012T100K/587-2045-1-ND/1788992 |
CONNECTOR | LB_CONN_2 | On Shore Technology | OSTTE020104 | 1 | 0.38 | 0.38 | 0.2548 | 0.2548 | 0.182 | 0.182 | http://www.digikey.com/product-search/en?x=19&y=18&lang=en&site=us&keywords=OSTTE020104 |
CONNECTOR | LB_HEADER_4 | N/A | N/A | 1 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | |
CONNECTOR | BNC | 4UCON – 1000 minimum | 6542 | 2 | 0.51 | 1.02 | 0.51 | 1.02 | 0.51 | 1.02 | http://www.digikey.com/product-detail/en/RR1220P-102-D/RR12P1.0KDCT-ND/432830 |
RESISTOR | 1K | Yageo | RC0805JR-071KL | 7 | 0.1 | 0.7 | 0.0073 | 0.0511 | 0.00326 | 0.02282 | http://www.digikey.com/product-detail/en/RC0805JR-071KL/311-1.0KARCT-ND/731165 |
RESISTOR | 1K .5% | Susumu | RR1220P-102-D | 1 | 0.11 | 0.11 | 0.0381 | 0.0381 | 0.01673 | 0.01673 | |
RESISTOR | 10K | Yageo | C0805JR-0710KL | 3 | 0.1 | 0.3 | 0.0073 | 0.0219 | 0.00326 | 0.00978 | http://www.digikey.com/product-detail/en/RC0805JR-0710KL/311-10KARCT-ND/731188 |
RESISTOR | 22K | Stackpole Electronics Inc | RMCF0805JT22K0 | 1 | 0.1 | 0.1 | 0.0088 | 0.0088 | 0.0032 | 0.0032 | http://www.digikey.com/product-detail/en/RMCF0805JT22K0/RMCF0805JT22K0CT-ND/1942581 |
RESISTOR | 3M | Panasonic Electronic Components | ERJ-6GEYJ305V | 1 | 0.1 | 0.1 | 0.011 | 0.011 | 0.00496 | 0.00496 | http://www.digikey.com/product-detail/en/ERJ-6GEYJ305V/P3.0MACT-ND/282477 |
RESISTOR | 6M | Yageo | RC0805FR-075M9L | 1 | 0.1 | 0.1 | 0.0099 | 0.0099 | 0.00443 | 0.00443 | http://www.digikey.com/product-detail/en/RC0805FR-075M9L/311-5.90MCRCT-ND/730958 |
OP AMP | MCP6242 | Microchip Technology | MCP6242-E/SN | 1 | 0.43 | 0.43 | 0.27 | 0.27 | 0.27 | 0.27 | http://www.digikey.com/product-detail/en/MCP6242-E%2FSN/MCP6242-E%2FSN-ND/716227 |
BLE Module | MDBT40 | Raytac | MDBT40-256V3 | 1 | 5 | 5 | 4.15 | 4.15 | 3.55 | 3.55 | |
OP AMP | MCP6244 | Microchip Technology | MCP6244-E/SL | 2 | 0.68 | 1.36 | 0.43 | 0.86 | 0.43 | 0.86 | http://www.digikey.com/product-search/en?keywords=mcp6244&stock=1 |
Total | 13.29 | 8.3407 | 7.0309 |
A next step would be to integrate ordering parts with the distributor. However, I do not do that in this prototype.
Perhaps this crude prototype is of use to you. (if it is – please consider addressing an ask above).
The Script
The Parts BoM Example Spreadsheet is view only. Here is the script:
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var BoMmenuEntries = [ {name: “Create BoM”, functionName: “createBOM”}];
ss.addMenu(“Create BoM”, BoMmenuEntries);
}
function createBOM() {
//I set the name of the imported CSV file that was created within Kicad’s Eeschema tool, the name of the parts inventory sheet, and the name of the built BOM as script properties.
//See the File/Project Properties UI…NOTE: It would be better if a UI came up and asked for these names. I’m entering the names through properties just for the prototype.
//BOMWS = Sheet in spreadsheet that is the built BoM
//KICADWS = Sheet that was imported from CSV file generated from Kicad’s EeSchema tool
//PARTSWS = Sheet containing the info on each part that is listed within the CSV file
var scriptProperties = PropertiesService.getScriptProperties();
var KICADWS = scriptProperties.getProperty(“KICADWS”);
if (!KICADWS) {
var ui = SpreadsheetApp.getUi();
ui.alert(“Can’t Complete: Did not find a sheet with the right name for the imported Kicad CSV file.”);
return;
}
var PARTWS = scriptProperties.getProperty(“PARTWS”);
if (!PARTWS) {
var ui = SpreadsheetApp.getUi();
ui.alert(“Can’t Complete: Did not find a sheet with the right name for the Parts Inventory sheet.”);
return;
}
var BOMWS = scriptProperties.getProperty(“BOMWS”);
if (!BOMWS) {
var ui = SpreadsheetApp.getUi();
ui.alert(“Can’t Complete: Need a name for the BoM sheet.”);
return -1;
}
ss = SpreadsheetApp.getActiveSpreadsheet();
var cvPCBws = ss.getSheetByName(KICADWS);
var partsws = ss.getSheetByName(PARTWS);
var BOMws = ss.getSheetByName(BOMWS);
//the sheet may not exist. In this case, create the sheet
if (!BOMws) {
BOMws = ss.insertSheet(BOMWS);
}
prepareBOMws(BOMws);
//get the name/value and quantity rows of the parts in the cvPCBnew file
//start at row = 1 and column = 1. The range goes to row = # rows in cvPCBnew worksheet column = 2.
var nPartsInDesign = cvPCBws.getLastRow();
var values = cvPCBws.getSheetValues(1,1,nPartsInDesign,2);
//get the info on parts to use from the Parts Inventory worksheet
//The Parts Inventory worksheet should have a row where the name is the same as the valueNameLookup. I’ll loop through the rows in the worksheet and find it.
//First, figure out how many rows are in the Parts Inventory worksheet.
var nParts = partsws.getLastRow();
//The PartsInventory starts with a header row. So the actual number of parts is one less than the total number of rows in the worksheet
var dataRangeOfNames = partsws.getRange(2,2,nParts -1);
var partsNames = dataRangeOfNames.getValues();
//loop through each part in the cvPCBnew worksheet and create a row in the BoM worksheet
//Using global variables to hold the total costs (see summing these up in the addRowToBOM function and then appending the row at the end of this function)
onePieceSKUcost = 0;
oneHundredPieceSKUcost = 0;
oneThousandPieceSKUcost = 0;
for (var i = 0;i<nPartsInDesign;i++){
var valueNameLookup = values[i][0];
var quantity = values [i][1];
//locate the row in the parts Inventory worksheet that matches the value name from the worksheet created through cvPCBnew
for (index in partsNames) {
var currentPartName = partsNames[index].toString();
if (currentPartName == valueNameLookup) {
//the row was located, add it to the BoM worksheet
//Note: the location of the row within the Parts Inventory worksheet adds 2, 1 for the Header, and another because the array starts at 0 but the worksheet rows start at 1.
var rowInPartsInventory = +index + 2;
//I use return to exit addRowToBOM. If addRowToBOM fails, I return a -1 so the script will stop because of the top level return
var err = addRowToBOM(rowInPartsInventory,quantity);
if (err == -1) {
return;
}
break;
}
}
}
//Figure out the total cost for units of 1, 100, 1000 and add the info to a row of the BoM worksheet
var totalRow = [‘TOTAL’,”,”,”,”,”,onePieceSKUcost,”,oneHundredPieceSKUcost,”,oneThousandPieceSKUcost];
BOMws.appendRow(totalRow);
var row = BOMws.getLastRow();
var boldRange = BOMws.getRange(row,1,1,12);
boldRange.setFontWeight(“bold”);
}
function addRowToBOM(rowInPartsInventory,quantity) {
var scriptProperties = PropertiesService.getScriptProperties();
var BOMWS = scriptProperties.getProperty(“BOMWS”);
if (!BOMWS) {
var ui = SpreadsheetApp.getUi();
ui.alert(“Can’t Complete: Need a name for the BoM sheet.”);
return -1;
}
var PARTWS = scriptProperties.getProperty(“PARTWS”);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var BOMws = ss.getSheetByName(BOMWS);
var partsws = ss.getSheetByName(PARTWS);
//create an array in which each element is one of the cells in the row of the spreadsheet.
//*******>The parts inventory worksheet has 9 columns. getSheetValues(row of interest, column of interest, number of rows, number of columns)
var partsRow = partsws.getSheetValues(rowInPartsInventory,1,1,9);
//fill in Category, Value, MFG, MFG_Part
var BOMRow = new Array();
for (var i=0;i<4;i++) {
BOMRow[i] = partsRow[0][i].toString();
}
//add quantity
BOMRow[4] = +quantity;
//add 1 piece price
BOMRow[5] = +partsRow[0][4];
//add 1 piece SKU cost
BOMRow[6] = +partsRow[0][4] * +quantity;
onePieceSKUcost = onePieceSKUcost + BOMRow[6];
//add 100 piece price
BOMRow[7] = +partsRow[0][5];
//add 100 piece SKU cost
BOMRow[8] = +partsRow[0][5]* +quantity;
oneHundredPieceSKUcost = oneHundredPieceSKUcost + BOMRow[8];
//add 1000 piece price
BOMRow[9] = +partsRow[0][6];
//add 1000 piece SKU cost
BOMRow[10] = +partsRow[0][6]* +quantity;
oneThousandPieceSKUcost = oneThousandPieceSKUcost + BOMRow[10];
//add BUY link (most likely link to Digikey page where part can be ordered)
BOMRow[11] = partsRow[0][7];
//add row to BoM worksheet
BOMws.appendRow(BOMRow);
return 0
}
function prepareBOMws(BOMws) {
BOMws.clearContents();
BOMws.clearFormats();
//******> ..currently 12 columns are defined
var headerRange = BOMws.getRange(1,1,1,12);
headerRange.setFontWeight(“bold”);
var headers = [“Type”,”Name”,”MFG”,”Part”,”Quantity”,”1 Piece Price”,”1 Piece SKU”,”100 Piece Price”,”100 Piece SKU”,”1,000 Piece Price”,”1,000 Piece SKU”,”Link”];
BOMws.appendRow(headers);
//turn off bold
var range = BOMws.getRange(2,1,1,12);
range.setFontWeight(“normal”);
}
Thanks for reading this far. Please find many things to smile about.