プログラミング経験ゼロ(簡単なHTMLを書いたりバッチ作成くらいならできる)の人間がjavascriptを独学して書きました。
コードがスマートではない部分もあるかと思いますが、ご容赦ください。
やりたかったこと
以下のようなGoogleスプレッドシートについて、
「日付」がスクリプト実行日よりも後であれば、「状況」列を「完了」にするようなスクリプトです。
日付 | タスク | ステータス |
---|---|---|
2018/05/13 | 書類提出 | 完了 |
2019/01/20 | 懇親会 | 未 |
飲み会(日付未定) | 未 | |
2017/12/16 | 同窓会 | 完了 |
2018/11/03 | 旅行 | 未 |
上記の例はフェイクですが、
適宜ステータスを正確に変更する必要があるスプレッドシートだったので自動化を試みました。
書いたスクリプト
myFunction
function myFunction() {
// このスクリプトの実行日の取得
var today = Utilities.formatDate(new Date(), 'Asia/Tokyo','yyyy/MM/dd');; // 今日の日付をyyyy/mm/dd形式に直して取得
// スプレッドシートで指定された日付の取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // ①:スプレッドシートを取得
var sheet = spreadsheet.getActiveSheet(); // ②:スプレッドシートを元にシートを取得
var col = "C"; // ③:検索する列(C列)を指定
var last_row = sheet.getLastRow(); // ④:シート全体のうち、データが入力されている最終行を取得
var range = sheet.getRange(col + "1:" + col + last_row); // ⑤:②で取得したシートについて、④で指定した行と⑤で取得した列の範囲を取得
var value = range.getValues(); // ⑥:範囲の値を取得
var array = []; //⑦:⑥で取得した範囲の値を変換するための配列を用意
var isDate = []; //⑧:取得した値が日付かどうかを判定して、結果を格納するための配列を用意
// 取得した値を元にセルを書き換え
for(var i = 0; i < value.length; i++){ // 取得したデータについて、一つ一つ以下の作業を行っていく
array.push(value[i][0]); // シートから取得した2次元配列を1次元配列に変換
isDate.push(Object.prototype.toString.call(value[i][0])) // value[i]を日付かどうかを判定し、isDateに1行1行の結果を格納
if (value[i] !== undefined && isDate[i] === "[object Date]"){ // もしvalue[i]が空欄ではなく、かつ日付であれば、以下の処理を行う
array[i] = Utilities.formatDate(array[i], 'Asia/Tokyo','yyyy/MM/dd'); // ⑤取得した値(Javascript形式の日付)を、todayと比較するためyyyy/mm/dd形式の日付に変換
if (array[i] <= today){ // この関数を実行した日(today)が、シートに書いてある日(specifiedDay)よりも後ならば
sheet.getRange(i+1, 3).setValue("完了") // i+1行、3列目の文字を「完了」にする(配列は0番目から始まるが、行は1行目なので、格納されている順番+1して行を更新)
}
}
}
}
このスクリプトに時間主導型のトリガーを適切なタイミングで設定すれば、
自動的にステータスの変更ができるようになります。
ひとこと
プログラミングド初心者としては、Utilities.formatDateで取得できる日付がGMT+9の形式で
スプレッドシートの日付と比較するには変換が必要だったのが少々やっかいでした。
どなたかのお役に立つことを祈ります。