Geekly articles weekly

Python中的Google表格入门。 从注册到读取数据

在相当长的一段时间里，我设法将数据上传到Excel，但是mod发生了变化，用户希望将其存储在云端。

开始将许多项目转换为Python之后，我决定是时候使用更现代的功能来更改（或添加）Excel了。

当我第一次需要使用Python处理Google电子表格时，我幻想可以通过单击几下就可以完成所有这些工作。 事实证明事实并不那么乐观，但是我们没有其他的世界。

这些文章对我有很大帮助：


像往常一样-当您第一次尝试做某事时，您会遇到大量问题，这些问题后来只会引起困惑-任何人都怎么会想到这一点。 小学的！

也许我走了很长一段路-如果您纠正我，我将很高兴。

所有操作均在运行Windows + Python 3.6.6的计算机上执行，并且还使用了Jupyter Notebook。

我在初步设置阶段遇到的主要困难。 查找可行的代码并不困难。

存储库中提供了本文中使用的代码。

注册Google服务并安装库


要使用表格，您需要向Google注册，设置项目并安装必要的库。

英文的官方文档在这里 。

首先，您需要在gmail.com上注册（您可以自己注册）。 然后，您需要创建一个项目（因为Google提供了对其服务的访问权限）。

这是一个漫长而乏味的过程，可让您了解为什么称Google界面不是最方便和直观的原因（有些人认为Google+社交网络并没有因此而腾飞）。

为此，请转到console.developers.google.com/cloud-resource-manager并单击“创建项目”



输入项目名称，然后单击“创建”



在更新的项目列表中，转到菜单“权限”


在打开的窗口中，单击“添加”，从gmail.com域中输入您的电子邮件地址，然后选择“项目”-“所有者”组



保存更改。

您创建了一个项目，但被迫向您自己授予权利似乎有些奇怪。 这实际上很奇怪，但这是编写本课程时必须走的一条路，以便一切都能按预期开始。

再次访问console.developers.google.com/cloud-resource-manager

选择项目上的“设置”菜单



在打开的窗口中，选择“服务帐户”，然后选择“创建服务帐户”



输入帐户名称，然后单击“创建”



选择所有者角色，然后单击继续。



在出现的窗口中，单击“创建密钥”



选择json键类型，然后单击创建



具有密钥的文件将被创建并立即下载。 保存，多亏了它，我们将能够访问Google服务。

单击带有三个水平笔划的按钮，在题词“ Google API”的左侧，选择“ API and Services”，然后在其中子项“ Control Panel”。



在打开的窗口中，单击“启用API和服务”



在搜索栏中输入“ google drive”，然后单击“ Google Drive API”服务



点击启用



该网站将通知您API已启用，并警告您必须创建凭据。 忽略此警告（我们已经创建了服务帐户）。



返回控制面板



在打开的窗口中，单击“启用API和服务”



在搜索栏中输入“表格”，然后点击“ Google Sheets API”服务



确保已连接此API。 当您连接Google云端硬盘API时，它应该会自动打开。 如果已连接，您将看到“管理API”按钮，如果没有，则将看到“启用”按钮。 如有必要，将其打开。

上一次，访问console.developers.google.com/cloud-resource-manager

选择项目上的“设置”菜单


在打开的窗口中，选择“服务帐户”，然后复制并保存该服务帐户的电子邮件地址。 这将使您可以方便地访问表。



现在我们继续安装库。 在控制台中运行命令

pip3 install --upgrade google-api-python-client

然后

 pip3 install oauth2client

运行第二个命令时，可能会收到一条消息，提示已安装oauth2client库。

转到raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py

按下鼠标右键，然后选择“另存为”



将文件另存为quickstart.py

并使用命令运行它

 python quickstart.py

将在浏览器中打开一个新页面（也许会说该页面不安全，但是大胆地进行下去），您将必须接受条件。

至此，我们的道路已经完成。

填写表格并设置其格式


创建第一个表

 #   import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials CREDENTIALS_FILE = 'seraphic-effect-248407-7ac2c44ec709.json' #     ,     #     credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']) httpAuth = credentials.authorize(httplib2.Http()) #    service = apiclient.discovery.build('sheets', 'v4', http = httpAuth) #      4  API spreadsheet = service.spreadsheets().create(body = { 'properties': {'title': '  ', 'locale': 'ru_RU'}, 'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': '  ', 'gridProperties': {'rowCount': 100, 'columnCount': 15}}}] }).execute() spreadsheetId = spreadsheet['spreadsheetId'] #    print('https://docs.google.com/spreadsheets/d/' + spreadsheetId)

如果一切顺利，将显示指向该表的链接。

