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.......

ozeebee · Answer 1 · 2022-09-09T07:25:10.263

50

As suggested by @Joshua, it's possible to create a Spreadsheet (in a specific folder) with the Advanced Drive Service (you'll need to activate this if you haven't already, by going into Services +, find Drive API and click Add).

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 !

edited Sep 09 '22 at 07:25

answered Jan 06 '17 at 16:26

ozeebee

1,878
2
23
26

This is just what I was looking for. Thank you! It should now be the accepted answer. – Ellen Spertus Jan 10 '17 at 18:04
10

One thing i had to do in order to make it work: Activate "Drive API". Go to in the script editor to: Resources > Advanced Google services ... > Drive API > On – RandomDude Nov 28 '17 at 15:44
Official documentation: https://developers.google.com/apps-script/advanced/drive ; Also insert() has two parameters: Drive.Files.insert(resource, fileData) – Constantin Zagorsky Mar 25 '18 at 01:23
This works. (As of this comment date.) But why Google don't fix this until now. https://developers.google.com/apps-script/reference/drive/folder#createFile(String,String,String) still don't work. – kenchew Jul 09 '20 at 14:16

Riyafa Abdul Hameed · Answer 2 · 2020-10-01T05:24:39.057

14

  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

edited Oct 01 '20 at 05:24

answered Mar 23 '17 at 02:11

Riyafa Abdul Hameed

7,417
6
40
55

addFile have been deprecated in July 27, 2020, here you can find how to use new method moveTo: https://stackoverflow.com/questions/19607559/how-to-create-a-spreadsheet-in-a-particular-folder-via-app-script/64137750#64137750 – Michele Pisani Sep 30 '20 at 12:41

score 11 · Answer 3 · edited Mar 10 '14 at 21:25

11

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
}

edited Mar 10 '14 at 21:25

rickcnagy

1,774
18
24

answered Oct 26 '13 at 19:02

Serge insas

45,904
7
105
131

1

this seems to be a new restriction... it used to work when I published the code. It still works for other mimetypes that are NOT google docs (html, csv...) – Serge insas Nov 06 '14 at 12:44
3

This thread shows that the new way to do this is to use Advanced Drive Service. https://code.google.com/p/google-apps-script-issues/issues/detail?id=4080 – Joshua Nov 04 '15 at 23:04
As of the current times, folder.createFile seems to the right way to creat a file in the desired folder. – Moiz Tankiwala Nov 14 '20 at 23:52

score 11 · Accepted Answer · edited Dec 18 '15 at 15:50

11

Since you can no longer create Google Docs (Docs or SpreadSheets) using DriveApp, nor use addToFolder because DocList is deprecated. There is only one way to create or "move" Google Docs or Google SpreadSheets..

  //"Move" file to folder-------------------------------//
  var fileID = '12123123213321'
  var folderID = '21321312312'
  var file = DriveApp.getFileById(fileID).getName()
  var folder = DriveApp.getFolderById(folderID)
  var newFile = file.makeCopy(file, folder)

  //Remove file from root folder--------------------------------//
  DriveApp.getFileById(fileID).setTrashed(true)

As you can see this DOES NOT move the file, it makes a copy with the same name in the folder you want and then moves the original file to the trash. I'm pretty sure there is no other way to do this.

edited Dec 18 '15 at 15:50

phollingdrake

417
3
7

answered Mar 25 '15 at 14:06

maeq

1,073
1
12
23

When I'm using Drive as a user and I create New > Google Sheets (in a shared folder, for example), the file is created in the folder directly (if I allow it). I don't see odd copies of those files in the trash. Seems like there's a better way (but maybe the API for mortals doesn't allow it). – Fuhrmanator Oct 06 '16 at 20:20
Alternative method without makeCopy or setTrashed: https://stackoverflow.com/questions/19607559/how-to-create-a-spreadsheet-in-a-particular-folder-via-app-script/64137750#64137750 – Michele Pisani Sep 30 '20 at 12:40

score 7 · Answer 5 · answered Sep 30 '20 at 12:38

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)

score 5 · Answer 6 · answered Jun 16 '19 at 03:01

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 !

addFile have been deprecated in July 27, 2020, here you can find how to use new method moveTo: https://stackoverflow.com/questions/19607559/how-to-create-a-spreadsheet-in-a-particular-folder-via-app-script/64137750#64137750 — Michele Pisani, Sep 30 '20 at 12:41

score 2 · Answer 7 · answered Oct 26 '13 at 18:54

2

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);

answered Oct 26 '13 at 18:54

Hari Das

10,145
7
62
59

ok, I'm 7 minutes too late ;-) but have a look nevertheless... there is a better way to do it. ;-) – Serge insas Oct 26 '13 at 19:03

score 1 · Answer 8 · answered Oct 26 '13 at 15:25

1

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

answered Oct 26 '13 at 15:25

Zig Mandel

19,571
5
26
36

score 0 · Answer 9 · answered Jun 03 '19 at 02:56

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)

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

9 Answers9

Linked