What is the gspread import_csv file_id parameter?
I am trying to use gspread Python package to import CSV data into google sheet from command line.
Using this tutorial I got everything working and was able to read and write to cells.
However, updating cells 1 by 1 is too slow, so now I am trying to use the method import_csv()
. The docs say :
import_csv (file_id, data) Imports data into the first page of the spreadsheet.
Parameters: data - CSV data string.
file_id
not described here and I can't figure out what it should be. Several other methods are also used file_id
, and for them it is described as:
file_id is the ID of the spreadsheet (also known as the file ID).
I'm not sure where I find the table id, and no matter what I try to get it, I get a permissions error. Since I can use update_cell()
as above, I think I have the permissions working fine, but I am using the wrong one file_id
.
Here's a 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 is 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')
source to share
Add https://www.googleapis.com/auth/drive
to your variable scope
and it will work:
scope=[
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
The reason you want Google Drive in this area is because it import_csv
actually makes an HTTP request to call the Google Drive API, not the Google Sheets API.
source to share
I came to this with my own problems. Although already accepted by the author, let me share my 2 cents. First: yes, you must have the right path in your field. But to clarify what the id is:
From the gspread API reference: http://gspread.readthedocs.io/en/latest/
The model spreadsheet has an id field. And this is what should be used in the import_csv (file_id, data) function. Please note that there are 3 different models: - spreadsheet - worksheet - cell.
From your sample code, you actually get a worksheet object.
sheet = client.open("SheetTitle").sheet1
Which has an ID as well, but I'm pretty sure it 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 check if the id is the same as the one you are using to input directly by printing it
print(sheet.id)
source to share