HOWTO: Use Ai2 to Delete Images (or other files) on Google Drive

[TimAI2]

Further to a couple of recent queries on the forum I have worked up a solution to delete files (this demo uses images) in a specific google drive folder.

To do this I have used a google sheet with two bound script projects, one of which is a web app.

The workflow is as follows:

1. Run a script on google sheets to gather data about the files in the folder
2. In Ai2 call the sheet with this data as a csv
3. Set Names for files to a listpicker for selection
4. Decide to delete the file if so
5. Send the required file ID to the webapp
6. The webapp will then delete the file and refresh the sheet
7. AI2 will update its file csv and the listpicker
8. AI2 captures the success output from the web app and displays it

(for the demo I have included routines to restore deleted files every night, therefore it should at least be a workable example as long as testers do not delete all the files!!)

(all files and folders are set to public, web app is set to be executed by me, but can be run by anyone, even anonymous)

GOOGLE SHEET SCRIPTS for collecting file data

function getFileIdAndNames() {

 var FileFolderID = '1wPcxm9jYoxbsbXqYOLxkGBstlSoZHmF6'; // the ID of the folder with files

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = ss.getSheets()[0];

 var urlCol = 1;

 var urlRow = 1;

 var folder = DriveApp.getFolderById(FileFolderID);

 var files = folder.getFiles();

 sheet.clear();

 while (files.hasNext()) {

 var file = files.next();

 

 

   if (file.getName().indexOf(".jpg") != -1)  {

     sheet.getRange(urlRow, urlCol).setValue(file.getName());

     sheet.getRange(urlRow, urlCol+1).setValue(file.getId());

     sheet.getRange(urlRow, urlCol+2).setValue('https://drive.google.com/uc?export=view&id='+ file.getId());

     sheet.getRange(urlRow, urlCol+3).setValue('JPG');

     sheet.getRange(urlRow, urlCol+4).setValue(Math.round(file.getSize()/1000) + " KB");

     sheet.getRange(urlRow, urlCol+5).setValue(file.getName().slice(0, -4));

   } else if (file.getName().indexOf(".png") != -1) {

     sheet.getRange(urlRow, urlCol).setValue(file.getName());

     sheet.getRange(urlRow, urlCol+1).setValue(file.getId());

     sheet.getRange(urlRow, urlCol+2).setValue('https://drive.google.com/uc?export=view&id='+ file.getId());

     sheet.getRange(urlRow, urlCol+3).setValue('PNG');

     sheet.getRange(urlRow, urlCol+4).setValue(Math.round(file.getSize()/1000) + " KB");

     sheet.getRange(urlRow, urlCol+5).setValue(file.getName().slice(0, -4));

   } 

   

 urlRow = urlRow+1;

 }

  sheet.sort(1, true);

  sheet.sort(4, true);

  sheet.insertRows(1);

  var title = [["Filename","File ID","Direct Link to View","File Type","File Size (KB)","Description"]];

  var fontStyles = [ [ "bold", "bold", "bold", "bold", "bold","bold" ]];

  var titleRange = sheet.getRange("A1:F1");

  titleRange.setValues(title);

  titleRange.setFontWeights(fontStyles);

 }

GOOGLE SHEET after collecting file information

GOOGLE WEB APP SCRIPTS for actioning the Delete (note the reuse of the google sheet code)

uncomment the three lines in the example section if you want to record a list of the deleted files, but you must add a second sheet to the spreadsheet

//for AI2 app

function doGet(e) {

  deleteImage(e)

  return ContentService.createTextOutput("Image File Deleted").setMimeType(ContentService.MimeType.TEXT);

}

//for PC browser

function doPost(e) {

  deleteImage(e)

  return ContentService.createTextOutput("Image File Deleted").setMimeType(ContentService.MimeType.TEXT);

}

function deleteImage(e) {

  

  DriveApp.getFileById(e.parameter.id).setTrashed(true);

  

  //##This section just for the example - to allow images to be replaced from trash

  //var ss = SpreadsheetApp.getActiveSpreadsheet();

  //var sheet = ss.getSheets()[1];

  //sheet.appendRow([e.parameter.id]);

  //##end of example section

  

  getFileIdAndNames();

}

function getFileIdAndNames() {

 var FileFolderID = '1wPcxm9jYoxbsbXqYOLxkGBstlSoZHmF6'; // the ID of the folder with files

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = ss.getSheets()[0];

 var urlCol = 1;

 var urlRow = 1;

 var folder = DriveApp.getFolderById(FileFolderID);

 var files = folder.getFiles();

 sheet.clear();

 while (files.hasNext()) {

 var file = files.next();

 

 

   if (file.getName().indexOf(".jpg") != -1)  {

     sheet.getRange(urlRow, urlCol).setValue(file.getName());

     sheet.getRange(urlRow, urlCol+1).setValue(file.getId());

     sheet.getRange(urlRow, urlCol+2).setValue('https://drive.google.com/uc?export=view&id='+ file.getId());

     sheet.getRange(urlRow, urlCol+3).setValue('JPG');

     sheet.getRange(urlRow, urlCol+4).setValue(Math.round(file.getSize()/1000) + " KB");

     sheet.getRange(urlRow, urlCol+5).setValue(file.getName().slice(0, -4));

   } else if (file.getName().indexOf(".png") != -1) {

     sheet.getRange(urlRow, urlCol).setValue(file.getName());

     sheet.getRange(urlRow, urlCol+1).setValue(file.getId());

     sheet.getRange(urlRow, urlCol+2).setValue('https://drive.google.com/uc?export=view&id='+ file.getId());

     sheet.getRange(urlRow, urlCol+3).setValue('PNG');

     sheet.getRange(urlRow, urlCol+4).setValue(Math.round(file.getSize()/1000) + " KB");

     sheet.getRange(urlRow, urlCol+5).setValue(file.getName().slice(0, -4));

   } 

   

 urlRow = urlRow+1;

 }

  sheet.sort(1, true);

  sheet.sort(4, true);

  sheet.insertRows(1);

  var title = [["Filename","File ID","Direct Link to View","File Type","File Size (KB)","Description"]];

  var fontStyles = [ [ "bold", "bold", "bold", "bold", "bold","bold" ]];

  var titleRange = sheet.getRange("A1:F1");

  titleRange.setValues(title);

  titleRange.setFontWeights(fontStyles);

 }

BLOCKS

GIF of App in Use

https://drive.google.com/open?id=1SeJw4GbJfrSuWKZIaeTrJU97c88ZUr8r

aia attached