Replace text in Google Sheet hyperlink using Script

Question

I'm not much of a coder, but I've used some bits I've found on this site and others to put together a small Google Script for organizing files into a Google sheet. Basically it pulls the file names and links from a specified Drive folder, and prints these along with the respective hyperlinks into a Google Sheet, and the script works perfectly for that. I want to remove part of the file name from the hyperlink though and can't quite figure it out. Here's what the script pulls so far, and what I want the edited hyperlink to show:

File ID	File Link	Hyperlink	Edited Hyperlink
Adam Smith - Document 2023-1	https://drive.google.com/file/d/1mh82jf89j23/view?uspdrivesdk	=hyperlink("https://drive.google.com/file/d/1mh82jf89j23/view?uspdrivesdk", "Adam Smith - Document 2023-1")	=hyperlink("https://drive.google.com/file/d/1mh82jf89j23/view?uspdrivesdk", "2023-1")
John Maynard - Document 2023-1	https://drive.google.com/file/d/ijeficoq38vc8/view?uspdrivesdk	=hyperlink("https://drive.google.com/file/d/ijeficoq38vc8/view?uspdrivesdk", "John Maynard - Document 2023-1")	=hyperlink("https://drive.google.com/file/d/ijeficoq38vc8/view?uspdrivesdk", "2023-1")

I know how to use regexreplace and substitute as a Google Sheets formula, but it doesn't work on hyperlinks, and I don't know how to incorporate either into the script, which is below:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var c1 = s.getRange("C1")
  var c2 = s.getRange("B1");
  var c3 = s.getRange("A1")
  var fldr = DriveApp.getFolderById("1Kt43CdFyWBFrx1Wo4eJMLf0UB0RTPYiS");
  var files = fldr.getFiles();
  var urls = [], names = [], ids = [], f, hyp, str;
  while (files.hasNext()) {
    f = files.next();
    hyp = f.getUrl();
    str = '=hyperlink("' + f.getUrl() + '", "'+ f.getName() + '")';
    urls.push([str]);
    names.push([f.getUrl()]);
    ids.push([f.getName().replace(/\.[^/.]+$/, "")]); //Remove filename extensions
    //ids.push([f.getName()]); //Filename with extensions
  }

  s.getRange(c1.getRow(), c1.getColumn(), urls.length).setFormulas(urls);
  s.getRange(c2.getRow(), c2.getColumn(), names.length).setValues(names);
  s.getRange(c3.getRow(), c3.getColumn(), ids.length).setValues(ids);


}

Would appreciate any help with this - thanks!

Answer 1

From your reply, how about modifying your script as follows?

Modification points:

• In order to retrieve your expected value, I used split.
• In your script, setFormulas and setValues are used. I thought that in your situation, only setValues can be used.

When these points are reflected in your script, how about the following modification?

Modified script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var fldr = DriveApp.getFolderById("1Kt43CdFyWBFrx1Wo4eJMLf0UB0RTPYiS");
  var files = fldr.getFiles();
  var values = [];
  while (files.hasNext()) {
    f = files.next();
    var hyp = f.getUrl();
    var name = f.getName().replace(/\.[^/.]+$/, "");
    var str = '=hyperlink("' + hyp + '", "' + name.split(" ").pop() + '")';
    values.push([name, hyp, str]);
  }
  s.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When this script is run, the value of 2023-1 is retrieved from Adam Smith - Document 2023-1.