在此链接中使用了文件标识符，我们将其保存在变量电子表格ID中，以后将使用它。

跟随它。 Google会通知您您无权访问



不请求许可！ 您将收到一条通知，通知您无法将请求信发送到Google自己分配给系统帐户的地址。 但是您不能更改此地址。 也许这仅在自由模式下不起作用。

但是我们可以让自己通过Google云端硬盘访问。 您需要用自己的替换my_test_address@gmail.com。

 driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) #    Google Drive  3  API access = driveService.permissions().create( fileId = spreadsheetId, body = {'type': 'user', 'role': 'writer', 'emailAddress': 'my_test_address@gmail.com'}, #     fields = 'id' ).execute()

现在您可以访问了，不要关闭表，我们将对其进行管理并立即查看更改。

每个文档都有自己的代码-电子表格ID-正是我们在浏览器中打开表格时在地址栏中显示的代码（在带有已打开表格的页面的网址中，它位于“ https://docs.google.com/spreadsheets/d/”之间和“ / edit＃gid = 0”）。

我们将其保存在变量电子表格ID中，并将继续使用它。

首先，一点理论。

在每个文件（电子表格）中，都有工作表标签（工作表）。

每个工作表都有自己的数字代码（sheetId）。 在文档中创建的第一张图纸的ID等于0。其余的图纸具有非常不同的非零ID（也就是说，它们没有连续编号）。

确保这一点

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "addSheet": { "properties": { "title": "  ", "gridProperties": { "rowCount": 20, "columnCount": 12 } } } } ] }).execute() #   ,  Id   spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute() sheetList = spreadsheet.get('sheets') for sheet in sheetList: print(sheet['properties']['sheetId'], sheet['properties']['title']) sheetId = sheetList[0]['properties']['sheetId'] print('     Id = ', sheetId)

屏幕上会出现这样的内容：

0第一张纸
415832263另一张纸
我们将使用Id = 0的工作表


实际上，第一张纸的ID为零，第二张纸的编号不同。

另一个问题：如何指定单元格范围。 显然，Google电子表格是由不同的团队在不同的经理的指导下并在不同的架构师的帮助下开发的。 因为单元的坐标以两种不同的方式设置。

选项1：采用文本格式“第一页！B2：D5”，即 工作表的名称，后跟一个感叹号，位于-左上方的单元格，格式为“字母（列）+数字（字符串）” +右下方的单元格，格式相同。

 {"range": "  !B2:D5"}

