What is the gspread import_csv file_id parameter?

Question

I am trying to use the gspread Python package to import CSV data into a Google sheet from the command line.

Using this guide, I got everything working, and was able to both read and write to cells.

However updating cells 1-by-1 is too slow, so I am now trying to use the import_csv() method. The docs say:

import_csv(file_id, data) Imports data into the first page of the spreadsheet.

Parameters: data – A CSV string of data.

file_id is not described here, and I can't work out what it should be. A few other methods also use a file_id and for them it is described as:

file_id – a spreadsheet ID (aka file ID.)

I am not sure where I find spreadsheet ID, and no matter what I try I get a permissions error. Since I am able to use update_cell(), as described above, I think I have permissions working fine but am using the wrong file_id.

Here's simplified code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open("SheetTitle").sheet1

# This works fine, so I think permissions etc are all set up correctly
sheet.update_cell(1, 1, 'Foo')

# Now try importing CSV data from a string
csv="""2016, 2017
1,2
3,4
"""

# Does not work
client.import_csv(sheet, csv);

# Using the spreadsheet_id in the URL as described here https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id
client.import_csv('11x...', csv);

# Using the "#gid=0" value in the query string in the browser when looking at this sheet
client.import_csv(0, csv);

Here's the error I get, no matter which of the above I try:

Traceback (most recent call last):
  File "./simple.py", line 22, in <module>
    client.import_csv(sheet, csv);
  File "/Library/Python/2.7/site-packages/gspread/client.py", line 297, in import_csv
    headers=headers
  File "/Library/Python/2.7/site-packages/gspread/httpsession.py", line 82, in put
    return self.request('PUT', url, params=params, data=data, **kwargs)
  File "/Library/Python/2.7/site-packages/gspread/httpsession.py", line 69, in request
    response.status_code, response.content))
gspread.exceptions.RequestError: (403, '403: {\n "error": {\n  "errors": [\n   {\n    "domain": "global",\n    "reason": "insufficientPermissions",\n    "message": "Insufficient Permission"\n   }\n  ],\n  "code": 403,\n  "message": "Insufficient Permission"\n }\n}\n')

Offenbach

Answer 1

Add https://www.googleapis.com/auth/drive to your scope variable and it will work:

scope=[
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]

The reason you need the Google Drive in the scope is because import_csv actually makes an HTTP request to Google Drive API call and not to Google Sheets API.

Diaphragm

Works! Thank you! This combined with the 11x... spreadsheet_id does the trick. Did I miss this in the documentation somewhere? – Hefty

It is documented, but for other method: gspread.readthedocs.io/en/latest/#gspread.Client.create – Diaphragm

Answer 2

Came across this while having issues of my own. Although already accepted by the author, let me share my 2 cents. First: yes, you need to have the correct path in your scope. But to clarify what's the id:

From gspread API Reference: http://gspread.readthedocs.io/en/latest/

The model spreadsheet has an id field. And that's what should be used into import_csv(file_id, data) function. Notice that there are 3 different models: - spreadsheet - worksheet - cell.

From your sample code, you're actually getting the worksheet object.

sheet = client.open("SheetTitle").sheet1

Which also has an id, but I'm fairly sure this won't work.

You need to get the spreadsheet object and use its id.

sheet = client.open("SheetTitle") client.import_csv(sheet.id, csv);

You can verify if the id is the same as the one you're using to input directly by printing it print(sheet.id)

Berlin