Ccmmutty logo
Commutty IT
7 min read

【実装編】GASでムードチャートを作成する

https://cdn.magicode.io/media/notebox/b90bfe81-0ebd-4ab2-94af-3a67f8005db9.jpeg

はじめに

対象読者

・GASでムードチャートを作成したい方

実運用イメージ

環境

GSuiteアカウント
▷Google Forms
▷Google スプレッドシート

実装

以下の順番で作成していきます。
  1. Google Forms
  2. スプレッドシート
  3. GAS

Google Forms

まずはGoogle Formsを作成します。

Forms本体の作成

Googleホーム画面のアプリランチャー(画面右上の点々9つ)から「Forms」を選択し、
空白のフォームから適当なフォーム・選択肢を作成します。
以下作成例です。
  1. スプレッドシート連携 回答タブからスプレッドシートアイコン(赤枠)をクリックします。
「新しいスプレッドシートを作成」をクリックします。
名前は任意の名前でOKです。
続いてはスプレッドシートとの連携です!

スプレッドシート

作成されたスプレッドシートの編集

Forms本体の作成で作成されたスプレッドシートには「フォームの回答n」といったシートが作成されたかと思います。
このシートには実際にFormsのアンケートを展開したときに回答が入ってくるため 編集しません

シートを追加

「シートを追加」します。以下二つのシートを作成してください。
  1. ムードチャート
  2. リスト
1の「ムードチャート」シートでメンバーの日々の状況を追跡します。
2のリストにはアンケートフォームから送信された回答を「ムードチャート」シートに表示する際に
見やすいように変換するためのものです。

「ムードチャート」シートの編集

ここの作業が肝になります。
まずは各セルに入れる関数を挙げます。
①A3
=IF(C3<>"",ROW()-2,"")
A3セルを任意の行までドラッグ&ドロップします。
メンバーが表示された行のみ番号表示をするための関数です。
②D1
=IFERROR(MIN(INDIRECT("フォームの回答"&"!AA2:AA10")),DATE(2022,4,1))
Formsの回答があった最初の日を拾うようにしています。
回答がない間はエラーになるので、2022/4/1で仮置きします。
③D2
=IFS(WEEKDAY(D1)=1,"日",WEEKDAY(D1)=2,"月",WEEKDAY(D1)=3,"火",WEEKDAY(D1)=4,"水",WEEKDAY(D1)=5,"木",WEEKDAY(D1)=6,"金",WEEKDAY(D1)=7,"土")
D2セルを任意の列までドラッグ&ドロップします。
日付から曜日を算出しています。
④D3
=IFERROR(VLOOKUP(QUERY(INDIRECT("フォームの回答"&"!AA2:C"),"SELECT C WHERE B = '"&B3&"' and A >= date '"&TEXT(D1,"YYYY-MM-DD")&"'and A <= date '"&TEXT(E1,"YYYY-MM-DD")&"'"),'リスト'!AA1:BB4,2,FALSE),"")
任意のセルまでドラッグ&ドロップします。
回答があったメンバー・日付でムードチャートシートにその結果を反映します。
⑤E1
=D1 + 1`
E1セルを任意の列までドラッグ&ドロップします。
日付を表示させます。
⑥適当な画像をC1あたりに配置
これは半分遊び心です。
残りの半分は毎回GASのスクリプトエディタ開くのが面倒なので置いてます。
次章で理由がわかりますので必要不必要のご判断はお任せです。

「リスト」シートの編集

A列に回答、B列にムードチャートシートに表示したい絵文字・顔文字その他を入れます。
これでスプレッドシート編は完了です。

GASの編集

拡張機能タブ - App Scriptを押して、GASエディタを開きます。

スクリプトの作成

「ファイルを追加」から main.gsbutton.gsを作成し、
以下のスクリプトを貼り付けます。
javascript
// main.gs
function main() {
  // シート情報取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // シート指定して情報取得
  var sheet = ss.getSheetByName('Summary');
  // UNIQUE関数を設定するセルの定義
  var mailaddressrange = sheet.getRange("B3");
  // クリアするセルの定義
  var clearrange = sheet.getRange("B3:C20");
  // 二回目以降の実行対応のため、コンテンツを一度削除(値・数式クリア)
  clearrange.clearContent()

  // 全てのシートを取得
  var sheets = ss.getSheets();
  // シート名の変更
  for(var i=0;i<sheets.length;i++){
    var _sheetname = sheets[i].getName();
    if (_sheetname.match(/フォームの回答/))
    {
      sheets[i].setName("フォームの回答");
    }else{
      continue;
    }
  }

  // セルに数式を入力する
  mailaddressrange.setFormula("UNIQUE('フォームの回答'!B2:B100)");

  // SetNameSummarysheetの実行
  set_name_summarysheet(sheet);
}


// メールアドレスからユーザー名を取得する
function set_name_summarysheet(sheet){
  // シートのデータ取得
  var data = sheet.getRange("B3:C20").getValues();
  // 空白の要素を除いた行の値を取得
  var last_row = data.filter(String).length;
    //最終行までfor文を回す
    for (var i=0;i<last_row;i++)
    {
      //メールアドレスを取得する
      var value = data[i][0]
      if(value!="")
      {
          // メールアドレスから名前を情報取得
          var contact = ContactsApp.getContact(value); 
          // フルネームを取得
          var fullName = contact.getFullName(); 
          sheet.getRange(i+3,3).setValue(fullName);
      }
      else
      {
        break
      }
    }
}
javascript
// button.gs
function button() {
  // 検索ボタンを押下した時に出てくる文言
  var result = Browser.msgBox("メールアドレスから名前を検索します。","全員が1回以上ムードチャートに回答している場合、【OK】を押してください", Browser.Buttons.OK_CANCEL);

  // okの場合の処理
  if(result == "ok"){
      main();
  }else{
      return false;
  }
}

スクリプトの割り当て

「ムードチャート」シートに仮置きしていた適当な画像(ここでは虫眼鏡)を右クリックし、
点々をクリックするとメニューが表示されるため、
「スクリプトを割り当て」を選択し、「button」と入れます。
googleの許可設定みたいなポップアップは全てOKで大丈夫です。

実際に動かす!

作成したFormsをチームメンバーに展開します。
画面右上「送信」→メールで送信します。
全メンバーが一回以上Formsに回答したら、
スクリプトを割り当てた画像をクリックしてスクリプトを実行します。

実運用イメージ(再掲)

この例だとAさんは週末にかけて怪しいですね。
Cさんはヤマを乗り越えた感じでしょうか。

その他

土曜・日曜のグレー表示は条件付き書式で作ってます。

おわりに

思いのほか長文になりました。
記事を書くってムツカシイ。
ツッコミどころがあればいただければと思います。

Discussion

コメントにはログインが必要です。