Google Apps Script - get URL of File in Drive with File Name

Question

I am attempting to create a form in Google Spreadsheets which will pull an image file from my Drive based on the name of the file and insert it into a cell. I've read that you can't currently do this directly through Google Scripts, so I'm using setFormula() adn the =IMAGE() function in the target cell to insert the image. However, I need the URL of the image in order to do this. I need to use the name of the file to get the URL, since the form concatenates a unique numerical ID into a string to use the standardized naming convention for these files. My issue is that, when I use getFilesByName, it returns a File Iteration, and I need a File in order to use getUrl(). Below is an snippet of my code which currently returns the error "Cannot find function getUrl in object FileIterator."

  var poNumber = entryFormSheet.getRange(2, 2);
  var proofHorizontal = drive.getFilesByName('PO ' + poNumber + ' Proof Horizontal.png').getUrl();

  packingInstructionsSheet.getRange(7, 1).setFormula('IMAGE(' + proofHorizontal + ')');

Answer 1

If you know the file name exactly, You can use DriveApp to search the file and getUrl()

function getFile(name) {
  var files = DriveApp.getFilesByName(name);
  while (files.hasNext()) { 
    var file = files.next();
    //Logs all the files with the given name
    Logger.log('Name:'+file.getName()+'\nUrl'+ file.getUrl());
  }
}

If you don't know the name exactly, You can use DriveApp.searchFiles() method.

Answer 2

You're close - once you have the FileIterator, you need to advance it to obtain a File, i.e. call FileIterator.next().

If multiple files can have the same name, the file you want may not be the first one. I recommend checking this in your script, just in case:

var searchName = "PO + .....";
var results = DriveApp.getFilesByName(searchName);
var result = "No matching files";
while (results.hasNext()) {
  var file = results.next();
  if (file.getMimeType() == MimeType. /* pick your image type here */ ) {
    result = "=IMAGE( .... " + file.getUrl() + ")");
    if (results.hasNext()) console.warn("Multiple files found for search '%s'", searchName);
    break;
  }
}
sheet.getRange( ... ).setFormula(result);

You can view the available MimeTypes in documentation