Get Latest Updated from a column of folder IDs

user_14160585 0
Viewed 42

Hello I have a column with a list of Google Drive folder links and I would like to get latest updated date of all the files contained in the folders. Example

By looking around I was able to build the following scripts which works fine by giving me the list of all the files contained in the folder and their latest update.

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'UpdateCheck', functionName: 'files'},
  ]; 
  spreadsheet.addMenu('Menu', menuItems);
}


function files() {
  var ui = SpreadsheetApp.getUi();
  var result  = ui.prompt("Paste the ID of Google Drive Folder");
  var folderId = result.getResponseText();

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  var output = []; 
  while (files.hasNext()) {
    var file = files.next();
    output.push([file.getName(), file.getLastUpdated()]);
  }
  output.sort(function(a, b) {
    return a[0] == b[0] ? 0 : a[0] < b[0] ? -1 : 1;
  });
   SpreadsheetApp.getActiveSpreadsheet().insertSheet().getRange(2, 1, output.length, output[0].length).setValues(output);

However I have to use the UI to open each link, copy and paste the ID from the URL in the input field. I would like to just read the column range, get the IDs from the strings without duplicates and run my script and have my result in a new tab. Any help would be great.

google-apps-script formula google-sheets javascript
1 Answers

I believe your current situation and your goal are as follows.

  • You have a sheet that the folder links are put to the column "D" in your providing image.

  • You want to retrieve the folder ID from the links and want to retrieve the filename and the value of getLastUpdated() of all files in the folders.

  • You want to put the retrieved data on a new sheet.

Modified script:

Please set the sheet names of srcSheetName and dstSheetName.

function myFunction() {
  var srcSheetName = "Sheet1"; // Please set the source sheet name.
  var dstSheetName = "Sheet2"; // Please set the destination sheet name.

  // Retrieve folder links from "D2:D" and check the duplication.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(srcSheetName);
  var folderIds = [...new Set(sheet.getRange("D2:D" + sheet.getLastRow()).getRichTextValues().flatMap(([d]) => {
    var url = d.getLinkUrl();
    if (url && /^https:\/\/drive\.google\.com\/drive\/folders\/.+/.test(url)) {
      return [url.split("/")[5].trim()];
    }
    return [];
  }))];
  
  // Create an array for putting to sheet.
  var res = folderIds.flatMap(id => {
    var folder = DriveApp.getFolderById(id);
    var files = folder.getFiles();
    var temp = [];
    while (files.hasNext()) {
      var file = files.next();
      temp.push([file.getName(), file.getLastUpdated()]);
    }
    return temp;
  });
  
  // Put the array to the destination sheet.
  ss.insertSheet(dstSheetName).getRange(1, 1, res.length, 2).setValues(res);
}

About some unclear points, I guessed as follows.

  • I couldn't understand the format of folder links in the cells. In this modification, I guessed that the folder link is like https://drive.google.com/drive/folders/###.

  • I couldn't understand get the IDs from the strings without duplicates. In this modification, the duplication of retrieved folder IDs is checked.

References:

Added:

From the following your replying,

Quick question, when I run it I get an Unexpected error while getting the method or property getFolderById on object DriveApp at line var res = folderIds.flatMap(id => { var folder = DriveApp.getFolderById(id); it's solvable or should I open another thread?

the folder link was indeed incorrect instead of https://drive.google.com/drive/folders/### the format was https://drive.google.com/corp/drive/u/0/folders/####?resourcekey=#### so I correct the code with /^https://drive.google.com/corp/drive/u/0/folders/.+/.test(url)) { return [url.split("/")[8].trim()]; but now I get 1CHKEThZJ0P-ISv0g0MoGjCCqdZuuJJyA?resourcekey=0-1gzYTJvJv1XDwF2Hx4HvEQ and I need to find a way to get rid of everything right before the "?"

For this, I modified my proposed script as follows.

Modified script:

function myFunction() {
  var srcSheetName = "Sheet1"; // Please set the source sheet name.
  var dstSheetName = "Sheet2"; // Please set the destination sheet name.

  // Retrieve folder links from "D2:D" and check the duplication.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(srcSheetName);
  var folderIds = [...new Set(sheet.getRange("D2:D" + sheet.getLastRow()).getRichTextValues().flatMap(([d]) => {
    var url = d.getLinkUrl();
    if (url) {
      if (/^https:\/\/drive\.google\.com\/drive\/folders\/.+/.test(url)) {
        return [url.split("/")[5].trim()];
      } else if (/^https:\/\/drive\.google\.com\/.+\/drive\/u\/0\/folders\/.+/.test(url)) {
        return [url.split("/")[8].trim().split("?")[0]];
      }
    }
    return [];
  }))];
  
  // Create an array for putting to sheet.
  var res = folderIds.flatMap(id => {
    try {
      var folder = DriveApp.getFolderById(id);
      var files = folder.getFiles();
      var temp = [];
      while (files.hasNext()) {
        var file = files.next();
        temp.push([file.getName(), file.getLastUpdated()]);
      }
      return temp;
    } catch (e) {
      console.log({id, msg: e.message});
    }
  });
  
  // Put the array to the destination sheet.
  ss.insertSheet(dstSheetName).getRange(1, 1, res.length, 2).setValues(res);
}
  • By this modification, 2 kinds of URLs like https://drive.google.com/drive/folders/### and https://drive.google.com/corp/drive/u/0/folders/####?resourcekey=#### can be used, and when an error occurs when the files are retrieved from the folder, you can see the folder ID at the log. And, the script is not stopped.
user_71086530
Related