Thursday, January 24, 2019

Google Apps Script - Create New File in Google Drive Folder

Using Google Apps Script ("GAS") can be an adventure in frustration.  A perfect example is when I tried to create a new Google Sheets file in a specific Google Drive Folder.  The sheet file was intended to house temporary files created when a particular script was ran.  The ability to store the file in a specific folder was needed to avoid the main folder from getting messy and difficult to administer.  Keeping the files in a specific folder makes clean up a lot easier.

Its pretty straightforward to create a file using the SpreadsheetApp service, but I learned you can't create a file directly in a specific folder without first creating the file in the main folder, copy the file to the folder you wanted and then deleting the old.  If you go through the DriveApp or DocsList chain it would appear that you can do this by specifying the mime type... except you cant without getting an error message.  This issue was addressed on the Google-Apps-Scripts-Issues blog here:  Issue 4080. After being kicked around a bit between users and the Google Project Team, Google officially stated that creating a spreadsheet file in a specific folder using DriveApp, DocList, or SpreadsheetApp couldn't be done.  However, it can be done with Advanced Drive Services.

Advanced Drive Services, like other Google advance services, must be manually enabled to turn it on.  It must also be enabled in the Google Developers Console.  Here are the steps to activate according to the documentation:
  1. In the script editor, select Resources > Advanced Google services....
  2. In the dialog that appears, click the on/off switch next to the service you want to use.
  3. At the bottom of the dialog, click the link for the Google Developers Console.
  4. In the new console, again click the on/off switch next to the service you want to use.
  5. Return to the script editor and click OK in the dialog. The advanced service you enabled will now be available in autocomplete.
Once activated, Advance Drive Services will be available in GAS by typing Drive and using auto-complete to navigate through the options.  Advanced Drive Services is not as intuitive as regular GAS services.  However, it does expand your capabilities.  Here's an example of a script I used to accomplish my original task:

function createSpreadsheetFileinFolder(){
var folderId = DocsList.getFolder('YOUR_FOLDER_NAME').getId()  
var file = {
    "title": "MY_SPREADSHEET_NAME",
    "mimeType": MimeType.GOOGLE_SHEETS,
    "parents": [
        "id": folderId

Of course, the above script can be applied for Google Docs or about any file type as long as you specify the right mime type.