「Quip Python APIs を使用して Quip スプレッドシートからデータを読み書きする方法」

「Quip Python APIsを使ってQuipスプレッドシートでのデータの読み書き方法」

アナリストはしばしば、エンドユーザーが入力を提供し、それを最終的な分析ソリューションでのオーバーライド/追加コンテキストとして使用できるソリューションを提供するよう求められます。

電子商取引のショッピングアプリの例を考えてみましょう。システムには、顧客から100件のネガティブ評価があるとサプライヤーをブラックリストに登録するロジックがあります。しかし、ネガティブな評価はアプリ内の体験や配送/返品の体験による場合があります。公平性を保つために、サプライヤーには6か月に1回だけブラックリスト登録に異議を申し立てるオプションが与えられます。この記事では、承認/却下がオフラインのスプレッドシートに記録されていると仮定します。

1週間で作成された全ての新しいブラックリスト削除の申立ては、スプレッドシートにエクスポートされ、チームに送信されてレビューされます。チームはデータを確認し、承認または却下します。それからデータをシステムに更新するために戻します。これは毎週の作業です。

今、この手動介入データをシステムに追加する必要があります。やり方はいくつかあります。個々のユーザーはデータをs3バケットにアップロードし、それをデータベースに読み込むようにスケジュールすることができます。または、全ての個人が同じスプレッドシートを実行時に更新できるようにquipを使用することができます。そして、これは定期的にデータベースにアップロードすることができます。

Quipは、複数の人がドキュメントやスプレッドシートを編集し、デスクトップ/モバイルの任意のエンドクライアントを使用できる共同作業ソフトウェアです。

• 『アポロ8が月レースを制した方法』
• 「JAXにおけるディープ強化学習の優しい入門」
• 「時間の最適化を送る」

この記事では、クイップのスプレッドシートを自動化して、ユーザーが入力したデータを読み取り、データベーステーブルにアップロードし、新しいデータで同じスプレッドシートに戻す方法をご紹介します。この演習では、Redshiftをデータベースとして使用します。

このタスクは、2つの別々のパートに分割できます。まず、Quipのスプレッドシートからデータを読み取り、データベースのテーブルに保存すること。次に、このデータに対してデータ操作やチェックを行い、データベース内の既存データと結合し、その操作されたデータを既存のquipスプレッドシートに書き込むことになる。個別のケースを一つずつ見ていきましょう。読み取りだけしたい場合や書き込みだけしたい場合でも、この記事はそのための手助けとなるでしょう。まずは最初のパートを見てみましょう。

パート1 – Quipのスプレッドシートからデータを読み込み、データベースのテーブルに書き込む

ステップ1：Quipに接続するためのアクセストークンを取得する。

個人のQuipアカウントにAPIアクセスを提供するアクセストークンを生成する必要があります。個人アクセストークンを生成するには、次のページを訪れてください：https://quip.com/dev/token。企業のSSO対応Quipアカウントをお持ちの場合は、URLが僅かに異なります。例えば、https://quip-corporate.com/dev/tokenのようになります。

上記のGet Personal Access Tokenボタンをクリックすると、後でAPIを使用してquipスプレッドシートにアクセスする際に使用するトークンが表示されます。

ステップ2：ライブラリのインポート

まず必要なライブラリをインポートしましょう。このパートでは、主にquipclientおよびpandas_redshiftのライブラリを使用します。

import quipclient as quipimport pandas as pdimport numpy as npimport pandas_redshift as primport boto3from datetime import datetime as dtimport psycopg2import warningswarnings.filterwarnings('ignore')import socket

ステップ3：トークンIDを使用してQuipに接続する

QuipClient APIでは、ベースURL、スレッドID、アクセストークンが必要です。これらを使用して、任意のファイルにアクセスできます。ベースURLは、読み取り（または書き込み）を試行しているquipサーバーのURLです。企業アカウントの場合、一般的にURLに企業名が含まれます。スレッドIDは、Quipサーバー上のすべてのファイルの一意の識別子です。これは、対象ファイルのベースURLの後にある英数字の値です。スプレッドシートの場合はそのようなものになります。

ファイルのURLが次のようになっている場合、ベースURLはhttps://platform.quip-XXXXXXXXX.com、スレッドIDはabcdefgh1234になります。

アクセストークンは、Step 1で生成したトークンです。

今、QuipClient APIを使用して、アクセストークンとスレッドIDを使用してURLに接続します。

##################################### # Quip変数の宣言 #####################################baseurl = 'https://platform.quip-XXXXXXXXX.com'access_token = "************************************************************************" thread_id = 'abcdefgh1234'########################################### Quipへの接続##########################################client = quip.QuipClient(access_token = access_token, base_url=baseurl)rawdictionary = client.get_thread(thread_id)

ステップ4：Quipからデータをデータフレームに読み込む

上記のステップ3でのrawdictionaryの出力はHTMLのリストを返します。Pandasのread_html関数を使用して、HTMLの部分をデータフレームdfsに読み込むことができます。したがって、dfsはデータフレームのリストです。このリスト内の各データフレームには、quipスプレッドシートの各タブのデータが含まれています。この例では、最後のタブのデータのみを考慮しています。したがって、raw_dfの最後のデータフレームを取得するためにインデックス-1を使用しています。

