1

I have hundreds of audio files (.mp3) in a Google Drive folder that I need to list with links in a Google Sheet. Very similar to the problem described here, but I was not able to adapt the code to .mp3 files. The example code that I adapted:

function ListFldPdfs() {
  // replace FOLDER-ID with your folder's ID
  // replace SHEET-NAME with the name of the sheet that will hold the list
  var myPDFfolder = DriveApp.getFolderById('FOLDER-ID'); // replace FOLDER-ID with your folder's ID
  var thesheet = 'SHEET-NAME' // give a name to YOUR list sheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newPDFsheet = ss.getSheetByName(thesheet); 
     if (!newPDFsheet) { 
       ss.insertSheet(thesheet, 0); 
       var newPDFsheet = ss.getSheetByName(thesheet) ;
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
     }
  // clear all existing content
  newPDFsheet.clear();
  // append the header row
  newPDFsheet.appendRow([ "Name", "VIEW", "HYPERLINK"]);
  var results = [];
  // list all pdf files in the folder
  var mypdfs = myPDFfolder.getFilesByType('audio/mp3');
  // loop through found files in the folder
  while (mypdfs.hasNext()) { 
    var myfile = mypdfs.next(); 
    var fname = myfile.getName();
    var furl = myfile.getUrl();
    results = [ fname, furl, ];
    //Logger.log("results = "+results); // for de-bugging
    newPDFsheet.appendRow(results); 
       } 
     var fcell = ss.getSheetByName(thesheet).getRange("C2"); 
               fcell.setFormula('=arrayformula(if(A2:A10="",,hyperlink(B2:B10,""&A2:A10&"")))'); 
}

This code effectively creates a new sheet in my Google Sheet with the columns Name, VIEW and HYPERLINK, but without listing the actual files in the original folder or providing links to see them. I appreciate any help on this.

7
  • 1
    Welcome to Web Applications. If the script throw an error message add it textually, also add the logs. – Rubén Oct 20 '20 at 15:20
  • 1
    Thanks for the welcome. There is no error message, the script just does not give me the desired output. I only get the column names (Name, VIEW, HYPERLINK) in the sheet, but I don't get the audio files names or links in the remaining rows. – Crimc Oct 21 '20 at 8:54
  • Try audio/mpeg instead of audio/mp3 – Tedinoz Nov 13 '20 at 7:31
  • Have a look at Google Drive mime-types listing? on StackOverflow. The linked answer shows the relationship between many file extensions and mine types. Other answers to this question also provide useful relevant information. – Tedinoz Nov 14 '20 at 4:42
  • I also use scripts for Google Drive based on appendRow(). Today I wondered about the ineffiency of that method. I modified a script to use temporary arrays and write output once. In a folder of @750 spreadsheets, an appendRow() script takes about 3 minutes; the equivalent array script took about 20 seconds. Yikes! This made me wonder about inefficiencies in my day-to-day coding. I think I shall kick off by looking through Taking Advantage of Google Apps Script (Tanaike's list) by @Tanaike. – Tedinoz Nov 14 '20 at 8:43
0

The following minor change to the right mime-type files solves the issue:

function ListFldPdfs() {
  // replace FOLDER-ID with your folder's ID
  // replace SHEET-NAME with the name of the sheet that will hold the list
  var myPDFfolder = DriveApp.getFolderById('FOLDER-ID'); // replace FOLDER-ID with your folder's ID
  var thesheet = 'SHEET-NAME' // give a name to YOUR list sheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newPDFsheet = ss.getSheetByName(thesheet); 
     if (!newPDFsheet) { 
       ss.insertSheet(thesheet, 0); 
       var newPDFsheet = ss.getSheetByName(thesheet) ;
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
     }
  // clear all existing content
  newPDFsheet.clear();
  // append the header row
  newPDFsheet.appendRow([ "Name", "VIEW", "HYPERLINK"]);
  var results = [];
  // list all mp3 files in the folder
  var mypdfs = myPDFfolder.getFilesByType('audio/mpeg');
  // loop through found files in the folder
  while (mypdfs.hasNext()) { 
    var myfile = mypdfs.next(); 
    var fname = myfile.getName();
    var furl = myfile.getUrl();
    results = [ fname, furl, ];
    //Logger.log("results = "+results); // for de-bugging
    newPDFsheet.appendRow(results); 
       } 
     var fcell = ss.getSheetByName(thesheet).getRange("C2"); 
               fcell.setFormula('=arrayformula(if(A2:A10="",,hyperlink(B2:B10,""&A2:A10&"")))'); 
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.