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: