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);
}

Answer 2

Try the script below. It finds the folder you want and lists the contents by file, name, and URL:

// replace your-folder below with the folder for which you want a listing
function listFolderContents() {
  var foldername = 'your-folder';
  var folderlisting = 'listing of folder ' + foldername;
  
  var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();
  
  var ss = SpreadsheetApp.create(folderlisting);
  var sheet = ss.getActiveSheet();
  sheet.appendRow( ['name', 'link'] );
  
  var file;
  var name;
  var link;
  var row;
  while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );     
  }  
};

You can change the location of the file listing to whatever sheet you want by switching

var ss = SpreadsheetApp.create(folderlisting);

to

var ss = SpreadsheetApp.openById('SHEET_ID').getSheetByName('SHEET_NAME');.

Don't forget to change SHEET_NAME and SHEET_ID to the ones that match your spreadsheet.