APPS SCRIPT - Get URL and File Name from array of folders ID

mau 0
Viewed 32

I would like to get the files URL and Name from an array of folders ID, which I'm retrieing with a script.

enter image description here

I would like the result to be placed on the Files tab.

The script I'm using for getting the filesID is the following:

function Folders_URL() {
var ss=SpreadsheetApp.getActive().getSheetByName("Overview");

var fldr=DriveApp.getFolderById("someID");

var files=fldr.getFolders();
var names=[],f,str; while (files.hasNext()) {
f=files.next();
names.push([f.getId(), f.getName()]);
}
ss.getRange(4,2,names.length, 2).setValues(names);
}

For getting files Names with the URL, I use this code. It would be very helpful if someone can join both codes or just getting the URLs and File names from the array of foldersID

function getFileNames() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("someLink");
 
  //Range where the links are set
  var links = sheet.getRange("F3:F").getValues();

  var filenames = []; 
  for (var i = 0; i < links.length; i++) {
    var url = links[i][0];
    if (url != "") {
      var filename = SpreadsheetApp.openByUrl(links[i][0]).getName();
      filenames.push([filename]);
    }
  }

  //Define cell range
  var startRow = 3; // print in row 2 since row 1 is the header row
  var fileNameColumn = 4; // Column B = column 2
  var destinationCell = sheet.getRange(startRow, fileNameColumn, filenames.length, filenames[0].length);
  destinationCell.setValues(filenames);
}

Please be aware that I cannot share any sheet since I have sensitive information from my end. Thanks!

google-sheets google-apps-script
1 Answers

Get Urls

function getUrls() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Overview");
  const osh = ss.getSheetByName("Files");
  const vs = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
  let obj = {pA:[]};
  vs.forEach((r,i) => {
    let fldr = DriveApp.getFolderById(r[0]);
    let files = fldr.getFilesByName(r[1]);
    while(files.hasNext()) {
      let file = files.next();
      let name = file.getName();;
      if(!obj.hasOwnProperty(name)) {
        obj[name] = [file.getUrl()];
        obj.pA.push(name)
      } else {
        obj[name].push(file.getUrl())
      }
    }
  });
  let o = obj.pA.map(p => [p,obj[p].join('\n')]);
  osh.clearContents();
  osh.getRange(1,1,o.length,o[0].length).setValues(o);
}
user30hu0
Related