Google Script for Sheets - Drive Document Search Loop for Entire Column

0
votes

I have used your extensive catalog of questions and answers innumerable times over the years so thanks for that.

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.

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.

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);
}

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.

I'm sure there's something simple I can do, but any help would be fantastic.

If you need anything clarifying please ask!

Cheers,

Update: I have spent too much time on this and I feel like I'm getting close.

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 < 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);
}
}

The code above gives the error Invalid argument: query (line 14, file"CurrentFiddle"). Which relates to the findNext() section of code.

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.

Any information to bring light to this situation would be hugely appreciated.

2
google-apps-scriptgoogle-sheets
Before anything else, shouldn't it be .setValue(output); ? Because you are just filling the url of the last document over and over again if the search returns more than one document – Akshin Jalilov
Also, let's say column 15 gives 3 url's for search based on row 2. Row 3 Col 15 will have one of those url's, therefore search for value in row3 will overwrite it. You need to decide how you want to keep your outputs. Do you want to push the url's in a row starting with Column 15? – Akshin Jalilov
@AkshinJalilov , Thanks for the reply! The items I'm searching for will return only one result (url) due to the complex names of the files. I can use "url" or "output" at the moment due to it only working for a single search. – Max
@AkshinJalilov, I am looking to get the script to get all of the search terms from within column 14, then return the relevant url into the adjacent cell in column 15. – Max
Try searchTerm[i].toString(); – Akshin Jalilov

2 Answers

0
votes

For this you could get the last row of the sheet by using this method.

sheet.getLastRow()

and then get the complete range of values for column 14 using this [getRange(row, column, numRows)][2]

So you get the range as 2D array. You can loop through each row and get the file names and add the urls.

So after creating the urls for each file, you can save them in an array and update the sheet(add url to column) only once(If the file name matches to one single file from Drive).

Hope that helps!

0
votes

Try 

 for (var i=0;i<searchTerms.length;i++) {
   var searchTerm = searchTerms[i].toString();
   var files = DriveApp.searchFiles("title contains '"+searchTerm.replace("'","\\'")+"'");

   while (files.hasNext()) {
     var file = files.next();
     var url = file.getUrl();
     Logger.log(url);
     output.push(url);
   } 
 }