使用Python下载超大Google Spreadsheet至XLSX时遇Error 503求助

我需要下载一个超大Google Spreadsheet，该表格包含15个工作表，每个表有15000行、20列。我们需要保留格式（如合并单元格、颜色等），但当前首要问题是下载失败。

我用ChatGPT生成了以下Python代码（Python版本3.10.12），但即便设置批量大小为5仍无法运行，程序持续卡在获取数据步骤，反复出现Error 503（API服务器不可用）错误。我推测是文件过大导致，而非谷歌服务器故障，已经尝试减小批量、增加等待时间等方法，仍未解决问题。

我们只需要成功下载该文档，也可考虑非Python方案，希望有类似经验者提供帮助。

import time
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from google.oauth2 import service_account
from googleapiclient.discovery import build
import gspread

# Set the path to your service account JSON file
SERVICE_ACCOUNT_FILE = 'credentials.json'

# Set the ID of the Google Spreadsheet
SPREADSHEET_ID = 'my_spreadsheet_Id'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets', "https://www.googleapis.com/auth/drive", 'https://www.googleapis.com/auth/drive.file']

def download_spreadsheet():
    # Authenticate using the service account credentials
    creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    client = gspread.authorize(creds)

    # Open the Google Spreadsheet
    spreadsheet = client.open_by_key(SPREADSHEET_ID)
    print('Spreadsheet opened successfully.')

    # Create a new workbook
    workbook = Workbook()

    # Fetch data for each sheet
    worksheets = spreadsheet.worksheets()
    for worksheet in worksheets:
        sheet_title = worksheet.title
        print(f'Fetching values for sheet: {sheet_title}...')

        # Get the total number of rows and columns in the sheet
        total_rows = worksheet.row_count
        total_cols = worksheet.col_count

        # Set the batch size
        batch_size = 5

        # Calculate the number of batches for rows and columns
        num_row_batches = (total_rows - 1) // batch_size + 1
        num_col_batches = (total_cols - 1) // batch_size + 1

        # Create a new sheet in the workbook
        ws = workbook.create_sheet(title=sheet_title)

        # Fetch data in batches
        for row_batch in range(num_row_batches):
            start_row = row_batch * batch_size + 1
            end_row = min(start_row + batch_size - 1, total_rows)

            for col_batch in range(num_col_batches):
                start_col = col_batch * batch_size + 1
                end_col = min(start_col + batch_size - 1, total_cols)

                # Fetch values for the current batch, KEEPS BEING STUCK HERE
                print(f'Fetching values for row batch {row_batch+1}/{num_row_batches}, col batch {col_batch+1}/{num_col_batches}...')
                range_str = f'{sheet_title}!{chr(start_col + 64)}{start_row}:{chr(end_col + 64)}{end_row}'
                values = worksheet.get(range_str)
                print(f'Values fetched successfully for row batch {row_batch+1}/{num_row_batches}, col batch {col_batch+1}/{num_col_batches}.')

                # Convert the values list to a DataFrame
                df = pd.DataFrame(values)

                # Append the DataFrame to the worksheet
                for row in dataframe_to_rows(df, index=False, header=False):
                    ws.append(row)

                # Pause for a few seconds to avoid rate limiting
                time.sleep(10)

        print(f'Values fetched successfully for sheet: {sheet_title}.')

        # Fetch formatting for the entire sheet
        format_rules = get_all_conditional_formatting(spreadsheet, sheet_title)

        # Apply formatting to the new sheet
        apply_formatting(ws, format_rules)

    # Remove the default sheet created by openpyxl
    workbook.remove(workbook['Sheet'])

    # Save the workbook as an Excel file
    output_path = '24234.xlsx'
    workbook.save(output_path)

    print(f'Data downloaded successfully and saved as {output_path}')

if __name__ == '__main__':
    download_spreadsheet()

内容的提问来源于stack exchange，提问作者felixnoctuae