Google Apps ScriptでAppSheet APIを使って差込印刷をする【GAS】

Google Apps Scriptは確かに万能なのですが、自身の仕組みで例えば「差し込み印刷」などを実現するのは非常に大変です。そして何よりも自身であらゆるロジックを構築できるスキルが求められます。

そんなGASの弱点を逆にAppSheetで補う事の出来る手段が、AppSheet API。この仕組みを利用することで、AppSheet経由でデータを追加してAutomationを発動させることが可能です。今回はこの仕組みにチャレンジ。

今回利用する素材

• AppSheet API Call - Google Spreadsheet
• AppSheet API Call - AppSheet Template
• 請求書テンプレート - Google Document

GASの弱点である差し込み印刷。しかし、AppSheetでは申請書や請求書についてDocumentに差し込む形でPDF化が容易に実現出来ます。しかし、土台のスプレッドシートに1行追加しても動いてはくれません。そこで、AppSheet API経由でレコード追加を行い、Automationを発火させてPDFを生成させる。

その為のPDF生成の仕組みについては以下のエントリーでまとめています。AppSheetテンプレートをコピーした後に請求書テンプレートの差し替えが必要ですが、この後にnot found問題が出るケースがあるので、その理由等を知っておくと慌てず対処が可能です。

尚、AppSheet APIはGoogle Workspace付帯のAppSheet Coreライセンスでも利用が可能です。他のシステムから請求書を発行したいなんて時にAPIを介してAppSheetに送り込めば、綺麗な請求書を発行することが可能です。

AppSheetで申請書等のPDFを生成してみた

差し込み印刷の過去の課題

過去にGASを持ってして何度か差し込み印刷にチャレンジしたことがあります。その中で得た知見としては、GASでGoogle Documentを操って差込印刷というかPDFを連続生成するのは非常に難しいです。主にぶつかった制約は以下のようなもの。

• Google Document複製のパターンだと1日1500枚までの制限があります。
• ドキュメント複製をバッチ処理で行うと、Drive APIの制約で503エラーが結構頻発する
• 複製してから文字のReplaceを行いPDF化するので1枚作るのに結構な時間が掛かる上に、メソッドを何度も叩くことになる。
• 複製せずにReplaceして元に戻す処理の実装が如何ともしがたいほど難しい（元に戻したら画像が表示されないなど）
• 結果、テーブルを使って部分的に差込と置き換えをする手法が現時点での出来る最高の方法

ましてや、明細部分となるとDocumentの表はスプレッドシートの表とは違って非常に扱いにくい上に、セル1個単位でReplaceをしていく必要があり、実装が複雑になるというデメリットだらけでした。

AppSheetであればこのあたりの課題がすべてクリア出来る為、AppSheet APIを使ってPDF生成をやらせてしまおういうのが今回の狙いになります。

Google Apps ScriptでDocumentのテーブルの値を読み書き【GAS】

Google Apps Scriptで差込で直接PDFを生成する【GAS】

事前準備

AppSheet側

APIを利用するためには、AppSheetの対象プロジェクトにていくつか取得しなければならないものがあります。以下の手順でそれらの値を取得しておきましょう。

1. AppSheetの対象プロジェクトを開く
2. まずはデータをAPIで追加するテーブルの名前を取得しておきます。
3. 左サイドパネルにて、Settingsを開く
4. 次に一番下にあるIntegrationsをクリックする
5. IN: from cloud services to your appという項目で、Enableのスイッチをオンにする
6. この時うっすら文字で表示されているApp Idの値を控えておきます。
7. Create Application Access Keyというボタンをクリックする
8. 1個生成されるのでShow Access keyをクリックする
9. 長い文字列でAccess Keyが出てくるのでこの値を控えておきます。

図：ここの2つの値の取得が必要です

GAS側

前述で取得しておいた3つの値をGAS側で利用しますが、App IdとAccess Keyは大切なものなので漏洩などしないように厳格に扱う必要があります。

これらの値をスクリプトプロパティに格納します。

• accessKey : ここに取得しておいたAccess Keyの値を格納します
• appId : ここに取得しておいたApp Idの値を格納します

データ追加をするテーブル名については、GASのグローバル設定.gsを作っておきそこで格納しておきます。

図：スクリプトプロパティに格納する

構築方法

AppSheet側

APIを呼び出して、挿入されたレコードに基づいてPDF生成は手動でも出来るようにしておくことくらいで、今回のミッションにおいて特別AppSheet側でやるべきことがありません。APIリクエストを行うと自動でレコード挿入され、結果がスプレッドシートに反映しますし、その際のAutomationも適切にセットしておけばAPIリクエスト経由でも発動するようになります。

注意点としては請求明細無しの請求書であれば、EventのData change typeはAddのみでOKですが、請求明細をつけたい場合にはこれだけだと具合が悪い。その理由は

• 請求部分のデータをインサートだけで発動してしまう。明細の追加前に動かれては困る。
• 故に請求明細もつけたい場合はUpdatesのみとして、GAS側から請求データ追加→明細データ追加→Trigger値を別途更新を掛ける→Conditionで判定して発火を制御　といった手順が必要になります。その際のConditionの数式例は以下の通り

  [_THISROW_BEFORE].[更新フラグ] <> [_THISROW_AFTER].[更新フラグ]

• また、ファイルパスをFile型の列に追加も必要になるのでその為のフローも必要になります。
• メールで請求書送信などのフローも作成しておくと尚良いでしょう。

今回はAppSheet側のUIはユーザーが直接アプリを使う機会は殆どないので作り込みは不要です。後で確認したり、PDFだけを手動で取り出したい時などに使う用途になるので、複雑な仕組みは必要ありません。

図：Automationでのロジックが重要

GAS側コード

今回は明細の追加はせずに請求書のみのシンプル仕様です。ゆえにレコードの追加だけで発動するようにAppSheet側も仕掛けをしてあります。明細を追加する場合は、前述の通り、AppSheet側の発動条件も変更し、GAS側も値の更新用の仕掛けが必要になります。

時間があれば明細を追加時の処理もここに追記してみたいと思います。

グローバル設定

スクリプトプロパティから値を取得や、AppSheet APIのリクエストエンドポイントのURLの構築はここで行っておきます。また、前述で取得しておいたデータ追加先のテーブル名をtableName変数にここで入れておきましょう。

//スクリプトプロパティから値を取得
const prop = PropertiesService.getScriptProperties()
const appId = prop.getProperty("appId");
const accessKey = prop.getProperty("accessKey");

//書き込み先テーブル名
const tableName = 'シート1';

//エンドポイントURL
const url = `https://api.appsheet.com/api/v2/apps/${appId}/tables/${tableName}/Action`;

データを追加するコード

顧客名と請求金額を適当に受け取って入れるだけのaddAppSheetRecord関数を作りました。中身は適当なので本番で使う場合にはもう少ししっかりと作り込みが必要です。

レコードデータをnewRowにてJSON形式で作りますがこの時の要素名は、対象テーブルの列名と完全一致していないと挿入されませんので要注意です。また、newRowは複数入れることが出来るので、リクエスト時には配列にしておく必要があります。

そしてリクエスト成功後にjson.Rows[0]の中に各列の値が入っています。PDF列を指定すれば生成したPDFファイルのファイル名を取り出せるので、直接PDFのIDを生成先フォルダから追跡することが出来ます。

図：無事にデータが挿入されました

図：AppSheet上でも確認出来た

// AppSheet APIを呼び出して、指定されたデータを1行追加する関数
function addAppSheetRecord(customerName,billingAmount) {
  //IDを生成する
  const recid = Utilities.getUuid();

  // 追加するレコードのデータを作成
  const newRow = {
    'ID': recid, // #ID : uuidの値を生成
    '顧客名': customerName,
    '請求日付': Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd'), // #日付: yyyy/mm/ddの形式で今日の日付
    '請求金額': billingAmount,
    '消費税8%': 0, // #8%消費税: 0固定
    '消費税10%': Math.floor(billingAmount * 0.1) // #10%消費税: 請求金額の10%
  };

  // AppSheet APIに送信するペイロードを構成
  const payload = {
    Action: 'Add',
    Properties: {
      Locale: 'ja-JP'
    },
    Rows: [newRow] // Rowsは配列であることに注意
  };

  // HTTPリクエストのオプションを設定
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'ApplicationAccessKey': accessKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true // エラー時に例外をスローせず、レスポンスを返す
  };

  try {
    // APIにリクエストを送信
    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const responseBody = response.getContentText();

    // 結果をログに出力
    if (responseCode === 200) {
      //JSONを取得する
      let json = JSON.parse(responseBody);

      //PDFのファイルパスを取得する
      let pdfpath = json.Rows[0].PDF;
      console.log(pdfpath)

    } else {
      console.log('レコードの追加に失敗しました。');
      console.log('ステータスコード: ' + responseCode);
      console.log('エラー内容: ' + responseBody);
    }
  } catch (e) {
    console.log('API呼び出し中にエラーが発生しました。');
    console.log(e.toString());
  }
}

関連リンク

• New Feature for the AppSheetApp Library: Run API Calls in Parallel
• AppSheet Automation構築におけるAppSheet APIの利用
• Call AppSheet API from Apps Script