はじめに

　こんにちは，学生アルバイトのYamamotoです．

　今回は，Google Apps ScriptのdoPost関数を用いて，Google Spread Sheetにデータを書き込む方法をご紹介いたします．Google Apps Scriptとは，Googleが提供しているアプリケーション開発プラットフォームで，これを用いることでGoogle Workspaceのサービス（スプレッドシート, Gmail, ドライブなど）と統合したアプリケーションを作成することができます．

　今回ご紹介する手法を用いる利点は，環境構築が不要であること，これに尽きます．インストールするものや，認証が必要なものがないため，どなたでもお手軽に使用することができます．

　環境構築なしでGoogle Spread Sheetの内容を操作するために，Google Apps Scriptを使いましょう！というのが今回のテーマです．

　それでは本題に移ります．

レポートツールとしてのGoogle Spread Sheet

　Google Spread Sheetはさまざまな目的で使用されていますが，レポートツールとしても非常に有用です．以下の画像に示す，りんご・みかん・ぶどうの1日あたりの売り上げ個数を集計した表を例として，その有用性をご紹介いたします．

　こちらの表では，カラースケールという機能が使用されています．カラースケールとは，入力されたデータに応じて自動的にセルの色付けを行うもので，「表示形式（もしくはセルを右クリック）」→「条件付き書式」から設定することができます．この機能を用いるだけでデータの傾向や異常値の検出が一目でわかるようになるため，Google Spread Sheetはとても便利なレポートツールであるといえます．

今回POSTするJSON

　本記事では，先ほどご紹介した表に対し，以下のようなJSONを外部からpostする場合についてご紹介します．

{
    "action": "replace",
    "sheetName": "sales",
    "rows": [
        {"年月日": "2021-12-16",
        "りんご": 13,
        "みかん": 6,
        "ぶどう": 9},
        {"年月日": "2021-12-17",
        "りんご": 9,
        "みかん": 3,
        "ぶどう": 7}
    ]
}

　それぞれのキーと値については，このようになっております．

• action...以下の4種類
  • replace...キーが同じならupdate，キーがなければinsert.キーは表の一番左のカラム名
  • insert...表の最終行に追加
  • truncate...表をクリア．ただしヘッダーは消さない
  • delete...行単位で削除（rowsの主キーフィールドの値を主キーにして削除して詰める）．
• sheetName...対象のシート名文字列
• rows...各行のハッシュの配列が入る．ハッシュの各キーは，表のヘッダーと対応．

　また，表のヘッダーはすでに入力されていると仮定し，セキュリティ上参照機能は設けないものとします．

　したがって，今回のJSONは"年月日"が"2021-12-16"の場所に関してはそれぞれのデータを更新し，"2021-12-17"の場所に関しては新たにデータを挿入するという処理を加えるものとなっております．

サンプルソース

　こちらがサンプルソースです．処理の詳細に関しては，Google Apps Scriptの公式レファレンスをご覧ください．

function doPost(e){
  let postContent = e.postData.getDataAsString();
  main(postContent);
  let output = ContentService.createTextOutput();
  output.setMimeType(ContentService.MimeType.JSON);
  output.setContent(JSON.stringify({ message: "success" }));
  return output;
}

function main(message){
  let json = JSON.parse(message);
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(json.sheetName);
  let headerRowNum = getHeaderRowNum(sheet);
  let headerRow = sheet.getDataRange().getValues()[headerRowNum - 1];
  let idColomnNum = getIdColomnNum(headerRow);

  if (json.action == "replace"){
    replaceRows(sheet, headerRowNum, idColomnNum, json);
  }else if(json.action == "truncate"){
    truncateTable(sheet, headerRowNum, idColomnNum);
  }else if(json.action == "delete"){
    deleteRows(sheet, headerRowNum, idColomnNum, json)
  }else if(json.action == "insert"){
    insertRows(sheet, headerRow, json)
  }
}

// 固定されている行数を取得する。なかったら1を返却（初めの行）
function getHeaderRowNum(sheet) {
  let frozen = sheet.getFrozenRows()
  if ( frozen == 0 ){
    return 1
  } else {
    return frozen
  }
}

// データの左端カラムの位置を取得。
function getIdColomnNum(headerRow){
  let i = 0
  for (const header of headerRow){
    if ( header === ""){
      i++;
      continue;
    } else {
      break;
    }
  }
  return i + 1;
}

