GASでGoogleドキュメントのコメントを集計する #GoogleAppsScript

はじめに

自分は海外版ゲームのQAに関わっています。

そこでは、ゲーム内お知らせに翻訳ミスなどがあった場合にGoogleドキュメントに本文を記載しコメントをつけて不具合報告をする、という運用になっています。
この方法は文章中の不具合箇所などがわかりやすいのですが、チケット管理されていない為テスト分析をおこなう際にどれだけの不具合が発生しているか把握することが難しい状態でした。

そこで不具合数を可視化する為にコメントを集計するGoogle Apps Script(以下GAS)を作成しました。
ドキュメントのコメント一覧の取得にGoogle Drive APIを使います。
必要なプロパティを配列に格納して、まとめてスプレッドシートに書き出します。

要件

集計したいドキュメントのURLをまとめて指定して複数ファイルを一気に処理する
リプライを含めて1コメント1行とし施策情報と紐付けて集計用スプレッドシートに記載する
施策毎に1つのドキュメントファイルが作成される
施策管理シートに全ての施策名やリリース日時、ドキュメントファイルのURLが記載されている

集計する情報

施策管理シートから取得する

プロダクト
施策名
リリース日時
ドキュメントURL

コメントから取得する

記載日時
FB箇所
コメント記載者
コメント内容
リプライ記載者
リプライ内容

実装

URLを指定してコメント一覧を取得する.gs

var url = "ドキュメントファイルのURL"
//ドキュメントの閲覧権限が無いとエラーで止まるので該当のURLを返す
try{
  var fileid = DocumentApp.openByUrl(url).getId();
}
catch(e){
  console.error(url);
}
var comments =Drive.Comments.list(fileid, {'maxResults':100});

複数のファイルをまとめて処理する時にどれかのファイルの権限がなくて読み込めずにエラーになる可能性があるので、エラーになったファイルURLをエラーログに返す様にしています。
APIに渡すパラメータのmaxResultsはレスポンスに含む最大のコメント数です。
デフォルトだと20までしか含まれないので最大の100を指定しています。

コメントを取得するAPI

Google Drive API のComents.listでコメントの一覧を取得しています。
GASから利用する方法はこちら(スプレッドシートのコメントを取得する)の記事を参考にしました。

コメントはこちら(Google Drive API:Comments)に記載のオブジェクト形式で取得できます。

ファイル内に複数のコメントがある場合は以下の様にそれぞれのコメントオブジェクトがitems配列内に格納されたオブジェクトとして渡されます。

{
     "items": [
        {コメントオブジェクト1},
        {コメントオブジェクト2},,,
    ]
}

必要なプロパティを取り出して配列に格納して書き出す

コメント書き出し.gs


  var data = [];
  var head = ["プロダクト","施策名","リリース日時","URL"];
  var comments = {コメントオブジェクト};
　var maxCol =0;
  var cCount = Object.keys(comments["items"]).length; 

  //コメントの必要なプロパティを1行ずつ取得
  for(var i=0; i<cCount; i++){
    var tempArray =[];
    Array.prototype.push.apply(tempArray,head);
    var items = comments["items"][i];

    var formattedDate = Utilities.formatDate(new Date(items["createdDate"]), "JST", "yyyy/MM/dd HH:mm:ss"); //日時のフォーマットを RFC3339から変換
    tempArray.push(formattedDate); //記載日時

    // コメント箇所に空白を選択していてcontextのvalueがUndifinedなら空欄にする
    if("context" in items) { 
     tempArray.push(items["context"]["value"]); 
    } else {
     tempArray.push(" ");
    }

    tempArray.push(items["author"]["displayName"]); //記載者
    tempArray.push(items["content"]); //コメント内容

    var replyCount = Object.keys(comments["items"][i]["replies"]).length;  //i番目のコメントのリプライ数  

    //リプライを行の末尾へ追加
    for(var j=0; j<replyCount; j++){
      var repContent = items["replies"][j]["content"];
      if(repContent != ""){
        tempArray.push(items["replies"][j]["author"]["displayName"]); //リプライ者
        tempArray.push(repContent); //リプライ内容
      }
    }
    //最大の列数(reply数)を取得
    if(maxCol < tempArray.length){
      maxCol =tempArray.length;
    }
    data.push(tempArray);
  }
  data.reverse();

//スプレッドシートに書き出す
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート名");
  var lastRow = sheet.getLastRow() + 1;
  data.forEach(function(item){
    item.length = maxCol
  })
  sheet.getRange(lastRow, 1, data.length, maxCol).setValues(data);

リプライが何回続くかコメント毎に違い、最大数もあらかじめわからないので少し面倒な処理になっています。

つまずいたポイントとしてはコメントする時に空白を選択されているとcontextがundifinedになってエラーになったことです。
スプレッドシートに書き出すので配列として処理してますが、オブジェクトのまま処理した方がもっとスマートかもしれません。

結果

1コメント1レコードでスプレッドシートにデータを書き出しています。
これによってQUERY()関数などを使ってプロダクトや施策数毎のコメント数(不具合報告数)が可視化でき、具体的な改善提案を行うことができるようになりました。

コメントの内容も記録しているので、テキストマイニングを使って頻出する指摘の傾向などの分析にも挑戦しています。