SHARE
TWEET

Extended wa135181-PDFlist

marikamitsos Oct 11th, 2019 (edited) 19 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     // How can I easily link to a bunch of files in my Google Drive from a Google Sheet (in the same account/drive)?
  2.     // https://webapps.stackexchange.com/questions/135181/how-can-i-easily-link-to-a-bunch-of-files-in-my-google-drive-from-a-google-sheet
  3.     // Answer: https://webapps.stackexchange.com/a/135188/117311
  4.  
  5. function extendListFldFiles() {
  6.     // Replace FOLDER-ID with the folder's ID
  7.     // Replace MimeType.PDF with your MimeType
  8.     // Replace SheetWithNamesOfFiles with the name of the sheet that ALREADY holds the list of your files
  9.    
  10.   var myFilesFolder = DriveApp.getFolderById('19c0ZGBBSyRXT8LmauSzYRneDTn0nYnTQ');
  11.     // replace FOLDER-ID with the folder's ID
  12.  
  13.   var myNeededMimeType = MimeType.PDF ;  // MimeType.PNG, MimeType.GOOGLE_SHEETS, MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY, etc
  14.     // Choose and place the MimeType you want.  (MORE INFO about mime-types: https://developers.google.com/apps-script/reference/base/mime-type)
  15.  
  16.   var oldList = 'SheetWithNamesOfFiles';
  17.     // We assume we ALREADY have a sheet named SheetWithNamesOfFiles where we have the names of our files.
  18.     // We only need columns A and B where row 1 is reserved for headers, names of the files start in cell A2, column B is empty AND
  19.     // in cell B2 we place the formula:  
  20.         //    =IFERROR(ArrayFormula(VLOOKUP(OFFSET(SheetWithNamesOfFiles!A2,,,COUNTA(exlistedFiles!A2:J)),exlistedFiles!A2:J,7,0)),"Wrong Type/Name")
  21.  
  22. // **************************************
  23. //   ***** END MODIFICATIONS HERE *****
  24. // **************************************
  25.  
  26.   var thesheet = 'exlistedFiles';
  27.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  28.   var newFILESsheet = ss.getSheetByName(thesheet);
  29.      if (!newFILESsheet) {
  30.        ss.insertSheet(thesheet, 0);
  31.        var newFILESsheet = ss.getSheetByName(thesheet) ;
  32.        Logger.log("SHEET IS: " +SpreadsheetApp.getActiveSheet().getName());
  33.      }
  34.     // clear all existing content
  35.   newFILESsheet.clear();
  36.     // append the header row
  37.   newFILESsheet.appendRow(["Name", "VIEW", "Type", "File ID", "DOWNLOAD", "OPEN", "HYPER", "Date", "Size", "Description"]);
  38.   var results = [];
  39.     // list all pdf files in the folder of CHOSEN MimeType.
  40.   var myFiles = myFilesFolder.getFilesByType(myNeededMimeType);
  41.     // loop through found files in the folder
  42.   while (myFiles.hasNext()) {
  43.     var myfile = myFiles.next();
  44.     var fname = myfile.getName();
  45.     var furl = myfile.getUrl();
  46.     var ftype = myfile.getMimeType();
  47.     var fid= myfile.getId();
  48.     var fdownurl = "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + myfile.getId();
  49.     var fopenurl = "https://drive.google.com/open?id=" + myfile.getId();
  50.     var fdate = myfile.getDateCreated();
  51.     var fsize = myfile.getSize();
  52.     var fdescription = myfile.getDescription();
  53.    
  54.     Logger.log("File Name is: "+myfile.getName());
  55.         // Logger.log("URL is: "+myfile.getUrl());
  56.         // Logger.log("File Type is: "+myfile.getMimeType());
  57.     results = [
  58.       fname,
  59.       furl,
  60.       ftype,
  61.       fid,
  62.       fdownurl,
  63.       fopenurl,
  64.       "", // Column reserved for the formula further down in fcell, that will give the Hyperlink
  65.       fdate,
  66.       fsize,
  67.       fdescription
  68.     ];
  69.         // Logger.log("results = "+results); // for de-bugging
  70.     newFILESsheet.appendRow(results);
  71.        }
  72.         // Now create the Hyperlinks in the sheet exlistedFiles by placing a formula in cell G2
  73.     var fcell = ss.getSheetByName(thesheet).getRange("G2");
  74.       fcell.setFormula('=arrayformula(if(A2:A10="",,hyperlink(B2:B10,""&A2:A10&"")))');
  75.    
  76.         // Finally, hide the newly created exlistedFiles (thesheet)
  77.      var thesheet = ss.getSheetByName(thesheet);
  78.        thesheet.hideSheet();
  79.  
  80.     // We can now check our already existing sheet (SheetWithNamesOfFiles) to find the hyperlinks of our files in column B, starting in cell B2
  81.     // By ONLY changing the columns' numbers to be shown in the formula on the sheet SheetWithNamesOfFiles, more columns can be shown with more of the info. It is all here :)
  82.     // EXAMPLE to also show the fileID and size: =IFERROR(ArrayFormula(VLOOKUP(OFFSET(A2,,,COUNTA(A2:A)),exlistedFiles!A2:J,{7,4,9},0)),"OUPS")
  83. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top