How to set where SpreadsheetApp saves spreadsheet?

user_8891062 0
Viewed 615

I am trying to save a spreadsheet to a specific folder on Drive using google app script. However, I cannot see a method that allows me to do so.

using: SpreadsheetApp.create(name)

saves it directly to Drive, is there a way to save it in a folder?

Any help much appreciated.

google-apps-script google-sheets
1 Answers

How about these solutions? You can choose one of them freely.

SpreadsheetApp.create() cannot directly create in a special folder. It is created to the root folder. When you want to create new spreadsheet in a special folder, you can achieve using DriveApp and Drive API. The sample scripts are as follows.

1. Use DriveApp

When you use this, you can use by only copy and paste this to your script editor.

var fileId = SpreadsheetApp.create(name).getId();
var file = DriveApp.getFileById(fileId);
DriveApp.getFolderById("### folder ID ###").addFile(file);
file.getParents().next().removeFile(file);

Flow :

  1. Create new spreadsheet to root folder.
  2. Add a special folder to the parents of created spreadsheet.
  3. Remove the root folder from the parents of created spreadsheet.

2. Use Drive API

This can create directly new spreadsheet to the special folder. When you use this, you can use by copy and paste this to your script editor. And then, please enable Drive API at Advanced Google Services and API console.

Drive.Files.insert({
  "mimeType": "application/vnd.google-apps.spreadsheet",
  "parents": [{"id": "### folder ID ###"}],
  "title": name
});

Enable Drive API at Advanced Google Services

  • On script editor
    • Resources -> Advanced Google Services
    • Turn on Drive API v2

Enable Drive API at API console

  • On script editor
    • Resources -> Cloud Platform project
    • View API console
    • At Getting started, click Enable APIs and get credentials like keys.
    • At left side, click Library.
    • At Search for APIs & services, input "Drive API". And click Google Drive API.
    • Click Enable button.

References :

If I misunderstand your question, I'm sorry.

Updated at August 28, 2020:

By updating at July 27, 2020, addFile(File), addFolder(Folder), removeFile(File) and removeFolder(Folder) have been deprecated. Ref From July 27, 2020, file.moveTo(destination) and folder.moveTo(destination) are used.

By this, above sample script can be changed as follows.

From:

var folderId = "###";
var fileId = SpreadsheetApp.create("sample name").getId();
var file = DriveApp.getFileById(fileId);
DriveApp.getFolderById(folderId).addFile(file);
file.getParents().next().removeFile(file);

To:

var folderId = "###";
var fileId = SpreadsheetApp.create("sample name").getId();
var file = DriveApp.getFileById(fileId);
var folder = DriveApp.getFolderById(folderId);
file.moveTo(folder);

References:

user_71086530
Related