Google script to retrieve Google Drive documents hyperlink to be set in a Google Sheet array

Question

I have a Google Sheet with Google Drive documents names in each cells. So each cells contain a Google Drive documents name. Together, the various cells form an array.

I'm trying to retrieve hyperlinks of these Google Drive documents based on the name in each Google Sheet cell, and set these hyperlink in each cell (by replacing documents name in each cell). To be noted that the Google Drive documents are not in the same Google Drive folders.

I managed to modify an existing code so that it works in my context; however, it only works for 1-column array. When I try to extend the range to several columns, all hyperlinks return "undefine" url (even for the first column that was previously working).

Google array example

See below my code. Any help would be much appreciated. Thanks a lot.

function getFile() {

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet1 = ss.getSheetByName('Sheet1');

var lastRow = sheet1.getLastRow();

var range = sheet1.getRange(2,1,lastRow - 1,4);
var names = range.getValues();

var nameUrl ={};

for (let i in names) {

var files = DriveApp.getFilesByName(names[i]);

while (files.hasNext()) {
 var file = files.next();
 var fullName = file.getName();
 var url = file.getUrl();
 nameUrl[fullName] = url;
 }
 }

var links = names.map(function(e) {
  return [
    '=HYPERLINK("' + nameUrl[e] + '","' + e + '")'
      ];
});
range.setValues(links);
}

Answer 1

Explanation:

• You are very close but your code is working for 2D arrays of one column and you need to redesign your code to work with 2D arrays with multiple columns as well.

I use a double map approach to iterate for every cell and by doing that you don't need to create the object nameUrl which also slows your code.

Solution:

function getFile() {   
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('Sheet1');
  const lastRow = sheet1.getLastRow();
  const range = sheet1.getRange(2,1,lastRow - 1,4);
  const names = range.getValues();
  const data = names.map(r=>r.map(c=>{
    let files = DriveApp.getFilesByName(c);
    while (files.hasNext()) {
      var file = files.next();
      var url = file.getUrl();
      return c!=''?'=HYPERLINK("' + url + '","' + c + '")':'';
    }}));
  range.setValues(data);
}