【GAS】Google Apps Script 活用事例 getActiveCellで、タスクが完了したら、現在時刻を自動入力する機能を実装してみた。
転職先でも、幸運な事にG Suite環境
どういう風にGASを書ける事を切り出すか、アピールするかを悩んでいます。アピールが強いと出る杭は打たれるというか、扱いにくい人材、そんな評価になってしまうと、もったいないし。
実際、僕はアスペルガー症候群で、こだわりが強めです。ただでさえ、扱いが難しいとされるタイプなので、そういう印象を与えないように、けれども単調なコピペ作業みたいな仕事が回ってきたら、マクロで何とか出来るかもしれないので、少しコードを書く時間を頂けませんか？と尋ねるようにしています。
コロナ禍の中、リモートワークで、何をやっているかを把握するのが難しいという業務上の課題があり、スプレッドシートで工数管理シートを作って、それに入力するようにしています。
ショートカットも知ってるけど、そもそも入力が面倒。
Ctrl + ; 今日の日付、Ctrl + Shift + ; 時刻を入力出来るショートカットがあるのですが、そもそもの入力が面倒って話ですよね.....。
そこで今回は、ステータス、F列を完了にする事で、B列のイベント名以外が全て自動入力されます。D2(終了時刻)が、D3入力時は（開始時刻）になります。D列に現在の時刻を書いています。
スクリプト全文はこちら
/*
* 工数管理シートで進捗状況、ステータスが完了になったら
* 終了時刻を自動入力する。
*/
function finishedTask() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const targetSheet = spreadsheet.getSheetByName('工数管理');
const targetSheetName = targetSheet.getSheetName();
const activeSheet = spreadsheet.getActiveSheet();
const activeSheetName = activeSheet.getSheetName()
//開いているシートが工数管理シートかどうかを判定し、違う場合は、直ちに処理を終了する。
if(targetSheetName !== activeSheetName){return};
console.log(`指定したシート： ${targetSheetName}`);
console.log(`アクティブなシート: ${activeSheetName}`);
//アクティブセルを取得
const activeCell = targetSheet.getActiveCell();
const activeRow = activeCell.getRow();
const activeColumn = activeCell.getColumn();
const activeValue = activeCell.getValue();
console.log(activeCell.getA1Notation());
const date = new Date();
const today = (date, format) => {
return Utilities.formatDate(date, 'JST', format);
};
//条件にマッチした場合のみ、これ以降の処理を行う F列の1行目、見出し以降が、完了になったら
if (activeColumn === 6
&& 1 < activeRow
&& activeValue === '完了'){
//1行前の終了時刻が、アクティブセルの開始時刻になる。
const targetRow = activeRow -1;
const startTime = targetSheet.getRange(targetRow, 4).getDisplayValue();
//arrow関数で、formatを指定する。
const endTime = today(date, 'HH:mm');
const startDate = today(date, 'yyyy/MM/dd');
//終了時刻-開始時刻
const formula = `=D${activeRow}-C${activeRow}`;
//配列で貼り付ける
const values = [[startDate, '', startTime, endTime, formula]];
targetSheet.getRange(activeRow, 1, values.length, values[0].length).setValues(values);
}//if
}//endせっかくなので、アロー関数を使ってみました。
アロー関数を使いこなせると、すごく楽ですね。関数に処理を分離すると、どこに何を書いたか忘れてしまう事って結構あります。
多分、やりがちな.....ミス
function myFunction() {
const date = new Date();
const today = (date, format) => {
return Utilities.formatDate(date, 'JST', format);
};
console.log(`失敗例： ${today}`);
console.log(`成功例： ${today(date, 'yyyy/MM/dd')}`);
}このサイトが参考になりました。
シートを公開しています。
欲しい方は、ファイルのコピーが可能なので、ご自由にお使いください。
2020/11/7 ソースコードのリライトをしました。
/*
* 工数管理シートで進捗状況、ステータスが完了になったら
* 日付や開始時刻、終了時刻を自動入力する。
*/
function getActiveCell_(sheet) {
const activeCell = sheet.getActiveCell();
const activeCellDetail = {
range: activeCell.getA1Notation(),
row: activeCell.getRow(),
column: activeCell.getColumn(),
value: activeCell.getValue()
};
return activeCellDetail
}
function finishedTask() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const targetSheet = spreadsheet.getSheetByName('工数管理');
//処理対象のシートと、アクティブなシートが一致しない場合は、処理を終了
const targetSheetName = targetSheet.getSheetName();
const activeSheetName = spreadsheet.getActiveSheet().getSheetName();
console.log(`指定したシート： ${targetSheetName}, アクティブなシート: ${activeSheetName}`);
if(targetSheetName !== activeSheetName){return};
//見出し行を検索して、処理対象の列を特定する。
const columnName = {
date: '日付',
event: 'イベント名',
startTime: '開始時刻',
endTime:'終了時刻',
result:'実績',
status:'ステータス',
}
//一次元配列の何番目に、見出し行の値が存在するかを判定。
//列の挿入や削除等に対応するために使っている。
const headerRow = targetSheet.getDataRange().getValues()[0];
const dateColumn = headerRow.indexOf(columnName.date) + 1;
const startTimeColumn = headerRow.indexOf(columnName.startTime) + 1;
const endTimeColumn = headerRow.indexOf(columnName.endTime) + 1;
const statusColumn = headerRow.indexOf(columnName.status) + 1;
//アクティブセルの取得
const activeCell = getActiveCell_(targetSheet);
//日付をdateオブジェクトから文字列へ変換する
const date = new Date();
const today = (date, format) => {
return Utilities.formatDate(date, 'JST', format);
};
//条件にマッチした場合のみ、これ以降の処理を行う
if (activeCell.column === statusColumn
&& 1 < activeCell.row
&& activeCell.value === '完了'){
//1行前の終了時刻が、アクティブセルの開始時刻になる。
const targetRow = activeCell.row -1;
const startTime = targetSheet.getRange(targetRow, endTimeColumn).getDisplayValue();
//arrow関数で、formatを指定する。
const endTime = today(date, 'HH:mm');
const startDate = today(date, 'yyyy/MM/dd');
//終了時刻-開始時刻 実績を算出する。
const formula = `=D${activeCell.row}-C${activeCell.row}`;
//今日の日付をの書き込み
targetSheet.getRange(activeCell.row, dateColumn).setValue(startDate);
//開始時刻、終了時刻、数式の書き込み
const values = [[startTime, endTime, formula]];
targetSheet.getRange(activeCell.row, startTimeColumn, values.length, values[0].length).setValues(values);
}//if
}//end上に掲載したスクリプトより長くなっていますが、列の挿入や、削除に対応するための処理が追加されています。
if (targetSheetName === activeSheetName
&& activeColumn === 6
&& 1 < activeRow
&& activeValue === '完了'){以前書いたスクリプトでも、きちんと動くのですが、6とかって書いていると、列の挿入や削除があって、7列目になったり、5列目になったりする度に、舌打ちをしながら、コードの修正をする必要があります。
それは、面倒なので、コードが動かなくなる芽を先に潰しておく、そんな感じです。
ブログのアウトプットが、新しい職場でも大いに役立っています。セキュリティが厳しいので、Evernoteとかクラウドサービスとか迂闊に開くと、マズいのかなと感じています。（Evernoteにエロ画像をたくさん保存しているので、職場では開けないんですけどね。ましてや貸与パソコンで閲覧履歴とかつけちゃ一番アカンやつ。）
・・・そんな時に自分のブログでソースコードを確認、サクッとコピーできるのは、めちゃめちゃ便利。結構、アクティブセルの取得に関した処理を書くのって、結構大変だったりします。自分のワークスペースで過去エントリーが簡単に検索できるようにしています。
こんな記事書いています。
この記事が気に入ったらサポートをしてみませんか？