//カラム名からカラムidの表を生成する (col_hash)
function createColIndex(sheet, headerRowNum, idColomnNum){
  let currentColomn = idColomnNum
  let result = {}
  let currentRange = sheet.getRange(headerRowNum,currentColomn)
  while(currentRange.getValue() != ""){
    result[currentRange.getValue()] = currentColomn
    currentColomn+=1;
    currentRange = sheet.getRange(headerRowNum, currentColomn)
  }
  return result;
}

function currentRowFromId(sheet, headerRowNum, idColomnNum, id){
  let last_row = sheet.getLastRow();
  let id_datas = sheet.getRange(headerRowNum, idColomnNum, last_row - headerRowNum + 1).getDisplayValues();
  for(let row_num = 1; row_num < id_datas.length; row_num++){
    if(id_datas[row_num][0] === id){
      return headerRowNum + row_num;
    }
  }
  return last_row + 1;
}

//replaceメソッド
function modifyRow(sheet, row, colIndex, currentRow){
  for (const colName of Object.keys(row)){
    sheet.getRange(currentRow, colIndex[colName]).setValue(row[colName])
  }
}

function replaceRows(sheet, headerRowNum, idColomnNum, jsonMessage){
  let colIndex = createColIndex(sheet, headerRowNum, idColomnNum);
  for (const row of jsonMessage.rows){
    let id_hash = Object.entries(row)[0]
    let currentRow = currentRowFromId(sheet, headerRowNum, idColomnNum, id_hash[1]);
    modifyRow(sheet, row, colIndex, currentRow);
  }
}

// insertメソッド
function insertRows(sheet, headerRow, jsonMessage){
  for (let row of jsonMessage.rows){
    let rowArray = []
    for (let colName of headerRow){
      rowArray.push(row[colName])
    }
    //作成した空配列とデータの配列を結合し末尾に挿入
    sheet.appendRow(rowArray);
  }
}

// truncateメソッド
// ヘッダーより下，左端カラムより右の、実データの範囲を取得(ヘッダーは含まない)
function getContentRange(sheet, headerRowNum, idColomnNum) {
  let start_row = headerRowNum + 1;
  let start_col = idColomnNum;
  let last_col = sheet.getLastColumn();
  let last_row = sheet.getLastRow();
  let row_range = last_row - start_row + 1;
  let col_range = last_col - start_col + 1;
  return sheet.getRange(start_row, start_col, row_range, col_range);
}

function truncateTable(sheet, headerRowNum, idColomnNum){
  let contentRange = getContentRange(sheet, headerRowNum, idColomnNum);
  contentRange.clearContent();
}

// deleteメソッド
function deleteRows(sheet, headerRowNum, idColomnNum, jsonMessage){
  for (const row of jsonMessage.rows){
    let id_hash = Object.entries(row)[0]
    let currentRow = currentRowFromId(sheet, headerRowNum, idColomnNum, id_hash[1]);
    sheet.deleteRows(currentRow);
  }
}

各機能の説明

replaceメソッド

　主キーの値が同じデータをシート内で検索し，見つかればその行を更新．見つからなければ最終行の下に挿入する．

insertメソッド

　シート内検索は行わず，最終行の下に挿入する．（主キーの重複を許す）

truncateメソッド

　シート内データのうち，ヘッダーを除いた部分の範囲を取得し，値をクリアする．clearContent()関数を用いることにより，メモや条件付き書式などは残ったまま，値のみが削除される．

deleteメソッド

　replaceメソッド同様，主キーの値が同じデータをシート内で検索．見つかればその行を削除．

デプロイ方法

　実際にこちらのスクリプトをWebアプリとして動かすには，スクリプトファイルを作成したのち，デプロイという処理を行う必要があります．以下，デプロイの手順についてご説明いたします．

• 「デプロイ」→「新しいデプロイ」をクリック
• 以下のように，"次のユーザーとして実行"はシートの管理者（今回の場合は私です），"アクセスできるユーザー"は全員にする

• デプロイID，ウェブアプリのURLが表示されるため，ウェブアプリのURLをコピーしておく（外部からのリクエストの際に用いるため）

リクエストサンプル

　デプロイまで完了しましたら，作成完了です．実際に外部からJSONをPOSTしてみましょう．curlでリクエストした場合のサンプルはこちらになります．WebアプリのURLは，デプロイした際に取得したURLをそのままペーストします．

curl -H "Accept: application/json" -H "Content-Type: application/json" \
-d '{"action": "replace","sheetName": "sales","rows": [{"年月日": "2021-12-16","りんご": 13,"みかん": 6,"ぶどう": 9},{"年月日": "2021-12-17","りんご": 9,"みかん": 3,"ぶどう": 7}]}' \
-L WebアプリのURL

