I decided to describe as detailed as I can. I will bookmark this for myself to may be link if someone in future will ask the same questions.
I used it with Protel may be from 2006. Those time it was simpler as in Protel I fully identified the part by its name. Moving in 2017 to KiCad I decided that I identify by name+footprint. Before designing my libraries according this assumption I have checked if I can do it in LibreOffice and found I can. So in my KiCad version formulas are little more complicated than they were in Protel version.
As in LibreOffice language selection is more complicated than in KiCad (for user interface, regional settings, currency settings, date formats, documents language) and may be (not sure) changing needs the net connection (to download needed dictionaries and…) while I was preparing my example at PC not connected to net I decided to left everything as is just believing that spreadsheet opened in LibreOffice (or OpenOffice) configured to other language will just show everything in that language.
I downloaded zip from those my post to have the same what you can have.
The key to realize the whole task is one formula you see at the top:
And I believe shown bigger:
I don’t know how it exactly looks opened in another language LibreOffice.
It says:
- search.vertically(concatenate.texts …
It says spreadsheet to:
- concatenate texts from $C6 and $D6 so making one texts having Value and Footprint,
- then use this text to search in tab named Elem (it is tab with all elements ‘database’) in the rectangle from $C$1 to $E$51. This have to contain all database so 51 will be much bigger number, but spreadsheet works that way that if you insert new rows at Elem tab this number in formulas are automatically modified.
- search is done in column 0 of this rectangle region (last formula parameter says this),
- when text is found than text from column 2 of rectangle region is then used as the content of this field.
$s used in formula allows it to be copied into following rows and still referring to the same rectangle (without them formula when copied from row 6 into 7 will be also shifting those numbers what we don’t want).
Now look at Elem tab:
Formula you see says: concatenate.texts so in column C I have name (from column A) concatenated with footprint (from column B). Here there are no $ in fields identyfication as when I copy this formula to next row I want it to use also values from next row and not always from this one row.
So when in search formula the rectangle is specified to be from C to E than column 0 is here column C containing concatenated name and footprint that is exactly the same as we use to search.
Now lets get back to Bom tab:
To the right from H column are the texts taken directly from KiCad BOM in csv file.
The formula I used is to take from footprint given me by KiCad only the footprint name without library name. I used the fact that all my footprint libraries are single letter name. If they are not you have to find the other way of selecting only what you need or in Elem tab use footprint names containing also library names. I am not very proficient in spreadsheet functions but believe there should be the some way to select text after ‘:’. It could need some experiments and may be even a hour or more of time.
Having in BOM needed fields on the left and copied from KiCad on the right allow to adopt spreadsheet when something from KiCad changes.
In KiCad V4/V5 I have used modified by me (with forum help) Python script generating BOMs to get it like I want it (only fields needed and footprint without library). But with KiCad V6 it was not up to date and instead of modifying it (I don’t know Python) I decided to make what is needed in spreadsheet (so this footprint with library name - it was not here in previous version).
With V8 I have first modified it assuming that I will set BOM columns in KiCad in order I want and then decided to use default column order in KiCad to just not have to set it for each BOM generation and needed selection done in spreadsheet (just in Bom tab correct columns from right copied to columns on left).
When you copy data from KiCad you have them on the right, but you have nothing on the left. You have to select one row (not whole, but only part on the left) and copy it into rows under it. You do it by dragging the small square on the bottom right of rectangle showing selected cells. When you do it everything is filled accordingly.
Column F is filled the same way as column E with numbers (from Elem tab) used then to sort BOM. This I do manually but it is few second task.
You select row set and from menu use function Data-Sort selecting first column to be sort by as F and second as C.
Now you have the BOM on the left in Bom tab.
The only what left is to copy it and paste into another spreadsheet but in ‘only text’ mode as you need texts and not formulas.
May be it looks long. But in use:
- copy BOM in KiCad (in V8 I don’t use csv file),
- paste into BOM tab,
- select one row on the left and copy it down,
- select all rows and use Data-Sort,
- copy into other spreadsheet.
Numbering rows I have done in that ‘other’ spreadsheet.
If all elements used are in Elem tab it takes less than a minute. But typically at that moment I find that I have used some new element I have defined in KiCad and not have it in Elem tab and spend time on searching at all distributors what to write here with what alternative parts.
Hope this helps and hope someone will use it 
as for me it is all I need. We use external assembly houses since 2004 and I use my spreadsheet to generate BOMs we send them.
.
