Python获取Google sheet中的内容

官方文档参考：https://developers.google.com/sheets/api

选择python即可：https://developers.google.com/sheets/api/quickstart/python

 

环境搭建

前提条件

官方说明：To run this quickstart, you'll need:

• Python 2.6 or greater
• The pip package management tool
• A Google account

（简单而言，就是需要python 2.6以上版本，安装第三方库，以及一个google账号）

启用GoogleAPI

点击官方文档的蓝色button，按图示往后走，下载凭据后，将凭据存到本地py文件同一目录。

安装Google库

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

官方示例

py文件粘贴如下代码，并且运行：

from __future__ import print_function
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'

def main():
    """Shows basic usage of the Sheets API.
    Prints values from a sample spreadsheet.
    """
    creds = None
    # The file token.pickle stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    # If there are no (valid) credentials available, let the user log in.
    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.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)

    service = build('sheets', 'v4', credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                range=SAMPLE_RANGE_NAME).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s, %s' % (row[0], row[4]))

if __name__ == '__main__':
    main()

运行后会跳转浏览器，并且提醒是否登录google账号，点击登录。就会跳转到一个网页，提示：The authentication flow has completed. You may close this window.

表示认证成功，再次运行程序，就会看到运行结果了。

Name, Major:
Alexandra, English
Andrew, Math
Anna, English
Becky, Art

 

官方示例

自己使用时，只需要修改这两个参数：

SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'

SAMPLE_SPREADSHEET_ID：Google spreadsheet的ID
SAMPLE_RANGE_NAME ：sheet名字!表中选择区域

以一个googlesheet来示例：

https://docs.google.com/spreadsheets/d/1zltm9VhprbZg35P2IvAIiR6dC_Gg5sJrXrOuCKR6BpE/edit#gid=544091473

红色为Google spreadsheetID，紫色为google sheetID

 

常见错误

如果出现下述错误：

/Users/alice/venv/untitled/bin/python /Users/alice/rainbow/getgoogle/get_google_sheet.py
Traceback (most recent call last):
  File "/Users/alice/rainbow/getgoogle/get_google_sheet.py", line 55, in <module>
    main()
  File "/Users/alice/rainbow/getgoogle/get_google_sheet.py", line 43, in main
    range=SAMPLE_RANGE_NAME).execute()
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/googleapiclient/_helpers.py", line 134, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/googleapiclient/http.py", line 915, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 404 when requesting https://sheets.googleapis.com/v4/spreadsheets/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2us/values/Class%20Data%21A2%3AE?alt=json returned "Requested entity was not found.". Details: "Requested entity was not found.">

Process finished with exit code 1

访问相应的表格连接：https://sheets.googleapis.com/v4/spreadsheets/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2us/values/Class%20Data%21A2%3AE?alt=json，会提示：

{
  "error": {
    "code": 403,
    "message": "The request is missing a valid API key.",
    "status": "PERMISSION_DENIED"
  }
}

那说明是表格ID或者是range有错误，排查修改下就好了哈。