Googleスプレッドシート

本ページでは、espar form で実装された問い合わせフォームから入力された値を、Google社のGoogleスプレッドシートに連携させる方法について解説します。

要件

espar form と連携する場合、以下の条件を満たす必要があります。

Google Workspace の契約またはGoogleアカウントを保有していること
連携に使用する Google Workspace アカウントに以下権限が付与されていること
- スプレッドシートの利用
- App Script サービス(GAS)の利用

設定

espar form と Google スプレッドシートの両方で連携のための設定が必要になります。以下の手順に従って設定を行ってください。

1. スプレッドシート側の準備

(1) Google Drive の画面から新規にスプレッドシートを作成します。
(2) (1)で作ったスプレッドシートを開き、分かりやすい名称をつけます。任意の文字列を指定することができます。
(3) シート名をダブルクリックして名前を「問い合わせ」に変更します。
(4) 1行目にフォームの各入力要素のnameの値を並べます。例えば、フォームでの会社名の入力欄が <input type="text" name="company"> となっている場合、company を入力します。このように「どの列にどの入力要素の内容を記録したいか」を指定します。順番が入れ替わったり、空白列があっても問題ありません。name の値にあわせて記録されます。必ず1行目に記入して下さい。
(5) メニューから [拡張機能]→[App Script] をクリックします。
(6) ブラウザの別タブでスクリプトエディタが開きますので、(1)と同様に分かりやすい名称をつけます。任意の文字列を指定することができます。

この画面で記述するプログラムをGAS(Google App Script)と呼び、スプレッドシートと連携して様々なプログラムを記述することができます。詳しくはこちらをご覧下さい。
espar form のGoogleスプレッドシート連携では、GASが備える機能のうち、(スプレッドシートから見た)外部システムと連携させるAPI機能を使用しています。
(7) デフォルトで選択されている コード.gs の内容を一度全て削除して下さい。その後、こちらの連携コードのページのコードをそのままコピー&ペーストして下さい。

自分でGASを書くことができる場合は独自に実装しても問題ありません。また連携コードを改造して使って頂いても結構です。
(8) ペーストしたあとに「保存」アイコンをクリックして保存します。
(9) 保存が完了したら、[デプロイ]→[新しいデプロイ] をクリックします。
(10) ダイアログが表示されますので、ギヤボタンから[ウェブアプリ]をクリックして下さい。
(11) 入力欄が現れますので、以下の画面キャプチャや表を参考に入力して、[デプロイ]をクリックして下さい。

項目面入力内容
説明任意の文章
ウェブアプリ自分(本操作を行っているアカウント)
アクセスできるユーザ全員
(12) 確認のダイアログが表示されますので[アクセスを承認]をクリックして下さい。
(13) 別ウィンドウで認証画面が表示されますので、対象の操作中アカウントを選択して下さい。
(14) その後、許可を求める内容に変わりますので [Allow] をクリックして下さい。
(15) デプロイが完了してIDやURLが表示されます。[コピー]をクリックしてURLを控えて下さい。後述の espar form 管理画面の(3)で使用します。
(16) [完了] をクリックしてダイアログを閉じます。

項目面	入力内容
説明	任意の文章
ウェブアプリ	自分(本操作を行っているアカウント)
アクセスできるユーザ	全員

2. espar form 管理画面の設定

(1) espar form の管理画面にログインして [フォーム] タブをクリックし、Google スプレッドシートと連携させたいフォームを一覧から選択します。
(2) [外部連携] タブをクリックして [Googleスプレッドシート] タブを選択して下さい。
(3) [Googleスプレッドシート連携を使用する]にチェックを入れ、「API URL」の欄に先の手順スプレッドシート側の準備の (11) でコピーしておいたURLをそのまま貼り付けて下さい。
(4) [変更を保存] をクリックします。これで設定は完了です。(espar form の管理画面上には他の設定項目がありますが、それらは高度な設定を参照して下さい)
(5) espar form を使った問い合わせフォームから送信テストを行って下さい。正しく連携されていると、スプレッドシートに問い合わせ内容が蓄積されていきます。

Googleスプレッドシートには、『1つのスプレッドシート内で500万セル』という上限があります。(行数ではなくセル数であることに留意)
列数にもよりますが、スプレッドシートは大量のレコードを扱うのには向いていません。軽快に動作させられるのは、数百レコード程度と考えておくのが賢明です。よって、入力フォームへの入力件数が多い場合、手動で定期的にCSVダウンロードしてシートは都度クリアしたり、自動的にCSV化してDriveにファイル保存するスクリプトを組む、などの工夫をすることが推奨されます。

