Append latest Google Drive Files to Google Sheet

Question

I need a script to append the latest files and URL's (all images .jpg) from a Google Drive Folder (ImagePath)into a Google Sheet at the end of the last row. The folder contains thousands of images, approx 6,000 so the time out function kicks in before 'All' filenames are returned. So ideally I only need to know what new files have been added for the current day as this should trigger once per day.

Does anyone have a script for this purpose, it is vital to our business but I can't an example anywhere. Much Appreciated in advance.

Did the answer below answered your question? – NightEye Dec 29 '20 at 15:09 — NightEye, Dec 29 '20 at 15:09

score 0 · Answer 1 · answered Dec 24 '20 at 15:55

Sample outputs:

(1st run)

(2nd run)

It appends the names on 1st column and urls on 2nd column. Ran with time difference of 2 minutes so it duplicated. But since yours is time triggered, this shouldn't happen.

I have found this code that fetches older than 1 month files and modified it for your conditions. Also added the 2nd function to populate your google sheet.

Code:

// Fetches the files newer than 1 day
function oneDayOldFiles() {
  var arrayOfFileURLs = [];
  var arrayOfFileNames = [];
  // Get folder ID where you want to search
  var folderID = '0B55C21aJsSBlfk9FTjRqOG8tb3hjR1N4MTU1YjVPNU4weGVhSldfU3F4OXladVVNMF9Ccms';

  // 3600 seconds = 1 hour, 1000 milliseconds = 1 second, 24 hours = 1 day
  var within24hours = new Date().getTime() - (3600 * 1000 * 24);

  var cutOffDate = new Date(within24hours);
  var cutOffDateAsString = Utilities.formatDate(cutOffDate, "GMT", "yyyy-MM-dd");

  // Create an array of files by date criteria
  var files = DriveApp.getFolderById(folderID).searchFiles(
     'modifiedDate > "' + cutOffDateAsString + '"');

  while (files.hasNext()) {
    var file = files.next();
    theFileURL = file.getUrl();
    theFileName = file.getName();

    arrayOfFileURLs.push(theFileURL);
    arrayOfFileNames.push(theFileName);
  }

  return [arrayOfFileNames, arrayOfFileURLs];
};

function appendLatestFiles() {
  var [fileNames, fileURLs] = oneDayOldFiles();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var lastRow = sheet.getLastRow();

  fileNames.forEach(function (name, index){
    sheet.getRange((lastRow + 1) + index, 1).setValue(name);
    sheet.getRange((lastRow + 1) + index, 2).setValue(fileURLs[index]);
  });
}

Note that this is not limited to .jpg files only (This fetches all files that are less than 24 hours, since you posted that all files are jpgs)

I didn't include the condition for it. But you'll be able to easily add it on the code above.

Upon first glance, you can check on using getMimeType() to fetch the mime type and add an if condition that let's you push the file only if the mime type is image/jpeg

Append latest Google Drive Files to Google Sheet

1 Answers1