🕰️ 👴🏽 ☮️ プログラムから美しいGoogleスプレッドシートを生成します（Google Sheets API v4を使用） 🆔 🧘 🛅

問題の声明

次のような Pythonでテーブルを作成する必要があるとします。

この表の特徴：

列の幅が指定されます。
一番上のセルは結合A1：E1です。
一部のセルでは、表示形式、フォントサイズ、太字、テキストの配置、背景色が設定されています。
最後の列の値は式によって計算されます（たとえば、 E4では= D4-C4と表示されます ）。
境界線はセルA3：E3の下に描画されます。
ピカチュウがいます（ただし、愛好家の宿題として残ります）。

面白い？それから猫にようこそ。

解決策

不適切なライブラリはすぐに破棄してください。たとえば、 gspread 。これは、 Google Sheets API v3のラッパーであり、テーブルデザインを設定する方法はありません 。列幅も設定できません。

Google Sheets API v4を使用します。

ステップ1.サービスアカウントを作成する

Google Developers Consoleに移動して、新しいプロジェクトを作成します（または既に存在するプロジェクトを使用します）。
このプロジェクトにDrive APIとSheets APIを含めます。
資格情報を作成し、秘密鍵を保存します。

ステップ2.必要なライブラリをインストールする

つまり、 google-api-python-client 。たとえば、 pipを使用してインストールできます。

pip install --upgrade google-api-python-client

このライブラリは、必要な依存関係（ oauth2clientなど）を描画します。

ステップ3.コーディング

3.1。サービスオブジェクト

必要なものをインポートします。

 import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials

Googleテーブルを操作するためのサービスオブジェクトを作成します。

 CREDENTIALS_FILE = 'test-proj-for-habr-article-1ab131d98a6b.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)

3.2。利用規約とid'shniki

ここで少しの間、用語について説明します。

スプレッドシートは、Google スプレッドシートドキュメントです。以下では、 ドキュメント （または英語名）を呼び出します。

1kygOW5wSSVqwf26M-OCT72i0FX0olZAz4duT2i6psp4

という形式のspreadsheetId

1kygOW5wSSVqwf26M-OCT72i0FX0olZAz4duT2i6psp4

ます。
sheetは、 スプレッド シート内のシートです。言い換えると、1つのテーブルを持つタブ（同じドキュメント内に複数ある場合があります）。

シートのシート sheetId

は数値です。ドキュメントで作成された最初のシートのidは0です。ドキュメントには常に少なくとも1つのシートがあります（削除することはできません）。すべてのシートには、異なるIDと異なる名前があります。
ワークシートに関する歴史的背景
古いAPIでは、ワークシートはworksheetと呼ばれます。 oowy6v0

ように見えるworksheetId

（またはwid

）があります。数値に変換するには、特別な倒錯が必要です：
```
 wid2sheetId = lambda wid: int(wid[1:] if len(wid) > 3 else wid, 36) ^ (474 if len(wid) > 3 else 31578)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
```

特定のシートへのリンクは次のように形成されます。

https://docs.google.com/spreadsheets/d/ spreadsheetId /edit#gid= sheetId

#gid= sheetId

を破棄すると、ドキュメントの最初のシートが参照によって開きます。

3.3。新しいスプレッドシート

コードに戻りましょう。作成したservice

オブジェクトには、 9つの関数しかありません。それらの1つはspreadsheets.createと呼ばれ、新しいスプレッドシートを作成します。スプレッドシートオブジェクトを引数として渡す必要があります。すべてのフィールドに入力する必要はありませんが、ほとんどのフィールドにはデフォルト値があります。

 spreadsheet = service.spreadsheets().create(body = { 'properties': {'title': '   ', 'locale': 'ru_RU'}, 'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': '   ', 'gridProperties': {'rowCount': 8, 'columnCount': 5}}}] }).execute()

応答として、スプレッドシートオブジェクトを再度取得します。さらに多くのパラメータがあります。

