How to duplicate a Google Spreadsheet to a designated Google Drive folder using Apps Script

Other Questions Google drive
1

I’m trying to figure out how to copy an existing Google Spreadsheet and place it in a particular folder using Google Apps Script. Here’s what I want to accomplish:

function duplicateSheetToFolder() {
  // Get the source spreadsheet
  var originalSheet = SpreadsheetApp.openById('source_sheet_id');
  
  // Get the target folder
  var targetFolder = DriveApp.getFolderById('folder_id');
  
  // Create a copy in the specified folder
  var newSheet = originalSheet.copy('NewSheetName');
  
  // Move to target folder
  DriveApp.getFileById(newSheet.getId()).moveTo(targetFolder);
}

I need help with the proper syntax and methods to make this work correctly. What’s the right approach to copy a spreadsheet and ensure it ends up in the correct Drive folder?

2

Heads up - if ur original sheet has multiple owners or sharing settings, they won’t transfer to the copy. You’ll have to set permissions separately using setSharing() methods. Also check your folder permissions since the script sometimes fails silently when it can’t write to the target folder.

3

You’re on the right track, but the folder placement needs work. The copy() method dumps the new spreadsheet in the root directory by default, so you’d have to move it after. There’s a cleaner way though - use makeCopy() on the Drive file directly:

function duplicateSheetToFolder() {
  var originalSheet = SpreadsheetApp.openById('source_sheet_id');
  var targetFolder = DriveApp.getFolderById('folder_id');
  
  // Get the original file and make a copy directly to the folder
  var originalFile = DriveApp.getFileById(originalSheet.getId());
  var copiedFile = originalFile.makeCopy('NewSheetName', targetFolder);
}

This skips the whole copy-then-move dance and puts the file exactly where you want it. I’ve used this in several projects and it’s way more reliable than the two-step process, especially with tricky folder permissions.

4

Your main problem is that copy() dumps the file in Drive’s root, then you’re trying to move it - that’s where things break. I hit this exact issue last month while automating reports. The Drive API works better, but here’s the catch - you need to clean up the original copy or you’ll have duplicates everywhere.

function duplicateSheetToFolder() {
  var originalSheet = SpreadsheetApp.openById('source_sheet_id');
  var targetFolder = DriveApp.getFolderById('folder_id');
  
  var originalFile = DriveApp.getFileById(originalSheet.getId());
  var copiedFile = originalFile.makeCopy('NewSheetName', targetFolder);
  
  // Remove from root if it was placed there
  var parents = copiedFile.getParents();
  while (parents.hasNext()) {
    var parent = parents.next();
    if (parent.getName() !== targetFolder.getName()) {
      parent.removeFile(copiedFile);
    }
  }
}

This way your copy lands only where you want it, no orphaned files cluttering up Drive.