Creating a Google Sheet in a specific folder with DriveApp

Question

I want to create a Google Sheet in a specific folder on Google Drive using Google Apps Script. According to the documentation of the File class:

createFile(name, content, mimeType)

Creates a file in the current folder with the given name, contents, and MIME type. Throws an exception if content is larger than 10MB.

// Create an HTML file with the content "Hello, world!"
DriveApp.getRootFolder().createFile('New HTML File', '<b>Hello, world!</b>', MimeType.HTML);

And according to the MimeType documentation:

Enum MimeType

An enumeration that provides access to MIME-type declarations without typing the strings explicitly. Methods that expect a MIME type rendered as a string (for example, 'image/png') also accept any of the values below, so long as the method supports the underlying MIME type.

There is no mention of createFile supporting only a subset of available MIME types. The page includes a table, which includes this MIME type:

GOOGLE_SHEETS Enum Representation of MIME type for a Google Sheets file.

My issue lies in implementing this method of creating a file as described in the File documentation.

var folder = DriveApp.createFolder('new folder');
var ss = folder.createFile('new sheet', '', MimeType.GOOGLE_SHEETS);

This will throw an error, stating

Invalid argument: file.contentType (line 2, file "Code")

Replacing MimeType.GOOGLE_SHEETS with the string application/vnd.google-apps.spreadsheet obviously did not help. I am aware of this question, but the accepted answer simply admits defeat and uses a common messy workaround by creating the file in SpreadsheetApp, copying it, and deleting the original. This question has a similarly disappointing accepted answer to the same problem, except for Google Docs. The SpreadsheetApp workaround is fine, but I'm hoping 3 years on that someone holds the answer as to how to use createFile properly.

Answer 1

The Google issue tracker has a long, long history of this question being asked. Way back in 2014, a Google staff member declared that it was a resolved issue, and they had decided that MimeType.GOOGLE_-type files could not be created by the createFile method.

Marked as fixed.

After extensive consideration, we have determined that DriveApp.createFile() should not be used to create MimeType.GOOGLE_* files.

Shortly, attempting to do so will fail and result in a more descriptive error message.

Unfortunately, despite promising otherwise, the error message was never changed and the documentation was never updated to reflect this "extensive consideration".

The correct solution to add a MimeType.GOOGLE_-type file to a specific folder with 1 server call (no copying, removing, or updating) is

var folder = DriveApp.createFolder('folder');
var file = {
    title: 'New Sheet',
    mimeType: MimeType.GOOGLE_SHEETS,
    parents: [{id: folder.getId()}]
}
ssId = Drive.Files.insert(file).id;

Drive.Files.insert does require the Advanced Drive Service, which you can enable on your script with this guide.

Answer 2

Try this:

function createFolderAndFile() {
  var folder = DriveApp.createFolder('RootSub');
  var file = SpreadsheetApp.create('RootSubSheet');
  Drive.Files.update({"parents": [{"id": folder.getId()}]}, file.getId());
}