Google Apps Scriptで社内勤務時間管理システム作ったので便利だったものを備忘録代わりに挙げる

f:id:fwww:20171012152229p:plain

どうも!巷ではMacbookチルドレン第一号と言われている19歳のFwww(ふうや)です(((((((((((っ・ω・)っ

 

9月から地元の動画メディアを運営している会社でバイトさせてもらってます。

元々勤務時間は専用の紙に書いて申告するという形態でした。

それを一新させたいという想いで、Google Apps Script(以後GASと略します)とスプレッドシートを連携させて勤務時間管理システムを開発しました。

開発時間は10時間ほどと少し時間はかかったものの次のようなことができるシステムを作り上げました。

  • 名前を選択して勤務開始ボタンを押すとスプレッドシートに勤務開始時間を挿入
  • 名前を選択して行ったタスクを選び勤務終了ボタンを押すとスプレッドシートに退勤時間とその日の勤務時間、行ったタスクを挿入
  • 毎日夜中の2〜3時に今日の日付と曜日を挿入
  • 月初めにそれぞれの人の勤務時間と合計勤務時間、給与を計算して行ったタスクを記述して(働いた日だけ抜き取る)、それぞれのシートを社長にエクセル形式でメールで送信

ざっくり言えばこれだけのことですが、社長からすれば月初めに紙に書かれた勤務時間を合算して計算する作業を短縮できるので結構喜んでいただいてます。

 

とこんな話は置いといて、GASを利用したに当たって便利だった関数や処理等を紹介させていただきます。

GASで便利な関数・処理をまとめてみた

GASの中でウェブアプリケーションを立ち上げる

function doGet(){
var html = HtmlService.createTemplateFromFile("page"); 
return html.evaluate(); 
}


GASの中で内部扱いのウェブアプリケーションを立ち上げる場合は既定のdoGet()関数を利用します。
上記のようなコードだとpage.htmlをウェブアプリケーションとして開くようになります。
常に最新のコードでテストしたい場合は「公開→ウェブアプリケーションとして導入→最新のコード」で行えます。

セルに値を挿入する

SpreadsheetApp.getActiveSheet().getRange('A1').setValue("あいうえお");

次のように処理すればA1にあいうえおという文字列を挿入することが出来ます。

また変数を利用したい場合は次のようにします。

var i = 1;
var text = "あいうえお";
SpreadsheetApp.getActiveSheet().getRange('A'+i).setValue(text);

こうすれば前者と同じ処理を行えます。

GASのシートの中でデータが記入されている最終行を取得する

var last_row = SpreadsheetApp.getActiveSheet().getLastRow();

通常はこんな処理でデータが記入されている最終行を取得することが出来るのですが、GAS付属の関数を使いすぎると重くなるため、jsでデータが記入されている最終行を取得する方法も書いておきます。

var last_row = 2;
  while(SpreadsheetApp.getActiveSheet().getRange('A'+i).getValue()){
    last_row++;
  }

この処理の場合だとA行と指定してデータが記入されている最終行を種痘することが出来ます。

 曜日付きで今日の日付を取得する

これはjavascriptのお話になりますが、

var dayNames = [
  '日曜日',
  '月曜日',
  '火曜日',
  '水曜日',
  '木曜日',
  '金曜日',
  '土曜日'
];
  
  var date = new Date ();
  var month = date.getMonth() + 1;
  var day = date.getDate();
  var dayOfWeek = date.getDay();
  var dayOfWeekStr = [ "日", "月", "火", "水", "木", "金", "土" ][dayOfWeek];
  var day_and_dayname = month+'月'+day+'日'+'('+dayOfWeekStr+')';

このようにすると、day_and_daynameを出力した時は次のような形になります。

10月12日(木)

その日の日付を毎日挿入したい場合はトリガーを設定するといいですね。

内部ウェブアプリケーションからgsファイルの関数を実行する

google.script.run.withSuccessHandler().fn();

このような場合はfnという関数を内部ウェブアプリケーションから実行することが出来ます。

また引数も渡すことが出来るため便利ですよね!

私はjQueryを内部ウェブアプリケーションで読み込んである要素がクリックされたときなんかにgsファイルの関数を実行するようにしています。

 内部アプリケーションから引数として受け取った数字の処理

内部アプリケーションからgsファイルの関数を引数付きで数字を受け取ったときは文字列となっているので、引数を使って演算を行う場合は次のように数字に変換する必要があります。

var hikisu = Number(hikisu);

あるセルに値があるかどうか判定するとき

先述したコードを利用して次のようにセルに値があるかどうか判定することが出来ます。

if(SpreadsheetApp.getActiveSheet().getRange('A1').getValue()){
//値があるとき
}else{
//値がなかったとき
}

要は指定したRange(セル)でgetValue(値をゲット)できるかどうかってことです。

関数の中で関数を実行する

これはどの言語でも当たり前のことですがまれに悩まされる人を見かけるので書いておきます。

fn(hikisu);

この場合fnという関数にhikisuという引数付きで実行させることが出来ます。

 

セルを範囲指定する

getRangeを使えば一つのセルだけでなく範囲指定してセルを指定することが出来ます。

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1:B3")

上記コードはA1からB3を指定してます

また次のようなコードでも同様なセル指定を行えます

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 3, 2)//A1:B3を指定

 

セルを範囲指定して一気に値をゲットする

セルを範囲指定した場合はgetValueではなくgetValuesを利用します。

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1:B3").getValues();

このようにすればA1からA3までの値を一気にgetValues(ゲット)できます。

※getValuesした値は連想配列になっているので注意が必要です

セルを範囲指定して一気に値をセットする

この場合はsetValueではなくsetValuesを利用します。

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1:A3").setValues("values");

注意することとしては自分で作り出した配列をsetValuesする場合は連想配列に変換してから処理させる必要があることです

次のようにして配列を連想配列に変換させることが出来ます

var values = [];
for (var i=0; i<value.length; i++) {
    values.push([value[i]]);
}

すでにvalueという配列が存在する場合上記コードを利用して連想配列化させることが出来ます。

セルの値を削除する

セルの値を削除したいときよくあります笑

そんなときはこうします。

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A1:A3").clear();

このように範囲指定してセルの値を一気に削除させることが出来ま。す

もちろん単一のセルを削除させることも出来ますよ。

シートを作成する

様々なデータをスプレッドシートで扱う場合は新たなシートをよく作成すると思います。

SpreadsheetApp.getActiveSpreadsheet().insertSheet("シート2");

上記コードの場合シート2を作成することができます。

※自動的にアクティブシートになるので注意が必要

シートを削除する

一時的な処理の場合シートを削除することがあります。

var sheet2 = SpreadsheetApp.getActive().getSheetByName("シート2"); 
SpreadsheetApp.getActive().deleteSheet(sheet2);

ちょっと面倒な書き方になりますが上記のようにしてシート2を削除させることが出来ます。



アクティブシートを変更する

2つ以上のシートを扱うようなプログラムだとアクティブシートを変更しないと予期しない結果が生まれてしまいます。

var set_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
SpreadsheetApp.setActiveSheet(set_sheet);

このようにすればシート1をアクティブシートに変更できます

演算してセルに時間形式の値が挿入されてしまった

演算した結果をセルに表示させた場合極稀に0:00のように時間形式の値が挿入されてしまう場合があります。

今回の開発でもこの現象に遭遇して無事に解決できたのでメモしておきます。

setNumberFormat("0")

setValueまたはsetValuesの手前に上記のようなコードを差し込むことで書式設定を行い正常に表示させるようにできます。

小数点を表示させたい場合は0.00のように手動で設定する必要があるので注意が必要です。

メールを送信する

トリガーなんか利用してメールを送信する場合があります。

MailApp.sendEmail('メールアドレス', 'メールの件名です', "メールの内容です");

次のような形でメールを送信することが可能です。

メールの送信元はスプレッドシートでログインしているGoogleアカウントになります

スプレッドシートをエクセル形式に変換

バックアップのためにスプレッドシート(全シート)をエクセル形式に変換する時があります。

var fileId = SpreadsheetApp.getActiveSpreadsheet().getId();
var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + fileId + "&exportFormat=xlsx";
UrlFetchApp.fetch(fetchUrl).getBlob().setName("ファイル名.xlsx");

特定シートをエクセル形式に変換する

メールなんかで特定シートのみエクセル形式に変換して送る場合があります。

var fileId = SpreadsheetApp.getActiveSpreadsheet().getId();
var set_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート2");
var sheetId = SpreadsheetApp.setActiveSheet(set_sheet).getSheetId();
var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + fileId + "&exportFormat=xlsx&gid="+sheetId;
UrlFetchApp.fetch(fetchUrl).getBlob().setName("ファイル名.xlsx");

処理としてはスプレッドシートのフィールドIDを取得(先程と同様)し、シートIDを取得。

その後専用のurlにfetchさせています

メールでエクセル形式のファイルを添付する

エクセル形式のファイルをメールで添付する場合は次のようにします

var fileId = SpreadsheetApp.getActiveSpreadsheet().getId();
var fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + fileId + "&exportFormat=xlsx;
var fetch = UrlFetchApp.fetch(fetchUrl).getBlob().setName("ファイル名.xlsx");
MailApp.sendEmail('メールアドレス', 'メールの件名です', "メールの内容です",{attachments:fetch});

メールでファイルを添付する場合はsendEmail関数のオプションで{attachments:フェッチさせたもの}を指定する必要があります。

おまけ:GASでAjax

GASではjsのXMLHttpRequestオブジェクトを生成することができません。

なのでUrlFetchApp.fetchを利用します。

これに関しては深掘りしていないのでググって実装してください笑

まとめ

GAS弄るのは初めてだったんですけど、ググればなんとかなるものだなあと実感しました笑

なのでこれからはGAS案件なども募集しようと思います!

ありがとうございました!!