スクリプト

スプレッドシート画面の Tools > Script editor の項目を選択し、スクリプトエディタを起動します。

テンプレートを選択するダイアログが出てきますが、Close ボタンで閉じてください。

以下のコードを貼り付けます。

//// Utilities//functionforEachRows(rows,startIndex,callback){if(arguments.length===2&&typeofstartIndex==="function"){callback=startIndex;startIndex=0;}varnumRows=rows.getNumRows(),values=rows.getValues();for(vari=startIndex;i<numRows;i++){varrow=values[i],isLast=i===numRows-1;if(!row[0]||callback(row,i,isLast)===false)break;}returnrows;};//// Accessors//functiongetJapaneseHolidays(startTime){varendTime=getDateByAddingDate(startTime,365),events=getCalendarByName("Holidays").getEvents(startTime,endTime),eventsNum=events.length,ret=[];for(vari=0;i<eventsNum;i++){ret.push(getYMDFormattedDate(events[i].getStartTime()));}returnret;};functiongetLastDate(rows){varfound=null,index=-1,isLast=undefined;forEachRows(rows,function(row,i,l){if(row[1]instanceofDate){found=row[1];index=i;isLast=l;}elsereturnfalse;});return{date:found,index:index,isLast:isLast};};functiongetDateByAddingDate(date,offset){returnnewDate(date.getTime()+offset*24*60*60*1000);};functiongetYMDFormattedDate(date){varfmt=function(i){returni<10?"0"+i:i+""};return[date.getFullYear(),fmt(date.getMonth()+1),fmt(date.getDate())].join("/");};functiongetDatesToFill(rows,step){varlastDate=getLastDate(rows),startDate=lastDate.date,isLast=lastDate.isLast,startIndex=isLast?0:(lastDate.index+1),holidays=getJapaneseHolidays(startDate),offset=step,dates=[];Logger.log(holidays);if(step===2&&startDate.getDay()===5){offset=5;}forEachRows(rows,startIndex,function(row,i,l){for(varday=-1;;){date=getDateByAddingDate(startDate,offset);day=date.getDay();if(step===2&&day===5){if(isInHolidays(getDateByAddingDate(date,3),holidays)||isInHolidays(getDateByAddingDate(date,4),holidays)){// QA Deploy on Fridays if next Monday or Tuesday is holidayoffset+=5;break;}else{// Do not deploy on Fridaysoffset+=3;}}else{offset+=step;if(!isDateToSkip(date,holidays))break;}}dates.push([date]);});returndates;};functionisDateToSkip(date,holidays){varday=date.getDay(),month=date.getMonth(),dateNum=date.getDate();returnday===0||day===6||isInHolidays(date,holidays)||(month===11&&dateNum>=27)||// nenmatsu(month===0&&dateNum<=4)// nenshi;};functionisInHolidays(date,holidays){returnholidays.indexOf(getYMDFormattedDate(date))!==-1;};varsettings=null,sheetNames=null;functiongetSetting(name,key){if(!settings){varsheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("_Settings"),range=sheet.getRange("A:C");settings=range.getValues();sheetNames=[];for(vari=0,n;i<settings.length;i++){n=settings[i][0];if(n&&!/^_/.test(n))sheetNames.push(n);}}varlegend=settings[0],x=legend.indexOf(key),y=sheetNames.indexOf(name);returnx>=0&&y>=0?settings[y][x]:undefined;};functiongetStepByName(name){returngetSetting(name,"Step");};functiongetCalendarByName(name){varid=getSetting(name,"Calendar ID");returnCalendarApp.getCalendarById(id);}//// Menu actions//functionfillInNextInterval(){varsheet=SpreadsheetApp.getActiveSheet(),rows=sheet.getDataRange(),lastDate=getLastDate(rows),isLast=lastDate.isLast,startIndex=isLast?0:lastDate.index+1,notation="B"+(startIndex+1)+":"+"B"+rows.getNumRows(),range=sheet.getRange(notation),values=getDatesToFill(rows,getStepByName(sheet.getName()));if(lastDate.isLast)sheet.insertColumnAfter(1);range.setValues(values);};functionexportToCalendar(){varsheet=SpreadsheetApp.getActiveSheet(),rows=sheet.getDataRange(),name=sheet.getName(),step=getStepByName(name),calendar=getCalendarByName(name),holidays=null;forEachRows(rows,function(row,i,l){varmasterName=row[0],startDate=row[1],date=startDate,count=0;if(!(startDateinstanceofDate))return;if(!holidays)holidays=getJapaneseHolidays(startDate);for(;;){if(!isDateToSkip(date,holidays)){calendar.createAllDayEvent(masterName,date);if(++count===step)break;}date=getDateByAddingDate(date,1);}});};functionfillInNextInterval2Cal(){fillInNextInterval();exportToCalendar();}//// App Script Handlers//functiononOpen(){varspreadsheet=SpreadsheetApp.getActiveSpreadsheet(),entries=[{name:"Fill in next interval",functionName:"fillInNextInterval2Cal"}];spreadsheet.addMenu("Masters2Cal",entries);};