高度な設定

Google スプレッドシート連携では、高度な連携を行うために使える設定を利用できます。

HTTPヘッダー

スプレッドシート側の準備の手順(14)で取得するウェブアプリのURLはAPIとして機能します。

espar form のGoogleスプレッドシート連携では、APIのURLにHTTPのPOSTメソッドでリクエストします。その際に、任意のHTTPヘッダーを付加することができます。詳しくは、RestAPIページのHTTPヘッダーをご覧下さい。

なお、スプレッドシート連携では、指定したHTTPヘッダーをGAS側で以下のように取得できます。

function doPost(e) {
  const headers = e && e.headers ? e.headers : {};
  const X-Esparform-Source = headers['X-Esparform-Source'] || '';
}

添付ファイルを送信する

espar form では、添付ファイルを受け付けるフォームを実装できます。「添付ファイルを送信する」のチェックをONにすると、添付ファイルもGAS側に渡すことができ、Google Drive に保存する等の処理ができるようになります。

フォームに <input type="file" name="file1"> のような添付ファイル要素がある場合、APIにPOSTする時のbodyは以下のようになります。(添付ファイル部分のみ記載)

{
  ...(略)...
  "file1": [
    {
      "filename": "test.pdf",
      "mediatype": "application/pdf",
      "data": "JVBERi0xLjMKJc...."
    }
  ]
}

<input type="file" name="file1" multiple> のように multiple が指定されていて、複数ファイルを添付できる場合、以下のようになります。

{
  ...(略)...
  "file1": [
    {
      "filename": "test.pdf",
      "mediatype": "application/pdf",
      "data": "JVBERi0xLjMKJc...."
    },
    ...(略)...
    {
      "filename": "sample.png",
      "mediatype": "image/png",
      "data": "EgMCBvYmoKPDwv...."
    }
  ]
}

添付ファイルはGAS上でファイルとして抽出することができます。以下は Google Drive に保存する例です。

function doPost(e){
  var postData = JSON.parse(e.postData.contents);

  // name=file1 の添付ファイルを保存
  if (postData.file1) {
    for (var attachment of postData.file1) {
      // Base64化されたファイルをデコード
      const data = Utilities.base64Decode(attachment.data);
      // ファイルオブジェクト(blob)を作る
      const blob = Utilities.newBlob(data, attachment.mediatype, attachment.filename);
      // 保存するフォルダを取得して保存
      const folder = DriveApp.getFolderById('xxxxxxxx');
      folder.createFile(blob);
    }
  }
}

この他、ファイルを加工してGoogleDriveに保存したり、ファイルを解析して値を抽出しスプレッドシートに記録するといったように要件に応じて自由に処理を実装できます。

API のレスポンスボディを返す

RestAPIページのAPI のレスポンスボディを返すをご覧下さい。

留意事項

連携に使うアカウントについて

本連携は無償のGogoleアカウントでも使用可能です。

しかし、パスワード紛失やGoogle社によるアカウント停止(BAN)等によって、連携先のスプレッドシートにアクセスできなくなるリスクがゼロではない点は十分に留意して下さい。定期的にCSVダウンロードをする等の運用を行うことをお勧めします。

espar form との連携を行う場合は、法人/組織用の Gogole Workspace の契約が推奨されます。諸事情で Google アカウントで連携するしかない場合、本連携のためだけの専用アカウントを作成し本用途以外には使用しない運用(当該アカウントでは Gmail や YouTube 等を使用しない)を強く推奨します。

デプロイの更新

稀に、Google社のサーバ負荷が原因で SpreadSheet 連携が動作しなくなることがあります。多くの場合、連携時のSpreadSheet 側の準備の手順(8)で生成したURLが機能しなくなっていることに起因しています。万が一、フォームの内容が SpreadSheet に登録できなくなった場合、以下の操作を行ってみてください。

(1) メニューから [拡張機能]→[App Script] をクリックします。
(2) スクリプトエディタが開きますので、[デプロイ]から[デプロイを管理]をクリックします。
(3) ダイアログが表示されます。「設定」欄の右端にある「編集」ボタンをクリックします。
(4) 編集可能になりますのでバージョンのリストボックスで [新バージョン] を選択します。
(5) その後、ダイアログ最下部にある「デプロイ」をクリックして下さい。
(6) デプロイを更新した旨のダイアログが表示されます。

以上で更新作業が完了です。espar form の管理画面を更新する必要はありません。改めてフォームに入力して、スプレッドシートに連携されることを確認してください。