Google Apps Script: DriveApp.getFileByNames

Question

Let me preface this by stating I'm fairly new to programming. Please be gentle :)

I'm creating a GAS that will look up the value in a row 2 column 8, find the file with the filename of the value, and write the fileURL to the row in column 9. Iterate for each row in the sheet.

The script executes, but when I print the variable "value", I get a strange result: "FileIterator". That name just repeats over and over until the last row in the sheet.

See code below:

function gdriveFileLink() {
   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DEA Keynote");
   var row = 2; 
   var col = 8;
   var value = ss.getRange(row, col).getValue();
   var writeCell = ss.getRange(row, 9)
   var lastRow = ss.getLastRow();
   Logger.log(value);

   while (row <= lastRow) {
     var file = DriveApp.getFilesByName(value); **// Here is where I'm having trouble**
     Logger.log(file)
     var url = file.getUrl;          **// No URL gets entered into this variable**
     Logger.log(url);
     var writeCell = ss.getRange(row,9);
     writeCell.setValue(url);
     var row = row + 1;

      }
     }

Here is a sample of the execution log:

    11:43:26 AM Notice  Execution started
    11:43:26 AM Info    Cascade18
    11:43:26 AM Info    FileIterator
    11:43:26 AM Info    null
    11:43:26 AM Info    FileIterator
    11:43:26 AM Info    null
    11:43:26 AM Info    FileIterator
    Iteration continues to last row in sheet

Thanks for your help!

Here is a link to the sheet: https://docs.google.com/spreadsheets/d/1spALvhKCvjs1M-0JhEWySGA_uu3Gf2pD0ccTUxOeWwg/edit?usp=sharing

user30hu · Answer

Get File Links:

function gdriveFileLinks() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("DEA Keynote");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();//not sure where data starts
  const folder = DriveApp.getFolderById("folderid");
  vs.forEach((r, i) => {
    let files = folder.getFilesByName(r[7]);
    let urls = [];
    while(files.hasNext()) {
      let file = files.next();
      urls.push(file.getUrl())
    }
    sh.getRange(i + 2,9).setValue(urls.join('
'))
  });
}

Google Apps Script: DriveApp.getFileByNames

1 Answers