Script to Copy Google Drive File Based on Google Sheet Value

copy-file.gs

function copyStudentFilesSameFolder() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the folder where this spreadsheet is located
  var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId());
  var sourceFolder = file.getParents().next(); 
  
  // Set destination folder (replace with your folder ID)
  var destinationFolderId = "GET_ID_FROM_URL";   // 👈 Replace with your destination folder ID
  var destinationFolder = DriveApp.getFolderById(destinationFolderId);
  
  // Get all files in the source folder
  var files = sourceFolder.getFiles();
  var fileMap = {};
  
  // Build lookup: filename (without .pdf, lowercase) → file object
  while (files.hasNext()) {
    var f = files.next();
    if (f.getMimeType() === MimeType.PDF) {  // only map PDFs
      var nameWithoutExt = f.getName().replace(/\.pdf$/i, "").trim().toLowerCase();
      fileMap[nameWithoutExt] = f;
    }
  }
  
  // Read student names from column A (starting row 2)
  var lastRow = sheet.getLastRow();
  var names = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
  var output = [];
  
  for (var i = 0; i < names.length; i++) {
    var studentName = names[i][0];
    if (!studentName) {
      output.push([""]);
      continue;
    }
    
    var key = studentName.trim().toLowerCase(); // case-insensitive
    if (fileMap[key]) {
      // Copy file into destination folder
      var copiedFile = fileMap[key].makeCopy(destinationFolder);
      output.push([copiedFile.getUrl()]);
    } else {
      output.push(["❌ Not Found"]);
    }
  }
  
  // Write results (destination file links) into column B
  sheet.getRange(2, 2, output.length, 1).setValues(output);
}