Delete old files

Question

I need help creating a Google Drive script that will automatically delete all old files except the 24 most recent files.

My network drive every hour sends to Google Drive packed 7z of a database file named:

DATABASENAME_01.09.2017_07.bak.7z

and in general the format:

DATABASENAME_DD.MM.RRRR_HH.bak.7z

The files on Google Drive are copied directly (I do not have any folders). I would like the files to be deleted instead of being moved to the trash. It's about releasing space.

I wanted to employ other solutions described on Stackoverflow, but they don't fulfill my requirements.

From what I've learned, the script does not work because it uses the old Doclists function. Unfortunately I do not know the programming language that is used in script.google

Answer 1

I think that the best way is by using the DriveApp api.

There are some functions that you should use:

• getFiles allows you to select all drive files
• the FileIterator you receive must be sorted on the basis of getDateCreated()
• remove the oldest file with the removeFile api OR set the oldest file as trashed with the setTrashed api. You can empty the trash with Drive.Files.emptyTrash() (this is in the Advanced Api). A we will see this method does not actually remove the file completely, and will lead to unexpected results.
• Working approach: it is necessary to pass through the advanced API Drive.Files (Please read carefully how to enable the Advanced API in your script). This allows to create also a faster query for the files that you want to delete (a query that already organize them for us)

This is more or less what I would do to solve your problem:

// Get all the files
files_iterator = DriveApp.getFilesByType("application/7z-compressed");

var file_list = [];
while (files_iterator.hasNext()) {
  var fl = files_iterator.next();
  file_list.push(fl); 
}

// Sort the files on date created
file_list = file_list.sort(function(fl1, fl2) {
  return fl1.getDateCreated() < fl2.getDateCreated();
});

// Removing uneeded file
while (file_list.length > 24) {
  var fl = file_list.pop();
  DriveApp.removeFile(fl);
}

This code is not tested and probably contains error. Use it at your own risk.

Another solution if Mime Type does not work is to list all files in your Drive and keep only the one with filename starting with DATABASENAME:

var files_iterator = DriveApp.getFiles();

var file_list = [];
while (files_iterator.hasNext()) {
  var fl = files_iterator.next();
  if (fl.getName().match("DATABASENAME"))
    file_list.push(fl); 
}

More stable version

I've written a new version that seems to work correctly:

function myFunction() {
  // Query drive for the files that has name containing "DATABASENAME" as prefix
  // the result (items) is ordered by creating date
  var files = Drive.Files.list({
    q: 'title contains "DATABASENAME"', 
    orderBy: 'createdDate'
  }).items;
  // While keeping the last 24 elements, runs a function
  // for each element of the Array
  files.slice(0, -25).forEach(function(file) {
    // Deletes the file (recognized with its ID)
    Drive.Files.remove(file.id)
  });
}

This function does not work if you don't enable advanced API. I've tested multiple times and only the last 24 elements remains. You HAVE TO WAIT that the function ends its execution before running it again, or the Arrays may become inconsistent and the second execution may delete unwanted files. Depending on the number of files in your drive account, the list function (as the getFiles function) performs queries that are counted and limited. This operations are quite expensive and running it every hour may bring you to exceed the quota. I suggest you to run this function once a day.

A nice thing about list is that actually it allows you to search by means of string of the 7z mimeType ("application/x-7z-compressed"):

Drive.Files.list({
  q: 'mimeType = "application/x-7z-compressed"'
});

Deprecated First Version with DriveApp

This is a version that I have actually tested in my AppScript and on my Drive:

function myFunction() {
  var file_iterator = DriveApp.getFiles();

  var file_list = [];
  while (file_iterator.hasNext()) {
    var fl = file_iterator.next();
    if (fl.getName().match("DATABASENAMES"))
      file_list.push(fl); 
  }

  // Sort the files on date created
  file_list = file_list.sort(function(fl1, fl2) {
    return fl1.getDateCreated() < fl2.getDateCreated();
  });

  // Removing uneeded file
  while (file_list.length > 24) {
    var fl = file_list.pop();
    // fl.setTrashed(true); // if you want it in the trash
                            // instead of fully removed.
    DriveApp.removeFile(fl);
  }
}

but the problem with this version is that actually does not remove the file from the drive. I don't understand why this function exists actually, I don't see its usage. It only removes the files from the view