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

 

Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using Python
Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using Python
Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using Python
Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using 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

 

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

 

基本用法

  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 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using Python
    Python 操作 google sheet, Python 读写 Google Sheets, Accessing Google Spreadsheet Data using 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参考,以获取有关这些功能以及其他几十个功能的完整详细信息。

将Google Spreadsheets与Python结合使用,可以创建诸如以电子表格作为持久层的Flask应用程序,或者将Google电子表格中的数据导入Jupyter Notebooks并在Pandas中进行分析。

 

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

 

Leave a Reply