トップ > CCI > Google Data Portalでリモートワーク中の出社状況を可視化してみた

Google Data Portalでリモートワーク中の出社状況を可視化してみた

CCI Google Apps Script Google Data Portal 可視化

はじめに

はじめまして。asuminです。

みなさん、出社してますか？
私はリモートワークがメインとなり、あまり出社していません。
職場近くのランチのお店が恋しい。。。

それはさておき。
密を回避する目的で出社率をある程度コントロールする必要がでてきたため、私が所属しているTechDivではGoogleスプレッドシートで出社状況を管理しています。
今回はGAS（Google Apps Script）でスプレッドシートに出社状況の集計データを出力し、そのデータをGDP（Google Data Portal※旧GoogleDataStudio）を使って可視化してみたいと思います。

出社状況の可視化フロー

以下のフローで可視化していきます。

  1. GASでスプレッドシートに集計データを出力
  2. GDPで集計データを接続
  3. GDPで接続したデータを可視化

GASでスプレッドシートに集計データを出力

■出社管理スプレッドシートサンプル
　下記のスプレッドシートの集計データを別のスプレッドシートに出力します。

■GASのコード
　下記のGASのコードを実行します。
　※コードの詳細説明は今回は割愛させていただきます。

// input spread sheet info
const inputSpreadSheetId = '出社表スプレッドシート（集計元）ID';
const inputSheetName = '集計元対象シート名';

// 今日の日付
const targetMonth = new Date();

// output spread sheet info
const outputSpreadSheetId = '集計データ出力先シートID';
const outputSheetName = '集計結果出力シート名';

function main() {
  console.log("start");

  // データ取得
  let spreadsheetData = getCalenderData();
 
  let length = spreadsheetData.length;
  let outputArray = [];
  let dateCount = spreadsheetData[0].length -3;
  
  for (let i = 3; i < length; i++) {
    let name = spreadsheetData[i][2];
    let divName = spreadsheetData[i][4];
    let teamName = spreadsheetData[i][6];
    
    // 名前がないときは除外
    if (!name) {
      break;
    }
    if (divName != "テクノロジー") {
      continue;
    }
     for (let j = 0; j <= dateCount; j++) {
      let tagetDate = spreadsheetData[0][j];
      // 日付がないか、今月の日付でないときは対象外
      if(!tagetDate || !isTargetMonth(tagetDate)){
        continue;
      }
       
      // 出力情報を設定
      let outputRowData = [];
      outputRowData.push(spreadsheetData[0][j]);　//日付
      outputRowData.push(divName); //Div名
      outputRowData.push(teamName); // チーム名
      outputRowData.push(name); // 氏名
      outputRowData.push(getType(spreadsheetData[i][j])); //午前
      outputRowData.push(getType(spreadsheetData[i][j+1])); //午前
      outputArray.push(outputRowData); // 1件のデータ追加
      
      j++;
    }
  }
  
  // 今月の日付チェック
  function isTargetMonth(tagetDate){
    let d = new Date(tagetDate)
    return targetMonth.getFullYear() == d.getFullYear() &&
           targetMonth.getMonth()  == d.getMonth()
  }
  
  // 出力内容変換
  function getType(param){
     if(param == "出社")    return "出社";
     if(param == "休")      return "休";
     return "在宅"
  }
  
  // 出力先のスプレッドシートを取得
  let spreadsheet = SpreadsheetApp.openById(outputSpreadSheetId);
  // 出力先のシートを取得
  let sheet = spreadsheet.getSheetByName(outputSheetName);
  if (outputArray.length != 0) {
    if (sheet.getLastRow() != 0) {
      // 出力データを全部削除
      sheet.getRange(2,1, sheet.getLastRow(), sheet.getLastColumn()).clear();
    }
    // 出力データを書き込む
    sheet.getRange(2,1, outputArray.length, 6).setValues(outputArray);
  } else {
    console.error("登録件数が0件です。データを確認してください。");
  }
  
  console.log("end");
};

