Google Sheets

Google Sheets Connectorは、スプレッドシートの宣言した範囲を型付きのテーブルとして読み、宣言したキー列によって行を追記、更新、UPSERT、削除し、範囲を結果で置き換え、シートを作り、範囲をクリアします。業務データの多くは実際にはスプレッドシートにあります。人が手で保守している一覧、エクスポート、参照用の表などです。プログラムはそれを他の読み取り元と同じように読み、人が見る場所に結果を書き戻せます。

読み取り元Copy Anchor Link

接続で宣言した各シートが読み取り元です。sheets.accounts、sheets.pricing のように参照します。読み取り元は1つのシートの1つの長方形、つまりタブと範囲で、見出しの1行、型付きの列、そして宣言したキー列を持ちます。行番号は識別子ではありません。だれかが並べ替えたり、行を挿入したり削除したりすれば変わるので、Connectorが行番号をキーとして使うことはありません。キー列のないシートは、読み取りと追記はできますが、更新、UPSERT、削除の対象にはできません。

範囲内の空白行は飛ばします。範囲内にセルのエラー（#REF!、#N/A など）があると、NULLになるのではなく読み取りが失敗します。数式はその計算結果で読みます。

• sheets.<sheet> — 宣言した範囲。スプレッドシートの1行が1レコードで、宣言した列と型を持ちます。キーは宣言したキー列です。
• sheets.sheets — スプレッドシートのタブ。id、title、index、row_count、column_count。キーは id。

型Copy Anchor Link

書き込みは同じ規則で逆方向に変換します。数値と日付は値として、テキストはテキストとして書きます。= で始まっていても、数式として書くことはありません。

Effectと操作Copy Anchor Link

レコードのEffectは、宣言したキーで書きます。

• insert — EFFECT INSERT sheets.<sheet> (col, …) は、送信時点で宣言した範囲の最終行の後ろにレコードを追記します。
• update — EFFECT UPDATE sheets.<sheet> ID key SET (…) は、キー列が key の行のセルを設定します。
• upsert — EFFECT UPSERT sheets.<sheet> ID key SET (…) は、その行を更新し、キーを持つ行がなければ追記します。
• delete — EFFECT DELETE sheets.<sheet> ID key は、その行を削除します。

Connectorは、プレビューを作ったときの行番号ではなく、適用時にキーで行を探します。キーが複数の行に一致する項目は failed になり、最初に一致した行への変更として適用されることはありません。

テーブルとコンテンツ：

• replace — EFFECT STORE sheets.<sheet> は、宣言した範囲（見出しより下）を現在のレコードで置き換えます。
• new sheet — EFFECT STORE sheets.sheets SET (title = …) は、現在のレコードと、その列名を見出しの行にしたタブを作ります。

操作：

• clear_range — EFFECT COMMAND sheets.clear_range TARGET range ID key は、'Review log'!A2:F のような範囲のセルをクリアします。値は消え、書式は残ります。

セットアップCopy Anchor Link

"sheets": {
  "connector": "sheets",
  "spreadsheet_id": "1AbC…",
  "sheets": {
    "accounts": {"tab": "Accounts", "range": "A1:G", "key": "id", "columns": {
      "id": "string", "name": "string", "owner": "string", "notes": "string",
      "plan": "string", "seats": "number", "active": "boolean"
    }},
    "review_log": {"tab": "Review log", "range": "A1:D", "key": "account", "columns": {
      "account": "string", "level": "string", "reason": "string", "reviewed_at": "datetime"
    }}
  },
  "credentials": {"service_account": {"env": "GOOGLE_SERVICE_ACCOUNT_JSON"}}
}

