[SOLVED] MACROS: LibreOffice puts out wrong row number when using relative reference in uno's cell.Validation.setFormula1()

LibreOffice 7.1.3.2 (x64) Calc, using python 3.8.

For the sake of this example I’ll be using the cell C3.

Get a cell’s absolute address. Outputs: "$'Sheet 1'.$C$3"

cell = sheet.getCellByPosition(2, 2)

cell_absolute_address = cell.AbsoluteName

Remove the $ sign before the row reference

Use the method you prefer, I am using regex for removing the $ sign before the number 3:

pre = re.sub('\$\d*$', '', cell.AbsoluteName)

post = re.sub('^\$[A-Za-z0-9.\s\'\$]+[A-Z]', '', cell.AbsoluteName).replace('$', '')

relative_address = f'{pre}{post}'

(Probably not the best way to convert it to relative but that’s not the problem)

relative_address = '$"Sheet 1".$C3'

Edit: As erAck pointed out the name of the sheet should be within single quotation marks as following:
'$\'Sheet1\'.$C3'

But that was not the origin of the issue. You can check the solution to this problem down below

Insert it as a formula via setFormula() method inside cell.Validation

validation = cell.Validation

validation.Type = "LIST"

c = f'INDIRECT(OFFSET({relative_address}; 0; -1))' # REFERENCES THE CELL TO THE LEFT

validation.setFormula1(c)

cell.Validation = validation

Now run the macro and check it’s verification formula and you’ll get this:

It says:

INDIRECT(OFFSET($'Sheet 1'.$C5; 0; -1))

Where it should be:

INDIRECT(OFFSET($'Sheet 1'.$C3; 0; -1))

Thank you in advance.

Could you please attach a Calc document to the start message.

you don’t need one, just open up a new one and you are set to run the macro. Im using a blank one

You probably need to add

validation.setSourcePosition(cell.getCellAddress)

Ok, that was the solution. Thank you so much sokol92

In your example at least relative_address = '$"Sheet 1".$C3' is wrong. Note " vs ' usage to quote the sheet name. In Python it should be

relative_address = '$\'Sheet 1\'.$C3'

The line below solved the problem. Thanks to sokol92

validation.setSourcePosition(cell.getCellAddress())