function getCalenderData() {
  let spreadsheet = SpreadsheetApp.openById(inputSpreadSheetId);
  let sheet = spreadsheet.getSheetByName(inputSheetName);
  return sheet.getRange(1,2,sheet.getLastRow(),sheet.getLastColumn()).getValues();

■出力された集計データのスプレッドシートサンプル
　GASを実行すると下記のフォーマットで集計データが出力されます。

■GASの自動実行設定（トリガー設定）
　毎日データ更新させたいので、任意の時間帯にGASが自動実行されるようにトリガー設定を行います。
　※ここでは、毎日午前9時台に実行されるようにトリガー設定してみます。

①トリガー（時計アイコン）を選択

②【トリガーを追加】ボタンをクリック

トリガー設定ポップアップ画面が表示されるので、下記の設定を行います。
③『イベントのソースを選択』で【時間主導型】を選択
④『時間ベースのトリガーのタイプを選択』で【日付ベースのタイマー】を選択
⑤『時刻を選択』で任意の時刻を選択※ここでは午前9時～10時を選択
⑥【保存】ボタンをクリック

トリガー一覧にトリガーが新規追加されます。
これで翌日から設定時間帯にGASが実行されるようになります。

GDPに集計データスプレッドシートを接続する

GDPにログインして、下記を実施します
※認証画面については割愛します。
①【作成】⇒【レポート】を選択
②【データのレポートへの追加】で『Googleスプレッドシート』を選択
③ 集計データが出力されたスプレッドシートを選択

レポートの編集画面が表示されます。

GDPで接続したデータを可視化する

ツールバーの『挿入』または、『グラフを追加』から追加したいグラフや表を選択してデータを可視化していきます。
※編集モードになっていない場合は、画面右上の【表示】ボタンをクリックして【編集】に切り替えてください。


今回は下記の項目を使用しました。

A：スコアカード


午前の出社数をスコアカードで表示します。
ツールバーの【挿入】⇒【スコアカード】で編集が可能です。
「データ」と「スタイル」を編集します。


「データ」では下記の通り設定しました。

・指標：出勤（午前）
・デフォルトに日付範囲：カスタム（今日）
　※比較期間：前の期間
・フィルタ：使用したいフィルタを選択します
　※該当フィルタがない場合は『フィルタを追加』リンクから必要に応じてフィルタを追加します。
※その他、初期値設定


「スタイル」では下記の通り設定しました。

・比較ラベルを隠す：チェック
・データが欠落：「0」と表示する
・指標名を隠す：チェック
※その他、初期値設定

B：円グラフ


チーム別の出社割合を円グラフで表示します。
ツールバーの【挿入】⇒【円グラフ】で編集が可能です。
「データ」と「スタイル」を編集します。


「データ」では下記の通り設定しました。

・配色：ディメンションの値
・デフォルトに日付範囲：カスタム（今日）
　※比較期間：前の期間
・フィルタ：使用したいフィルタを選択します
　※該当フィルタがない場合は『フィルタを追加』リンクから必要に応じてフィルタを追加します。
※その他、初期値設定


「スタイル」では下記の通り設定しました。

・配色：ディメンションの値
・ラベル：割合
※その他、初期値設定

C：縦棒グラフ


チーム別の出社割合を縦棒グラフで表示します。
ツールバーの【挿入】⇒【縦棒グラフ】で編集が可能です。
「データ」と「スタイル」を編集します。


「データ」では下記の通り設定しました。

・期間のディメンション：日付
・ディメンション：チーム
・内訳ディメンション：出勤（午前）
・デフォルトに日付範囲：カスタム（今日）
※その他、初期値設定


「スタイル」では下記の通り設定しました。

・棒グラフ：積み上げ棒グラフ
・配色：ディメンションの値
※その他、初期値設定

D：時系列グラフ


チーム別の出社状況を時系列グラフで表示します。
ツールバーの【挿入】⇒【時系列グラフ】で編集が可能です。
「データ」と「スタイル」を編集します。


「データ」では下記の通り設定しました。

・期間のディメンション：日付
・ディメンション：日付
・内訳ディメンション：出勤（午前）
・デフォルトに日付範囲：自動
※その他、初期値設定


「スタイル」では下記の通り設定しました。

・系列：折れ線
・全般：欠損データ：ゼロとして扱う
・軸：軸を表示
※その他、初期値設定

E：期間設定


表示期間を設定します。
ツールバーの【挿入】⇒【期間設定】でプロパティ編集が可能です。


「データ」では下記の通り設定しました。

・デフォルトに日付範囲：今月


「スタイル」では下記の通り設定しました。

・パディング：上下中央揃え
※その他、初期値設定

F：表グラフ


個人別の出社日数をランキング形式で表示します。
ツールバーの【挿入】⇒【表】で編集が可能です。
「データ」と「スタイル」を編集します。


「データ」では下記の通り設定しました。

・期間のディメンション：日付
・ディメンション：名前
・フィルタ：使用したいフィルタを選択します
　※該当フィルタがない場合は『フィルタを追加』リンクから必要に応じてフィルタを追加します。
※その他、初期値設定


「スタイル」では下記の通り設定しました。

・表のフッター：ページ設定を表示
・データが欠損：「０」と表示する
※その他、初期値設定

さいごに

GDP(Google Data Portal)はデータ可視化に慣れていなていない私にも、とても使いやすかったです！「データの可視化ってやってみたいけど難しそうだしどうしよう」って悩んでる人にも、おススメです。

業務外ですが、個人的に小ぶりなモンスターをゲットしてGoする位置ゲームをやっているので、モンスター捕獲数・バトル数・目標取得経験値など色々とGDPで可視化して、効率的な経験値UP方法について考察していこうと思います。
目指せ、レベル50！

GASについてはまだまだ勉強不足なので、次回以降はGASの活用について考えていければと思います。