Google adwords是很多外贸公司常用的推广方式之一,由于Google在搜索引擎领域的江湖地位,它的SEM还是很有效果的。不过很多新手在操作时,往往会出现这样那样的错误,流失的可都是白花花的银子啊。下面这10个错误是Goolgle adwords新手常犯的,要尽量避免。 1.使用Content Network (adsense) Content Network会带来可能欺诈性和非目标明确性的点击,所以开始时候要把Content…
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:
在出现的对话框中,单击“ 下载客户端配置”,然后将文件保存 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
- 该示例将尝试在默认浏览器中打开新窗口或标签。如果失败,请从控制台复制URL,然后在浏览器中手动打开它。如果尚未登录Google帐户,则会提示您登录。如果登录多个Google帐户,系统将要求您选择一个帐户进行授权。
- 单击接受按钮。
- 该示例将自动进行,您可以关闭窗口/选项卡。
个人实例:
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


基本用法
- 从Google Developers Console获取OAuth2凭据
- 开始使用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从电子表格中插入,更新和删除
我们只是从头开始研究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