My Google Apps Script renames all files in a folder from data in a spreadsheet. Can someone explain why it returns an exception error?
This project runs from a spreadsheet. It consists of two functions (see code below): The first function, getFilenames, clears a sheet named "Rename," sets up column headers, and populates columns A and B with the file IDs and file names from a given folder. The user then inputs the new file names into column C and runs the second function, "renameFiles" which renames the files and adds confirmation "Confirmed" to column D.
Both functions work perfectly. From the users perspective, nothing is wrong with this script. All files' data are correctly pulled, all names are correctly changed, and every row correctly received the confirmation "Confirmed."
However, the Execution log indicates otherwise. It throws this error every time I run renameFiles: "Exception: Unexpected error while getting the method or property getFileById on object DriveApp. renameFiles @ RenameFiles.gs:11"
function getFilenames (){
//UPDATE Folder ID before running
const parentFolderId = '<folder ID>';
//Clears sheet and sets up header row
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName ('Rename');
sheet.clear();
sheet.appendRow(['File ID','Old Name','New Name','Name updated?']);
//Pulls files from parent folder
const parentFolder = DriveApp.getFolderById(parentFolderId);
const queue = [{folder:parentFolder,parentName:''}];
while (queue.length >0){
const current = queue.shift();
const files = current.folder.getFiles();
while(files.hasNext()){
const file = files.next();
sheet.appendRow([file.getId(),file.getName()]);
}
}
}
function renameFiles() {
var s = SpreadsheetApp.openById("<spreadsheet ID>").getSheetByName('Rename');
// Starts at cell 2A and returns the number of rows with data and the number of columns with data
var dataRange = s.getRange(2,1,s.getLastRow(),s.getLastColumn()).getValues();
for (a = 0; a < dataRange.length; a++) {
var id = dataRange[a][0];
var newName = dataRange[a][2];
var filelocation = DriveApp.getFileById(id).setName(newName);
s.getRange(a+2,4).setValue("Confirmed");
SpreadsheetApp.flush();
}
Resources: YouTube video "List all Files in Folder ..." | Stack query: "Rename existing google drive folder names"
I tried fiddling with the parameters in the for loop (changing the initial value to 1, updating the logic in the condition to be equal-to-or-less-than, etc.), but nothing worked. Can someone help me understand why I'm getting the error?
Solution:
Change:
var dataRange = s.getRange(2,1,s.getLastRow(),s.getLastColumn()).getValues();
To:
var dataRange = s.getRange(2,1,s.getLastRow()-1,s.getLastColumn()).getValues();
Explanation:
Logging s.getRange(2,1,s.getLastRow(),s.getLastColumn()).getValues() outputs a data set with a blank array element at the end. This is because s.getLastRow() gives you the last row size starting from the first row of your sheet.
Sample:
Using the script var range = sheet.getRange(2,1, sheet.getLastRow(), sheet.getLastColumn()).getValues(); in this context logs the following:
[ [ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ 'Test 1 ', 'Test 2', 'Test 3' ],
[ '', '', '' ] ]
Since excluding the headers on your range while defining the number of rows including the header itself, it will output a blank array element at the end.
Now the reason why it outputs an error "Exception: Unexpected error while getting the method or property getFileById on object DriveApp. renameFiles @ RenameFiles.gs:11" is because at the last index of your loop, var id = dataRange[a][0]; has no value, thus the method DriveApp.getFileById(id) throws the exception error.
Reference: