Check a specific file in Google drive folder with Google Script

Question

I'm looking for a script to achieve the following. Tried googling few codes but they don't seem to address what I require. And since I'm new to scripts, I'm kind of lost on this.

Check name listed in "Column H" in google sheet and find matching file in Google Drive (need to give a path).
If matches, display the hyperlink to the file in "Column I"
And also the script should check through all the rows in the sheet, and do the above for each row.
And stop checking if Column "Column K" has as "Complete"

Thank you in advance.

The code I was trying the modify for this is follows;

  var searchFor ='title contains "2013"';
  var names =[];
  var files = DriveApp.searchFiles(searchFor);
  while (files.hasNext()) {
    var file = files.next();
    names.push(file.getName());
  }
  var folders = DriveApp.searchFolders(searchFor);
  while (folders.hasNext()) {
    var file = folders.next();
    names.push(file.getName());
  }
  for (var i=0;i<names.length;i++){
    Logger.log(names[i]);
  }

}```

soMario soMario · Accepted Answer · 2020-07-24T18:04:25

Here is my solution. Feel free to change the size range of column H ('H2:Hx') :

  function myFunction() {
  
  var folderId = '#FolderId';
  var folder = DriveApp.getFolderById(folderId)
  var extension = '.xlsx';
  
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheetByName('Sheet1');
  
  var RowSize=sheet.getRange('H:H').getValues().filter(String).length; //calculate max value of rows
  
  var file_name=sheet.getRange('H2:H'+RowSize).getValues()
  var file_checker=sheet.getRange('K2:K'+RowSize).getValues()
  
  for (i = 0; i < file_name.length; i++) {
  
    if (file_checker[i][0]=="Complete"){break;}  
    
  var files = folder.getFilesByName(file_name[i][0]+extension)
  
  
  while (files.hasNext()) {
      var childFile = files.next(); 
      var file_url =  childFile.getUrl() 
      sheet.getRange(i+2,9).setValue(file_url);
  } // while

} //for
  
} // function

Check a specific file in Google drive folder with Google Script

1 Answers