How to convert a relative (A1) reference to absolute ($A$1)?

[adjenks]

To use addNamedExpression you must not use relative addresses or else you get "error: Relative addresses not allowed in named expressions.".

So, if I have a relative address, is there any function that I just haven't found yet to convert it to absolute?

I see functions to convert string addresses to SimpleCellAddress and vice versa, but simpleCellAddressToString() for example does not have any option like absolute:boolean to get an absolute address with the dollar signs.

Do I need to manually parse the string and build it, or does something already exist somewhere?

If I must manually convert, is there any advice on how I should do it? Regex matching alpha and numeric boundary?

Thank you for your time.

[sequba]

I'd use regexps:

const relativeAddr = 'D42';
const sheetName = hfInstance.getSheetName(sheetId);
const colPart = relativeAddr.match(/[A-Z]+/)?.[0];
const rowPart = relativeAddr.match(/[0-9]+/)?.[0];
const absoluteAddr = `${sheetName}!$${colPart}$${rowPart}`;
console.log(absoluteAddr);

[adjenks]

@sequba Thank you. I'll use something like this. I think this simple time saver would be great addition to the library in the future.

[adjenks]

Hi @sequba ! Sorry to bother you again. just want to give some feedback.

After creating what I thought was the correct address I was still getting an error from addNamedExpression() about relative references. It was very unclear to me why it still wasn't working. I then used the debugger to follow the problem and found that isAbsolute() checked to make sure a reference to the sheet was present:

hyperformula/src/parser/CellAddress.ts

Lines 136 to 138 in 469c9dd

	public isAbsolute(): boolean {
	return (this.type === CellReferenceType.CELL_REFERENCE_ABSOLUTE && this.sheet !== undefined)
	}

So although in the AST the reference type was "CELL_REFERENCE_ABSOLUTE" the function returned false.

This wasn't intuitive either since I did provide a sheetID to addNamedExpression(), but I realised it was looking for a sheet as part of the AST, which would be the sheet name as part of the address. So after changing the address from =$A$1 to ='sheet name'!$A$1 it worked.

I just want to suggest that either:

1. The errors need to be more verbose about the problem. I.e. It's not absolute because it doesn't also contain the name.
2. Or the constraints need to be loosened, so it can rely on the sheetId from the addNamedExpression() call. The reference type was CELL_REFERENCE_ABSOLUTE anyhow. So isAbsolute()'s definition seems incorrect to me, unless it was renamed to isAbsoluteWithSheet() or something similar.
3. Or the docs need to change their definition of an absolute address, because after looking at the docs, they just say that dollar signs are needed, nothing about also specifying the sheet name.

Thanks for all the hard work on the great library. I hope this feedback is useful. If you think any other contributors might find my experience useful please share it with them.