Need help debugging Google Apps Script code intended to update hundreds of files in Google Drive

Question

The company I work for just updated its branding, affecting hundreds of Excel and PowerPoint files saved to a Shared Google Drive. There is a team working to update the files. My challenge is finding an efficient way to add the new files and delete the old files in Google Drive.

I created a Google Sheet to capture the current File Id, the new File Id, and the Folder Id, along with the following Google Apps Script code. The code throws the following error.

Exception: Unexpected error while getting the method or property getFileById on object DriveApp.
BulkUpdate @ Bulk Update.gs:25

Hoping someone can help me debug? I'm a novice and I've hit a wall.

function BulkUpdate() {
  
  // This constant is added to updated files
  var success = "Success"

  // Define range 
  var sheet = SpreadsheetApp.getActiveSheet();
  var firstRow = 2; // First row of data to process
  var lastRow = sheet.getLastRow(); // Last row of data to process

  // Fetch range
  var dataRange = sheet.getRange(firstRow, 1, lastRow, 4);

  // Fetch values for each row in range
  var data = dataRange.getValues();
  for (var i = 0; data.length; ++i) {
    var row = data[i];
    var folderId = row[0]; // First column
    var currentFileId = row[1]; // Second column
    var newFileId = row[2]; // Third column
    var confirmation = row[3]; // Fourth column

    // Copy new file to folder and trash current file
    if (confirmation !== success) {
      var currentFile = DriveApp.getFileById(currentFileId);
      var newFile = DriveApp.getFileByID(newFileId);
      var folder = DriveApp.getFolderById(folderId);
      newFile.makeCopy(folder);
      currentFile.setTrashed(true);

      // Update spreadsheet
      sheet.getRange(firstRow + i, 4).setValue(success);
      SpreadsheetApp.flush();
    }
  }
  
  // Create a popup window
  SpreadsheetApp.getUi().alert("Updates Complete");
}

Answer 1

I see a typo

var currentFile = DriveApp.getFileById(currentFileId);
var newFile = DriveApp.getFileByID(newFileId);

getFileByID id is all uppercase, I guess it should be getFileById according to the API docs. Also, try printing the currentFileId and newFileId.

Answer 2

Thanks all. Turns out I needed to subtract one from lastRow in defining 'dataRange'. Below you will find the final code for reference.

function onOpen() {
  var sheet = SpreadsheetApp.getActive();
  var menuItems = [{name: 'Update Google Drive', functionName: 'bulkUpdate'}];
  sheet.addMenu('Menu', menuItems);
}

function bulkUpdate() {
  
  // This constant is added to updated files
  var success = "Success"

  // Define range 
  var sheet = SpreadsheetApp.getActiveSheet();
  var firstRow = 2; // First row of data to process
  var firstColumn = 1; // First column of data to process
  var lastRow = sheet.getLastRow(); // Last row of data to process
  var lastColumn = 6; // Last column of data to process

  // Fetch range
  var dataRange = sheet.getRange(firstRow, firstColumn, lastRow - 1, lastColumn);

  // Fetch values for each row in range
  var data = dataRange.getValues();
  for (i = 0; i < data.length; i++) {
    var row = data[i];
    var folderId = row[1]; // Second column
    var currentFileId = row[3]; // Fourth column
    var newFileId = row[4]; // Fifth column
    var confirmation = row[5]; // Sixth column

    // Copy new file to folder and trash current file
    if (confirmation !== success) {
      var currentFile = DriveApp.getFileById(currentFileId);
      var newFile = DriveApp.getFileById(newFileId);
      var folder = DriveApp.getFolderById(folderId);
      newFile.moveTo(folder);
      currentFile.setTrashed(true);

      // Update spreadsheet
      sheet.getRange(firstRow + i, 6).setValue(success);
      SpreadsheetApp.flush();
      continue;
    }
  }
}