title: Google Script for Sheets - Drive Document Search Loop for Entire Column
body: <p>I have used your extensive catalog of questions and answers innumerable times over the years so thanks for that.</p>
  
  <p>I have been venturing into the world of Google Scripts inside Google Sheets - I'm a solid novice at js and scripts so bear with me.</p>
  
  <hr>
  
  <p>I currently have a sheet which has a column full of file names within the connected Google Drive (e.g. - Images/ImageTEST123.jpg). I have been tinkering away with a code which takes the file name, searches the Drive and spits out the URL for the image itself - see below.</p>
  
  <pre><code>function searchq() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(2,14);
  var searchTerm = range.getValue();
  var r = sheet.getLastRow();
  
  var files = DriveApp.searchFiles("title contains '"+searchTerm.replace("'","\\'")+"'");
  
  var output = [];
  
  while (files.hasNext()) {
  var file = files.next();
  var url = file.getUrl();
  
  output.push(url);
  }
  
  sheet.getRange(2, 15, output.length, 1).setValue(url);
  }
  </code></pre>
  
  <p>I am aware this is far from tidy but it currently works, (taking the search term from [2,14] and sticking the URL into [2,15]). I would like to get this script to go through every row of column 14 and give its respective URL into column 15.</p>
  
  <p>I'm sure there's something simple I can do, but any help would be fantastic.</p>
  
  <p>If you need anything clarifying please ask!</p>
  
  <p>Cheers,</p>
  
  <hr>
  
  <hr>
  
  <p>Update: I have spent too much time on this and I feel like I'm getting close.</p>
  
  <pre><code>function searchz() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var r = sheet.getLastRow();
  var range = sheet.getRange(2, 14, r - 1);
  var searchTerm = range.getValues();
  var output = [];
  Logger.log(searchTerm)
  
  for (i = 0; i &lt; searchTerm.length; i++) {
      var files = DriveApp.searchFiles('title contains'+ searchTerm[i]);
  
      while (files.hasNext()) {
          var file = files.next();
          var url = file.getUrl();
          Logger.log(url)
          output.push(url);
          Logger.log(output)
      }
  
      sheet.getRange(2, 15, output.length).setValue(output);
  }
  }
  </code></pre>
  
  <p>The code above gives the error Invalid argument: query (line 14, file"CurrentFiddle"). Which relates to the findNext() section of code.</p>
  
  <p>Other than that, it appears that the rest of the code up to that point appears to work. I have a sneaking suspicion that it has something to do with iterating through the array of search terms within the searchFiles() section.</p>
  
  <p>Any information to bring light to this situation would be hugely appreciated.</p>