【GAS】Google Apps Script 活用事例 日付の操作方法、getDisplayValues、引数を使ったスクリプト
バックオフィス系の仕事だと、2020/02 とか 2020/01 みたいな月次データを作成したいシーンが良くあります。日付の操作ですね。今回は日付けの操作をまとめてみたいと思います。
一番シンプルなのは、getDisplayValues
例えば、上記のようなデータがあった際に、A列を見た目通り取得してくれます。なので、日付けの操作は一番、コレが楽チンです。V8になってからログ見やすくなったなぁ、遅いけど.....。
function displayValues() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('DISPLAY');
const values = sheet.getDataRange().getDisplayValues();
console.log(values);
}
先月の日付けを出力する
function lastMonth_(){
const date = new Date();
date.setMonth(date.getMonth() -1);
const lastMonth = Utilities.formatDate(date, 'JST', 'yyyy_MM');
console.log(lastMonth);
return lastMonth
}
日付けをYYYY/MM/DDなどに変換して表示する方法です。ちょい面倒。
引数を使ったスクリプト
こちらは、etau さんに書いてもらったスクリプトです。これ、2020/01だったら、2019/12が返ってこないんじゃね?と思って実験したら、ちゃんと返ってきました。憎い.....。
function returnMonth() {
const date = new Date();
/*変数dateと、−1を引数として渡して、別の関数を呼び出し、先月の文字列を生成*/
const month = createStringDate(date, -1);
console.log(month);
}
function createStringDate(date, number) {
/*numberの引数が、空白だった時の対策で、|| 0が入っている?*/
date.setMonth(date.getMonth() + (number || 0));
return Utilities.formatDate(date, 'JST', 'YYYY/MM');
}
アロー関数
const stringDate = (date, number, format) => {
date.setDate(date.getDate() + number);
return Utilities.formatDate(date, 'JST', format);
}
//今日の日付は、0 明日の場合は、1などを入れればOK
const today = stringDate(date, 0, 'yyyy/MM/dd');
console.log(today);
reduceメソッドを使ったパターン
//formatを引数に指定して、日付や時刻を直す。
//const format = 'Y/M/D h:m:s';
function sampleFunction(format) {
const date = new Date();
let replaceLists = [
// [置き換えたい文字列(正規表現), 置き換える値]
[/Y/, date.getFullYear()],
[/M/, date.getMonth() + 1],
[/D/, date.getDate()],
[/h/, date.getHours()],
[/m/, date.getMinutes()],
[/s/, date.getSeconds()]
];
// replaceListsを使って置換
const reducer = (acc, list) => acc.replace(...list);
const formatStr = replaceLists.reduce(reducer, format);
console.log(formatStr);
}
こちらのコードは、下記のブログを参考にさせてもらいました。
配列の要素を加算して、単一の値を返すことが可能。
日付と通し番号が入った文字列を作成するスクリプト
日付のfor文
1年分の日付をスプレッドシートに書き出すスクリプト
※ CalendarAppのgetEvents(startDate, endDate)だと、予定が入っていない日は書き出されないため、使用していません。
function getCal2022_() {
const startDate = new Date('2021/01/01');
const endDate = new Date('2021/12/31');
let newValues = [];
for(let d = startDate; d < endDate; d.setDate(d.getDate()+1)) {
const day = formatedDay_(d.getDay());
const stringDate = Utilities.formatDate(d, 'JST', 'yyyy/MM/dd');
console.log(stringDate ,day);
newValues.push([stringDate, day, '']);
}//for
return newValues
}//en
function formatedDay_(day){
let dayOfTheWeek;
switch(day){
case 0: dayOfTheWeek = '日';
break;
case 1: dayOfTheWeek = '月';
break;
case 2: dayOfTheWeek = '火';
break;
case 3: dayOfTheWeek = '水';
break;
case 4: dayOfTheWeek = '木';
break;
case 5: dayOfTheWeek = '金';
break;
case 6: dayOfTheWeek = '土';
break;
}
return dayOfTheWeek
}
function setCalDate() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('イベントカレンダー');
const values = getCal2022_();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
土日を含む行に着色するスクリプト
function setColorHoliday() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('テスト');
const values = sheet.getDataRange().getValues();
const maxColumn = sheet.getLastColumn();
for(let column = 1; column < maxColumn; column +=3){
for(let row = 1; row < values.length; row++){
const targetRange = sheet.getRange(row, column, 1, 3);
const array = targetRange.getValues().flat();
console.log(array);
console.log('範囲', targetRange.getA1Notation());
if(array.indexOf('土') !== -1){
targetRange.setBackground('#c9daf8');
}else if(array.indexOf('日') !== -1){
targetRange.setBackground('#f4cccc');
}
}
}//for
}
関連する自分のEvernote
1ヶ月後や2ヶ月後を求められる、EDATE関数
シート上で日付けを生成する場合には、既存の関数を使った方が、GASで操作するより遥かに簡単です。それからデータを処理する、作成する段階で、関数の知識は何かと重宝するので、まとめておきます。
=EDATE(A1,1)
EDATE関数は、A1セルにある、2020/01/22の○ヶ月後を求める事ができます。先月を求めたい場合は、-1すればいいだけです。
ある期日までの残日数を調べられる、DATEDIF関数
=DATEDIF(B1,B2,"D")
受験日やタスクの締め切りなどをシートで管理している場合に使えそうです。
月末を求める関数まである、EOMONTH関数
End Of Month関数で、EOMONTH関数、知っていたら、ドヤ顔が出来るんじゃないかな?Google限定なのかな?分かりませんが、使えます!!
=EOMONTH(B1,0)
稼働日数を求める時に使える、NETWORKDAYS関数
月初と月末をセルに書いておいて、その月の就業日数なんかを割り出すのに使えます!!祝日カレンダーを用意しておけば、祝日分も差し引きしてくれます。
=NETWORKDAYS(C7,C8,'祝日'!A1:A18)
2020年の祝日カレンダーを出力するスクリプト
function getHolidays() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('祝日');
var startTime = new Date('2020/01/01');
var endTime = new Date('2020/12/31');
var calJa = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com');
var events = calJa.getEvents(startTime, endTime);
Logger.log(events);
var calArray = [];
for(var i = 0; i < events.length; i++){
var date = events[i].getStartTime();
var dayOfTheWeek = date.getDay();
var title = events[i].getTitle();
//祝日が日曜だったら振替休日があるので処理をスキップ
if(dayOfTheWeek === 0){
continue;
}
switch(dayOfTheWeek){
case 1: dayOfTheWeek = '月';
break;
case 2: dayOfTheWeek = '火';
break;
case 3: dayOfTheWeek = '水';
break;
case 4: dayOfTheWeek = '木';
break;
case 5: dayOfTheWeek = '金';
break;
case 6: dayOfTheWeek = '土';
break;
}
calArray.push([date, dayOfTheWeek, title]);
}//for
sheet.getRange(1, 1, calArray.length, calArray[0].length).setValues(calArray);
sheet.getRange(1, 1, sheet.getLastRow()).setHorizontalAlignment('center')
.setVerticalAlignment('middle');
}
下記の記事で紹介しているスクリプトをちょっと変更しました。祝日というシートを作り、スクリプトを実行するだけで2020年の祝日カレンダーを出力する事が出来ます。
この記事が気に入ったらサポートをしてみませんか?