########################################### データのクリーニングとデータフレームの作成##########################################dfs=pd.read_html(rawdictionary['html'])raw_df = dfs[-1]raw_df.columns=raw_df.iloc[0] #最初の行を列見出しにするraw_df=raw_df.iloc[1:,1:] #上記の手順の後、最初の2行が重複してしまいます。1行目を削除するraw_df=raw_df.replace('\u200b', np.nan) #空のセルをnanで置換する

ステップ5：データベースに接続してデータをテーブルに書き込む

Redshiftインスタンスにアクセスするには、RedshiftエンドポイントURLが必要です。例えば、インスタンスは次のようなものになります：

datawarehouse.some_chars_here.region_name.redshift.amazonaws.com.

データベースに接続し、データフレーム（ステップ4で作成した）を新しいテーブルまたは既存のテーブルに書き込みます。pandas_to_redshift関数を使用すると、既存のテーブルにデータを追加するか、完全に上書きすることができます。ただし、append = Falseを選択した場合、この操作を実行するたびにテーブルが削除されて再作成されます。データを上書きする際に特定の列のデータ型や文字数、ユーザー権限を維持する場合は、この操作を実行する前にテーブルを切り捨てる方が良いでしょう。直接Truncateコマンドを使用してテーブルを切り捨てることができます。これを行った後、append = Trueで操作を実行できます。私は通常、歴史データを維持する必要があるタイプ2のテーブルに対してappend=Trueを使用します。

#### テーブルの切り捨て ############################################### データベースへの接続##########################################user1="user"pswd1="password"connection_db=psycopg2.connect(dbname = 'test_db',                              host='test_host.com',                              port= '1234',                              user= user1,                              password= pswd1)########################################### 接続が確立されました##########################################df = pd.read_sql_query("""Select distinct from test_tableorder by 1 asc""",connection_db)result = df.to_markdown(index=False)cur = connection_db.cursor()cur.execute('TRUNCATE TABLE test_table')

#### テーブルへの書き込み ############################################### redshiftとs3への接続##########################################pr.connect_to_redshift(dbname = 'db',                        host = 'server.com',                        port = 1234,                        user = 'user',                        password = 'password')pr.connect_to_s3(aws_access_key_id = '*************',                aws_secret_access_key = '*************************',                bucket = 'test',                subdirectory = 'subtest')########################################### データフレームをS3およびRedshiftに書き込む##########################################pr.pandas_to_redshift(data_frame = raw_df,                        redshift_table_name = 'test_table',append = True,                        region = 'xxxxxxx')

これで、クイップのスプレッドシートからデータを読み込んでRedshiftテーブルに書き込む最初のパートが完了しました。次に、2番目のパートを見てみましょう。

パート2：既存のクイップスプレッドシートにデータを書き込む。

このパートでは、最初の3つのステップはパート1と同じです。したがって、上記のステップ1、2、3に従ってください。ここからはSTEP 4から始めます。

ステップ4：データベースに接続してデータを読み込む

ここでは、psycopg2を使用してRedshiftインスタンスに接続し、Quipスプレッドシートに書き込む必要のあるRedshiftテーブルからデータを読み込みます。ここでは、dataframeをmarkdownに変換してQuipClientライブラリの前提条件であるクリーンなテーブルを取得しています。

########################################### データベースに接続する##########################################user1="ユーザー"pswd1="パスワード"connection_db=psycopg2.connect(dbname = 'test_db',                              host='test_host.com',                              port= '1234',                              user= user1,                              password= pswd1)########################################### 接続が確立されました##########################################df = pd.read_sql_query("""Select distinct from test_tableorder by 1 asc""",connection_db)result = df.to_markdown(index=False) 

ステップ5：データをQuipファイルに書き込む

Quipスプレッドシートにデータを書き込むには、QuipClientライブラリのedit_document関数を使用することができます。この関数には複数のパラメータがあります。フォーマットはHTMLまたはmarkdownのいずれかを指定できます。デフォルトはHTMLであり、そのためにStep 4でdataframeをmarkdownに変換しました。データを追加する場所を指定するためにセクションIDと場所を指定する必要があります。「追加」や「前後の特定のセクションの前/後」などの方法でデータを追加したかった場合、この特定のシナリオでは既存のスプレッドシートに新しいタブにデータを追加するだけでした。詳細についてはこちらをご覧ください。

場合によっては、操作は実行されますが、APIの応答が遅れるためにスクリプトが失敗することがあります。try-exceptエラーブロックは、タイムアウトエラーをキャッチするためのものです。

########################################### データをQuipに挿入する##########################################  try:  client.edit_document(thread_id=thread_id,                      content = result,                    format='markdown',                    operation=client.APPEND)  print("Test DBが更新されました。")except socket.timeout:  print("エラーが予期されました！")

以上です！

この記事がお役に立てれば幸いです。何か追加の質問がある場合は、お気軽にご連絡ください。

お読みいただきありがとうございました！

また次回をお楽しみに…

We will continue to update VoAGI; if you have any questions or suggestions, please contact us!