Google-sheets – Link audio files in Google Drive folder in a Google Sheet

google sheetsgoogle-apps-scriptgoogle-drive

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.

Best Answer

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&"")))'); 
}