新しいGoogle Cloudプロジェクトを作成する

まず、Google Cloudプロジェクトを作成する必要があります。ブラウザでhttps://console.cloud.google.comに移動し、ユーザ名とパスワードを入力して自分のGoogleアカウントにサインインしてください。ようこそページに移動するはずです。そのページの画面上部の「プロジェクトの選択」をクリックしてください。ポップアップウィンドウが表示されますから、「新しいプロジェクト」をクリックします。新しいプロジェクトのページに移動するはずです。

Google Cloudでは、“My Project 23135”のようなプロジェクト名と“macro-nuance-362516”のようなプロジェクトIDが作成されます。これらの名前はPythonスクリプトを使うときに見えませんが、プロジェクト名は好きなように変更できます（プロジェクトIDは変更できません）。私はウェブサイトが作成したデフォルトの名前をそのまま使います。場所は「組織なし」のままで構いません。無料のGoogleアカウントが作成できるプロジェクトは12個までですが、本書のすべてのPythonスクリプトに1つのプロジェクトで対応できます。青色の「作成」ボタンをクリックしてプロジェクトを作成してください。

Google Sheets APIとGoogle Drive APIを有効化する

https://console.cloud.google.comで、左上のナビゲーションメニューボタンをクリックします（水平の三本線のアイコンで、しばしばハンバーガーアイコンと呼ばれるものです）。 APIとサービス  ライブラリと進み、APIのライブラリページに移動します。Gmail、Google Maps、Google Cloud Storage、その他のGoogleのサービスのGoogle APIが多数表示されます。このプロジェクトでGoogle Sheets APIとGoogle Drive APIを使えるようにする必要があります。EZSheetsはスプレッドシートファイルのアップロードとダウンロードにGoogle Drive APIを使います。

下にスクロールしてGoogle Sheets APIを探してクリックするか、検索バーにGoogle Sheets APIと入力して見つけます。Google Sheets APIのページに移動するので、青色の「有効にする」ボタンをクリックしてGoogle CloudプロジェクトでGoogle Sheets APIを使えるようにします。APIとサービスAPI / サービスの詳細ページにリダイレクトされ、そこでこのAPIの使用頻度の情報を確かめられます。Google Drive APIについても同じ手順で有効にしてください。

次に、このプロジェクトのOAuth同意画面を設定します。

OAuth同意画面を設定する

import ezsheetsの初回実行時にOAuth同意画面が表示されます。左上のナビゲーションメニューボタンから「OAuth同意画」をクリックして、青色の「開始」ボタンを押してください。OAuth同意画面の第一段階のアプリ情報では、アプリ名（私は“Python Google API Script”のような一般的な名前にしています）と、ユーザーサポートメールにメールアドレスを入力し、「次へ」ボタンを押します。 第二段階の対象では、外部を選んで「次へ」ボタンを押します。 第三段階の連絡先情報では、自分のメールアドレスを入力して、「次へ」ボタンを押します。 第四段階の終了では、Google API サービス: ユーザーデータに関するポリシーに同意しますというチェックボックスにチェックを入れて、「続行」ボタンを押します。 最後に青色の「作成」ボタンをクリックします。

次に、このプロジェクトのスコープ、すなわちプロジェクトがどのリソースを使えるかの権限を定義します。左サイドバーの「データアクセス」をクリックして、「スコープを追加または削除」ボタンを押します。新しいパネルが表示されるので、.../auth/drive (the Google Drive API)と.../auth/spreadsheets (the Google Sheets API)のチェックボックスにチェックを入れます。それから青色の「更新」ボタンをクリックして、「Save」ボタンをクリックします。

最後に、対象ページで、Pythonのスクリプトから操作するスプレッドシートを所有しているGoogleアカウントのGmailアドレスを追加します。Googleのアプリ承認プロセスを通過しなければ、スクリプトが操作できるのはここでメールアドレスを追加したものに限られます。左サイドバーの対象をクリックして、「Add users」ボタンを押します。新しく表示されるパネルで自分のGoogleアカウントのGmailアドレスを入力し、青色の「保存」ボタンをクリックします。

認証情報を作成する

認証情報ファイルを作成する必要があります。EZSheetsがGoogleのAPIを利用するにはこの認証情報ファイルが必要です。誰でも見られる公開状態で共有されているスプレッドシートであってもそうです。左上のナビゲーションメニューボタンからAPIとサービス、認証情報とたどると、認証情報のページに移動します。画面上部の「認証情報を作成」リンクをクリックします。APIキー、OAuthクライアントID、サービスアカウントのうちどの種類の認証情報を作成するかを尋ねるサブメニューが表示されるので、OAuthクライアントIDをクリックします。

次のページでは、アプリケーションの種類でデスクトップアプリを選び、名前はデフォルトのデスクトップ クライアント: 1のままにします。お望みなら別の名前に変更できます。この名前はPythonスクリプトで表示されません。青色の「作成」ボタンをクリックします。

