Apps Script help needed to convert filepath to hyperlink-CodePudding

I have a Google Sheet with 650 rows of data. Column A is 650 unique references (XX.23.INSP.0001 -> XX.23.INSP.0650) to external folders that is on a shared drive. Column Z contains 650 unique paths that reference relevant folder (for example: Cell A1 is "XX.23.INSP.001", Cell Z1 is "X:\Shared drives\XXXX XXX FSS County Folders\xxxx\DW\XX Drinking water name\Inspections\XX.23.INSP.0001" all the way up to Col A650 is "XX.23.INSP.0650" and Col Z650 is "X:\Shared drives\XXXX XXX FSS County Folders\zzzz\DW\ZZ Drinking water name\Inspections\XX.23.INSP.0650"). The common parent folder is X:\Shared drives\XXXX XXX FSS County Folders; multiple subfolders and large tree structure included unneeded folders for this exercise).

The company has determined that Google Sheets is the spreadsheet of use for the sharing capabilities.

I know how to write the VBA to add a hyperlink to Excel cells with the given filepath. I do not know how to write the Apps Script to loop over the worksheet cells, convert the filepath in Column Z to Google FileId and then add it as a hyperlink to the cell in column A.

Adding the hyperlink seems straightforward:

    var range = SpreadsheetApp.getActiveSheet().getRange("A" this_row);
    var richValue = SpreadsheetApp.newRichTextValue()
      .setText(data[this_row][0])
      .setLinkUrl(myhyperLinkID)
      .build();
      range.setRichTextValue(richValue);

How do I extract the FileId for each needed folder (X:\Shared drives\XXXX XXX FSS County Folders\xxxx\DW\XX Drinking water name\Inspections\XX.23.INSP.0001)?

Do I navigate to each folder and then extract the FileId? How do I do this?

I apologize for not catching the Apps Script, but I have been beating my head against a wall for a few days regarding this.

CodePudding user response：

You can create links with a plain vanilla spreadsheet function that uses hyperlink(), without resorting to scripting, like this:

=arrayformula( 
  if( 
    len(A1:A) * len(Z1:Z), 
    hyperlink("https://fileserver.mydomain.com/?file=" & Z1:Z, A1:A), 
    iferror(1/0) 
  ) 
)

Put the formula in row 1 of a free column.

The formula assumes that your file server is accessible through https://. The problem is that you seem to be after file:// links that are not supported in Google Sheets. From the documentation:

Only certain link types are allowed. http://, https://, mailto:, aim:, ftp://, gopher://, telnet://, and news:// are permitted; others are explicitly forbidden. If another protocol is specified, link_label will be displayed in the cell, but will not be hyperlinked.

I suspect that this is not a limitation of the hyperlink() function per se, but of the supported link types in general, so it may affect RichTextValueBuilder.setLinkUrl() as well.

With that in mind, try this script function:

function insertHyperlinks() {
  const baseUrl = 'file://';
  const sheet = SpreadsheetApp.getActiveSheet();
  const targetRange = sheet.getRange('B1:B');
  const values = sheet.getDataRange().getDisplayValues();
  const labels = values.map((row) => row[0]);
  const links = values.map((row) => row[25]);
  const richTextLinks = links.map((link, index) => [
    link && labels[index]
      ? SpreadsheetApp.newRichTextValue()
        .setText(labels[index])
        .setLinkUrl(baseUrl   link)
        .build()
      : SpreadsheetApp.newRichTextValue()
        .setText(labels[index])
        .build()
  ]);
  targetRange
    .offset(0, 0, richTextLinks.length, richTextLinks[0].length)
    .setRichTextValues(richTextLinks);
}