How to search for a file with an exact specific date in the title using DriveApp Searchfiles paramet-CodePudding

So I have a script that is designed to search through specific folders in google drive for files with specific dates and get their respective FileId. The problem is lately this script has been confusing files that have the same numbers in the date, for instance it will confuse 11/2/2022 with 2/11/2022 and thus give me 2/11/2022 file's id. How can I ensure that the search iterator pulls the file with the exact date specified? Thanks for any and all help.

function Builder() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
let indexsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Index");
 indexsheet.getRange(3, 3, 8).clearContent();
  for(let i = 0; i<8; i  ){
let cell = indexsheet.getRange(i 3, 2).getValue();
let cellfolder = indexsheet.getRange(i 3, 4).getValue();
let final = Utilities.formatDate(cell, "GMT", "MM-dd-yy"); //get the date in the right format
let finalx = Utilities.formatDate(cell, "GMT", "MM-dd-yyyy"); //get the date in the right form
let finalxyear = Utilities.formatDate(cell, "GMT", "yyyy"); //get the year in the right format
let filesource = DriveApp.searchFiles("title contains '"  final  "' and parents in '"   cellfolder  "'");
let filesourcex = DriveApp.searchFiles("title contains '"  finalx  "' and parents in '"   cellfolder  "'"); 
  if(filesource.hasNext() === true){
while(filesource.hasNext()){
    var File = filesource.next();
    var ID = File.getId(); 
}
  File.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  indexsheet.getRange(i 3, 3).setValue(ID);
  }
  else
  {{if(filesourcex.hasNext() === true){
while(filesourcex.hasNext()){
    var File = filesourcex.next();
    var ID = File.getId(); 
}
  File.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  indexsheet.getRange(i 3, 3).setValue(ID);
  }
}
}
}
}

CodePudding user response：

Search for a given report

m is a two digit string representing month 1 - 12 d is a two digit string representing dates 1 - 31 y is a four digit string representing desired year

function findReport(m,d,y) {
  const folder = DriveApp.getFolderById("folderid");
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  const id;
  while(files.hasNext()) {
    let file = file.next();
    if(file.getTitle() == `${m}-${d}-${y} CBU Report`) {
      id = file.getId();
    }
  }
  return id;
}

CodePudding user response：

The script itself seems to work fine. The problem is the way you are searching for the title. Specifically these lines:

let filesource = DriveApp.searchFiles("title contains '"  final  "' and parents in '"   cellfolder  "'");
let filesourcex = DriveApp.searchFiles("title contains '"  finalx  "' and parents in '"   cellfolder  "'");

The search parameters are documented here, the contains keyword searches for every word in the title but not necessarily in order. Apparently it treats hyphens as spaces so "title contains '02-11-2022'" will find files that have 02, 11 and 2022 anywhere in the title.

Surrounding the date with "double quotes" for an exact search doesn't work either. I tested that under files.list and it doesn't do anything for title contains, and in the search guide documentation it seems that enclosing in quotes is only supported for fullText, not title.

One way to fix it could be to use the = operator instead of contains and use the full name of the file. So it would look like title = '02-11-2022 CBU Report'. In practice you can just change both lines to look like this:

let filesource = DriveApp.searchFiles("title = '"  final  " CBU Report' and parents in '"   cellfolder  "'");
let filesourcex = DriveApp.searchFiles("title = '"  finalx  "CBU Report' and parents in '"   cellfolder  "'");

Another way could be to use fullText contains '"02-11-2022"'. The double quotes make it so you need an exact match for the date, but since I'm guessing that in your case that date could be found in many other files, it's probably better to match the title.

Note that for anyone else using the v3 API the title keyword is changed to name, but the principle remains the same.

Search for a given report

Sources: