2

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.

Improve this question
7
  • 1
    Welcome to Web Applications. If the script throw an error message add it textually, also add the logs. Commented Oct 20, 2020 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. Commented Oct 21, 2020 at 8:54
  • Try audio/mpeg instead of audio/mp3 Commented Nov 13, 2020 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. Commented Nov 14, 2020 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. Commented Nov 14, 2020 at 8:43

1 Answer 1

Reset to default
1

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.