ポップアップウィンドウが表示されます。JSONをダウンロードをクリックして認証情報ファイルをダウンロードします。client_secret_282792235794-p2o9gfcub4htibfg2u207gcomco9nqm7.apps.googleusercontent.com.jsonのような名前のファイルです。このファイルをPythonスクリプトと同じフォルダ内に置きます。credentials-sheets.jsonといったシンプルな名前に変更してもよいです。EZSheetsは、credentials-sheets.jsonという名前のファイルか、client_secret_*.jsonという形の名前のファイルを探します。

認証情報ファイルでログインする

認証情報JSONファイルと同じフォルダでPythonの対話型シェルを実行し、import ezsheetsを実行してください。EZSheetsは自動的にezsheets.init() 関数を呼び出し、現在の作業フォルダを確認して、認証情報JSONファイルを探します。ファイルが見つかれば、EZSheetsはウェブブラウザを立ち上げてOAuth同意画面を表示し、トークンファイルを作成します。EZSheetsは、スプレッドシートにアクセスするのに、認証情報JSONファイルに加えて、token-drive.pickleとtoken-sheets.pickleという名前のこれらのトークンファイルも必要とします。トークンファイルの作成は一度だけで、次にimport ezsheetsを実行したときには新しくトークンファイルが作成されることはありません。

（ブラウザでOAuth同意画面が表示されたら）自分のGoogleアカウントでサインインします。Google CloudプロジェクトのOAuth同意画面でテストユーザーに追加したのと同じメールアドレスでサインインしてください。「このアプリは Googleで確認されていません」という警告メッセージが表示されますが、自分が作成したアプリですから、気にしないでください。「続行」をクリックします。「Python Google API Script が Google アカウントへのアクセスを求めています」のように書かれている別のページに移動するはずですから（OAuth同意画面で入力したアプリ名が表示されます）、「続行」をクリックします。「The authentication flow has completed. You may close this window.」とだけ書かれたページが表示されます。もうブラウザウィンドウを閉じても構いません。

Sheets APIの認証フローが完了したら、次に開くウィンドウでDrive APIの同じ認証フローを繰り返します。2回目のウィンドウを閉じると、token-drive.pickleとtoken-sheets.pickleファイルが同じフォルダにあるはずです。これらのファイルはパスワードと同じように扱い、共有しないでください。これらのファイルを使ってログインしてGoogleスプレッドシートにアクセスできてしまいます。

認証情報ファイルを失効させる

認証情報ファイルまたはトークンファイルを共有してしまったら、そのファイルを入手した人が、Googleアカウント本体のパスワードの変更はできないとしても、スプレッドシートにアクセスできてしまいます。https://console.developers.google.comにログインすればそのファイルを失効させられます。サイドバーの認証情報のリンクをクリックします。OAuth 2.0 クライアント IDの表の中にある、共有した認証情報ファイルの横のゴミ箱のアイコンをクリックします。一度失効させると、その認証情報ファイルとトークンファイルは使えなくなります。新しい認証JSONファイルとトークンファイルをもう一度作成します。

スプレッドシートの作成、更新、一覧表示

既存のGoogleスプレッドシートからでも、新しい空白のスプレッドシートからでも、アップロードしたExcelファイルからでも、新しいSpreadsheetオブジェクトを作成できます。すべてのGoogleスプレッドシートには固有のIDがあり、URL中のspreadsheets/d/の後で/editの前の部分に含まれています。例えば、https://docs.google.com/spreadsheets/d/1TzOJxhNKr15tzdZxTqtQ3EmDP6em_elnbtmZIcyu8vI/edit#gid=0/というURLなら、IDは1TzOJxhNKr15tzdZxTqtQ3EmDP6em_elnbtmZIcyu8vIです。

Googleスプレッドシートはezsheets.Spreadsheetオブジェクトで表されます。id、url、titleの属性があります。Spreadsheet()関数で新しい空白のスプレッドシートを作成できます。

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet()
>>> ss.title = 'Title of My New Spreadsheet'
>>> ss.title
'Title of My New Spreadsheet'
>>> ss.url
'https://docs.google.com/spreadsheets/d/1gxz-Qr2-RNtqi_d7wWlsDlbtPLRQigcEXvCtdVwmH40/'
>>> ss.id
'1gxz-Qr2-RNtqi_d7wWlsDlbtPLRQigcEXvCtdVwmH40'

IDまたはURL（スプレッドシートのURLにリダイレクトするURLも可）を渡すことで、既存のスプレッドシートをロードすることもできます。

