GoogleSpreadSheetでJavaScriptのマクロ

(2017/06/22)
これもまた仕事でGoogleSpreadSheetでJavaScript(JS)のマクロを 組んでみたのでメモ書きです。 今回は「SyntaxHighlighter」で綺麗に表示できるかも実験。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

function createDateList() {     const START_ROW = 6;   const END_ROW=36;     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();     // 日本の祝日取得   var cal = CalendarApp.getCalendarById("ja.japanese#holiday@group.v.calendar.google.com");   var startTime = new Date(2012, 0, 1);   var endTime = new  Date(2020, 11, 31);   var events = cal.getEvents(startTime, endTime);     // 標準時間を取得   var nstart = sheet.getRange("H2").getValue();   var nend = sheet.getRange("I2").getValue();   var nrest = sheet.getRange("J2").getValue();     // 色をクリア   sheet.getRange("A" + START_ROW + ":N" + END_ROW).setBackgroundRGB(255, 255, 255);   // 時間・作業内容クリア   sheet.getRange("C" + START_ROW + ":E" + END_ROW).setValue("");   sheet.getRange("G" + START_ROW + ":I" + END_ROW).setValue("");   sheet.getRange("K" + START_ROW + ":L" + END_ROW).setValue("");   sheet.getRange("N" + START_ROW + ":N" + END_ROW).setValue("");   var yesterday;   for (i = START_ROW; i < END_ROW + 1; i++) {       var today = sheet.getRange("A" + i).getValue();     var weekday = sheet.getRange("B" + i).getValue();       // 不要な日を消す     if (i > 30) {       valY = yesterday.toString().slice(8, 10);       valT = today.toString().slice(8, 10);       if (valT != "" && valY > valT) {         sheet.getRange("A" + i + ":I" + i).setValue("");         continue;       }     }       if (weekday == "日") {       sheet.getRange("A" + i + ":N" + i).setBackgroundRGB(255, 230, 230);       } else if (weekday == "土") {       sheet.getRange("A" + i + ":N" + i).setBackgroundRGB(200, 230, 255);     } else {       sheet.getRange("C" + i).setValue(nstart);       sheet.getRange("D" + i).setValue(nend);       sheet.getRange("E" + i).setValue(nrest);     }       // 祝日     for (j = 0; j < events.length; j++) {       var holiday = events[j].getAllDayStartDate().toString();         if (today == holiday) {         sheet.getRange("A" + i + ":N" + i).setBackgroundRGB(255, 230, 230);         sheet.getRange("N" + i).setValue(events[j].getTitle());         sheet.getRange("C" + i).setValue("");         sheet.getRange("D" + i).setValue("");         sheet.getRange("E" + i).setValue("");         sheet.getRange("G" + i).setValue("");         sheet.getRange("H" + i).setValue("");         sheet.getRange("I" + i).setValue("");         sheet.getRange("K" + i).setValue("");         sheet.getRange("L" + i).setValue("");       }     }     yesterday = today;     }   }