Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using Python

1. 官网应用：

先决条件

要运行此快速入门，您需要：

• Python 2.6或更高版本
• 该PIP包管理工具
• Google帐号

步骤1：开启Google Sheets API

点击此按钮以创建新的Cloud Platform项目并自动启用Google Sheets API：

启用Google Sheets API

在出现的对话框中，单击“ 下载客户端配置”，然后将文件保存 credentials.json到您的工作目录中。

也可以参考： Python 读取gmail, Python 搜索gmail, Python操作gmail, How to access Gmail using Python

官网：https://developers.google.com/sheets/api/quickstart/python

步骤2：安装Google客户端库

运行以下命令以使用pip安装该库：

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

步骤3：设置样本

quickstart.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()

步骤4：运行示例

使用以下命令运行示例：

python quickstart.py

1. 该示例将尝试在默认浏览器中打开新窗口或标签。如果失败，请从控制台复制URL，然后在浏览器中手动打开它。如果尚未登录Google帐户，则会提示您登录。如果登录多个Google帐户，系统将要求您选择一个帐户进行授权。
2. 单击接受按钮。
3. 该示例将自动进行，您可以关闭窗口/选项卡。

个人实例：

from __future__ import print_function
import pickle
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',
          'https://www.googleapis.com/auth/drive',
          'https://www.googleapis.com/auth/drive.file']


# https://developers.google.com/sheets/api/quickstart/python
def get_credit():
    creds = None
    token_file = 'token_google_sheet'
    if os.path.exists(token_file):
        with open(token_file, 'rb') as token:
            creds = pickle.load(token)
    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)
        with open(token_file, 'wb') as token:
            pickle.dump(creds, token)

    return build('sheets', 'v4', credentials=creds).spreadsheets()


# SPREADSHEET_ID = '**************************'
# RANGE_NAME = 'sheet1!A14:B16'
# read_sheet(SPREADSHEET_ID, RANGE_NAME)
def read_sheet(spreadsheet_id, range_name):
    service = get_credit()
    result = service.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
    return result.get('values', [])


# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update?hl=zh-cn
# INPUT_VALUE_OPTION_UNSPECIFIED	默认输入值。不得使用该值。
# RAW	用户输入的值将不会被解析，并会按原样存储。
# USER_ENTERED	这些值将被解析，就像用户在UI中键入它们一样。数字将保留为数字，但是字符串可以按照通过Google表格用户界面在单元格中输入文本时所应用的相同规则转换为数字，日期等。
# SPREADSHEET_ID = '**************************'
# RANGE_NAME = 'sheet1!A14:B16'
# write_sheet(SPREADSHEET_ID, RANGE_NAME, [[1,2],[3,4],[5,6]])
def write_sheet(spreadsheet_id, range_name, values):
    service = get_credit()
    return service.values().update(spreadsheetId=spreadsheet_id, range=range_name,
                                   valueInputOption='RAW', body={'values': values}).execute()


def data_filter(spreadsheet_id, range_name, values):
    service = get_credit()
    request_body = {
        'requests': [],
    }
    return service.getByDataFilter(spreadsheetId=spreadsheet_id, body=request_body)


def create_sheet(title):
    spreadsheet = {'properties': {'title': title}}
    service = get_credit()
    spreadsheet = service.create(body=spreadsheet, fields='spreadsheetId').execute()
    # print('Spreadsheet ID: {0}'.format(spreadsheet.get('spreadsheetId')))
    return format(spreadsheet.get('spreadsheetId'))


# {'6': []}
def order_statuses(spreadsheet_id, range_name, order_id):
    order_ids = read_sheet(spreadsheet_id, range_name + '!B1:B')

    result = {}

    if order_ids and order_id:
        i = 1
        for row in order_ids:
            if row:
                key = str(i)
                row_value = row[0]
                # print(key+': '+row_value)
                if row_value == order_id:
                    result[key] = read_sheet(spreadsheet_id, range_name + '!AR' + key)

            i = i + 1

    return result


def order_row_num(spreadsheet_id, range_name, order_id):
    order_ids = read_sheet(spreadsheet_id, range_name + '!B1:B')

    key = 0

    if order_ids and order_id:
        i = 1
        for row in order_ids:
            if row:
                key = str(i)
                row_value = row[0]
                # print(key+': '+row_value)
                if row_value == order_id:
                    break

            i = i + 1

    # print(status)
    return key