>>> import ezsheets
>>> ss1 = ezsheets.Spreadsheet('https://autbor.com/examplegs')
>>> ss2 = ezsheets.Spreadsheet('https://docs.google.com/spreadsheets/d/1TzOJxh
NKr15tzdZxTqtQ3EmDP6em_elnbtmZIcyu8vI/')
>>> ss3 = ezsheets.Spreadsheet('1TzOJxhNKr15tzdZxTqtQ3EmDP6em_elnbtmZIcyu8vI')
>>> ss1 == ss2 == ss3  # これらは同じスプレッドシート
True

既存のExcel、OpenOffice、CSV、TSVファイルをスプレッドシートにアップロードするには、ファイル名をezsheets.upload()に渡します。対話型シェルに以下の内容を入力してください。my_spreadsheet.xlsxの部分はご自身のExcelファイルに置き換えてください。

>>> import ezsheets
>>> ss = ezsheets.upload('my_spreadsheet.xlsx')
>>> ss.title
'my_spreadsheet'

listSpreadsheets()関数を呼び出せば、自分のGoogleアカウントのスプレッドシートを一覧表示できます。この関数は、キーがスプレッドシートID、値がスプレッドシート名の辞書を返します。ゴミ箱フォルダにある削除されたスプレッドシートも含まれます。スプレッドシートをアップロードしてから、対話型シェルで以下の内容を実行してください。

>>> ezsheets.listSpreadsheets()
{'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU': 'Education Data'}

Spreadsheetオブジェクトを取得できたら、その属性やメソッドを通じて、Googleがホストしているオンラインのスプレッドシートを操作できます。

スプレッドシートの属性にアクセスする

実際のデータはスプレッドシートの各シートに存在しますが、Spreadsheetオブジェクトにはスプレッドシート自体を操作できる、title、id、url、sheetTitles、sheetsという属性があります。https://autbor.com/examplegsのスプレッドシートで確かめてみましょう。このスプレッドシートを閲覧する権限はあっても編集する権限はありませんが、自分のアカウントのスプレッドシートにコピーして新しいスプレッドシートを作成することはできます。

>>> import ezsheets
>>> example_ss = ezsheets.Spreadsheet('https://autbor.com/examplegs')
>>> ss = ezsheets.Spreadsheet()
>>> example_ss.sheets[0].copyTo(ss)
>>> ss.sheets[0].delete()  # Sheet1シートを削除
>>> ss.url
'https://docs.google.com/spreadsheets/d/15gjrbgTmUzItRt9KUcL4JajLaQU70xanstB1dXKoSlM/'

新しくコピーされたシートは、Booksという元のシート名から、Copy of Booksというシート名になります。対話型シェルで以下のコードを続けて実行してください。

>>> ss.title  # スプレッドシート名
'Untitled spreadsheet'
>>> ss.title = 'Sweigart Books'  # スプレッドシート名の変更
>>> ss.id  # 一意のID（読み取り専用属性）
'15gjrbgTmUzItRt9KUcL4JajLaQU70xanstB1dXKoSlM'
>>> ss.url  # 元URL（読み取り専用属性）
'https://docs.google.com/spreadsheets/d/15gjrbgTmUzItRt9KUcL4JajLaQU70xanstB1dXKoSlM/'
>>> ss.sheetTitles  # すべてのシート名
('Copy of Books',)
>>> ss.sheets  # このスプレッドシートのSheetオブジェクト（順序あり）
(<Sheet sheetId=1464919459, title='Copy of Books', rowCount=1000, columnCount=26>,)
>>> ss.sheets[0]  # このスプレッドシートの最初のSheetオブジェクト
<Sheet sheetId=1464919459, title='Copy of Books', rowCount=1000, columnCount=26>
>>> ss['Copy of Books']  # シート名でもアクセス可能
<Sheet sheetId=1464919459, title='Copy of Books', rowCount=1000, columnCount=26>
>>> ss.Sheet('New blank sheet')  # 新しいシートの作成
<Sheet sheetId=1759616008, title='New blank sheet', rowCount=1000, columnCount=26>
>>> ss.sheets[1].delete()  # このスプレッドシートの2番目のシートを削除

誰かがブラウザでスプレッドシートを変更した場合、refresh()メソッドを呼び出すと、オンラインのデータに合うように、Spreadsheetオブジェクトをスクリプトから更新できます。

>>> ss.refresh()

Spreadsheetオブジェクトの属性だけでなく、Sheetオブジェクトのデータも更新されます。Spreadsheetオブジェクトに適用した変更はリアルタイムでオンラインのスプレッドシートに反映されます。

スプレッドシートのダウンロードとアップロード

Googleスプレッドシートは、Excel、OpenOffice、CSV、TSV、PDFと様々な形式でダウンロードできます。スプレッドシートのデータのHTMLを含むZIPファイルとしてダウンロードすることもできます。EZSheetsはこれらの形式でのダウンロードに対応する関数があります。

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('https://autbor.com/examplegs')
>>> ss.title
'Sweigart Books (DO NOT DELETE)'
>>> ss.downloadAsExcel()  # スプレッドシートをExcelファイルとしてダウンロード
'Sweigart_Books.xlsx'
>>> ss.downloadAsODS()  # スプレッドシートをOpenOfficelファイルとしてダウンロード
'Sweigart_Books.ods'
>>> ss.downloadAsCSV()  # スプレッドシートをCSVファイルとしてダウンロード
'Sweigart_Books.csv'
>>> ss.downloadAsTSV()  # スプレッドシートをTSVファイルとしてダウンロード
'Sweigart_Books.tsv'
>>> ss.downloadAsPDF()  # スプレッドシートをPDFファイルとしてダウンロード
'Sweigart_Books.pdf'
>>> ss.downloadAsHTML()  # スプレッドシートをHTMLを含むZIPファイルとしてダウンロード
'Sweigart_Books.zip'

CSVとTSV形式のファイルには一つのシートしか含まれません。よって、このどちらかの形式でGoogleスプレッドシートをダウンロードする場合は、最初のシートしか取得できません。別のシートをダウンロードするには、ダウンロードする前にSheetオブジェクトの順番を変える必要があります。

ダウンロード関数はすべてダウンロードしたファイル名の文字列を返します。ダウンロード関数に新しいファイル名を渡してファイル名を指定することもできます。

>>> ss.downloadAsExcel('a_different_filename.xlsx')
'a_different_filename.xlsx'

この関数はローカルに保存されたファイル名を返します。

スプレッドシートの削除

スプレッドシートを削除するには、delete()メソッドを呼び出します。

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet()  # スプレッドシートの作成
>>> ezsheets.listSpreadsheets()  # スプレッドシートの作成確認
{'1aCw2NNJSZblDbhygVv77kPsL3djmgV5zJZllSOZ_mRk': 'Delete me'}
>>> ss.delete()  # スプレッドシートの削除
>>> ezsheets.listSpreadsheets()  # ゴミ箱フォルダにあるスプレッドシートも一覧表示される
{'1aCw2NNJSZblDbhygVv77kPsL3djmgV5zJZllSOZ_mRk': 'Delete me'}

delete()メソッドは、スプレッドシートをGoogleドライブのゴミ箱フォルダに移動させます。 https://drive.google.com/drive/trashで自分のゴミ箱フォルダの中身を見ることができます。ゴミ箱フォルダ内のスプレッドシートもlistSpreadsheets()が返す辞書に含まれます。スプレッドシートを完全に削除するには、permanentキーワード引数にTrueを渡します。

>>> ss.delete(permanent=True)
>>> ezsheets.listSpreadsheets()
{}

一般的に、自動化スクリプトでスプレッドシートを完全に削除するのはいい考えではありません。スクリプトにバグがあってもスプレッドシートを回復する術がないからです。無料のGoogleアカウントでもドライブはギガバイト単位の容量が利用できますから、容量を心配する必要はあまりないでしょう。

データの読み書き

Excelと同じように、Googleスプレッドシートのワークシートには、データを含むセルの行と列があります。角かっこ演算子[] でセルの読み書きができます。例えば、対話型シェルに以下の内容を入力すると、新しいスプレッドシートを作成してデータを追加します。

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet()
>>> ss.title = 'My Spreadsheet'
>>> sheet = ss.sheets[0]  # このスプレッドシートの最初のシートを取得
>>> sheet.title
'Sheet1'
>>> sheet['A1'] = 'Name'  # A1セルの値を設定
>>> sheet['B1'] = 'Age'
>>> sheet['C1'] = 'Favorite Movie'
>>> sheet['A1']  # A1セルの値を読み取り
'Name'
>>> sheet['A2']  # 空白セルは空文字列を返す
''
>>> sheet[2, 1]  # 2列目1行目はB1と同じ
'Age'
>>> sheet['A2'] = 'Alice'
>>> sheet['B2'] = 30
>>> sheet['C2'] = 'RoboCop'
>>> sheet['B2']  # データはすべて文字列として返される
'30'

これらの指示によりGoogleスプレッドシートは図15-2のようになります。

SheetオブジェクトのすべてのデータはSpreadsheetオブジェクトが最初にロードされたときにロードされます。そのため、データの読み取りは一瞬でできます。しかし、値をオンラインのスプレッドシートに書き込むには、ネットワーク接続が必要で、1秒程度かかります。数千セルを一つずつ更新すると非常に時間がかかります。一つずつ更新するのではなく、行全体や列全体を一度に更新する方法を次の節で示します。

>>> import ezsheets
>>> ezsheets.convertAddress('A2')  # 文字スタイルの座標を変換
(1, 2)
>>> ezsheets.convertAddress(1, 2)  # タプルスタイルの座標を変換
'A2'
>>> ezsheets.getColumnLetterOf(2)
'B'
>>> ezsheets.getColumnNumberOf('B')
2
>>> ezsheets.getColumnLetterOf(999)
'ALK'
>>> ezsheets.getColumnNumberOf('ZZZ')
18278

列全体または行全体を読み書きする

先ほど述べたように、一度に一つのセルに書き込むと時間がかかりすぎます。EZSheetsには、列全体または行全体を読み書きするSheetメソッドがあります。getColumn()、getRow()、updateColumn()、updateRow()のメソッドが、それぞれ列全体または行全体を読み書きします。これらのメソッドはGoogleスプレッドシートのサーバーにリクエストを送り、スプレッドシートを更新します。よってインターネット接続が必要になります。この節の例では、第14章のproduceSales3.xlsxをGoogleスプレッドシートにアップロードして使います。このファイルは本書のオンライン素材からダウンロードできます。最初の8行を表15-1に示します。

表 15-1：produceSales3.xlsxファイルの最初の8行

	A	B	C	D
1	PRODUCE	COST PER POUND	POUNDS SOLD	TOTAL
2	Potatoes	0.86	21.6	18.58
3	Okra	2.26	38.6	87.24
4	Fava beans	2.69	32.8	88.23
5	Watermelon	0.66	27.3	18.02
6	Garlic	1.19	4.9	5.83
7	Parsnips	2.27	1.1	2.5
8	Asparagus	2.49	37.9	94.37

produceSales3.xlsxファイルを現在の作業ディレクトリに置いて、対話型シェルで以下の内容を実行してこのExcelファイルをアップロードしてください。

>>> import ezsheets
>>> ss = ezsheets.upload('produceSales3.xlsx')
>>> sheet = ss.sheets[0]
>>> sheet.getRow(1)  # 最初の行は0ではなく1
['PRODUCE', 'COST PER POUND', 'POUNDS SOLD', 'TOTAL', '', '']
>>> sheet.getRow(2)
['Potatoes', '0.86', '21.6', '18.58', '', '']
>>> sheet.getColumn(1)
['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon', 'Garlic',
--snip--
>>> sheet.getColumn('A')  # getColumn(1)と同じ
['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon', 'Garlic',
--snip--
>>> sheet.getRow(3)
['Okra', '2.26', '38.6', '87.24', '', '']
>>> sheet.updateRow(3, ['Pumpkin', '11.50', '20', '230'])
>>> sheet.getRow(3)
['Pumpkin', '11.50', '20', '230', '', '']
>>> columnOne = sheet.getColumn(1)
>>> for i, value in enumerate(columnOne):
...     # Pythonのリスト内の文字列を大文字に
...     columnOne[i] = value.upper()
...
>>> sheet.updateColumn(1, columnOne)  # 列全体を1回のリクエストで更新

getRow()関数とgetColumn()関数は指定した行または列のすべてのセルのデータをリストとして取得します。空白セルはリスト内で空白文字列値になります。getColumn()に列番号とアルファベット文字のどちらを渡しても、指定した列のデータを取得します。先の例ではgetColumn(1)とgetColumn('A')が同じリストを返すことを示しています。

updateRow()関数とupdateColumn()関数は、渡した値のリストで、行または列のデータを上書きします。この例では、3行目は最初okra（オクラ）の情報を含んでいましたが、updateRow()呼び出しによりpumpkins（かぼちゃ）のデータに置き換えました。sheet.getRow(3)をもう一度呼び出すと、3行目の新しい値を確認できます。

たくさんのセルを更新するときに、セルを一つずつ更新するのは遅いです。列または行をリストとして取得し、リストを更新して、そのリストで列全体または行全体を更新すれば、ずっと速いです。すべての変更をGoogle Cloudのサービスへの一度のリクエストでできるからです。

getRows()メソッドを呼び出すと、リストのリストが返され、すべての行を一度で取得できます。外側のリストの中に入っている内側のリストはシートの一つの行を表します。このデータ構造中の、農産物の種類、販売ポンド数、総売上の一部の値を変更します。そして、それをupdateRows()メソッドに渡します。以下の対話型シェルの内容を実行してください。

>>> rows = sheet.getRows()  # スプレッドシートのすべての行を取得
>>> rows[0]  # 最初の行の値
['PRODUCE', 'COST PER POUND', 'POUNDS SOLD', 'TOTAL', '', '']
>>> rows[1]
['POTATOES', '0.86', '21.6', '18.58', '', '']
>>> rows[1][0] = 'PUMPKIN'  # 農産物の種類を変更
>>> rows[1]
['PUMPKIN', '0.86', '21.6', '18.58', '', '']
>>> rows[10]
['OKRA', '2.26', '40', '90.4', '', '']
>>> rows[10][2] = '400'  # 販売ポンド数を変更
>>> rows[10][3] = '904'  # 総売上を変更
>>> rows[10]
['OKRA', '2.26', '400', '904', '', '']
>>> sheet.updateRows(rows)  # 変更点についてオンラインのスプレッドシートを更新

getRows()から返されたリストのリストの1行目と10行目を修正して、updateRows()にそのリストのリストを渡すことで、シート全体を一回のリクエストで更新しています。

このGoogleスプレッドシートには最後に空白の列があることに注意してください。アップロードしたシートには6列ありますが、データは4列しかありません。rowCount属性とcolumnCount属性でシートの行数と列数を読み取れます。これらの属性に値を設定すると、シートのサイズを変えられます。

>>> sheet.rowCount  # シートの行数
23758
>>> sheet.columnCount  # シートの列数
6
>>> sheet.columnCount = 4  # 列数を4に変更
>>> sheet.columnCount  # シートの列数は4
4

この指示によりproduceSales3.xlsxの5列目と6列目を削除します。図15-3にその様子を示しました。

Googleのドキュメントによると、Googleスプレッドシートは1000万セルまで収納できます。しかし、データの更新にかかる時間を短くするためにシートを必要なだけの大きさにするのがよいでしょう。

シートの作成、移動、削除

Googleスプレッドシートは、Sheet1という名前の一つのシートだけがある状態で始まります。Sheet()メソッドでシートのリストの末尾にシートを追加できます。文字列を渡して新しいシート名を指定することもできます。オプションの第二引数には、新しいシートを作成するインデックスを整数で指定します。対話型シェルで以下の内容を実行して、スプレッドシートを作成して新しいシートを追加してみだくさい。

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet()
>>> ss.title = 'Multiple Sheets'
>>> ss.sheetTitles
('Sheet1',)
>>> ss.Sheet('Spam')  # シートのリストの末尾に新しいシートを作成
<Sheet sheetId=2032744541, title='Spam', rowCount=1000, columnCount=26>
>>> ss.Sheet('Eggs')  # さらに新しいシートを作成
<Sheet sheetId=417452987, title='Eggs', rowCount=1000, columnCount=26>
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Eggs')
>>> ss.Sheet('Bacon', 0)  # シートのリストのインデックス0に新しいシートを作成
<Sheet sheetId=814694991, title='Bacon', rowCount=1000, columnCount=26>
>>> ss.sheetTitles
('Bacon', 'Sheet1', 'Spam', 'Eggs')

この指示では、デフォルトのSheet1に加えて、Bacon、Spam、Eggsという3つの新しいシートをスプレッドシートに追加しています。スプレッドシートのシートには順序があり、Sheet()の第二引数で追加するシートのインデックスを指定しない限り新しいシートはリストの末尾に追加されます。Baconという名前のシートをインデックス0に追加したので、Baconはこのスプレッドシートの最初に来ています。他の3つのシートは一つずつ位置がずれます。この挙動はinsert()リストメソッドに似ています。

図15-4に示すように、新しいシートが画面下部のタブで確認できます。

シートの順序はindex属性からわかり、index属性に新しい値を代入するとシートの順序を変えられます。

>>> ss.sheetTitles
('Bacon', 'Sheet1', 'Spam', 'Eggs')
>>> ss.sheets[0].index
0
>>> ss.sheets[0].index = 2  # シートのインデックスを0から2に移動
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Bacon', 'Eggs')
>>> ss.sheets[2].index = 0  # シートのインデックスを2から0に移動
>>> ss.sheetTitles
('Bacon', 'Sheet1', 'Spam', 'Eggs')

Sheetオブジェクトのdelete()メソッドは、スプレッドシートからシートを削除します。シートを残してそのシートのデータを削除したければ、clear()メソッドを呼び出してすべてのセルをクリアして空白のシートにします。以下の式を対話型シェルに入力してみてください。

>>> ss.sheetTitles
('Bacon', 'Sheet1', 'Spam', 'Eggs')
>>> ss.sheets[0].delete()  # インデックス0の"Bacon"シートを削除
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Eggs')
>>> ss['Spam'].delete()  # "Spam"シートを削除
>>> ss.sheetTitles
('Sheet1', 'Eggs')
>>> sheet = ss['Eggs']  # "Eggs"シートを変数に代入
>>> sheet.delete()  # "Eggs"シートを削除
>>> ss.sheetTitles
('Sheet1',)
>>> ss.sheets[0].clear()  # "Sheet1"シートのセルをすべてクリア
>>> ss.sheetTitles  # "Sheet1"シートは空白だが存在
('Sheet1',)

シートは完全に削除されます。データを回復する方法はありません。copyTo()メソッドで別のスプレッドシートにコピーすれば、バックアップできます。次の節ではコピーについて説明します。

シートのコピー

SpreadsheetオブジェクトにはSheetオブジェクトのリストがあります。（前の節で説明したように）このリストの順序を変えればシートの順序を変えられます。このリストを別のスプレッドシートにコピーすることもできます。Sheetオブジェクトを別のSpreadsheetオブジェクトにコピーするには、copyTo()メソッドを呼び出します。コピー先のスプレッドシートのSpreadsheetオブジェクトを引数として渡します。スプレッドシートを2つ作成し、1つ目のスプレッドシートのデータを2つ目のスプレッドシートにコピーします。対話型シェルに以下の内容を入力してください。

>>> import ezsheets
>>> ss1 = ezsheets.Spreadsheet()
>>> ss1.title = 'First Spreadsheet'
>>> ss1.sheets[0].title = 'Spam'  # ss1にSpamという名前のシートを持たせる
>>> ss2 = ezsheets.Spreadsheet()
>>> ss2.title = 'Second Spreadsheet'
>>> ss2.sheets[0].title = 'Eggs'  # ss2にEggsという名前のシートを持たせる
>>> ss1[0]
<Sheet sheetId=0, title='Spam', rowCount=1000, columnCount=26>
>>> ss1[0].updateRow(1, ['Some', 'data', 'in', 'the', 'first', 'row'])
>>> ss1[0].copyTo(ss2)  # ss1の1番目のシートをss2スプレッドシートにコピー
>>> ss2.sheetTitles  # ss2にはss1の1番目のシートのコピーがある
('Eggs', 'Copy of Spam')

コピーしたシートは、コピー先のスプレッドシートのシートのリストの末尾にCopy ofという接頭辞つきで現れます。新しいスプレッドシートのindex属性を変更すればシートの順序を変えられます。

ステップ1：うそのブロックチェーン監査

「ブロックチェーン」全体を精査し、すべてのアカウントの現在残高を判定するプログラムを書きます。このデータを保持するのに辞書を使います。キーはアカウント名で値はボーリングコインの保有量です。このプログラムでは暗号資産ネットワークに存在するボーリングコインの総量も表示します。EZSheetsをインポートして辞書を設定するところから始めます。

import ezsheets
ss = ezsheets.Spreadsheet('https://autbor.com/boringcoin')
accounts = {}  # キーはアカウント名、値は保有量

次に、スプレッドシートの各行を反復処理します。送信者と受信者と取引量を把握します。Googleスプレッドシートはデータを常に文字列として返しますから、amountの値で計算を行うために整数値に変換する必要があります。

# 各行は取引であり、一行ずつ反復処理する
for row in ss.sheets[0].getRows():
    sender, recipient, amount = row[0], row[1], int(row[2])

送信者が'PRE-MINE'という特別なアカウントであれば、これは無限のお金の源です。暗号資産詐欺のいいところは事前に採掘されたコインを使えることで、ボーリングコインも例に漏れません。取引量をaccountsの受信者のアカウントに追加します。辞書に受信者が存在しなければ、setdefault()メソッドでそのアカウントの保有量として0という値を設定します。

    if sender == 'PRE-MINE':
        # 送信者'PRE-MINE'は何もないところからお金を作り出す
        accounts.setdefault(recipient, 0)
        accounts[recipient] += amount

それ以外の場合は、その取引量を、送信者のアカウントから差し引き、受信者のアカウントに追加します。

    else:
        # 送信者から受信者に資金移動
        accounts.setdefault(sender, 0)
        accounts.setdefault(recipient, 0)
        accounts[sender] -= amount
        accounts[recipient] += amount

ループが終われば、辞書accountsを表示すれば現在残高がわかります。

print(accounts)

この監査の一部として、この辞書の全員の残高を足し合わせてネットワーク全体におけるボーリングコインの総量を計算します。変数totalを0で始めて、forループで辞書accountsのキーと値のペアを反復処理します。すべての値をtotalに足し合わせてから、ボーリングコインの総量を表示します。

total = 0
for amount in accounts.values():
    total += amount
print('Total Boringcoins:', total)

このプログラムを実行すると、出力は次のようになります。

{'Al Sweigart': 999058553, 'Miles Bron': 38283, 'not_a_scammer': 48441,
'some hacker': 44429, 'Tech Bro': 53424, 'Claire Debella': 54443,
'Credulous Journalist': 50408, 'Birdie Jay': 36832, 'Carol': 82867, 'Mark Z.':
 68650, 'Bob': 37920, 'Andi Brand': 57218, 'Eve': 88296, 'Al Sweigart sock
#27': 78080, 'Tax evader': 40937, 'Duke Cody': 17544, 'Lionel Toussaint':
54650, 'some scammer': 2694, 'Alice': 44503, 'David': 41828}
Total Boringcoins: 1000000000

総量は1000000000で、事前採掘されたボーリングコインと一致します。

ステップ2：取引の実行

次のプログラムは、新たな取引を追加するためにブロックチェーンGoogleスプレッドシートに行を追加する、addBoringcoinTransaction.pyです。sys.argvから、送信者、受信者、取引量という、コマンドライン引数を3つ読み取ります。例えば、ターミナルで次のように実行します。

python addBoringcoinTransaction.py "Al Sweigart" Eve 2000

このプログラムはGoogleスプレッドシートにアクセスして、最終行に空行を追加してから、'Al Sweigart'、'Eve'、'2000'という値を埋めます。ターミナルではスペースを含むコマンドライン引数は、"Al Sweigart"のようにダブルクォートで囲まなければなりません。そうしないと、ターミナルはこれらを2つの別の引数であると解釈してしまいます。

addBoringcoinTransactions.pyはまずコマンドライン引数をチェックし、コマンドライン引数に基づいて送信者、受信者、取引量の変数を代入します。

import sys, ezsheets

if len(sys.argv) < 4:
    print('Usage: python addBoringcoinTransaction.py sender recipient amount')
    sys.exit()

# コマンドライン引数から取引情報を取得
sender, recipient, amount = sys.argv[1:]

スプレッドシートには文字列として書き込みますから、amountを文字列から整数に変換する必要はありません。

次に、EZSheetsが、うそのブロックチェーンを記録したGoogleスプレッドシートに接続して、インデックス0の最初のシートを選択します。読者のみなさんはボーリングコインGoogleスプレッドシートの編集権限がありませんから、Googleアカウントにログインした状態でウェブブラウザでURLを開いて、ファイルコピーを作成で自分のGoogleアカウントにコピーしてください。それから、'https://autbor.com/boringcoin'を、ブラウザのアドレスバーに表示されている自分のGoogleスプレッドシートのURLに置き換えてください。

# URLを自分のGoogleスプレッドシートのURLに変更
# そうしないと"The caller does not have permission"エラーが発生
ss = ezsheets.Spreadsheet('https://autbor.com/boringcoin')
sheet = ss.sheets[0]

最後に、シートの行数を取得して、それを1増やし、その行を送信者、受信者、取引量のデータで埋めます。

# シートに新しい取引の1行追加
sheet.rowCount += 1

sheet[1, sheet.rowCount] = sender
sheet[2, sheet.rowCount] = recipient
sheet[3, sheet.rowCount] = amount

ターミナルからpython addBoringcoinTransaction.py "Al Sweigart" Eve 2000を実行すると、Googleスプレッドシートの最終行にAl Sweigart、Eve、2000の新しい行が追加されます。auditBoringcoin.pyプログラムをもう一度実行すると、暗号資産ネットワーク内の最新の全員のアカウントの残高がわかります。

このGoogleスプレッドシートでのブロックチェーンデータ構造は無責任なものです。エラー処理をしていませんから、大惨事が起こるでしょう。これは大部分の市場化されているブロックチェーン製品も同じようなものです。ネズミ講が破綻する前にボーリングコインを購入するこの機会を見逃さずご連絡ください。

Googleフォームのデータダウンロード

先に触れたように、Googleフォームを使うと、人々から情報を簡単に集められるシンプルなオンラインフォームを作成できます。フォームに入力された情報はGoogleスプレッドシートに蓄積されます。このプロジェクトでは、ユーザーが入力したフォームの情報を自動的にダウンロードするプログラムを書きます。https://docs.google.com/forms/で新しい空白のフォームを開始して、ユーザーに名前とメールアドレスの入力を求めるフィールドを追加してから、新しいフォームの右上にある「公開」ボタンをクリックします。このフォームで回答例をいくつか入力して準備します。

フォームの回答タブで、「スプレッドシートにリンク」ボタンをクリックして、ユーザーが送信した回答を蓄積するGoogleスプレッドシートを作成します。回答例がスプレッドシートの最初の行に表示されているはずです。EZSheetsを使ってこのスプレッドシートのメールアドレスのリストを収集するPythonのスクリプトを書いてください。

スプレッドシートを別の形式に変換する

Googleスプレッドシートでは、スプレッドシートを別の形式に変換できます。アップロードするファイルをupload()に渡すスクリプトを書いてください。Googleスプレッドシートにアップロードされたら、downloadAsExcel()やdownloadAsODS()などの関数を使って別の形式でそのスプレッドシートのコピーをダウンロードしてください。

スプレッドシートの誤りを発見する

丸一日かけて豆を数える仕事をして、豆の合計をスプレッドシートにまとめてGoogleスプレッドシートにアップロードしました。このスプレッドシートは誰でも閲覧可能です（編集はできません）。このスプレッドシートは以下のコードで取得できます。

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg')

https://docs.google.com/spreadsheets/d/1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jgでこのスプレッドシートをブラウザでご覧ください。シートの列は、BEANS PER JAR、JARS、TOTAL BEANSです。TOTAL BEANS列は、BEANS PER JARとJARSの数値の積です。しかし、このシートの15,000行の中に一つだけ誤りがあります。手動で確認するには行数が多すぎます。合計の誤りをチェックするスクリプトを書いてください。

ヒントとして、ss.sheets[0].getRow(rowNum)で行内の各セルにアクセスします。ssはSpreadsheetオブジェクトで、rowNumは行番号です。Googleスプレッドシートの行番号は0ではなく1から始まることに注意してください。セルの値は文字列ですから、計算をする前に整数に変換する必要があります。int(ss.sheets[0].getRow(2)[0]) * int(ss.sheets[0].getRow(2)[1]) == int(ss.sheets[0].getRow(2)[2])という式は2行目の合計が正しければTrueを返します。このコードをループ内で用いて合計が誤っている行を特定してください。