If I want to download a Google sheet to CSV locally, do I need "redirect_uris" defined for Google API v4?
I'm trying to use the Python API with Google API v4 to locally download a Google sheet and convert it to a CSV file. I'm happy for this to run as a script on my local machine. I don't need web abilities. I have this Python code ...
from gsheets import Sheets
...
sheets = Sheets.from_files('/Users/davea/Documents/workspace/chicommons/maps/web/configuration_g_client.json')
url = 'https://docs.google.com/spreadsheets/d/1ifpqxxxxxxxxxxxxx5gmvevJ7jc-xxxxxxxxjDS8Me7U/edit#gid=0'
s = sheets.get(url)
out = s.sheets[3].to_csv('Spam.csv', encoding='utf-8', dialect='excel')
print(out)
The configuration_g_client.json file that was downloaded from Google looks like the below ...
{"web":
{
"client_id":"124xxxx.apps.googleusercontent.com",
"project_id":"ccc-directory-xxxxxxxxx08","auth_uri":"https://accounts.google.com/o/oauth2/auth",
"token_uri":"https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
"client_secret":"xxxxxx13s9",
"javascript_origins":["http://localhost:8000"]
}
}
But this line
sheets = Sheets.from_files('/Users/davea/Documents/workspace/chicommons/maps/web/configuration_g_client.json')
dies with
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/gsheets/api.py", line 31, in from_files
creds = oauth2.get_credentials(scopes, secrets, storage, no_webserver)
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/gsheets/oauth2.py", line 48, in get_credentials
flow = client.flow_from_clientsecrets(secrets, scopes)
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/oauth2client/_helpers.py", line 133, in positional_wrapper
return wrapped(*args, **kwargs)
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/oauth2client/client.py", line 2134, in flow_from_clientsecrets
client_type, client_info = clientsecrets.loadfile(filename,
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/oauth2client/clientsecrets.py", line 165, in loadfile
return _loadfile(filename)
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/oauth2client/clientsecrets.py", line 126, in _loadfile
return _validate_clientsecrets(obj)
File "/Users/davea/Documents/workspace/chicommons/maps/web/venv/lib/python3.8/site-packages/oauth2client/clientsecrets.py", line 99, in _validate_clientsecrets
raise InvalidClientSecretsError(
oauth2client.clientsecrets.InvalidClientSecretsError: Missing property "redirect_uris" in a client type of "web".
If I want to run locally, should I be using a different client type? Otherwise, what value do I need to set for "redirect_uris"?
Edit: In response to the answer given, I updated my credentials JSON file to include
"redirect_uris":["http://localhost:8000"]
and now when I run my script locally, it attempts to spawn a browser
This is bad becauase I want to run the script locally through a cron job and don't want to have to interact with a browser.
Edit: Per the suggestion given about running the script using a service account. I created the service account following teh instructions here -- https://developers.google.com/ad-manager/api/authentication#service . Below are the resulting project and key screens ...
The credentials JSON file looks like this
{
"type": "service_account",
"project_id": "chicommons",
"private_key_id": "ecf14d...",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCcSISusTKvBcO1\nRn9bdD/vYWYyoi0ERi5P0u8+iJQ6WUmYIkKG2aYfyp3kt1/KQJ4YW0M4/AUGAQlz\n5FdS1kTqHkmzwtd+FN8FE6...xxx.../OmXZkvjQej2eA6Txcm/hfuyebkrXnyUm\nycuVzNRsywvGQf/7gWP0y9oQ\n-----END PRIVATE KEY-----\n",
"client_email": "google-sheets-download@chicommons.iam.gserviceaccount.com",
"client_id": "116134130252723375625",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/google-sheets-download%40chicommons.iam.gserviceaccount.com"
}
but now when I run my script to attempt to download my Google sheet, I get this error
oauth2client.clientsecrets.InvalidClientSecretsError: Invalid file format. See https://developers.google.com/api-client-library/python/guide/aaa_client_secrets Expected a JSON object with a single property for a "web" or "installed" application
In your case, how about the following modification?
From your configuration_g_client.json, I noticed "javascript_origins":["http://localhost:8000"] and no redirect_uris. So please add http://localhost:8080/ to redirect_uris and test it again. The following image is for setting http://localhost:8080/ to redirect_uris. In this case, you can remove javascript_origins as follows.
- Here, please set
http://localhost:8080/as the value ofredirect_urisand click "SAVE" button. The last character ofhttp://localhost:8080/is/. It seems that the defaultredirect_urisishttp://localhost:8080/foroauth2.get_credentials. Please be careful.
Answer for updated question:
- In your updated question, you want to download a CSV data from Google Drive using the service account instead of OAuth2 with python.
For this, I think that your script for authorizing is required to be modified. In this answer, I would like to propose a sample script for achieving your goal using the service account.
IMPORTANT:
When the service account is used, there is an important point. It's that the Google Drive of the service account is different from your Google Drive. So in order to download the Google Spreadsheet in your Google Drive, please share the Google Spreadsheet with the email of the service account. By this, the file can be downloaded by the service account. Please be careful this. You can see it as client_email in the credential file of service account.
Sample script 1:
Before you use this, please set the variables of credentialFileOfServiceAccount, emailOfServiceAccount and file_id.
import requests
from oauth2client.service_account import ServiceAccountCredentials
credentialFileOfServiceAccount = './###.json'
emailOfServiceAccount = '###'
file_id = '###'
creds = ServiceAccountCredentials.from_json_keyfile_name(credentialFileOfServiceAccount, scopes='https://www.googleapis.com/auth/drive.readonly')
access_token = creds.create_delegated(emailOfServiceAccount).get_access_token().access_token
url = 'https://www.googleapis.com/drive/v3/files/' + file_id + '/export?mimeType=text%2Fcsv'
headers = {'Authorization': 'Bearer ' + access_token}
res = requests.get(url, headers=headers)
print(res.text)
with open('Spam.csv', 'wb') as f:
f.write(res.content)
- When above script is run, the Spreadsheet of
file_idis exported as the CSV data, and the data is saved as a file ofSpam.csv. In this case, the data of 1st tab of Spreadsheet is retrieved. This is the same with your script.
Sample script 2:
In this sample script, googleapis for python is used.
import io
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build
from googleapiclient.http import MediaIoBaseDownload
credentialFileOfServiceAccount = './###.json'
file_id = '###'
creds = ServiceAccountCredentials.from_json_keyfile_name(credentialFileOfServiceAccount, ['https://www.googleapis.com/auth/drive.readonly'])
service = build('drive', 'v3', credentials=creds)
request = service .files().export_media(fileId=file_id, mimeType='text/csv')
fh = io.FileIO('Spam.csv', mode='wb')
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print('Download %d%%.' % int(status.progress() * 100))
Answer for one last follow-up question:
At above sample scripts, the 1st tab is exported as the CSV data. In order to export the sheets except for the 1st tab the Google Spreadsheet, in this answer, I would like to propose to change the endpoint. I thought that this way might be simpler. The sample script is as follows.
Sample script:
Before you use this, please set the variables of credentialFileOfServiceAccount, emailOfServiceAccount and file_id. And also, please set the sheet name you want to export.
import requests
from oauth2client.service_account import ServiceAccountCredentials
credentialFileOfServiceAccount = './###.json'
emailOfServiceAccount = '###'
file_id = '###'
sheet_name = 'Sheet2' # <--- Please set the sheet name you want to export.
creds = ServiceAccountCredentials.from_json_keyfile_name(credentialFileOfServiceAccount, scopes='https://www.googleapis.com/auth/drive.readonly')
access_token = creds.create_delegated(emailOfServiceAccount).get_access_token().access_token
url = 'https://docs.google.com/spreadsheets/d/' + file_id + '/gviz/tq?tqx=out:csv&sheet=' + sheet_name
headers = {'Authorization': 'Bearer ' + access_token}
res = requests.get(url, headers=headers)
print(res.text)
with open('Spam.csv', 'wb') as f:
f.write(res.content)
If you want to use the sheet ID (gid), the endpoint is as follows.
url = 'https://docs.google.com/spreadsheets/d/' + file_id + '/gviz/tq?tqx=out:csv&gid=' + gidWhen above script is run, "Sheet2" in the Spreadsheet of
file_idis exported as the CSV data, and the data is saved as a file ofSpam.csv.