Geekly Articles each Day

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

問題の声明


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

画像

この表の特徴：


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

解決策


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

Google Sheets API v4を使用します 。

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


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



ステップ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


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


特定のシートへのリンクは次のように形成されます。
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パラメーターを受け入れます。


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

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

 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クラスの全コード とその使用例を 読むことができます 。これは記事の冒頭にある問題の解決策です。

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


いくつかの微妙な点


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テーブルを使用したいという思いに燃えていることでしょう。

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

Source: https://habr.com/ru/post/J305378/

More articles:


All Articles