选项2：采用json格式，以数字形式（行号和列号）指示工作表ID和左上和右下单元格的坐标

 {"range": { "sheetId": sheetId, # ID  "startRowIndex": 1, #    startRowIndex "endRowIndex": 5,#  endRowIndex - 1 (endRowIndex  !) "startColumnIndex": 0, #    startColumnIndex "endColumnIndex": 1 #  endColumnIndex - 1 }}

不同的功能使用不同的格式。

现在我们已经足够了解如何用数据填充单元格，绘制框架并突出显示标题。

 results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B2:D5", "majorDimension": "ROWS", #   ,   "values": [ [" B2", " C2", " D2"], #    ['25', "=6*6", "=sin(3,14/2)"] #    ]} ] }).execute()

用数据填充几个单元格。 因为 如果指定了USER_ENTERED参数，则表将像接收用户输入一样来感知该数据-将数值转换为数字，并将以等号开头的值转换为公式。

看你的桌子，里面装满了数据



设置列宽。 batchUpdate函数可以一次接受多个命令，因此我们将通过一个请求设置三组列的宽度。 第一组和第三组中有一个列，第二组中有两列。

 results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheetId, body = { "requests": [ #    A: 20  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", #    "startIndex": 0, #     "endIndex": 1 #    startIndex  endIndex - 1 (endIndex  !) }, "properties": { "pixelSize": 20 #    }, "fields": "pixelSize" # ,     pixelSize } }, #    B  C: 150  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 3 }, "properties": { "pixelSize": 150 }, "fields": "pixelSize" } }, #    D: 200  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 3, "endIndex": 4 }, "properties": { "pixelSize": 200 }, "fields": "pixelSize" } } ] }).execute()

查看表，列宽已更改。



在桌子周围画一个框架

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'updateBorders': {'range': {'sheetId': sheetId, 'startRowIndex': 1, 'endRowIndex': 3, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'bottom': { #      'style': 'SOLID', #   'width': 1, #  1  'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, #   'top': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'left': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'right': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerHorizontal': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerVertical': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}} }} ] }).execute()



合并表格上方的单元格，然后在其中输入标题。

 #   A2:D1 results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'mergeCells': {'range': {'sheetId': sheetId, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'mergeType': 'MERGE_ALL'}} ] }).execute() #    results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B1", "majorDimension": "ROWS", #   ,   "values": [[" " ] ]} ] }).execute()



设置表格标题单元格的格式

 #    results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "repeatCell": { "cell": { "userEnteredFormat": { "horizontalAlignment": 'CENTER', "backgroundColor": { "red": 0.8, "green": 0.8, "blue": 0.8, "alpha": 1 }, "textFormat": { "bold": True, "fontSize": 14 } } }, "range": { "sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 1, "endColumnIndex": 4 }, "fields": "userEnteredFormat" } } ] }).execute()



有一种简单的方法可以找出需要为单元格设置的宽度或颜色。 为此，手动格式化其中一个单元格并读取其属性就足够了。

 ranges = ["  !C2:C2"] # results = service.spreadsheets().get(spreadsheetId = spreadsheetId, ranges = ranges, includeGridData = True).execute() print(' ') print(results['properties']) print('\n  ') print(results['sheets'][0]['data'][0]['rowData'] ) print('\n ') print(results['sheets'][0]['data'][0]['rowMetadata']) print('\n ') print(results['sheets'][0]['data'][0]['columnMetadata'])

我们得到回应
主数据
{'title'：'第一个测试文档'，'locale'：'ru_RU'，'autoRecalc'：'ON_CHANGE'，'timeZone'：'Etc / GMT'，'defaultFormat'：{'backgroundColor'：{'red' ：1，'green'：1，'blue'：1}，'padding'：{'top'：2，'right'：3，'bottom'：2，2，'left'：3}，'verticalAlignment'： 'BOTTOM'，'wrapStrategy'：'OVERFLOW_CELL'，'textFormat'：{'foregroundColor'：{}，'fontFamily'：'arial，sans，sans-serif'，'fontSize'：10，'bold'：False， '斜体'：错误，'删除线'：错误，'下划线'：错误}}}

价值观和色彩
[{'values'：[{'userEnteredValue'：{'stringValue'：'Cell C2'}，'effectiveValue'：{'stringValue'：'Cell C2'}，'formattedValue'：'Cell C2'，'userEnteredFormat' ：{'backgroundColor'：{'red'：1，'green'：0.6}，'horizo​​ntalAlignment'：'CENTER'，'textFormat'：{'fontSize'：14，'bold'：True，'italic'：True }}，'effectiveFormat'：{'backgroundColor'：{'red'：1，'绿色'：0.6}，'padding'：{'top'：2，'right'：3，'bottom'：2，'左'：3}，'horizo​​ntalAlignment'：'CENTER'，'verticalAlignment'：'BOTTOM'，'wrapStrategy'：'OVERFLOW_CELL'，'textFormat'：{'frontColorColor'：{}，'fontFamily'：'Arial'， 'fontSize'：14，'bold'：True，'italic'：True，'strikethrough'：False，'underline'：False}，'hyperlinkDisplayType'：'PLAIN_TEXT'}}}}]]]]]]]]]]]]

单元高度
[{'pixelSize'：21}]

像元宽度
[{'pixelSize'：150}]
此代码将显示单元格C2的属性。 您可以手动选择字体和填充颜色（在表中），然后查看它们在json中的体现。

从表中读取数据


为了充分揭示数据读取的功能，我如图所示手动填写了单元格B4，C7和D5。



读取数据的代码

 ranges = ["  !A2:F8"] # results = service.spreadsheets().values().batchGet(spreadsheetId = spreadsheetId, ranges = ranges, valueRenderOption = 'FORMATTED_VALUE', dateTimeRenderOption = 'FORMATTED_STRING').execute() sheet_values = results['valueRanges'][0]['values'] print(sheet_values)

结果
[[“，'Cell B2'，'Cell C2'，'Cell D2']，[”，'25'，'36'，'0,9999996829']

函数的一些参数： valueRenderOption-读取数字数据的格式。

  • FORMATTED_VALUE-基于显示格式的读数。 即 表格中可见的内容将被读取。 例如，在单元格D3中，数字为0.9999999，但是格式为“两位小数”，因此显示为“ 1.00”，它将以这种格式读取。
  • UNFORMATTED_VALUE-读取单元格的内容，不考虑格式设置（即将读取0.9999999）
  • 公式 -显示公式（在这种情况下，“ = sin（3,14 / 2）”。）如果在单元格中输入了数字，则在此模式下将被读取。

此代码读取数据并逐行显示。 可读范围A2：F8。
如屏幕上所示：

  • 如果正在读取的行中没有单元格被填充，则不会显示该行的数据。
  • 最后填充的单元格之后的数据不会显示。

Source: https://habr.com/ru/post/zh-CN483302/

More articles:


All Articles