【GAS初級者向け】Google Apps Scriptで バイトの給与を予測するアプリを作ろう

私はバイト掛け持ちなう！なJK（N高等学校ネットコース4期生、プログラミングスクールNepps講師）です。ある時「今月、どのくらいお給料が入るかな？」と気になったので、Googleカレンダーに入力しているシフトから大まかな給与を計算して表示するスクリプトを書きました。GoogleスプレッドシートとGoogleカレンダーの連携方法を解説する記事です。

Google Apps Scriptとは

Google Apps Script (以下GAS)は、Googleのサービスをクラウド上でプログラムを組むことで操作できる、JavaScriptをベースとしているサービスです。

今回は、GoogleカレンダーとGoogleスプレッドシートを連携させて、バイト給与をスプレッドシートに表示させるツールを作ります。

準備

Googleスプレッドシート で任意のスプレッドシートを作り、メニューからツール→スクリプトエディタを選択してください。

次のような画面が開けばOKです。

保存しようとすると次のようなダイアログが出るので、好きな名前に変更して「OK」を押しましょう。

実装

スプレッドシートを開いたら実行するスクリプトを書く

function onOpen() { //スプレッドシートが開かれたときに実行する
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //開いているスプレッドシートを取得
  var objSheet = objSpreadsheet.getActiveSheet(); //開いているスプレッドシートのシートを取得
  objSheet.getRange('C3').setValue("わあ！"); 
  //開いているスプレッドシートのシートの、'C3'に位置するセルの値を'わあ！'にする。
}

では、実際に実行してみましょう。「▶」というボタンをクリックします。

実行すると次の画像のような「Authorization required」というダイアログが出ますね。
「<アプリ名> needs your permission to access your data on Google」とは、「<アプリ名> は、Google上のデータにアクセスするための許可が必要です」という意味です。Googleスプレッドシートの編集などの許可が必要なコードを書いて、初めての実行だとダイアログが表示されます。

「許可を確認」を押します。許可するアカウントをクリックし次に進むと、次の画像のように「このアプリは確認されていません」というページになります。「よく知っている信頼できるデベロッパーの場合に限り続行してください。」とあります。今回、このアプリを作ったのは自分自身なので、信頼できますね。
ここで「詳細」をクリックします。

「詳細」をクリックすると「＜アプリ名＞(安全ではないページ)に移動」とかかれたところがあるので、クリックします。

「Googleドライブのスプレッドシート作成や編集を許可しますよ」といった内容のページになるので、確認して「許可」します。これで実行の準備が整いました。

再度「▶」のボタンを押します。うまく思い通りに表示されたか、スプレッドシートを確認しましょう。次のようになっていれば成功です！

書いたコードはスプレッドシートが開かれたときに実行するものなので、きちんと動いているのか確認するために、表示された「わあ！」という文字列を消して再読み込みしてみます。スクリプトが実行されて自動的に文字列が表示されたら成功です！

Tips: 指定のセルの内容を書き換える方法

先程も出てきましたが、指定のセルの値を設定する方法は次のコードです。

スプレッドシートのシート.getRange(場所).setValue(値); 

例えば、シートの左上に「左上だぴょーん」という文字列を表示したかったら、スプレッドシートのシート.getRange('A1').setValue('左上だぴょーん'); というコードで表示できます。

カレンダーから予定を取得する

カレンダーIDの取得

さて、今回のアプリではカレンダーからバイトの予定を取得します。予定を取得するときには、「カレンダーID」が必要です。カレンダーIDを調べましょう。

Googleカレンダーにアクセスして、予定を取得したいカレンダーのオーバーフローメニューをクリックし、「設定と共有」をクリックします。

下の方に「カレンダーの統合」という見出しがあり、そのすぐ下に「カレンダー ID」という項目があります。ほにゃららほにゃらら@group.calendar.google.comという文字列が表示されています。(ほにゃららほにゃららの部分はそれぞれ異なります)これが「カレンダーID」です。

イベントとそれぞれの時間を取得する

次のようなコードで、Googleカレンダーから予定を取得できます。

CalendarApp.getCalendarById('ほにゃららほにゃらら@group.calendar.google.com').getEvents(予定を取得する範囲(初めの日にち,終わりの日にちという形));

このコードを使って、次のような一ヶ月間の予定を取得する関数を作ります。

//特定の日付から一ヶ月間の予定たちを取得する関数
function getEvent(date){
  var myCal = CalendarApp.getCalendarById('ほにゃららほにゃらら@group.calendar.google.com'); 
  var startDate = new Date(date);
  var endDate = new Date(date);
  endDate.setMonth(endDate.getMonth()+1);//受け取った日付の一ヶ月後の日にちを設定
  return myCal.getEvents(startDate,endDate);//受け取った日付〜受け取った日付の一ヶ月後の予定を取得して渡す
}

次に、特定の名前の予定(CalendarEventObject)が合計で何時間なのかを調べる関数を作ります。
こんな技を組み合わせます。

• 予定の始まる日時(Date型)は予定.getStartTime()で取得できる
• 予定の終わる日時(Date型)は予定.getEndTime()で取得できる
• 日時(Date型).getTime()で日時の「時間(ミリ秒)」を取得できる
• 予定のタイトル(string型)は予定.getTitle()で取得できる

