I am re wording this question to be more specific:
Using the site, path, and file name how do you build a link to a document, programmatically or using an excel formula.
In previous versions you simply built a link like the example below and its would allow a user to link to that document.
Site = constant (https://site.sharepoint.com/sites/TestSite)
Path = variable (Document Library/subfolder/subfolder)
file = variable (filename.*)
Been going in circles on the forums and can't find a reference to what the character substitutions should be for sub-folders if that is the source of the problem. This is very similar to the question asked Can I link to a SharePoint document using the site name, folder, and name? how ever this is specific to the modern pages in SharePoint Online. If the problem is just in the formating of the link as specified for sub folders what is the reference for the character substitution?
For context this will be going in a cell within a spreadsheet that is already populated with a large volume of data.
Not interested in the GUI features within SharePoint like export to excel or the option to copy link by pressing a button. I would like to avoid coding if possible and building an API call.