Question 53532 in Javascript Question posted in
A very good W3school tutorial can be found here.

Send Previously Last File Created in a Google Drive Folder to a Spreadsheet (Google Sheets) VIA APPS SCRIPT – Javascript

I have a folder named "Folder A" that contains Google Docs files that are added every month.

I want to export in a Google Spreadsheet named "Exports" the previously last created file (the penultimate file created) in the "Folder A". In the Spreadsheet "Exports" I need the name of the penultimate file and the date created.

How do I do that? I want to implement this through Apps Script.

Thank you very much!

I have tried to use the code that I already use that is basically retrieving the files in the last created subfolder in a parent folder, but I am not able to comprehend how to do that for files at the moment of writing so I NEED some help.

function getMyFilesFromDrive() {
var parentid = "1zDS4MidTalTBxxxxxxxxxx"; 
var parent = DriveApp.getFolderById(parentid); 
var parentfolder= parent.getFolders();
  var array=[];
  var arrayFolders=[];
  while (parentfolder.hasNext()) {
  var folders = parentfolder.next();
    array.push(folders.getDateCreated());
    arrayFolders.push(folders.getId());
  } 
  var date=array.reduce(function (a, b, c) { 
    return a > b < c ? a : b; 
  }); 
  var index=array.indexOf(date);
  var newestFolder=DriveApp.getFolderById(arrayFolders[index]);
  var myFiles=newestFolder.getFiles();
 var sheet = SpreadsheetApp.getActive().getSheetByName("Exports"); 
 sheet.clear();
 var rows = [];
 rows.push(["ID", "Name", "Url"]);
 while(myFiles.hasNext()) {
   var file = myFiles.next();
   if(file != null) {
     rows.push([file.getId(), file.getName(), file.getUrl()]);
   }
 }
 sheet.getRange(1,1,rows.length,3).setValues(rows);
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I solved the issue in the meantime and I am gonna leave it here for the people that might have the same issue as me.

    Here is the working code for my described above issue.

    function exportPenultimateFile() {
  var folderName = "Folder A";
  var sheetName = "Exports";
  
  var folder = DriveApp.getFoldersByName(folderName).next();
  var files = folder.getFiles();
  
  var sortedFiles = [];
  while (files.hasNext()) {
    var file = files.next();
    sortedFiles.push(file);
  }
  sortedFiles.sort(function(a, b) {
    return b.getDateCreated() - a.getDateCreated();
  });
  
  if (sortedFiles.length < 2) {
    return; // Not enough files in the folder
  }
  
  var file = sortedFiles[1]; // Second to last file
  
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  sheet.clear();
  sheet.appendRow(["Name", "Date Created"]);
  sheet.appendRow([file.getName(), file.getDateCreated()]);
}

  2. 0 
    function exportPenultimateFile() {
  const folderId = "fid";
  const sheetName = "Exports";
  const folder = DriveApp.getFolderById(folderId);
  let files = folder.getFilesByType(DriveApp.getFilesByType(MimeType.GOOGLE_DOCS));
  let fA = [];
  while (files.hasNext()) {
    var file = files.next();
    fA.push([file.getName(),file.getDateCreated()])
  }
  fA.sort(function (a, b) { return b.getDateCreated().valueOf() - a.getDateCreated().valueOf(); });
  if (fA && fA.length > 1) {
    let sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    sheet.clear();
    sheet.appendRow(["Name", "Date Created"]);
    sheet.appendRow(fA[1]);
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search