// 合計で何時間の予定かを取得する関数
function getHour(str,date){ //getHour(予定の名前, 日付) という形で使う
  var myEvents = getEvent(date); //受け取った日付から一ヶ月間の予定を取得
  var workHours = 0;
  for each(var evt in myEvents){ //一ヶ月間の予定らそれぞれにたいして処理する
    if(evt.getTitle()==str){ //もし予定の名前が引数で設定された予定の名前なら
      workHours += (evt.getEndTime().getTime() - evt.getStartTime().getTime())/3600000;
      //予定の終わりの時間から始まりの時間を引いた値を「働いた時間」に足す(ミリ秒の形式なので、(6000(60秒*1000)*60(分)=) 3600000 で割る)
    }
  }
  return workHours; //足し合わせ終わった変数を返り値として渡す
}

これで、特定の予定が月にどれくらいの時間入っているのか、知ることができました。

2019/12/16追記:
masciiさんより、次のようなコメントを頂きました。ありがとうございます m(_ _)m

• とてもわかりやすい記事を書いてくださり、ありがとうございます。
  本文中で紹介されている for each...in 文ですが、現在の JavaScript の仕様では廃止されており代替として for...of 文が推奨されています。
  https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Statements/for_each...in
  ですので GAS の開発をする場合に限り for each...in 文を使い、その他フロントエンドなどの JavaScript の開発では for...of 文を使うと良いかと思います！

上記コード中のfor each (var evt in myEvents){ ...という部分は、問題なく動作します(2019/12/16現在)が、他のフロントエンド開発でJavaScriptを書くときには注意してください(； ･`ω･´)

スプレッドシートで表示する

勤務時間(予定の時間)をスプレッドシートで表示します。onOpen()関数を次のように変更します。

🍔、💻となっている部分は任意の文字列に変更してください。私の場合はバイト先1は🍔、バイト先2は💻というタイトルの予定として登録しているので、このようなコードになっています。

function onOpen() { //スプレッドシートが開かれたときに実行する
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();//開いているスプレッドシートを取得
  var objSheet = objSpreadsheet.getActiveSheet(); //開いているスプレッドシートのシートを取得
 
  var today = new Date(); //今日の日にち
  var date = today.setDate(1); //今日の日にちの「日」を1日にすることで「今月1日」になる
  
  objSheet.getRange('C3').setValue(getHour("🍔",date)); //「今月1日」から一ヶ月間の"🍔"というタイトルの予定が入っている時間をスプレッドシートのC3に表示
  objSheet.getRange('D3').setValue(getHour("💻",date)); //「今月1日」から一ヶ月間の"💻"というタイトルの予定が入っている時間をスプレッドシートのD3に表示
}

動くか確認します。「▶」ボタンを押して実行します。すると、先程と同じダイアログが表示されます。これは新しく「カレンダーを操作するコード」を書いたからですね。前述の手順にそって許可してもう一度実行してみましょう。
次のように表示されれば成功です！

コードはこれで完成です。コード全体は次のような感じになっています。

function onOpen() { //スプレッドシートが開かれたときに実行する
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();//開いているスプレッドシートを取得
  var objSheet = objSpreadsheet.getActiveSheet(); //開いているスプレッドシートのシートを取得

  var today = new Date(); //今日の日にち
  var date = today.setDate(1); //今日の日にちの「日」を1日にすることで「今月1日」になる

  objSheet.getRange('C3').setValue(getHour("🍔",date)); //「今月1日」から一ヶ月間の"🍔"というタイトルの予定が入っている時間をスプレッドシートのC3に表示
  objSheet.getRange('D3').setValue(getHour("💻",date)); //「今月1日」から一ヶ月間の"💻"というタイトルの予定が入っている時間をスプレッドシートのD3に表示
}

//特定の日付から一ヶ月間の予定たちを取得する関数
function getEvent(date){
  var myCal = CalendarApp.getCalendarById('ほにゃららほにゃらら@group.calendar.google.com'); 
  var startDate = new Date(date);
  var endDate = new Date(date);
  endDate.setMonth(endDate.getMonth()+1);//受け取った日付の一ヶ月後の日にちを設定
  return myCal.getEvents(startDate,endDate);//受け取った日付〜受け取った日付の一ヶ月後の予定を取得して渡す
}

// 合計で何時間の予定かを取得する関数
function getHour(str,date){ //getHour(予定の名前, 日付) という形で使う
  var myEvents = getEvent(date); //受け取った日付から一ヶ月間の予定を取得
  var workHours = 0;
  for each(var evt in myEvents){ //一ヶ月間の予定らそれぞれにたいして処理する
    if(evt.getTitle()==str){ //もし予定の名前が引数で設定された予定の名前なら
      workHours += (evt.getEndTime().getTime() - evt.getStartTime().getTime())/3600000;
      //予定の終わりの時間から始まりの時間を引いた値を「働いた時間」に足す(ミリ秒の形式なので、(6000(60秒*1000)*60(分)=) 3600000 で割る)
    }
  }
  return workHours; //足し合わせ終わった変数を返り値として渡す
}

取得した予定から給与を計算する

さて、どのくらい働いているかはわかりましたが、このままでは給与の計算が面倒ですね。自動で計算をしましょう。コードを書く方法もありますが、単純な計算で可変でもないので、スプレッドシートで計算させたほうが早いです。

任意のセルに=C3(先程表示させた働く時間のセルの位置)*時給と入力しましょう。すると、働く時間 * 時給でおおよそのもらえる給与がわかるようになりました。
こんな感じです。

これで、働いた時間ともらえると予想される給与がわかるようになりました。

見た目を整えて完成！

このままだと「なんの数字だ？」となってしまうので、お好みで見た目を整えます。

いい感じです。ふとした時にスプレッドシートを開くだけの給与予測ツールができました。

おわりに

今回はごく簡単なものを作りましたが、アレンジを加えて「先月と今月の比較」や「有給・休日の計算」を実装することもできます。環境構築がいらなく、簡単にツールを作って動かせるGASで是非様々なツールを作ってみてください！