スプレッドシートをサービスアカウントと共有します。読み取りには閲覧者、書き込みには編集者の権限です。アカウントには、読み取りに spreadsheets.readonly、書き込みに spreadsheets のスコープが必要です。突き合わせのためにスプレッドシートの変更履歴を読むには、drive.file も必要です。プログラムが書き込むシートは、Runが承認を待っているあいだに人が手で編集しないタブにしてください。ActQL Cloudでは、ホスト型のOAuthでサービスアカウントなしにアカウントにサインインできます。要求するスコープは同じです。

CapabilityとプッシュダウンCopy Anchor Link

explain は、読み取りをSite connector:sheets、Engine Sheets API で表示し、それより後ろのステップはすべてHostで実行します。

プレビューと突き合わせCopy Anchor Link

プレビュー。 更新、既存の行のUPSERT、削除は full_diff です。Connectorはキーで行を読み、現在の値とスプレッドシートのリビジョンidを記録し、プレビューには現在の値と新しい値が出ます。適用時には、もう一度キーで行を読みます。値が変わった行、別の行番号に動いた行、もう存在しない行は、項目が failed（conflict）で終わり、続けるよう求められればRunは新しいプレビューを作ります。追記、追記になるUPSERT、範囲の置き換え、新しいシートは targeted です。プレビューには、書かれるレコードとその場所がすべて並びます。まだ存在しない行に「現在の値」はありません。

突き合わせ。 Connectorが書くバッチには、リクエストのマーカーが書いた行のデベロッパーメタデータとして付きます（Sheetsは、行が並べ替えられたり動かされたりしてもデベロッパーメタデータを行とともに保持します）。応答が失われた場合は、スプレッドシートのデベロッパーメタデータからそのマーカーを探して突き合わせます。期待どおりの値があってもマーカーのない行は証跡になりません。さらに、スプレッドシートの変更履歴を読んで、書き込みがリクエストの時間枠の中で着地したことを確認します。クリアは同じバッチでクリアした範囲にマーカーをデベロッパーメタデータとして書き、それで突き合わせます。なければ項目は unknown のままです。どれでも結果が確定しなければ、項目は unknown のままで、バッチが再送されることはありません。

上限と費用Copy Anchor Link

• 読み取り元の範囲には上限があります。Connectorはプログラムの件数の上限までしか読まず、それより大きな範囲は途中で切られるのではなく失敗します。
• Sheets APIには、プロジェクトごととユーザーごとの1分あたりのリクエスト数の制限があります。Connectorはその範囲内でペースを調整し、書き込みは行のバッチで送るので、大きなEffectも1行1リクエストではなく数リクエストで済みます。
• conflict の結果の主な原因は、人による同時編集です。承認を待っているあいだに人が同じ行を編集するRunでは、conflict が起こるものと考えてください。
• 1つのスプレッドシートは最大1,000万セルです。それを超える範囲の置き換えは、何も書く前に失敗します。

例Copy Anchor Link

有料プランなのに担当者がいない取引先です。

FROM sheets.accounts
|> WHERE plan != 'free' AND owner IS NULL AND active
|> SELECT id, name, plan, seats
|> ORDER BY seats DESC;

レビューの記録を、取引先ごとに1行で最新に保ちます。

SELECT a.name AS account, risk.value.level AS level, risk.value.reason AS reason
FROM sheets.accounts AS a
JOIN snowflake.usage_30d AS u ON a.id = u.account_id
|> INFER `model-task:churn-risk.v1`
     INPUT (notes = a.notes, active_users = u.active_users, trend = u.trend) AS risk
|> WHERE risk.outcome = 'ok' AND risk.value.level = 'high'
|> EXTEND CURRENT_DATETIME() AS reviewed_at
|> EFFECT UPSERT sheets.review_log ID account SET (level = level, reason = reason, reviewed_at = reviewed_at);

すべての取引先の担当者をCRMから埋めます。

FROM sheets.accounts AS s
|> WHERE s.owner IS NULL
|> JOIN salesforce.accounts AS a ON s.id = a.id
|> EFFECT UPDATE sheets.accounts ID s.id SET (owner = a.owner_email);