I had a similar issue, wanting to link all rows in column one (except the first that is the header) with a custom link. What I did was the following (json is my array for the excel file while links is an array containig the link for each row):

const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
for (let i = 1; i < json.length + 1; i++) {
    worksheet[XLSX.utils.encode_cell({
      c: 1,
      r: i
    })].l = { Target: links[i-1] };
}

That had as a result an excel file with the second column having internal custom links

To put a "Friendly Name" in the hyperlink use the string argument of write_url() method. For example, I did the following after setting the variable sheet_name, which in this case is both the name of the sheet to link to and the friendly name:

write_url(row, col, f"internal:'{sheet_name}'!A1", string=sheet_name)

The HYPERLINK function is used to make a link to another sheet link this:

=HYPERLINK("[File]SheetName!A1", "NiceName" )

Since the first part is a string, if your value for SheetName is stored in cell A1 you could use CONCATENATE to build that string like this (line breaks added inside the CONCATENATE to hopefully add some clarity)

=HYPERLINK( CONCATENATE("[",
   MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),
   "]",
   A1 ,
   "!B1" ) , "Name" )

This is quite long and painful, sorry, so someone might have a better suggestion - but I think this will work. Note that this will only work on saved files as it requires a filename to work on.

Use B1 for the cell or named ranged to link to (I guess just use A1 if you just want to open that sheet and note bothered about a specific point within it).
And "NiceName" is what appears in the cell to the user.

As way of a brief explanation, what the CONCATENATE is doing, is first extracting the filename from CELL("filename"), wrapping it in the required [], appending the sheet name (taken from cell A1), and finally appending ! and a cell name to complete the link. The result, for example, is something like the following, which should work as a target for HYPERLINK.

[FileName.xls]SheetName!A1