　実際に動かした結果がこちらになります．はじめに添付した表と比較して，2021-12-16のデータが更新され，2021-12-17のデータが新たに加わっているのがわかるかと思います．また，新たに更新・追加されたデータにもカラースケールが適用されているため，セルに設定されている書式などを破壊せずに値を編集できていることも確認できます．

RubyのActiveRecordとの連携サンプル

　ここからは応用例として，RubyでDB上のデータを集計するクエリを流し，それをJSONにしたのちSpread SheetにPOSTするサンプルコードをご紹介します．Ruby, DBのバージョンはそれぞれ以下の通りです．

• Ruby : ruby 2.6.5p114 (2019-10-01 revision 67812) [-darwin20]
• mysql  : Ver 15.1 Distrib 10.5.9-MariaDB, for osx10.16 (arm64) using readline 5.1

集計対象のデータについて

　今回は，以下の画像のようなテーブルを作成し，集計を行います．"datetime"は購入された日時，"product"は購入された商品であり，各商品において日別の集計を行います．

集計クエリ

　以下のSQLを用いることで，集計ができます．AS '◯◯◯'の部分の表記は，Spread Sheetのheaderに合わせてください．

SELECT DATE_FORMAT(products.datetime, '%Y-%m-%d')AS '年月日', 
   　　　　　　　sum(products.product = 'りんご') AS 'りんご', 
          sum(products.product = 'みかん') AS 'みかん', 
          sum(products.product = 'ぶどう') AS 'ぶどう' 
          FROM products 
          GROUP BY 年月日;

Rubyサンプル

　お待たせいたしました．こちらがRubyのサンプルです．sqlやwebapi_urlなどを適宜変更するだけで，DB上のデータを集計しSpread Sheetに挿入することができます．このサンプルプログラムでは，JSONにシングルクオートは入らない前提で，-d '#{post_json}'部分のシングルクオートにエスケープは入れておりません．

require 'json'
require 'active_record'

# DB接続処理
ActiveRecord::Base.establish_connection(
        :adapter   => 'mysql2',
        :database  => 'daily_sales',
        :host      => 'localhost',
        :username  => 'root',
        :charset   => 'utf8mb4',
        :encoding  => 'utf8mb4',
        :collation => 'utf8mb4_general_ci',
        :password  => ''
)

# sqlコマンドで集計した結果を配列で取得
sql_str = "SELECT DATE_FORMAT(products.datetime, '%Y-%m-%d')AS '年月日', \
          sum(products.product = 'りんご') AS 'りんご', \
          sum(products.product = 'みかん') AS 'みかん', \
          sum(products.product = 'ぶどう') AS 'ぶどう' \
          FROM products \
          GROUP BY 年月日;"
data_rows = ActiveRecord::Base.connection.select_all(sql_str).to_a

# 投稿するためのjsonを作成
post_hash = {
  'action'    => 'replace',
  'sheetName' => 'sales',
  'rows'      => data_rows
}
post_json = post_hash.to_json

# curlでwebアプリにリクエスト
webapi_url = ◯◯◯
curl = <<~CURL
      curl -H "Accept: application/json" \
      -H "Content-Type: application/json" \
      -d '#{post_json}' \
      -L #{webapi_url}
CURL
IO.popen(curl, &:read)

セキュリティについて

　今回作成したスクリプトは，WebアプリのURLさえわかればどこからでも投稿できるものとなっております．しかし，今回はPOSTのみを許容しており，データの取り出し機能（GET）は設けていないため，データが外部に漏洩するリスクは低いと言えます．

　外部からSpread Sheetに勝手に書き込まれることを懸念される方は，JSONのキー（Spread Sheetのヘッダー）の中にuniqueな文字列を入れておくと良いでしょう．今回のスクリプトでは，JSONのキーとSpread Sheetのヘッダーが紐づけられたのち処理が行われるので，キーを工夫することで外部書き込みのリスクを低減できます．

最後に

　今回は，Google Apps ScriptのPOST機能を用いて，外部からSpread Sheetに書き込むスクリプトをご紹介しました．また，RubyのActiveRecordと連携させることで，DB上のデータを集計し書き込む方法も述べました．後者のように応用的な使い方をする場合を除き，環境構築や認証などが不要で誰でも使用できるという強みがあります．「これ使ってみたい」，「便利そうだな」と思った方は，ぜひ活用してみてください！

　最後まで読んでいただきありがとうございました．それでは．