はじめに
Googleカレンダーでは全員が完全に空いている時間しかわからない。
しかし数十人の予定で完全な空き時間を得るのは無理。
なので比較的空いている時間をGAS+スプレッドシートで取れるようにした
APIは使わない方法なので、全員のカレンダーが参照出来る前提。
日本の祝日対応。
目次
出来上がりイメージ
インプットとアウトプットを先に図示して意図していることを共有したい。
インプット
A列は自由欄、B列は人名、
C列はGoogleカレンダーの紐づくアカウントのメルアドを書く。
E,F列の3行目はスケジュールを取得する期間、H,I列の3行目に時間
K~R列にどの曜日がチェック対象か、チェックする=空欄、チェックしない=-で表現している。
アウトプット
1行目が予定の混み具合、
2行目が月、
3行目が日、
4行目が時を表していて、4行目以下がその時間に予定がある=1,ない=0で表現している。
混んでない時間=1行目の数字が小さい時間。
パッと空き具合がわかるように値の大小で色の濃さの条件書式をコード中でつけてる。
コード
ツールを作るにあたり、いじる構成要素は2つ → スプレッドシート、そしてGASコード。
スプレッドシートをいじる
1.まずスプレッドシートを新しく作る。やり方はGoogle公式を参照。
2.ファイル名を変える。
左上の緑アイコン右にあるファイル名記入欄へ
「ツール-隙間時間ゲッター」とでも入力しEnterを押す
3.シートを整える。
左下にある＋のマークのシート追加ボタンを1回押す。
4.シート名をコードに沿うよう整える。
２つのシートの名前を("シート1","シート2"の右の下向き三角をクリックして名前変更)を
"Setting", "Output"に変更する
5.設定シートをコードに沿うよう整える。
"Setting"シートのA1に↓の枠内を貼り付けてカンマ区切りする
(メニューからデータ→テキストを列に分割)
実行する前にOutputシートをクリアしてね。,,,,,,,,,,,,,,,,,
チェック,人名,メルアド,,開始日,終了日,,開始時間,終了時間,,日,月,火,水,木,金,土,祝
,予定空いてる,sample.president@gmail.com,,2023/09/23,2023/09/24,,9.00,18.00,,,-,-,-,-,-,,
,忙しいひと１,sample.imbusy@gmail.com,,,,,,,,,,,,,,,
,忙しいひと２,sample.noprivate@gmail.com,,,,,,,,,,,,,,,
,もぶ1,sample.mob1@gmail.com,,,,,コピペ用よく使う設定置き場,,,,,,,,,,
,もぶ2,sample.mob2@gmail.com,,,,,10:00,15:00,,-,,,,,,-,-
,もぶ3,sample.mob3@gmail.com,,,,,,,,,,,,,,,
,もぶ4,sample.mob4@gmail.com,,,,,,,,,,,,,,,
6.入力欄がわかりやすいように網掛け(セルに色付け)する
例図
7.既存の記入例に従いB~R列を自分の条件に応じて編集する。
以上。Outputシートはいじらない。
GASコードをいじる
1.GASコード編集ツール起動
左上にあるメニュー「拡張機能」をクリック、プルダウンからApps Scriptをクリック
2.プロジェクト名変更
左上のApps Scriptの右にある「無題のプロジェクト」をクリックし
「ツール-隙間時間ゲッター用コード」とでもしておく
3.コードコピペ
中央のfunction myfunction(と書いてあるところを全て選択し
下記コードに置き換えてCtrl+Sで保存して実行する
(中央上の方にある右向き三角ボタン押す)
先に言っておくと、当方は趣味プログラマーなので動けば何でも良い = コーディング論なぞ知らぬ。
// Settings
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const shtSetting = sheet.getSheetByName("Setting");
const shtOutput = sheet.getSheetByName("Output");
const startDurationDate = shtSetting.getRange("E3").getValue(); // start Date
const endDurationDate = shtSetting.getRange("F3").getValue(); // end Date. included in the duration
const timeStartOfDay = shtSetting.getRange("H3").getValue(); // start hour. has to be x:00. cannnot be in the middle of hour
const timeEndOfDay = shtSetting.getRange("I3").getValue(); // end hour. has to be after start hour + x times of 15 minutes
const durationOfDay = timeEndOfDay - timeStartOfDay
const needHoliday = shtSetting.getRange("R3").getValue(); // holiday setting. 0 = check holiday, - = won't check holiday
const calendarH = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com')
const weekSettingArr = shtSetting.getRange("K3:Q3").getValues();
const startRowNum2Print = 5;
/**
* 起点
*/
function aaamain() {
shtOutput.getRange(1,1,shtOutput.getMaxRows(), shtOutput.getMaxColumns()).clear();
// get schedule
var calendarList = getCalendarList();
var dateArray = makeListOfDays();
putDateTime2OutputTitle(dateArray);
for (var c=0;c<calendarList.length;c++) {
// get events for each user
const userEvents = fetchEvents(calendarList[c]);
// get free time & turn them into bit
const bitEmptyTimeOfDays = calculateEmptyTimeOfDays(userEvents, dateArray);
// print 2 sheet "Output"
putBit2Cells(bitEmptyTimeOfDays, dateArray, c)
}
// set formula in sheet "Output"
var formula = "=sum(C5:C)";
shtOutput.getRange(1,3,1, dateArray.length * durationOfDay * 4).setValue(formula);
shtOutput.setColumnWidths(1, shtOutput.getMaxColumns(), 50);
// set conditional format
var range2Format = shtOutput.getRange(1,3,1,shtOutput.getMaxColumns() - 3)
range2Format.clearFormat(); // delete
var rule = SpreadsheetApp.newConditionalFormatRule()
.setGradientMinpoint("#FFFFFF") // MIN color
.setGradientMaxpoint("#FF0000") // MAX color
.setRanges([shtOutput.getRange(1,3,1,shtOutput.getMaxColumns() - 3)])
.build();
var rules = shtOutput.getConditionalFormatRules().concat([rule]);
shtOutput.setConditionalFormatRules(rules);
}
/**
* making calendar id list
* @return addressList An array to process
*/
function getCalendarList(){
let userList = shtSetting.getRange('B3:C' + shtSetting.getLastRow()).getValues();
shtOutput.getRange(startRowNum2Print, 1, userList.length, 2).setValues(userList);
userList = shtSetting.getRange('C3:C' + shtSetting.getLastRow()).getValues();
addressList = userList.filter(userList => userList[0] !== '').flat();
for (let i = 0; i < addressList.length; i++) {
CalendarApp.subscribeToCalendar(addressList[i]);
#これ、登録したら最後に解除したいのに、そのための関数が無いから天文学的な登録カレンダー数になってしまう弊害あり
}
return addressList;
}
/**
* making date list to process
* @return date2BProcessedArrM Array of dates
*/
function makeListOfDays(){
var date2BProcessedArrM = [];
for (var currentDate = new Date(startDurationDate); currentDate <= endDurationDate; currentDate.setDate(currentDate.getDate() + 1)) {
// make an array of days-to-process
if (neededHoliday(currentDate, needHoliday) && neededWeekday(currentDate, weekSettingArr)) {
date2BProcessedArrM.push(Utilities.formatDate(new Date(currentDate),"JST", "YYYY/MM/dd"));
}
}
return date2BProcessedArrM;
}
/**
* check if the date is Holiday and is needed
* @param date
* @param needHoliday : whether you need to process Holiday
* @return isInNeedH : true if you need the day, false if you do not
*/
function neededHoliday(date, needHoliday){
let holidayEvents = calendarH.getEventsForDay(date);
let isInNeedH = true;
if (needHoliday == '-' && holidayEvents.length > 0){
isInNeedH = false;
}
return isInNeedH
}
/**
* check if the date is weekday and is needed
* @param date
* @param weekSettingArr : whether you need the day
* @return isInNeedW : true if you need the day, false if you do not
*/
function neededWeekday(date, weekSettingArr) {
var isInNeedW = true;
var weekNum = date.getDay();
if (weekSettingArr[0][weekNum] == '-'){
isInNeedW = false;
}
return isInNeedW
}
/**
* output dates & time to sheet Output
*/
function putDateTime2OutputTitle(arrDates){
shtOutput.getRange("B2").setValue("月"); // month
shtOutput.getRange("B3").setValue("日"); // day
shtOutput.getRange("B4").setValue("時"); // hour
for (let i = 0; i < arrDates.length; i++) {
let startArr = arrDates[i].split('/');
let timeTemp = new Date(startArr[0], startArr[1]+1, startArr[2], timeStartOfDay, 0); //print date
shtOutput.getRange(2, 3 + i * durationOfDay * 4, 1, durationOfDay * 4).setValue(startArr[0]+ "/"+ startArr[1]);
shtOutput.getRange(3, 3 + i * durationOfDay * 4, 1, durationOfDay * 4).setValue(startArr[2]);
for (let j = 0; j < durationOfDay * 4; j++) {
//print time
shtOutput.getRange(4, 3 + i * durationOfDay * 4 + j).setValue(Utilities.formatDate(timeTemp, Session.getScriptTimeZone(), "HH:mm"));
timeTemp.setMinutes(timeTemp.getMinutes() + 15);
}
}
}
/**
* fetch events from calendar
*/
function fetchEvents(calendarId) {
const calendar = CalendarApp.getCalendarById(calendarId);
const events = calendar.getEvents(startDurationDate, new Date(Date.parse(endDurationDate) + (60 * 60 * 24 * 1000)));
return events;
}
/**
* return Free time in bit format
* @param events : events retrieved from the calendar
* @param date2BprocessedArrC : array of the dates to be processed
* @return bitDays : array of the dates and free-time-bits
*/
function calculateEmptyTimeOfDays(events, date2BProcessedArrC) {
var bitDays = [];
events.forEach(e => {
// 「終日」の予定はToDoのケースが多いので除外する
if (!e.isAllDayEvent()){
const bitDay = changeDate2Bit(e);
const {key, value} = bitDay;
if (date2BProcessedArrC.indexOf(key) >= 0) {
if (bitDays[key] !== undefined){
// 論理和を取って日毎の予定を埋める
bitDays[key] = "0b" + (BigInt(bitDays[key]) | BigInt(value)).toString(2).padStart(durationOfDay * 4, '0');
} else {
bitDays[key] = value;
}
}
} else {
/* ウチの会社は仕組み上要らないが、欲しい人は休日チェックロジックをここに入れても良いかもしれない。雰囲気こんな感じで。
// イベント名に"全休"や"午前休"が入ってるという前提なら
var string2Check = e.getTitle();
let newBit = "0b";
if(string2Check.indexOf("全休") != -1 | string){
// 条件に合致した場合は、その日分のbitを全て埋める
for (let i = 0; i < durationOfDay * 4; i++) {
newBit += "1";
};
} else if (string2Check.indexOf("午前休") != -1 | string) {
// 午前だけ1で埋める処理を書く
} else if (string2Check.indexOf("午後休") != -1 | string) {
// 午後だけ1で埋める処理を書く
}
bitDays[key] = newBit;
*/
}
})
return bitDays;
}
/**
* make bits for each day
* @param event
* @return {"key": key, "value": value}, key = date, value = bit
*/
function changeDate2Bit(event){
const start = event.getStartTime();
const end = event.getEndTime();
timeFrom = new Date(start.getFullYear(), start.getMonth(), start.getDate(), timeStartOfDay, 0);
timeTo = new Date(start.getFullYear(), start.getMonth(), start.getDate(), timeEndOfDay, 0);
let dateBit = "0b";
let checkDuration = new Date(timeFrom);
for (let i = 0; i < durationOfDay * 4; i++) {
if (start <= checkDuration && checkDuration < end){
dateBit += "1";
}else {
dateBit += "0";
}
if (checkDuration >= timeTo){
break;
}
checkDuration.setMinutes(checkDuration.getMinutes() + 15);
}
const dateKey = Utilities.formatDate(new Date(start),"JST", "YYYY/MM/dd");
return {"key": dateKey, "value": dateBit};
}
/**
* put bits per each day to sheet Output
* @param bitDays : array of dates and bit
* @param dateArrayP : array of dates to be processed
* @param userCounterP : the row # to output
*/
function putBit2Cells(bitDays, dateArrayP, userCounterP ){
for(let daysCounter = 0; daysCounter < dateArrayP.length; daysCounter++) {
key = dateArrayP[daysCounter];
if (bitDays.indexOf(key) >= 0) {
// remove unnecessary data(was originally set for bit calculation)
const dateBit = bitDays[key].replace(/^0b/,"");
var valuesArray = dateBit.split("");
var numCols = dateBit.length;
// keyの日付とstart日付の差の日数 * durationOfDay * 4(15分刻み前提) が出力する先の列番号。
shtOutput.getRange(startRowNum2Print + userCounterP, (3 + daysCounter * durationOfDay * 4), 1, numCols).setValues([valuesArray]);
} else {
shtOutput.getRange(startRowNum2Print + userCounterP, (3 + daysCounter * durationOfDay * 4), 1, numCols).setValue(0);
}
}
}
あとがき
誰かのスケジュール調整も楽になればいいなとUPしてみた。
みなさまのQiita記事に、いつも学ばせていただいています。
ありがとうございます。
--2024/1/25更新--
カレンダーを"その他のカレンダー"なるものに登録してないと動かないことに気づいたので登録ロジック追加
予定が1件もない日用の0埋めロジックを追加
出力シートの中身をクリアするロジック追加