Last active
February 25, 2023 01:59
-
-
Save Tomokatsu-Sakamoto/0bd53489f6764b53f73dc4f30be7f2e5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'use strict' // | |
const HOLIDAY_CALENDAR = 'ja.japanese#holiday@group.v.calendar.google.com'; | |
const HOLIDAY_RANGE = 'HOLIDAY'; // 休日情報を書き出す見出し部分の「名前付き範囲」 | |
const MONTH_OFFSET = -1; // Date 型の「月」は 0 ~ 11 で表すため、補正用の値 | |
//---------------------------------------------------------------------------- | |
// チェック結果をクリア | |
function checkClear() { | |
checkClear0(); | |
checkClear1(); | |
} | |
//---------------------------------------------------------------------------- | |
// チェック結果をクリア(列 A) | |
function checkClear0() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('カレンダー情報'); | |
// 見出しを再設定 | |
sheet.getRange('A1').setValue("実行アカウント"); | |
sheet.getRange('A4').setValue("登録されているカレンダーの数"); | |
// 可変の欄をクリア | |
sheet.getRange('A2').setValue(""); | |
sheet.getRange('A5').setValue(""); | |
} | |
//---------------------------------------------------------------------------- | |
// チェック結果をクリア(列 B~I) | |
function checkClear1() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('カレンダー情報'); | |
// 一旦、範囲をすべて削除 | |
sheet.getRange('B:I').clearContent(); | |
// 先頭行の見出しを再設定 | |
sheet.getRange('C1').setValue("登録されているカレンダーの ID"); | |
sheet.getRange('D1').setValue("名称"); | |
sheet.getRange('E1').setValue("isHidden"); | |
sheet.getRange('F1').setValue("isMyPrimaryCalendar"); | |
sheet.getRange('G1').setValue("isOwnedByMe"); | |
sheet.getRange('H1').setValue("isSelected"); | |
sheet.getRange('I1').setValue("Timezone"); | |
sheet.getRange('A4').setValue("登録されているカレンダーの数"); | |
sheet.getRange('A5').setValue(""); | |
} | |
//---------------------------------------------------------------------------- | |
// 現在のアカウントのカレンダー情報を取得 | |
function getMyCalendars() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('カレンダー情報'); | |
// 現在の実行アカウントを確認 ※実行されているアカウントによって、動作が変わってくるので... | |
var eMailID = Session.getActiveUser().getUserLoginId(); | |
checkClear0(); | |
sheet.getRange('A2').setValue(eMailID); | |
// ユーザーが所有またはサブスクライブしているすべてのカレンダーを取得します。 | |
// Determines how many calendars the user can access. | |
var calendars = CalendarApp.getAllCalendars(); | |
checkClear1(); | |
sheet.getRange('A5').setValue(calendars.length); // カレンダーの総数 | |
for (var i = 0; i < calendars.length; i++) { | |
sheet.getRange(i + 2, 2).setValue(i + 1); | |
sheet.getRange(i + 2, 3).setValue(calendars[i].getId()); // C | |
sheet.getRange(i + 2, 4).setValue(calendars[i].getName()); // D | |
sheet.getRange(i + 2, 5).setValue(calendars[i].isHidden()); // E | |
sheet.getRange(i + 2, 6).setValue(calendars[i].isMyPrimaryCalendar()); // F | |
sheet.getRange(i + 2, 7).setValue(calendars[i].isOwnedByMe()); // G | |
sheet.getRange(i + 2, 8).setValue(calendars[i].isSelected()); // H | |
sheet.getRange(i + 2, 9).setValue(calendars[i].getTimeZone()); // I | |
} | |
} | |
// ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- | |
/** | |
* 現在選択されている行のカレンダーの予定をスプレッドシートに書き出す | |
*/ | |
function getEventsByCalendar() { | |
const ss = SpreadsheetApp.getActiveSpreadsheet(); | |
let sheet = SpreadsheetApp.getActiveSheet(); // 現在開いているスプレッドシート | |
let row = sheet.getActiveRange().getRow(); | |
let calId = sheet.getRange(row, 3).getValue(); | |
let calName = sheet.getRange(row, 4).getValue(); | |
if ((calId != '') && (calName != '')) { // カレンダーの名前と ID が設定されている? | |
let res = Browser.inputBox( | |
'カレンダー「' + calName + '」の何年度の予定を取得しますか? ' + | |
'\n西暦で入力してください。入力された年の 4/1 から翌年の 3/31 までの予定を取得します。', | |
Browser.Buttons.OK_CANCEL); | |
if (res == 'cancel') { | |
ss.toast('カレンダーから予定を取得せずに、処理を終了します'); | |
return; | |
} | |
let year = Number(res); // 入力された年を数値化 | |
console.info('Target year : ' + year); | |
if ((year < 1900) || (year > 2099) || (Number.isInteger(year) == false)) { | |
ss.toast('入力する年は 1900 ~ 2099 の範囲内にしてください。'); | |
return; | |
} | |
let nSheet = ss.getSheetByName(calName); // カレンダー名のシートを開く | |
if (!nSheet) { | |
nSheet = ss.insertSheet(); // シートを新規作成 | |
nSheet.setName(calName); // シート名をカレンダー名で設定 | |
} | |
//期間を指定する | |
let startDate = new Date(year, 3, 1, 0, 0, 0); // 取得開始日 | |
let endDate = new Date(year + 1, 2, 31, 23, 59, 59); // 取得終了日 | |
console.log(startDate); | |
console.log(endDate); | |
let calendar = CalendarApp.getCalendarById(calId); // 対象となるカレンダーを取得 | |
let myEvents = calendar.getEvents(startDate, endDate); // 対象期間内の予定を取得 | |
nSheet.appendRow( | |
[ | |
'No.', | |
'Title', | |
'AllDay', | |
'Start', | |
'End', | |
'Description', | |
'Location', | |
'Creator', | |
] | |
); | |
for (let i = 0; i < myEvents.length; i++) { | |
nSheet.appendRow( | |
[ | |
i + 1, //No | |
myEvents[i].getTitle(), // タイトル | |
myEvents[i].isAllDayEvent(), // 終日イベントかどうか? | |
myEvents[i].getStartTime(), // 開始時刻 | |
myEvents[i].getEndTime(), // 終了時刻 | |
myEvents[i].getDescription(), // 予定の詳細 | |
myEvents[i].getLocation(), // イベントの場所 | |
myEvents[i].getCreators()[0] // 登録者名 | |
] | |
); | |
} | |
} | |
} | |
// ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- | |
/** | |
* 入力されているイベント情報をクリアする(サブ関数) | |
*/ | |
function eventClearSub(sheet, row, col) { | |
for (let i = 0; i < 12; i++) { | |
sheet.getRange(row + 1, col + 1 + i * 3, 31, 2) | |
.clearContent() | |
.clearNote() | |
.setBackground('white'); | |
} | |
} | |
/** | |
* 入力されているイベント情報をクリア | |
* 確認したうえで、スプレッドシートに入力されている予定の情報を削除する。 | |
*/ | |
function eventClear() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var result = Browser.msgBox( | |
'入力されているイベント情報をクリアしても構いませんか?\\n ' + | |
'【注意】 この操作は取り消せません!', | |
Browser.Buttons.OK_CANCEL); | |
if (result == "ok") { | |
} | |
let postRow = sheet.getRange('postEvent').getRow(); // | |
let postCol = sheet.getRange('postEvent').getColumn(); // | |
let idRow = sheet.getRange('idEvent').getRow(); // | |
let idCol = sheet.getRange('idEvent').getColumn(); // | |
eventClearSub(sheet, postRow, postCol); | |
eventClearSub(sheet, idRow, idCol); | |
} | |
/** | |
* 指定されているカレンダーの当該年度の予定を削除する | |
*/ | |
function deleteEvent() { | |
// 実行確認 | |
let result = Browser.msgBox( | |
'選択されているカレンダーから、指定されている年度の予定をすべて削除して構いませんか?\\n ' + | |
'【注意】 この操作は取り消せません!', | |
Browser.Buttons.OK_CANCEL); | |
if (result != 'ok') { | |
// 以降の処理を行わない | |
return; | |
} | |
const sheet = SpreadsheetApp.getActiveSheet(); // 現在開いているシートを取得する | |
let postYear = Number(sheet.getRange('A1').getValue()); // 対象となる年度 | |
let postID = sheet.getRange('calendarID').getValue(); // 書き出す対象のカレンダー ID | |
let calendar = CalendarApp.getCalendarById(postID); // 処理対象のカレンダーを取得 | |
let idRow = sheet.getRange('idEvent').getRow(); // | |
let idCol = sheet.getRange('idEvent').getColumn(); // | |
let sDate = new Date(postYear, 4 + MONTH_OFFSET, 1); // 今年度の 4/1 | |
let eDate = new Date(postYear + 1, 3 + MONTH_OFFSET, 31); // 今年度末の 3/31 ※1年後の 3/31 | |
const events = calendar.getEvents(sDate, eDate); | |
for (let i = 0; i < events.length; i++) { | |
console.log(events[i].getTitle()); | |
events[i].deleteEvent(); // イベントを削除する | |
} | |
eventClearSub(sheet, idRow, idCol); | |
} | |
/** | |
* 入力されているイベント情報を登録する | |
* 指定された日に、既に「終日イベント」が登録されていれば、その内容を更新する。 | |
*/ | |
function postEvent() { | |
// 実行確認 | |
let result = Browser.msgBox( | |
'入力されているイベント情報で、イベントを出力しても構いませんか?\\n ' + | |
'【注意】 この操作は取り消せません!', | |
Browser.Buttons.OK_CANCEL); | |
if (result != 'ok') { | |
// 以降の処理を行わない | |
return; | |
} | |
// これ以降がカレンダーへの登録処理 | |
const sheet = SpreadsheetApp.getActiveSheet(); // 現在開いているシートを取得する | |
let postData = sheet.getRange('postEvent').getValues(); // 現在開いているシートのデータを二次元配列で取得する(タイトル、詳細) | |
let postYear = Number(sheet.getRange('A1').getValue()); // 対象となる年度 | |
let postID = sheet.getRange('calendarID').getValue(); // 書き出す対象のカレンダー ID | |
let calendar = CalendarApp.getCalendarById(postID); // 処理対象のカレンダーを取得 | |
let idData = sheet.getRange('idEvent').getValues(); // 現在開いているシートのデータを二次元配列で取得する(イベント ID) | |
let idRow = sheet.getRange('idEvent').getRow(); // | |
let idCol = sheet.getRange('idEvent').getColumn(); // | |
for (let i = 0; i < 12; i++) { | |
SpreadsheetApp.getActiveSpreadsheet().toast('処理中 ' + (i + 1) + '/12'); | |
let yy = postYear; // 処理対象となる「年」 | |
if (i >= 9) { | |
yy = postYear + 1; // 1月からは翌年 | |
} | |
let mm = i + 4; // 処理対象となる「月」 | |
if (mm > 12) { | |
mm -= 12; // 13月 → 1月 | |
} | |
for (let dd = 1; dd <= 31; dd++) { | |
if (postData[dd][i * 3 + 0] == '') { // 「曜日」が空欄になっているときは、存在しない日 | |
continue; // | |
} | |
if (postData[dd][i * 3 + 1] == '') { // 登録するデータが入力されていない | |
if (idData[dd][i * 3 + 1] != '') { // 処理対象の日に「イベント ID」が記録されていたら、削除されたということ | |
calendar.getEventById(idData[dd][i * 3 + 1]).deleteEvent(); | |
sheet.getRange(idRow + dd, idCol + i * 3 + 1).setValue(''); | |
} | |
continue; | |
} | |
if (idData[dd][i * 3 + 1] != '') { // 処理対象の日に「イベント ID」が記録されていたら、既に登録されている | |
let event = calendar.getEventById(idData[dd][i * 3 + 1]); | |
event.setTitle(postData[dd][i * 3 + 1]); // 「タイトル」を更新 | |
event.setDescription(postData[dd][i * 3 + 2]); // 「詳細」を更新 | |
} | |
else { // 予定を新規に作成する | |
let eventId = calendar.createAllDayEvent( | |
postData[dd][i * 3 + 1], | |
new Date(yy, mm + MONTH_OFFSET, dd), | |
{ | |
'description': postData[dd][i * 3 + 2], | |
} | |
); | |
sheet.getRange(idRow + dd, idCol + i * 3 + 1).setValue(eventId.getId()); // 「イベント ID」を記録しておく | |
} | |
} | |
} | |
} | |
/** | |
* 「日本の祝日」カレンダーから、指定された年度の祝日を取得する | |
* | |
* ※取得対象となるカレンダーは、プログラムの冒頭に HOLIDAY_CALENDAR として定義 | |
*/ | |
function getholiday() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const year = sheet.getRange('A1').getValue(); // 処理対象の年度 | |
const jpHoliday = CalendarApp.getCalendarById(HOLIDAY_CALENDAR); | |
// 「名前付きの範囲」として設定された休日を書き出す位置を特定する | |
// ※多少の行や列の追加があっても動作するように… | |
const range = sheet.getRange(HOLIDAY_RANGE); | |
let holidayRangeStr = range.getA1Notation(); // 何も変更されていなければ、'AM3:AO3' が戻るはず | |
let column = range.getColumn(); // 何も変更されていなければ、列 AM は 39 | |
let rangeArray = holidayRangeStr.split(':'); // 'AM3:AO3' を 'AM3' と 'AO3' に分割 | |
let startRow = Number(rangeArray[0].substring(rangeArray[0].length - 1)); // 'AM3' から 3 を切り出す | |
let startCol = rangeArray[0].substring(0, rangeArray[0].length - 1); // 'AM3' から 'AM' を切り出す | |
let endCol = rangeArray[1].substring(0, rangeArray[1].length - 1); // 'AMO' から 'AO' を切り出す | |
// 休日の情報を読み込む前に、現在のシートの内容をクリアする | |
let clearRange = startCol + String(startRow + 1) + ':' + endCol; // クリアする範囲は 'AM4:AO' | |
sheet.getRange(clearRange).clear(); // スプレッドシートの情報を一旦削除 | |
// 余裕を見て、指定された 今年度と次年度の 2年分の祝日データを取得 | |
let sDate = new Date(year, 4 + MONTH_OFFSET, 1); // 今年度の 4/1 | |
let eDate = new Date(year + 2, 3 + MONTH_OFFSET, 31); // 来年度末の 3/31 ※2年後の 3/31 | |
const holidays = jpHoliday.getEvents(sDate, eDate); | |
// 取得した祝日の情報をスプレッドシートに書き出す | |
for (let i = 0; i < holidays.length; i++) { | |
let dateStr = Utilities.formatDate(holidays[i].getStartTime(), "JST", "yyyy/MM/dd"); | |
sheet.getRange(startRow + 1 + i, column).setValue(dateStr); | |
sheet.getRange(startRow + 1 + i, column + 2).setValue(holidays[i].getTitle()); | |
} | |
let arrayStr = startCol + (startRow + 1) + ':' + startCol; | |
sheet.getRange(startRow + 1, column + 1).setValue('=ARRAYFORMULA(IF(' + arrayStr + '<>"", TEXT(' + arrayStr + ', "ddd"), ""))'); | |
} | |
/** | |
* 各月の「詳細」の列を隠す | |
*/ | |
function hideDetail() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const startCol = 4; // 4月の「詳細」は列 D なので 4列目 | |
for (let i = 0; i < 12; i++) { | |
sheet.hideColumns(startCol + i * 3, 1); | |
} | |
} | |
/** | |
* 各月の「詳細」の列を表示する | |
*/ | |
function showDetail() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
const startCol = 4; // 4月の「詳細」は列 D なので 4列目 | |
for (let i = 0; i < 12; i++) { | |
sheet.showColumns(startCol + i * 3, 1); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment