はじめに

Google Spreadsheets に対して、外部のAPIから行を追加したいケースがあると思います。
今回は、Sheets API を使って実行しようと思ったのですが、チェックボックスの追加がうまく行かなったかったので、その解決方法を記事にしました。

Sheets API

認証は GCP での設定なども必要なのでちょっと面倒です（本記事の趣旨ではないでの省略）。

やりたいこと

Google Spread Sheets に　API経由で新しい行を追加したい。
行にチェックボックスを追加したい。

↓こんな感じですね。

何が難しいの？

新しい行を追加するには、spreadsheets.value.append()を使うのが良さそうです。

具体的なコードはこんな感じになります。

行の追加

    // 認証情報の生成
    const auth = new google.auth.GoogleAuth({
      keyFile: [認証情報が記載されたJSONファイルのパス],
      scopes: 'https://www.googleapis.com/auth/spreadsheets',
    });

    // 認証クライアントの作成
    const authClientObject = await auth.getClient();

    // Google sheets インスタンス作成
    const googleSheetsInstance = google.sheets({
      version: 'v4',
      auth: authClientObject,
    });

    const now = new Data();
    const number = '+8180XXXXXXXX';
    // Google Spreadsheets に新しい行を追加する
    const result = await googleSheetsInstance.spreadsheets.values.append({
      auth,
      spreadsheetId: [シートID],
      range: 'シート1!A:B',
      valueInputOption: 'USER_ENTERED',
      resource: {
        values: [[now, `'${number}`]],
      },
    });

うん。簡単ですね。
ちなみに、numberは電話番号なんですけど、先頭に+が入ってしまうとうまく表示されないので、（シングルクォーテーション）を先頭に入れています。

本当は、このタイミングでチェックボックスも入れちゃいたいのですが、実はspreadsheets.value.append()ではチェックボックスが入れられないんです。

であれば、GASを使って行が追加されたことを認識してチェックボックスをいれようかとも考えたのですが、APIで行を追加すると Apps Script の編集トリガーが発火しないことがわかり、この方法もボツになりました（しかもこの方法は筋が悪い）。
先人の知恵もほとんどなく（appendじゃ追加できないってことはわかった）、ここで途方に暮れるわけです。

解決方法

悩んだ末に、以下のコードでチェックボックスを追加することができました。

チェックボックスの追加

    // 先ほど紹介した行の追加部分は省略

    // チェックボックスを入れたい行（Row）を検索します(resultはappendメソッドの返却値)。
    const updateRow = Number(
      result.data.updates.updatedRange.match(/!A\d*/)[0].replace('!A', ''),
    );
    // チェックボックスを追加するリクエスト文
    const resource = {
      requests: [
        {
          updateCells: {
            rows: [
              {
                values: [
                  {
                    dataValidation: {
                      condition: {
                        type: 'BOOLEAN',
                      },
                    },
                  },
                ],
              },
            ],
            fields: 'dataValidation',
            range: {
              sheetId: 0,
              startRowIndex: updateRow - 1,
              endRowIndex: updateRow,
              startColumnIndex: 2, // 今回はC列に入れるので「2」
              endColumnIndex: 3,
            },
          },
        },
      ],
    };

    // APIの呼び出し
    await googleSheetsInstance.spreadsheets.batchUpdate({
      spreadsheetId: context.SPEADSHEETS_ID,
      resource,
    });

まず、spreadsheets.value.append()を実行した結果から追加した行を探します。
その後、チェックボックスをセルに追加（正確にはセルの内容を更新）するリクエストを作成します。
セルを更新するためのAPIとして、spreadsheets.batchUpdate()を利用します。

とりあえず、これで行の追加とチェックボックスの追加を同時に行うことができます。

GoogleのAPIはすごくドキュメントが充実しているように見えますが、なれるまでは意外と分かりづらかったです。

自己紹介

高橋克己（Katsumi Takahashi）自称「赤い芸人」
グローバル・インターネット・ジャパン株式会社　代表取締役
株式会社KDDIウェブコミュニケーションズ　Twilio事業部エバンジェリスト

2001年より大手通信事業者の法人サービスの教育に携わり、企業における電話のしくみや重要性を研究。2016年よりTwilio事業部にジョインし、Twilioを使ったスマートコミュニケーションの普及活動を精力的に行っている。
2015 Hall of Doers
2019 Twilio Champions