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:

BoMCreationWorkflow

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.