def order_status(spreadsheet_id, range_name, order_id):
    key = order_row_num(spreadsheet_id, range_name, order_id)
    cell_status = read_sheet(spreadsheet_id, range_name + '!AR' + key)

    # print(status)
    return cell_status[0][0]

SPREADSHEET_ID = '××××××××××××××××××××××××××××××××'
RANGE_NAME = 'sheet1'
order_status(SPREADSHEET_ID, RANGE_NAME, '1360000016')
print(read_sheet(SPREADSHEET_ID, RANGE_NAME+'!AR6'))

更多可以参考官网文档： https://developers.google.com/sheets/api/quickstart/python

2.  第三方库 gspread：

使用Python中的gspread管理电子表格。

特征：

• Google Sheets API v4。
• 按标题或网址打开电子表格。
• 提取范围，整个行或列的值。
• Python 3支持。

安装

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

基本用法

1. 从Google Developers Console获取OAuth2凭据
2. 开始使用gspread：

   import gspread
   from oauth2client.service_account import ServiceAccountCredentials
   
   
   # use creds to create a client to interact with the Google Drive API
   scope = ['https://spreadsheets.google.com/feeds']
   creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
   client = gspread.authorize(creds)
   
   # Find a workbook by name and open the first sheet
   # Make sure you use the right name here.
   sheet = client.open("Copy of Legislators 2017").sheet1
   
   # Extract and print all of the values
   list_of_hashes = sheet.get_all_records()
   print(list_of_hashes)

   运行 python 电子表格。

   

    

使用Python从电子表格中插入，更新和删除

我们只是从头开始研究gspreads的详细文档和全面的功能。

例如，我们将数据提取到哈希列表中，但是如果愿意，您可以获取列表列表：

sheet.get_all_values()

sheet.row_values(1)

sheet.col_values(1)

sheet.cell(1, 1).value

sheet.update_cell(1, 1, "I just wrote to a spreadsheet using Python!")

row = ["I'm","inserting","a","row","into","a,","Spreadsheet","with","Python"]
index = 1
sheet.insert_row(row, index)

sheet.delete_row(1)

sheet.row_count

请查阅gspread API参考，以获取有关这些功能以及其他几十个功能的完整详细信息。

More Examples

Opening a Spreadsheet

# You can open a spreadsheet by its title as it appears in Google Docs
sh = gc.open('My poor gym results') # <-- Look ma, no keys!

# If you want to be specific, use a key (which can be extracted from
# the spreadsheet's url)
sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

# Or, if you feel really lazy to extract that key, paste the entire url
sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

Creating a Spreadsheet

sh = gc.create('A new spreadsheet')

# But that new spreadsheet will be visible only to your script's account.
# To be able to access newly created spreadsheet you *must* share it
# with your email. Which brings us to…

Sharing a Spreadsheet

sh.share('otto@example.com', perm_type='user', role='writer')

Selecting a Worksheet

# Select worksheet by index. Worksheet indexes start from zero
worksheet = sh.get_worksheet(0)

# By title
worksheet = sh.worksheet("January")

# Most common case: Sheet1
worksheet = sh.sheet1

# Get a list of all worksheets
worksheet_list = sh.worksheets()

Creating a Worksheet

worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")

Deleting a Worksheet

sh.del_worksheet(worksheet)

Getting a Cell Value

# With label
val = worksheet.acell('B1').value

# With coords
val = worksheet.cell(1, 2).value

Getting All Values From a Row or a Column

# Get all values from the first row
values_list = worksheet.row_values(1)

# Get all values from the first column
values_list = worksheet.col_values(1)

Getting All Values From a Worksheet as a List of Lists

list_of_lists = worksheet.get_all_values()

Finding a Cell

# Find a cell with exact string value
cell = worksheet.find("Dough")

print("Found something at R%sC%s" % (cell.row, cell.col))

# Find a cell matching a regular expression
amount_re = re.compile(r'(Big|Enormous) dough')
cell = worksheet.find(amount_re)

Finding All Matched Cells

# Find all cells with string value
cell_list = worksheet.findall("Rug store")

# Find all cells with regexp
criteria_re = re.compile(r'(Small|Room-tiering) rug')
cell_list = worksheet.findall(criteria_re)

Cell Object

Each cell has a value and coordinates properties.

value = cell.value
row_number = cell.row
column_number = cell.col

Updating Cells

worksheet.update_acell('B1', 'Bingo!')

# Or
worksheet.update_cell(1, 2, 'Bingo!')

# Select a range
cell_list = worksheet.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
worksheet.update_cells(cell_list)

本文：Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using Python