如何用Python结合Google API遍历子文件夹并读取Google Sheets

我在Google Drive中有一个名为「main」的主文件夹，其下包含「A」「B」「C」三个子文件夹，子文件夹内存储着Google Sheets文件。我希望用Python结合Google API遍历这些子文件夹，并用Pandas读取其中的Google Sheets内容。目前我已经实现了读取文件夹（含主文件夹）信息的功能，但返回的文件夹相关信息冗余，不确定当前实现思路是否正确，恳请提供帮助。

现有代码 #

import openpyxl
import pandas as pd
import requests
from io import BytesIO
from Google import Create_Service

from httplib2 import Http
from oauth2client import file, client, tools
from getfilelistpy import getfilelist


SCOPES = 'https://www.googleapis.com/auth/drive.metadata.readonly'


store = file.Storage('token.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('../HP_assay/client_secret_file.json', SCOPES)
    creds = tools.run_flow(flow, store)

resource = {
    "oauth2": creds.authorize(Http()),
    "id": "1HkAYszdQa3OjTC59uQd8dbtXNjs1sV2R",
    "fields": "files(name,id)",
}
res = getfilelist.GetFileList(resource)  # or r = getfilelist.GetFolderTree(resource)
r = getfilelist.GetFolderTree(resource)
listing = list(list(res.items()))
print(listing)  

# columns = []
# for i in listing:
#     columns.append(i[0])
# resource_info = pd.DataFrame.from_dict(listing)

参考的Google服务授权代码 #

我参考官方示例实现了Create_Service函数，代码如下：

import pickle
import os
from google_auth_oauthlib.flow import Flow, InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload
from google.auth.transport.requests import Request
import datetime

def Create_Service(client_secret_file, api_name, api_version, *scopes):
    print(client_secret_file, api_name, api_version, scopes, sep='-')
    CLIENT_SECRET_FILE = client_secret_file
    API_SERVICE_NAME = api_name
    API_VERSION = api_version
    SCOPES = [scope for scope in scopes[0]]
    print(SCOPES)

    cred = None

    pickle_file = f'token_{API_SERVICE_NAME}_{API_VERSION}.pickle'
    # print(pickle_file)

    if os.path.exists(pickle_file):
        with open(pickle_file, 'rb') as token:
            cred = pickle.load(token)

    if not cred or not cred.valid:
        if cred and cred.expired and cred.refresh_token:
            cred.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)
            cred = flow.run_local_server()

        with open(pickle_file, 'wb') as token:
            pickle.dump(cred, token)

    try:
        service = build(API_SERVICE_NAME, API_VERSION, credentials=cred)
        print(API_SERVICE_NAME, 'service created successfully')
        return service
    except Exception as e:
        print('Unable to connect.')
        print(e)
        return None

def convert_to_RFC_datetime(year=1900, month=1, day=1, hour=0, minute=0):
    dt = datetime.datetime(year, month, day, hour, minute, 0).isoformat() + 'Z'
    return dt

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