How to Create a Spreadsheet in a particular folder via App Script
Solution 1
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.
Solution 2
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 !
Solution 3
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
}
Solution 4
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
Solution 5
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)
Related videos on Youtube
Hari Das
A full-stack web developer, UX designer and a JavaScript lover. I work with web technologies like HTML, JS, CSS, PHP, Node JS, Java, MySql. Some of the related lib/frameworks I work with include Angular, React, Vue, Bootstrap, jQuery. A few other technologies I know: git, Visual Basic, Google Apps Script [ will add few more if I recall :P ] I am the founder of Tabgraf (IT service) and Fun But Learn (a personal tech blog) since 2015. I enjoy coding but my favorite has been JavaScript always. I was working as a software developer for multi national companies till late 2015. Then after, I quit my day-job and became a full-time freelancer. I have participated in many international coding competitions hosted on Topcoder and have honor to win the prizes among top developers. You can view my Topcoder profile here. In past few years I have published a dozen of free and premium products, apps, services and plugins. They have been used and loved by many companies world wide. I love to help people. These days I have been busy with some of my projects but I manage to get some time to contribute here in Stack Overflow. A few of my works: Email Export Sync Sheet Tabgraf Fun But Learn Spreadsheet Mailer Special Characters Email Scheduler Pro For any query or help drop me an email at [email protected] or alternatively you can also start a live chat with me.
Updated on July 09, 2022Comments
-
Hari Das almost 2 years
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.......
-
Serge insas over 10 yearsok, I'm 7 minutes too late ;-) but have a look nevertheless... there is a better way to do it. ;-)
-
Serge insas over 9 yearsthis 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...)
-
Joshua over 8 yearsThis thread shows that the new way to do this is to use Advanced Drive Service. code.google.com/p/google-apps-script-issues/issues/…
-
Fuhrmanator over 7 yearsWhen 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).
-
Ellen Spertus over 7 yearsThis is just what I was looking for. Thank you! It should now be the accepted answer.
-
RandomDude over 6 yearsOne 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
-
Constantin Zagorsky about 6 yearsOfficial documentation: developers.google.com/apps-script/advanced/drive ; Also insert() has two parameters: Drive.Files.insert(resource, fileData)
-
kenchew almost 4 yearsThis works. (As of this comment date.) But why Google don't fix this until now. developers.google.com/apps-script/reference/drive/… still don't work.
-
Michele Pisani over 3 yearsAlternative method without makeCopy or setTrashed: stackoverflow.com/questions/19607559/…
-
Michele Pisani over 3 yearsaddFile have been deprecated in July 27, 2020, here you can find how to use new method moveTo: stackoverflow.com/questions/19607559/…
-
Michele Pisani over 3 yearsaddFile have been deprecated in July 27, 2020, here you can find how to use new method moveTo: stackoverflow.com/questions/19607559/…
-
Moiz Tankiwala over 3 yearsAs of the current times, folder.createFile seems to the right way to creat a file in the desired folder.