The following content is automatically translated. If there are any errors, please contact me.
These days, I need to download Google Sheets through an API, but it turns out to be quite troublesome and there isn't much information available. Therefore, I'm recording the process here.
1. Basic code
First, let me provide an example file called sample Spreadsheet.xlsx. Assuming that we need to download this file, the basic code required is as follows:
import io
import os
from typing import List
from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from googleapiclient.http import MediaIoBaseDownload
def run(file_id: str, save_path: str, credentials_path: str, scopes: List[str]):
creds = None
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", scopes)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(credentials_path, scopes)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
service = build("drive", "v3", credentials=creds)
# Different files require different download method, and I don't know how to distinguish them at the moment, so I can only try another one after an error
# Export：Docs Editors files
# Get：files with binary content
try:
request = service.files().get_media(fileId=file_id)
fh = io.BytesIO()
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print("Download %d%%." % int(status.progress() * 100))
except Exception:
request = service.files().export(
fileId=file_id, mimeType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
fh = io.BytesIO()
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print("Download %d%%." % int(status.progress() * 100))
with open(save_path, "wb") as f:
fh.seek(0)
f.write(fh.read())
if __name__ == "__main__":
file_id = "1JI17N-NFAIOxX_2Y88gmuKMlsuGhBPcB"
save_path = "Sample Spreadsheet.xlsx"
scopes = ["https://www.googleapis.com/auth/drive"]
credentials_path = "credentials.json"
run(file_id, save_path, credentials_path, scopes)
Install dependencies
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
To execute the above code, we need to obtain a crucial credentials file called credentials.json. This is Google's credentials file, and accessing Google Drive files requires passing through Google's authorization check, which verifies if you have the permission to access the resource. Below is a guide on how to obtain this file.
2. Create a new project
To create a credential file, you first need to create a project in Google Cloud Console. If you already have a project, you can use it directly.
3. Enable the Google Cloud Drive API service
Since Google Sheets actually exists on Google Drive, we only need the Google Drive API here. If you need to access and modify the cell data of a spreadsheet, you can enable the Google Sheets API service.
After creating a new project, select it and click on Enable API & Services. Find the Google Drive API and click on Enable to grant the project access to call Google Drive services.
4. Create credentials
After enabling it, go back to the main page and click on Credentials. Select OAuth Client ID.
5. Config consent screen
For a newly created project, you will need to configure consent screen before creating credentials. Click on Configure Consent Screen.
User Type can only be external
Fill in your email address. You can use your own Google email address for the other email fields.
6. Select the scope of authorization
Since I only need to download files, I have only selected drive.readonly here. If you need to perform other operations, select the corresponding scope.
Test users can continue to use your own Google email addresses.
At this point, the consent screen is completely configured.
7. Generate credentials.json file
Go back to step 4, create the credentials again, and select the Desktop app
Just download and rename the file to credentials.json
8. Run code
The first time you run the code, you need to log in with your account password to generate a token.json file. After that, you won't need to log in again (as long as it hasn't expired). This is a personal application you created yourself, so there's no need to worry about security issues.
9. Check out results
After waiting for the terminal output, you can see that it has been downloaded.
Download 100%.
If you find that the downloaded file does not sync the cloud modification records, it is usually because the editor is still on the page and needs to save and exit, and then download it again to sync the latest editing results.
Top comments (0)