回答を見る

 {'properties': {'autoRecalc': 'ON_CHANGE', 'defaultFormat': {'backgroundColor': {'blue': 1, 'green': 1, 'red': 1}, 'padding': {'bottom': 2, 'left': 3, 'right': 3, 'top': 2}, 'textFormat': {'bold': False, 'fontFamily': 'arial,sans,sans-serif', 'fontSize': 10, 'foregroundColor': {}, 'italic': False, 'strikethrough': False, 'underline': False}, 'verticalAlignment': 'BOTTOM', 'wrapStrategy': 'OVERFLOW_CELL'}, 'locale': 'ru_RU', 'timeZone': 'Etc/GMT', 'title': '   '}, 'sheets': [{'properties': {'gridProperties': {'columnCount': 5, 'rowCount': 8}, 'index': 0, 'sheetId': 0, 'sheetType': 'GRID', 'title': '   '}}], 'spreadsheetId': '1Sfl7EQ0Yuyo65INidt4LCrHMzFI9wrmc96qHq6EEqHM'}

リクエストでそれらの多くを設定することは可能でしたが、現在の問題を解決するために、デフォルトのパラメーターに満足しています。

locale

パラメータは偶然ではなくru_RU

に設定されましたが、後で詳しく説明します。

答えには、 spreadsheetId

が含まれています。やった！作成されたドキュメントを目で見ていきますが、アクセスできないため中断します。読んでも。すべては、ユーザーが手動で作成した通常のGoogleスプレッドシートのようなものです。

そして誰がアクセスできますか？サービスアカウントで。

[アクセス許可のリクエスト]ボタンをクリックしますか？

自分でスパムしないでください。このボタンをクリックすると、 account@test-proj-for-habr-article.iam.gserviceaccount.comのようなメールにメールが送信されます。（ドメインが存在しないため）このレターを配信することはできません。また、レターの配信の失敗に関するメッセージがメールに送信されます。アクセスを発行するリンクは、所有者のアカウント、つまりサービスアカウントでログインしている場合にのみ機能するため、手紙の内容も役に立たない。

どうする？答えは明らかです。APIも使用してドキュメントへのアクセスを許可します。

まあ、または別のオプション

Googleドライブでドキュメントを手動で作成し、サービスアカウントにアクセスできます（つまり、 account@test-proj-for-habr-article.iam.gserviceaccount.comなどの電子メールにアクセス許可を手動で発行できます ）。次に、APIを介してこのドキュメントを操作します。

プログラムに多くの異なるドキュメントを作成する方法を教える必要があったため、このオプションは私には向いていませんでした。

3.4。新しいドキュメントへのアクセス

service

