How to match files in Google Drive with script and populate each row in Sheets with link?

Question

I'm trying to run the following script and populate all rows which match the file name (test1.pdf) in google drive folder. There are multiple rows which has the same file name, so I need to bring the same link to each row if it matches.

I tried the following code, but it does not work if there are blank rows in between the rows. Would appreciate your help on this. Thanks in advance.

Expected Result : Match the names in Column K and populate hyperlink in Column M in respective row, if "Yes" is not mentioned in COlumn N in the row. (There are blank rows in between these rows)

function myGDriveInt() {
  
  var folderId = 'myfileid';
  var folder = DriveApp.getFolderById(folderId)
     
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheetByName('Sheet1');
  
  var RowSize=sheet.getRange('K:K').getValues().filter(String).length;
    
  var file_name=sheet.getRange('K3:K'+RowSize).getValues()
  var file_checker=sheet.getRange('N3:N'+RowSize).getValues()
  
  
  for (i = 0; i < file_name.length; i++) {
  
    if (file_checker[i][0]=="Yes"){continue;}
        
  var files = folder.getFilesByName(file_name[i][0])
   
  
  while (files.hasNext()) {
      var childFile = files.next(); 
      var file_url =  childFile.getUrl() 
      sheet.getRange(i+3,13).setValue(file_url);
  } // while

} //for
  
} // function

Sample Sheet

Welcome to Stack Overflow. Please add some sample data input (spreadsheet values for the referred columns) and the corresponding expected results. — Rubén
It's better to add the data as text rather than as image, actually, it's even better that a minimal reproducible example is created (try to reproduce the problem with the simplest spreadsheet structure and the fewer code lines as you can) — Rubén

Cooper Cooper · Accepted Answer · 2020-07-31T19:29:38

Try it this way:

function myGDriveInt() {
  var folderId = 'myfileid';
  var folder = DriveApp.getFolderById(folderId)
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getSheetByName('Sheet1');
  var file_name=sh.getRange(3,11,sh.getLastRow()-2,1).getValues();//k
  var col13=sh.getRange(3,13,sh.getLastRow()-2,1).getValues();//m
  var file_checker=sh.getRange(3,14,sh.getLastRow()-2,1).getValues()//n
  for (var i=0;i<file_name.length;i++) {
    if(file_checker[i][0]!="Yes") {
      var files=folder.getFilesByName(file_name[i][0]);
      while (files.hasNext()) {
        var childFile=files.next(); 
        var file_url=childFile.getUrl(); 
        col13[i][0]=file_url;//if you have more than one file with that name you will only get the last one
      }
    }
  }
  sh.getRange(3,13,col13.length,1).setValues(col13);//store results in m  all at one time.  Much quicker
}

How to match files in Google Drive with script and populate each row in Sheets with link?

1 Answers