# Revision history [back]

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
dst_col.getCellByPosition(0, row_index).String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
dst_col.getCellByPosition(0, row_index).String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
# note: see comments under this post: you may want to use Value
# instead of String
dst_col.getCellByPosition(0, row_index).String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
# note: cell = dst_col.getCellByPosition(0, row_index)
# see comments under this the post: a cell has different state when a number is
# assigned compared to a string. So here I test for whether we're dealing
# with strings or numbers. But you may want to use Value
# instead remove overhead of String
this test
# if you know what you deal with right away
if cell.String.isdigit():
dst_col.getCellByPosition(0, row_index).Value \
else:
dst_col.getCellByPosition(0, row_index).String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
cell = dst_col.getCellByPosition(0, row_index)
# see comments under the post: a cell has different state when a number is
# assigned compared to a string. So here I test for whether we're dealing
# with strings or numbers. But you may want to remove overhead of this test
# if you know what you deal with right away
if cell.String.isdigit():
dst_col.getCellByPosition(0, row_index).Value cell.Value \
else:
dst_col.getCellByPosition(0, row_index).String cell.String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
cell = dst_col.getCellByPosition(0, row_index)
# see comments under the post: a cell has different state when a number is
# assigned compared to a string. So here I test for whether we're dealing
# with strings or numbers. But you may want to remove overhead of this test
# if you know what you deal with right away
if cell.String.isdigit():
cell.Value \
else:
cell.String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
src_cell = row.getCellByPosition(I_COL_MARKS, 0)
if row.getCellByPosition(I_COL_MARKS, 0).String src_cell.String == MARK:
cell dst_cell = dst_col.getCellByPosition(0, row_index)
# see comments under the post: a cell has different state when a number is
# assigned compared to a string. So here I test for whether we're dealing
# with strings or numbers. But you may want to remove overhead of this test
# if you know what you deal with right away
if cell.String.isdigit():
cell.Value src_cell.String.isdigit():
dst_cell.Value \
else:
cell.String dst_cell.String \
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot:

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
cursor = sheet.createCursor()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
for row in rectangle.Rows:
accum = f(row, accum)

def fillNewCol(row, col_write_to):
(dst_col, row_index) = col_write_to
src_cell = if row.getCellByPosition(I_COL_MARKS, 0)
if src_cell.String 0).String == MARK:
dst_cell = dst_col.getCellByPosition(0, row_index)
# see comments under the post: a cell has different state when a number is
# assigned compared to a string. So here I test for whether we're dealing
# with strings or numbers. But you may want to remove overhead of this test
# if you know what you deal with right away
if src_cell.String.isdigit():
dst_cell.Value \
= src_cell.Value
else:
dst_cell.String \
= src_cell.String
return (dst_col, row_index+1)
return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
fillNewCol,
(focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))


The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager" 
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file.
4. Run python calc:gen-new-col.py

Screenshot: