Automatically insert thumbnails of Google Drive images directly into the cells of a Google Sheet using the =IMAGE(url) formula (not floating image overlays)

SheetThumbs.gs

// Insert thumbnails of your Drive image files into a Google Sheet.
// Images are inserted directly into the cells of the sheet, not as those useless overlays that float around.
//
// This is set up to scan the first two columns of the active sheet. Column A will contain an =IMAGE(url) formula, which is calculated by finding the image thumbnail for the file referred to by column B.
// While this is simply the code I used for my own particular purpose, you can easily adapt it for your own needs. Please note that while an onEdit() version of this script would be cool, it will not work.
// Formulas cannot access most other Google Apps services, though you could probably use a data store if you felt ambitious enough.
// Note: the Drive Advanced API must be activated for this script to work.
//
// The script looks for files based on the pattern below:

IMAGE_FILE_PATTERN = "AL_MLD_";

// For example, if the value in column B is "17" then the script will look for the files "AL_MLD_17.JPG" and "AL_MLD_17.jpg".

ui = SpreadsheetApp.getUi(); // global variable for UI
sheet = SpreadsheetApp.getActiveSheet(); // global variable for the active sheet

function onOpen() { // triggered when document is opened or reloaded
  ui.createMenu("Images")
    .addItem("Refresh image thumbnails", "refreshThumbnails") // add a custom menu
  .addToUi();
}

function refreshThumbnails() {
  var lastRow = sheet.getLastRow(); // last row with content
  var range = sheet.getRange(1, 1, lastRow, 2); // get column A and all rows with content
  doRefreshThumbnails(range); // the function that does the work
} // end refreshThumbnails()

function doRefreshThumbnails(range) {
  var values = range.getValues(); // returns an array of arrays, each subarray being a row of columns [[column, column], [column, column], [column, column], ...]
  var newDataArray = [];
  var fileArray = getDriveFiles(); // list of all files on Drive
  values.forEach( function(cell, index) { // for each [row]
    var cellRange = sheet.getRange((index + 1), 1);
    var formula = cellRange.getFormula()
    var content = cell[0]; // column A
    var partNumber = cell[1]; // column B
    if (formula !== "") { // image urls don't count as content, so we have to avoid deleting the formulas every time we update the spreadsheet (probably don't need this anymore)
      content = formula;
    }
    var newData = [content, partNumber]; // keep the original cell contents if a file cannot be found
    var imageFile_JPG = IMAGE_FILE_PATTERN + partNumber + ".JPG"; // some people use JPG as a file extension
    var imageFile_jpg = IMAGE_FILE_PATTERN + partNumber + ".jpg"; // and some people use jpg
    var fileIter = null;
    if (fileArray.indexOf(imageFile_JPG) !== -1) { // check if it's in our array of all files to avoid making unnecessary API calls
      fileIter = DriveApp.getFilesByName(imageFile_JPG); // if so, get a file iterator object
    } else if (fileArray.indexOf(imageFile_jpg) !== -1) {
      fileIter = DriveApp.getFilesByName(imageFile_jpg);
    }
    if (fileIter !== null && fileIter.hasNext()) { // double check that this iterator actually has a value to return
      var file = fileIter.next(); // get that value, which is a file object
      var id = file.getId(); // file id, the magic number
      var link = getThumbnailLink(id); // retrieve the thumbnailLink field from the Drive SDK File object
      formula = "=IMAGE(\"" + link + "\")"; // build the formula, which is a string to be added to the cell, which the SpreadsheetApp will convert into a formula (retrievable with getFormula())
      var newData = [formula, partNumber]; // make a new sheet row with two columns
    }
    newDataArray.push(newData); // whatever the result, push the row onto the array of new data (could be made more efficient by only updating changes)
  });
  range.setValues(newDataArray); // reset the values in the sheet
} // end doRefreshThumbNails()

function getDriveFiles() {
  var files = DriveApp.getFiles(); // get a file iterator object containing a list of all the files in the Drive
  var fileArray = [];
  while (files.hasNext()) { // create an array of filenames, to limit API calls
    var filename = files.next().getName();
    fileArray.push(filename);
  }
  return fileArray;
} // end getDriveFiles()

function getThumbnailLink(fileId) { // you can "get" whatever you want from the File object if you have authorization: https://developers.google.com/drive/v2/reference/files
  var file = Drive.Files.get(fileId);
  return file.thumbnailLink; // this is the value we need to fetch
} // end getThumbnailLink()

I tried to use your useful instructions to load the google spreadsheet cells of displayable images (private and not public) in a folder. But I see that if I use the DriveApp.files.get(fileId) (driveapp is the actual class of API drive) I get the error "TypeError: Cannot call method "get" of undefined" I did all the tests, but I do not understand where I'm wrong. For clarity I add the script and the log. can you help me? Thank you
Vanni
`
function FilesInFolder() {

var idFolder="0BzCBg2hs_KPXdnkxbWhIVldBY2M" ;
var folder = DriveApp.getFolderById(idFolder);

var contents = folder.getFiles();
Logger.log("folder = " + folder);
Logger.log("contents = " + contents);
var cnt = 0;
//var file;

while (contents.hasNext()) {
    var file = contents.next();
  Logger.log("file = " + file);

    var fileId = file.getId();
  Logger.log("fileId = " + fileId)

    var thumb= getThumbnail(fileId);
  Logger.log("thumb = " + thumb);

} //end while
} //end FilesInFolder

function getThumbnail(fileId) {
var fileProp = DriveApp.files.get(fileId);
Logger.log("fileProp.thumbnailLink = " + fileProp.thumbnailLink);
return fileProp.thumbnailLink;
} // end getThumbnail `

LOGS
[16-04-17 21:11:23:249 CEST] folder = FOTO
[16-04-17 21:11:23:249 CEST] contents = FileIterator
[16-04-17 21:11:23:356 CEST] file = M000001494202-1.jpg
[16-04-17 21:11:23:357 CEST] fileId = 0BzCBg2hs_KPXT0pxSlhPazBXeTQ

Thanks for this link. With your help I was able to put Filename, FileType, fileURL and a thumbnail into my Spreadsheet. Here's the Google Apps Script code I used. I doubt that I would have ever found the thumbnailLink on my own.

function myFiles() 
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('MyImages');
  var files = DriveApp.getFiles();
  var s='';
  var cnt=1;
  while(files.hasNext()) 
  {
    var fi=files.next();
    var type=fi.getMimeType();
    
    if(type==MimeType.GIF || type==MimeType.JPEG || type==MimeType.PNG)
    {
      sh.appendRow([cnt++,fi.getName(),type,fi.getUrl(),'=IMAGE("' + getThumbNailLink(fi.getId()) + '",1)']);
    }
  } 
}

function getThumbNailLink(fileId)
{
  var file=Drive.Files.get(fileId);
  return file.thumbnailLink;
}

I've attached a copy of my SpreadSheet

@JerryGarcia Could you share that google sheet file sample?

Is it possible to get a copy of the Spreadsheet. thanks.