Google Sheets API: Create or move Spreadsheet into

Question

Is it possible to create a Spreadsheet in a specified Folder or do I have to use the Drive API to move it afterwards?

Answer 1

Depends on how you want to use and how do you create your Spreadsheet, it is possible to create Spreadsheet like so.

 function create_ss_in_folder(folder, name) {
  var ss = SpreadsheetApp.create(name);
  var id = ss.getId();
  var file = DriveApp.getFileById(id);
  var folder = get_folder_by_name(folder);
  folder.addFile(file);
  DriveApp.getRootFolder().removeFile(file);
  return id;
}

Again with not much info i do not know why would you create Spreadsheets manually when there is programmatic way to do it.

Some helpful links:

https://developers.google.com/apps-script/reference/drive/folder

https://developers.google.com/apps-script/advanced/drive

Update:

  //Move it to desired folder
  var fileID = 'Your file ID'
  var folderID = 'Folder ID'
  var file = DriveApp.getFileById(fileID).getName()
  var folder = DriveApp.getFolderById(folderID)
  var newFile = file.makeCopy(file, folder)

  // This will remove it from root.
  DriveApp.getFileById(fileID).setTrashed(true)

Answer 2

Slightly modified version of the method given by @ukaric. This creates a new Form (can be any doc type you need) in the same folder as the current spreadsheet. This can also be used for moving files around, then just replace the creation part with getting a reference to the target file.

function createInCurrentFolder() {
  // Create a new form in drive root
  var form = FormApp.create("New_Form");
  var formFile = DriveApp.getFileById(form.getId())

  // Get the parent folders of the open document
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssFile = DriveApp.getFileById(ss.getId());
  var ssParents = ssFile.getParents();

  // Check if we have a parent and
  // assume the first parent to be the current folder
  if (ssParents.hasNext()){
    var parentFolder = ssParents.next();

    // Check for root folder
    if (parentFolder.getId() == DriveApp.getRootFolder().getId()) return;

    // Add the created form to current folder
    // and remove it from root folder
    parentFolder.addFile(formFile);
    DriveApp.removeFile(formFile);
  }
}

Answer 3

Use the drive API to create an empty sheet and open it using the sheets api:

function getClient()
{
    $client = new \Google_Client();

    putenv(
        'GOOGLE_APPLICATION_CREDENTIALS='.__DIR__."/config/google-creds.json"
    );
    $client = new \Google_Client();
    $client->setScopes(
        [
            \Google_Service_Drive::DRIVE,
            \Google_Service_Storage::CLOUD_PLATFORM,
            'https://www.googleapis.com/auth/spreadsheets',
        ]
    );
    $client->useApplicationDefaultCredentials();

    return $client;
}

// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Drive($client);

$ROOT_FOLDER_ID='you-must-set-this-to-your-folder-id';
// create the empty sheet:

$googleServiceDriveDriveFile = new \Google_Service_Drive_DriveFile();
$googleServiceDriveDriveFile->setMimeType(
    'application/vnd.google-apps.spreadsheet')
    ;
$googleServiceDriveDriveFile->setName('Test');
$googleServiceDriveDriveFile->setParents([$ROOT_FOLDER_ID]);
$res  = $service->files->create($googleServiceDriveDriveFile);

// print the id of the file we just made
print 'Created file with id : ' . $res->getId() . "\n";

// Print the names and IDs for up to 10 files.
$optParams = array(
    'pageSize' => 10,
    'fields'   => 'nextPageToken, files(id, name)',
    'q' => "'$ROOT_FOLDER_ID' in parents"
);

$results = $service->files->listFiles($optParams);

if (count($results->getFiles()) == 0) {
    print "No files found.\n";
} else {
    print "Files:\n";
    foreach ($results->getFiles() as $file) {
        printf("%s (%s)\n", $file->getName(), $file->getId());
    }
}
// fetch the sheet you created and edit it.
$service = new Google_Service_Sheets($client);
$sheet = $service->spreadsheets->get($res->getId());

print "Fetched sheet with name: " . $sheet->getSpreadsheetUrl() . "\n";

Answer 4

I'm a little late but the way I found doesn't require copying/deleting the file. You simply remove and add parents. My code is in Ruby instead of JS but I'm sure something similar exists.

file = service.get_file(file_id,fields: 'parents')
service.update_file(
  file_id,
  file
  remove_parents: file.parents, 
  add_parents: new_folder_id,
  fields: 'id,parents'
)

After a few minutes of searching the drive api docs, I found the code below for Node.JS to move a file into a specific folder. Hope this helps.

fileId = '1sTWaJ_j7PkjzaBWtNc3IzovK5hQf21FbOw9yLeeLPNQ'
folderId = '0BwwA4oUTeiV1TGRPeTVjaWRDY1E'
// Retrieve the existing parents to remove
drive.files.get({
  fileId: fileId,
  fields: 'parents'
}, function(err, file) {
  if (err) {
    // Handle error
    console.log(err);
  } else {
    // Move the file to the new folder
    var previousParents = file.parents.join(',');
    drive.files.update({
      fileId: fileId,
      addParents: folderId,
      removeParents: previousParents,
      fields: 'id, parents'
    }, function(err, file) {
      if(err) {
        // Handle error
      } else {
        // File moved.
      }
    });
  }
});