Creating empty spreadsheets in Google Drive using Drive API

Question

I want to create an empty Google Sheet (created only with metadata) in Google Drive. When I referred to the Google SpreadSheet API documentation, it says to use the DocumentsList API, but it's deprecated and instead asks me to use the Google Drive API. In the Drive API docs, I could not find any way to create an empty Sheet. Anyone have a clue on how to do this?

Solution 1

You can do this using the Drive API by setting the MIME type to application/vnd.google-apps.spreadsheet:

To do this in Python:

from apiclient.discovery import build
service = build('drive', 'v2')

import httplib2
credentials = ... # Obtain OAuth 2.0 credentials
http = credentials.authorize(httplib2.Http())

body = {
  'mimeType': 'application/vnd.google-apps.spreadsheet',
  'title': 'Name of Spreadsheet',
}
file = service.files().insert(body=body).execute(http=http)
# or for version 3 it would be
# file = service.files().create(body=body).execute(http=http)

Head over to the Google APIs Explorer to try it out!

Solution 2

(Jul 2016) BossyLobster's answer above is still valid (as Drive API v2 has not been deprecated [yet]). However, below are more modern ways of doing the same thing and some videos to help with understanding:

Authorization boilerplate for both examples below

from googleapiclient import discovery
from httplib2 import Http
from oauth2client import file, client, tools

store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
    creds = tools.run_flow(flow, store)

NOTE: to create your API project and OAuth2 credentials as well as download those credentials to the client_secret.json (or client_id.json) file, go to the Google Developers Console.

• To learn how to use the Developers Console, see this video.
• To walk through this boilerplate authorization code, see this video. (NOTE: the boilerplate above is slightly newer/improved from the code in the video)
• To get an intro to using the Google Drive API v2 (listing your Drive files), see this video.
• To learn about the Google Drive API v3 (up/downloading files), see this blogpost & video. (NOTE: v2 & v3 live side-by-side... v2 not deprecated yet; v3: fewer API calls, better performance vs. v2)
• To learn about the Google Sheets API v4 (migrating SQL data to a Sheet), see this blogpost & video.

Create new/blank Sheet w/Google Drive API v3 (& v2)

# above: SCOPES = 'https://www.googleapis.com/auth/drive.file'
DRIVE = discovery.build('drive', 'v3', http=creds.authorize(Http()))
data = {
    'name': 'My new Sheet',
    'mimeType': 'application/vnd.google-apps.spreadsheet',
}
sheet = DRIVE.files().create(body=data).execute() # insert() for v2

Create new/blank Sheet w/Google Sheets API v4

# above: SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
SHEETS = discovery.build('sheets', 'v4', http=creds.authorize(Http()))
data = {'properties': {'title': 'My new Sheet'}}
sheet = SHEETS.spreadsheets().create(body=data).execute()

Now you may ask, "Why are there two different ways of creating a blank Sheet?" To put it succinctly, the Sheets API is meant primarily for spreadsheet-oriented operations, i.e., inserting data, reading spreadsheet rows, cell formatting, creating charts, adding pivot tables, etc., not file-oriented requests like create/delete and import/export, where the Drive API is the correct one to use. It just so happens that create is sort-of both, hence why there are two ways of doing it.

Solution 3

you could also try the pygsheets library i wrote , It ofcourse provies more features than just creating spreadsheets,

import pygsheets

gc = pygsheets.authorize(outh_file='client_secret.json')

# Open spreadsheet and then worksheet
gc.create('my new sheet')

Solution 4

The api reference to create spreadsheet is at https://developers.google.com/sheets/reference/rest/v4/spreadsheets/create.

The code snippet to create a new spreadsheet is as follows.

String[] SCOPES = { SheetsScopes.SPREADSHEETS };

GoogleAccountCredential credential = GoogleAccountCredential.usingOAuth2(
        getApplicationContext(),
        Arrays.asList(SCOPES)).setBackOff(new ExponentialBackOff());
credential.setSelectedAccountName("[email protected]");

HttpTransport transport = AndroidHttp.newCompatibleTransport();
JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();

com.google.api.services.sheets.v4.Sheets service =
    new com.google.api.services.sheets.v4.Sheets.Builder(
        transport,
        jsonFactory,
        credential)
    .setApplicationName("Google Sheets API Android Quickstart")
    .build();

Spreadsheet spreadsheet = new Spreadsheet();
SpreadsheetProperties properties = new SpreadsheetProperties();
properties.setTitle("SpreadSheetTitle");
spreadsheet.setProperties(properties);

service.spreadsheets().create(spreadsheet).execute()

View more solutions

Comments

[Santosh Ghimire]

How do I obtain OAuth 2.0 credentials??

[bossylobster]

"Head over to the Google APIs Explorer to try it out!"

[Santosh Ghimire]

I tried and it worked in the Google APIs Explorer. But I want to do it from python code and am having a hard time in the authorization part.

[John]

its not working,,i am able to create text file using "text/plain" MIME Type..but not spreadsheet

[pfried]

thanks for this, not because of the question. simply because it is the first example i found on how to post a model. All documentation i found uses get and list examples

[tol4trob]

Get this error: File "/Library/Python/2.7/site-packages/oauth2client/client.py", line 1427, in _generate_refresh_request_body body = urllib.parse.urlencode({ AttributeError: 'Module_six_moves_urllib_parse' object has no attribute 'urlencode'

[bossylobster]

File an issue: github.com/google/oauth2client/issues/new It seems your install is broken.

[wescpy]

The good news: this uses the newest version of the Sheets API, v4. The bad news: the OP was requesting a Python example.

[João Abrantes]

Can I specify in which folder of the google drive to create this spreadsheet on?

[wescpy]

Yes you can. Check the documentation on how to do that: developers.google.com/drive/v3/reference/files/create Look for the "parents" attribute.