オブジェクトには、ドキュメントへのアクセスを設定する方法がありません。 Google Sheets APIにはありません。しかし、それは Google Drive API v3にあります。コードを書いています。

 driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) shareRes = driveService.permissions().create( fileId = spreadsheet['spreadsheetId'], body = {'type': 'anyone', 'role': 'reader'}, #      fields = 'id' ).execute()

このようなコードにより、参照による読み取りがすべてのユーザーに許可されます。 代わりにuser@example.comに編集アクセス権を与えたいとします。その代わりに

 {'type': 'anyone', 'role': 'reader'}

書きます

 {'type': 'user', 'role': 'writer', 'emailAddress': 'user@example.com'}

3.5。もう少し理論

列の幅を設定して、テーブルを始めましょう。ああ、そしてそのような機能はどこにありますか？すべてがそれほど透明ではなく、一部のsetColumnWidth

よりも少しスマートsetColumnWidth

。

関数sheetlets.batchUpdateがあります。彼女はすぐにドキュメントに一連の変更を適用します。より正確には、最初にバンドル全体の正確性をチェックします。すべてが正常である場合、アトミックにすべてを適用し、対応する結果のパケットを返します。この関数によって適用できる変更のリストはこちらです。

3.6。列幅

列の幅を設定するには、 UpdateDimensionPropertiesRequestを実行する必要があります。

コードを読む

 results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = { "requests": [ #    A: 317  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", # COLUMNS -    "startIndex": 0, #     "endIndex": 1 # startIndex  , endIndex -  , # ..        [0,1), ..    A }, "properties": { "pixelSize": 317 #    }, "fields": "pixelSize" #    pixelSize       } }, #    B: 200  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 2 }, "properties": { "pixelSize": 200 }, "fields": "pixelSize" } }, #    C  D: 165  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 2, "endIndex": 4 }, "properties": { "pixelSize": 165 }, "fields": "pixelSize" } }, #    E: 100  { "updateDimensionProperties": { "range": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 4, "endIndex": 5 }, "properties": { "pixelSize": 100 }, "fields": "pixelSize" } } ] }).execute()

それは非常に面倒で多くのコピー＆ペーストが判明しました。この段階で、Sheets APIに小さなラッパークラスを記述することにしました。これにより、必要なメソッドが便利に提供されます。

3.7。ラッパークラスロジック

ラッパークラス（ Spreadsheetと呼びましょう）にリクエストのリストを保存させ、 runPreparedメソッドでそれをsheetreaders.batchUpdate関数に渡して、クリアします。フォームprepare_respectiveQueryのメソッドは、このリストに要素を追加します。

これで、列の幅を設定するコードは次のようになります。

 # ss -    Spreadsheet ss.prepare_setColumnWidth(0, 317) ss.prepare_setColumnWidth(1, 200) ss.prepare_setColumnsWidth(2, 3, 165) ss.prepare_setColumnWidth(4, 100) ss.runPrepared()

次に、 prepare_setColumnWidthメソッドとprepare_setColumnsWidthメソッドのコードを示します。

 class Spreadsheet: # ... def prepare_setDimensionPixelSize(self, dimension, startIndex, endIndex, pixelSize): self.requests.append({"updateDimensionProperties": { "range": {"sheetId": self.sheetId, "dimension": dimension, "startIndex": startIndex, "endIndex": endIndex}, "properties": {"pixelSize": pixelSize}, "fields": "pixelSize"}}) def prepare_setColumnsWidth(self, startCol, endCol, width): self.prepare_setDimensionPixelSize("COLUMNS", startCol, endCol + 1, width) def prepare_setColumnWidth(self, col, width): self.prepare_setColumnsWidth(col, col, width)

runPreparedメソッドのコードには、他の何かで補充されるため、もう少し詳しく説明します。

3.8。セルにデータを入力する

セルに情報を入力するために、Google Sheets API v4には、sheetsheets.values.batchUpdate関数が用意されています。この関数は、 spreadsheets.batchUpdateと同じ原理で動作します。長方形のリストと、それぞれに書き込む必要がある値を受け取ります。さらに、 ValueInputOptionパラメーターを受け入れます。

USER_ENTERED

場合、データはユーザー入力として解釈されます。
RAW

場合、それらはいかなる方法でも解釈されず、未加工で保存されます。

最初のオプションが必要なのは、日付と数式を認識するためにテーブルが必要だからです。

これは、ラッパークラスを使用せずに、シート上のいくつかの長方形に記入する方法です。

 results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheet['spreadsheetId'], body = { "valueInputOption": "USER_ENTERED", "data": [ {"range": "   !B2:C3", "majorDimension": "ROWS", #   ,   (..     values -  ) "values": [["This is B2", "This is C2"], ["This is B3", "This is C3"]]}, {"range": "   !D5:E6", "majorDimension": "COLUMNS", #   ,   (..     values -  ) "values": [["This is D5", "This is D6"], ["This is E5", "=5+5"]]} ] }).execute()

このようなドキュメントを取得します。

ラッパークラスに、同じ結果を得るための便利なメソッドを提供させてみましょう。

runPreparedメソッドで関数sheetsheets.values.batchUpdate が呼び出され、 prepare_setValuesメソッドがvalueRangesリストに長方形とデータを追加すると、runPreparedが呼び出されると、sheets.values.batchUpdateに渡されます。

prepare_setValuesおよびrunPreparedメソッドのコード：

 class Spreadsheet: # ... def prepare_setValues(self, cellsRange, values, majorDimension = "ROWS"): self.valueRanges.append({"range": self.sheetTitle + "!" + cellsRange, "majorDimension": majorDimension, "values": values}) # spreadsheets.batchUpdate and spreadsheets.values.batchUpdate def runPrepared(self, valueInputOption = "USER_ENTERED"): upd1Res = {'replies': []} upd2Res = {'responses': []} try: if len(self.requests) > 0: upd1Res = self.service.spreadsheets().batchUpdate(spreadsheetId = self.spreadsheetId, body = {"requests": self.requests}).execute() if len(self.valueRanges) > 0: upd2Res = self.service.spreadsheets().values().batchUpdate(spreadsheetId = self.spreadsheetId, body = {"valueInputOption": valueInputOption, "data": self.valueRanges}).execute() finally: self.requests = [] self.valueRanges = [] return (upd1Res['replies'], upd2Res['responses'])

上記の例と同じ長方形のペアでデータを入力しますが、既にラッパークラスを使用しています。

 # ss -    Spreadsheet ss.prepare_setValues("B2:C3", [["This is B2", "This is C2"], ["This is B3", "This is C3"]]) ss.prepare_setValues("D5:E6", [["This is D5", "This is D6"], ["This is E5", "=5+5"]], "COLUMNS") ss.runPrepared()

3.9。セルの結合、太さの調整、表示形式、背景色など

誰も待ちきれません。すぐにSpreadsheetクラスの全コードとその使用例を読むことができます。これは記事の冒頭にある問題の解決策です。

より忍耐強い読者のために：

MergeCellsRequest-セルをマージします。

例

 #   A1:E1 {'mergeCells': {'range': {'sheetId': 0, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 5}, 'mergeType': 'MERGE_ALL'}} #       Spreadsheet ss.prepare_mergeCells('A1:E1')

RepeatCellRequest-指定された範囲内のすべてのセルに同じ変更を適用します。

例

 #        A3:E3 {'repeatCell': {'range': {'sheetId': 0, 'startRowIndex': 2, 'endRowIndex': 3, 'startColumnIndex': 0, 'endColumnIndex': 5}, 'cell': {'userEnteredFormat': {'horizontalAlignment': 'CENTER', 'textFormat': {'bold': True}}}, 'fields': 'userEnteredFormat'}} #       #   «»   E4:E8 {'repeatCell': {'range': {'sheetId': 0, 'startRowIndex': 3, 'endRowIndex': 8, 'startColumnIndex': 4, 'endColumnIndex': 5}, 'cell': {'userEnteredFormat': {'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}}}, 'fields': 'userEnteredFormat.numberFormat'}} #       #       Spreadsheet ss.prepare_setCellsFormat('A3:E3', {'horizontalAlignment': 'CENTER', 'textFormat': {'bold': True}}) ss.prepare_setCellsFormat('E4:E8', {'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}}, fields = 'userEnteredFormat.numberFormat')

UpdateCellsRequest-指定された範囲内の各セルに指定された変更を適用します。

例

 #      : B4 - , C4 - , B5 - , C5 -  {'updateCells': {'range': {'sheetId': 0, 'startRowIndex': 3, 'endRowIndex': 5, 'startColumnIndex': 1, 'endColumnIndex': 3}, 'rows': [{'values': [{'userEnteredFormat': {'backgroundColor': {'red': 1, 'green': 0, 'blue': 0}}}, {'userEnteredFormat': {'backgroundColor': {'red': 0, 'green': 1, 'blue': 0}}}]}, {'values': [{'userEnteredFormat': {'backgroundColor': {'red': 0, 'green': 0, 'blue': 1}}}, {'userEnteredFormat': {'backgroundColor': {'red': 1, 'green': 1, 'blue': 0}}}]}], 'fields': 'userEnteredFormat'}} #       Spreadsheet ss.prepare_setCellsFormats('B4:C5', [[{'backgroundColor': {'red': 1, 'green': 0, 'blue': 0}}, {'backgroundColor': {'red': 0, 'green': 1, 'blue': 0}}], [{'backgroundColor': {'red': 0, 'green': 0, 'blue': 1}}, {'backgroundColor': {'red': 1, 'green': 1, 'blue': 0}}]])

UpdateBordersRequest-セルの境界線を設定します。

例

 #     1  A3:E3 {'updateBorders': {'range': {'sheetId': 0, 'startRowIndex': 2, 'endRowIndex': 3, 'startColumnIndex': 0, 'endColumnIndex': 5}, 'bottom': {'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}}}

いくつかの微妙な点

Q1：では、なぜ（3.3で）ドキュメントを作成するときに、 locale

パラメータがru_RU

設定されてru_RU

ですか？

A1：実際には、この場合2 2016 17:57:52

形式の行がテーブルによって日時として認識されます。したがって、このようなセルを数式で使用して、（たとえば、2つの日付の差として）期間を計算できます。

Q2： 「duration」フォーマットは{'numberFormat': {'pattern': '[h]:mm:ss', 'type': 'TIME'}}

からどこに来ましたか？

A2：ドキュメントを調べることでこれに到達できます。しかし、表示形式「期間」を特定のセルに手動で設定した後、 spreadsheets.get関数を使用してドキュメントを受け取り、 includeGridData

パラメーターをTrue

に設定して、そのセルのnumberFormat

パラメーターの値をnumberFormat

ました。

Q3： sheetsheets.batchUpdate関数に渡されたリクエストでは、 range

パラメーターはGridRange形式です。

 { 'sheetId': , 'startRowIndex': , 'endRowIndex': , 'startColumnIndex': , 'endColumnIndex':  }

また、 spreadsheets.values.batchUpdate関数のデータ四角形では、 range

パラメーターは_!A5:E7

（ A1表記法）の形式の文字列です。変です。

A3：はい。おそらく誰かが記事へのコメントでその理由を説明するでしょう。

ラッパークラスでは、便宜上toGridRangeメソッドを作成しました。

Q4： 記事の冒頭のテーブルにいるピカチュウは、プログラムでそこに置かれましたか？

A4：いいえ、ピカチュウをテーブルに手動で配置しました。 Google Sheets API v4を使用してプログラムでこれを行うことができるかどうかはわかりませんが、適切な機能がすぐに見つかりませんでした。

Q5： Google Sheets API v4の使用に制限はありますか？

A5：はい、それらはクォータと呼ばれます。 Google Developers Consoleでそれらをフォローできます。クォータが十分でない場合は、リクエストを送信してクォータを増やすことができます。

おわりに

ここまで読んだことがある方は、プログラムでスプレッドシートを作成する方法を習得し、すべてのプロジェクトでGoogleテーブルを使用したいという思いに燃えていることでしょう。

最も重要なリンクを繰り返します。

プログラムから美しいGoogleスプレッドシートを生成します（Google Sheets API v4を使用）

問題の声明

解決策

ステップ1.サービスアカウントを作成する

ステップ2.必要なライブラリをインストールする

ステップ3.コーディング

3.1。 サービスオブジェクト

3.2。 利用規約とid'shniki

3.3。 新しいスプレッドシート

3.4。 新しいドキュメントへのアクセス

3.5。 もう少し理論

3.6。 列幅

3.7。 ラッパークラスロジック

3.8。 セルにデータを入力する

3.9。 セルの結合、太さの調整、表示形式、背景色など