python - 如何使用 Drive API 和 Python 中的服务帐户将电子表格中的所有工作表导出为 CSV 文件？

Question

我已经成功建立了与 Drive API 的服务连接，并且我正在创建导出 URL，通过使用 Google 的 AuthorizedSession 类发送请求，将电子表格中的每个工作表下载为 CSV 文件。出于某种原因，只有一部分 CSV 文件返回正确，而其他文件包含损坏的 HTML。当我发送单个请求时，工作表总是返回正确的，但是当我遍历工作表并开始发送请求时，事情开始中断。我发现我以这种方式传递凭据的方式存在问题，但我不确定我是否正确使用了 AuthorizedSession。谁能帮我解决这个问题？

from googleapiclient.discovery import build
from google.oauth2 import service_account
from google.auth.transport.requests import AuthorizedSession
import re
import shutil
import urllib.parse


CLIENT_SECRET_FILE = "client_secret.json"
API_NAME = "sheets"
API_VERSION = "v4"
SCOPES = ["https://www.googleapis.com/auth/drive.readonly"]
SPREADSHEET_ID = "Spreadsheet ID goes here"
print(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES, sep="-")

cred = service_account.Credentials.from_service_account_file(
    CLIENT_SECRET_FILE, scopes=SCOPES
)

try:
    service = build(API_NAME, API_VERSION, credentials=cred)
    print(API_NAME, "service created successfully")
    result = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()
    export_url = re.sub("\/edit$", "/export", result["spreadsheetUrl"])
    authed_session = AuthorizedSession(cred)

    for sheet in result["sheets"]:
        sheet_name = sheet["properties"]["title"]
        params = {"format": "csv", "gid": sheet["properties"]["sheetId"]}
        query_params = urllib.parse.urlencode(params)
        url = export_url + "?" + query_params
        response = authed_session.get(url)

        file_path = "./Downloads/" + sheet_name + ".csv"
        with open(file_path, "wb") as csv_file:
            csv_file.write(response.content)
            print("Downloaded sheet: " + sheet_name)
    print("Downloads complete")
except Exception as e:
    print("Unable to connect")
    print(e)

Answer 1

此代码应该为您提供表格服务

"""Hello sheets."""

from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials


SCOPES = ['"https://www.googleapis.com/auth/drive.readonly']
KEY_FILE_LOCATION = '<REPLACE_WITH_JSON_FILE>'
VIEW_ID = '<REPLACE_WITH_VIEW_ID>'


def initialize_sheet():
  """Initializes an sheetservice object.

  Returns:
    An authorized sheetservice object.
  """
  credentials = ServiceAccountCredentials.from_json_keyfile_name(
      KEY_FILE_LOCATION, SCOPES)

  # Build the service object.
  sheet= build('sheet', 'v4', credentials=credentials)

  return sheet

如果您使用通过此方法构建的相同工作表服务，那么您不会有任何循环问题