Fetching latest file URL in Drive and posting it to Google Spreadsheet based on a criteria

Question

I am wondering whether it's possible or not to retrieve the URL of the latest file in a Drive Folder then have it posted into a cell in Column B of a Spreadsheet.

Then there's the other part where the URL should only be posted if Column A has the word "YES". So if A has "NO" URL isn't necessary.

The full scenario would be like this.

1. Picture uploaded into Drive Folder.

2. "YES" is entered into A1.

3. URL of latest uploaded picture is fetched then pasted into B1.
4. Then it continues for all the other rows as well.

Theoretically, that's how I want it to work. But I don't know whether it could work or not.

This is the script that I use to upload picture into Drive:

    function doGet(e) {
return message("Error: no parameters in doGet");

}

function doPost(e) {
 if (!e.parameters.filename || !e.parameters.file || !e.parameters.imageformat) {
   return message("Error: Bad parameters in doPost");
 } else {
   var imgf = e.parameters.imageformat[0].toUpperCase();
   var mime =
       (imgf == 'BMP')  ? MimeType.BMP
     : (imgf == 'GIF')  ? MimeType.GIF
     : (imgf == 'JPEG') ? MimeType.JPEG
     : (imgf == 'JPG')  ? MimeType.JPEG
     : (imgf == 'PNG')  ? MimeType.PNG
     : (imgf == 'SVG')  ? MimeType.SVG
     : false;
   if (mime) {
     var data = Utilities.base64Decode(e.parameters.file, Utilities.Charset.UTF_8);
     var blob = Utilities.newBlob(data, mime, e.parameters.filename);
     DriveApp.getFolderById('Folder Id').createFile(blob);
     return message("Success");
   } else {
     return message("Error: Bad image format");
   }
 }
}

function message(msg) {
 return ContentService.createTextOutput(JSON.stringify({Result: msg })).setMimeType(ContentService.MimeType.JSON);
}

While this is the script I use to enter "YES" or "NO" into Column A:

    function doGet(e) {
    var ss = SpreadsheetApp.openByUrl("Sheet_URL");
    var sheet = ss.getSheetByName("Sheet_Name");

    addRecord(e,sheet);

    }

    function doPost(e) {
    var ss = SpreadsheetApp.openByUrl("Sheet_URL");
    var sheet = ss.getSheetByName("Sheet_Name");

    addRecord(e,sheet);

    }

    function addRecord(e,sheet) {
    var HvPict = e.parameter.HvPict;

sheet.appendRow([Hv.Pict]);

}

Answer 1

• You want to retrieve the URL of the latest uploaded file in the specific folder.
• You want to achieve this using Google Apps Script.

I could understand like above. If my understanding is correct, how about this sample script? Please think of this as just one of several possible answers.

In your case, I thought that the method of getDateCreated() in Class File might be able to be used. So how about the following sample script?

Sample script:

function getLatestFile(folderId) {
  var files = DriveApp.getFolderById(folderId).getFiles();
  var fileObj = [];
  while (files.hasNext()) {
    var file = files.next();
    fileObj.push({url: file.getUrl(), date: file.getDateCreated()});
  }
  fileObj.sort(function(a, b) {return new Date(b.date) - new Date(a.date)});
  return fileObj[0].url;
}

function myFunction() {
  var folderId = "###";
  var lastFileUrl = getLatestFile(folderId);
  Logger.log(lastFileUrl)
}

• When you run the script, please set folderId. This script retrieves the files in the folder of folderId.
• When you run the function of myFunction(), the URL of the latest created file is retrieved.

Note:

• In your case, above function might should be used at the script of the 2nd HTML form.
• If in your actual situation, the uploaded file is modified and you want to retrieve the latest modified file, please try to use getLastUpdated() instead of getDateCreated().
• In this case, fileObj.sort(function(a, b) {return new Date(b.date) - new Date(a.date)}); might not be required.

References:

• getDateCreated()
• getLastUpdated()

Added:

function getLatestFile(folderId) {
  var files = DriveApp.getFolderById(folderId).getFiles();
  var fileObj = [];
  while (files.hasNext()) {
    var file = files.next();
    fileObj.push({url: file.getUrl(), date: file.getDateCreated()});
  }
  fileObj.sort(function(a, b) {return new Date(b.date) - new Date(a.date)});
  return fileObj[0].url;
}

function doGet(e) { // or doPost(e)
  var folderId = "###";

  var ss = SpreadsheetApp.openByUrl("Sheet_URL");
  var sheet = ss.getSheetByName("Sheet_Name");
  sheet.appendRow([e.parameter.HvPict, getLatestFile(folderId)]);
}

• When you modified the script of Web Apps, please redeploy Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.