How to Create a Spreadsheet in a particular folder via App Script

Question

Can anybody help me out,

I want to create a Spreadsheet through App Script in a particular folder. How to do that.

Presently I am doing as follow:

var folder = DocsList.getFolder("MyFolder");
var sheet = SpreadsheetApp.create("MySheet");
var file = DocsList.getFileById(sheet.getId());
file.addToFolder(folder);
file.removeFromFolder(file.getParents()[0]);

It is not working.......

Answer 1

As suggested by @Joshua, it's possible to create a Spreadsheet (in a specific folder) with the Advanced Drive Service:

var name = 'your-spreadsheet-name'
var folderId = 'your-folder-id'
var resource = {
  title: name,
  mimeType: MimeType.GOOGLE_SHEETS,
  parents: [{ id: folderId }]
}
var fileJson = Drive.Files.insert(resource)
var fileId = fileJson.id

No need to move files around with this method !

Answer 2

The other answer is a bit short (and not very explicit). While your approach is logic and should work if you replace

file.removeFromFolder(file.getParents()[0]); 

with

file.removeFromFolder(DocsList.getRootFolder());

there is a better way to do the same job using the new Drive app and the Folder Class, Folder has a method to create a file and you can specify the file type using the mimeType enum.

Code goes like this :

function myFunction() {
  var folders = DriveApp.getFoldersByName('YOUR FOLDER NAME'); // replace by the right folder name, assuming there is only one folder with this name
  while (folders.hasNext()) {
   var folder = folders.next();
  }
  folder.createFile('new Spreadsheet', '', MimeType.GOOGLE_SHEETS); // this creates the spreadsheet directly in the chosen folder
}

Answer 3

  folder = DriveApp.getFolderById("FOLDER_ID")
  var ss = SpreadsheetApp.create("SPREADSHEET_NAME")
  DriveApp.getFileById(ss.getId()).moveTo(folder);

You may use the above code to achieve the same without using advanced drive services

Answer 4

In July 27, 2020 there have been these updates:

The File class now has the following methods:

• file.getTargetId(): Gets a shortcut's file ID.
• file.getTargetMimeType(): Returns the mime type of the item a shortcut points to.
• file.moveTo(destination): Moves a file to a specified destination folder.

The Folder class now has the following methods:

• folder.createShortcut(targetId): Creates a shortcut to the provided Drive item ID, and returns it.
• folder.moveTo(destination): Moves an item to the provided destination folder.

The following Folder class methods have been deprecated:

• addFile(File)
• addFolder(Folder)
• removeFile(File)
• removeFolder(Folder)

https://developers.google.com/apps-script/releases/#july_27_2020

So you can create a Spreadsheet file in a folder using file.moveTo(destination) method:

function createSpreadSheetInFolder(ss_new_name, folder_dest_id) {
    var ss_new = SpreadsheetApp.create(ss_new_name);
    var ss_new_id = ss_new.getId();
    var newfile = DriveApp.getFileById(ss_new_id);
    newfile.moveTo(DriveApp.getFolderById(folder_dest_id))
    return ss_new_id;
}

var file_name = 'SPREADSHEET NAME';
var folder_id = 'DESTINATION FOLDER ID';
var new_ssId = createSpreadSheetInFolder(file_name, folder_id)

Answer 5

You can create a spreadSheet and then add it to the folder.

function createSpreadSheetInFolder(name,folder){
    var ss = SpreadsheetApp.create(name);
    var id = ss.getId();
    var file = DriveApp.getFileById(id);
    folder.addFile(file);
    return ss;
}
folderId='your_folder_id'
name='my_new_ss'
folder=DriveApp.getFolderById(folderId)
createSpreadSheetInFolder(name,folder)

By using the folder.addFile method there's no need to use a temp file (no need to duplicate and remove file). Pretty straightforward !

Answer 6

I finally got the answer to my question. The following works

var file = DocsList.getFileById(sheet.getId());
var folder = DocsList.getFolder("MyFolder");
file.addToFolder(folder);

// remove document from the root folder
folder = DocsList.getRootFolder();
file.removeFromFolder(folder);

Answer 7

What is not working? Use getRootFolder in the last line.

Answer 8

Creating a new spreadsheet in a file can be done using this link as a reference.

createFile(name, content, mimeType)

Therefore using the enum MimeType we can do:

var folder = DriveApp.getFolderById("your-folder-id");
folder.createFile("My File Name","",MimeType.GOOGLE_SHEETS)