Workaround: createdDate cannot be used with searchFiles of DriveApp in Google Apps Script

submit.md

Workaround: createdDate cannot be used with searchFiles of DriveApp in Google Apps Script

Unfortunately, in the current stage, in order to retrieve the file list using the created date, the search query of createdDate cannot be used with searchFiles method of DriveApp in Google Apps Script. This has already been reported at this issue tracker In this post, I would like to introduce a workaround for searching the files using the created date.

Issue and workaround

• The parameter of "searchFiles" method of DriveApp uses the search query for Drive API v2. When I tested createdDate > '####-##-##' for "searchFiles" and "Files: list" of Drive API v2, I confirmed errors like Invalid argument: q and Invalid query occurred, respectively.

• Fortunately, when Drive API v3 is used, createdTime can be used. createdTime of Drive API v3 is the same with createdDate of Drive API v2.

In this post, as a workaround, I would like to introduce a sample script using Drive API v3 instead of Drive API v2 ("searchFiles" of DriveApp).

Sample script

This script uses Drive API. So, please enable Drive API at Advanced Google services.

function myFunction() {
  const after = 1; // This is a sample. The file list of the files created after 1 day is retrieved.

  let fileList = [];
  const query = `createdTime > '${Utilities.formatDate(
    new Date(new Date().getTime() - 3600 * 1000 * 24 * after),
    "GMT",
    "yyyy-MM-dd"
  )}' and trashed=false`;
  let pageToken = "";
  do {
    const url = encodeURI(
      `https://www.googleapis.com/drive/v3/files?q=${query}&pageSize=1000&pageToken=${pageToken}&fields=files(id,name,createdTime),nextPageToken&orderBy=createdTime`
    );
    const res = UrlFetchApp.fetch(url, {
      headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
    });
    const obj = JSON.parse(res.getContentText());
    if (obj.files.length > 0) {
      fileList = [...fileList, ...obj.files];
    }
    pageToken = obj.nextPageToken;
  } while (pageToken);
  console.log(fileList);
}

• When this script is run, from your Google Drive, the file list of the files created after 1 day is retrieved.

References

• Files: list of Drive API v3
• This has posted at the following threads.
  • https://groups.google.com/g/google-apps-script-community/c/wujyXLzl6wY
  • https://stackoverflow.com/q/74114388