[Excel]カレンダー生成（曜日・祝日入り）GoogleAPI
こんな感じのカレンダーを生成します
#!/usr/bin/env osascript
----+----1----+----2----+-----3----+----4----+----5----+----6----+----7
#
#
#
#
# com.cocolog-nifty.quicktimer.icefloe
----+----1----+----2----+-----3----+----4----+----5----+----6----+----7
##自分環境がos12なので2.8にしているだけです
use AppleScript version "2.8"
use framework "Foundation"
use scripting additions
property refMe : a reference to current application
property refNSString : a reference to refMe's NSString
property refNSData : a reference to refMe's NSData
property refNSURL : a reference to refMe's NSURL
property refNSDictionary : a reference to refMe's NSDictionary
property refNSMutableDictionary : a reference to refMe's NSMutableDictionary
property refNSJSONSerialization : a reference to refMe's NSJSONSerialization
property refNSDate : a reference to refMe's NSDate
property refNSDateComponents : a reference to refMe's NSDateComponents
property refNSCalendar : a reference to refMe's NSCalendar
property refNSDateFormatter : a reference to NSDateFormatter
####設定項目
set numFontSize to 10.5 as number
##set strFontName to "Osaka−等幅" as text
set strFontName to "Arial Unicode MS" as text
##set strFontName to "メイリオ" as text
###APIkey
set strApiKye to "XXXXXXXXXXXXXXXXXXXXXXXX"
########################
#####ここから処理
####日付情報の取得--> 今の『年』の数値を求める
set ocidDate to refNSDate's |date|()
set ocidCalendar to refNSCalendar's autoupdatingCurrentCalendar()
set ocidCalendarUnitYear to refMe's NSCalendarUnitYear
set ocidCalendarUnitMonth to refMe's NSCalendarUnitMonth
set ocidDateComponents to ocidCalendar's components:((ocidCalendarUnitYear) + (ocidCalendarUnitMonth)) fromDate:ocidDate
set numSetYear to (ocidDateComponents's |year|) as integer
set numSetMonth to (ocidDateComponents's |month|) as integer
#####各種リスト
set listWeekDay to {"日", "月", "火", "水", "木", "金", "土"} as list
set listYear to {(numSetYear - 1), (numSetYear), (numSetYear + 1)} as list
set listMonth to {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} as list
########################
#####年ダイアログを出す
try
set objResponseYear to (choose from list listYear with title "年選択" with prompt "年を選択してください" default items (item 2 of listYear) OK button name "OK" cancel button name "キャンセル" with multiple selections allowed without empty selection allowed)
on error
log "エラーしました"
return
end try
if objResponseYear is false then
return
end if
set numSetYear to (objResponseYear) as integer
########################
#####月ダイアログを出す
if numSetMonth = 12 then
set numSetMonth to 1 as integer
else
set numSetMonth to numSetMonth + 1 as integer
end if
try
set objResponseMonth to (choose from list listMonth with title "月選択" with prompt "月を選択してください" default items (item (numSetMonth) of listMonth) OK button name "OK" cancel button name "キャンセル" with multiple selections allowed without empty selection allowed)
on error
log "エラーしました"
return
end try
if objResponseMonth is false then
return
end if
set numSetMonth to (objResponseMonth) as integer
########################
#####日付コンポーネント初期化
set ocidDateComponents to refNSDateComponents's alloc()'s init()
ocidDateComponents's setYear:numSetYear
ocidDateComponents's setMonth:numSetMonth
########################
###エクセルの基本処理
set strMonthNoZeroSupp to (text -2 through -1 of ("00" & numSetMonth)) as text
set strSheetName to ("" & numSetYear & "-" & strMonthNoZeroSupp & "") as text
set strFileName to ("" & strSheetName & "カレンダーxlsx") as text
set strDesktopPath to POSIX path of (path to desktop folder from user domain) as text
set strSaveFilePath to (strDesktopPath & strFileName) as text
####新規ワークブック
tell application "Microsoft Excel"
activate
set objWorkBook to make new workbook with properties {excel 8 compatibility mode:false}
tell objWorkBook
set strWorkBookName to name as text
end tell
end tell
####シートの名前の変更
tell application "Microsoft Excel"
tell objWorkBook
tell active sheet
set name to strSheetName
end tell
end tell
end tell
###A1 左上のセルの処理
tell application "Microsoft Excel"
tell objWorkBook
tell sheet strSheetName
tell cell "A1"
set number format to "@"
###Aに年月を入力
set value to ("" & (numSetYear as text) & "/" & strMonthNoZeroSupp) as text
####タイトル行の処理
tell cell "$1:$1"
tell font object
set font size to numFontSize
set name to strFontName
set color to {0, 0, 0}
end tell
tell interior object
set color to {255, 255, 255}
end tell
tell border id border top
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
tell border id border bottom
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
tell border id border left
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
tell border id border right
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
end tell
end tell
end tell
end tell
end tell
########################
###日付
set ocidDate to refNSCalendar's currentCalendar's dateFromComponents:ocidDateComponents
####日付のカレンダーの『レンジ』
set ocdiMaxDateRange to refNSCalendar's currentCalendar's rangeOfUnit:(refMe's NSDayCalendarUnit) inUnit:(refMe's NSMonthCalendarUnit) forDate:ocidDate
########################
###祭日処理
###URL
set strURL to "https://www.googleapis.com/calendar/v3/calendars/" as text
set strCalendarID to "ja.japanese%23holiday%40group.v.calendar.google.com" as text
###
set strTimeZone to "T00%3A00%3A00.000Z"
set strTimeMin to "" & numSetYear & "-01-01" & strTimeZone as text
set strTimeMax to "" & numSetYear & "-12-31" & strTimeZone as text
set strURL to (strURL & strCalendarID & "/events?timeMax=" & strTimeMax & "&timeMin=" & strTimeMin & "&key=" & strApiKye) as text
########################
#####################
###NSURLに
set ocidJsonURL to (refNSURL's URLWithString:strURL)
###URLの内容を読み込む
set listDownLoadData to refNSData's dataWithContentsOfURL:ocidJsonURL options:0 |error|:(reference)
-->戻り値がList
####JSONのデータ部
set ocidJsonData to (item 1 of listDownLoadData)
####エラー情報
set ocidNSErrorData to item 2 of listDownLoadData
if ocidNSErrorData is not (missing value) then
doGetErrorData(ocidNSErrorData)
return "エラー"
end if
#####################
###JSON初期化
set listJSONSerialization to (refNSJSONSerialization's JSONObjectWithData:ocidJsonData options:0 |error|:(reference))
set ocidJsonData to item 1 of listJSONSerialization
#####レコードに格納
set ocidJsonResponse to (refNSDictionary's alloc()'s initWithDictionary:ocidJsonData)
#####################
###使いやすいようなレコードを作成
set ocidHolidayDictionary to refNSMutableDictionary's alloc()'s initWithCapacity:0
###参照
####items
set ocidItemsArray to ocidJsonResponse's valueForKey:"items"
repeat with ocidArrayItems in ocidItemsArray
###祝日名
set strSummary to (ocidArrayItems's valueForKey:"summary") as text
###日付
set strDate to ((ocidArrayItems's valueForKey:"start")'s valueForKey:"date") as text
(ocidHolidayDictionary's setObject:strSummary forKey:strDate)
end repeat
########################
###日付毎処理
set numCntRows to 2
repeat with numDayNo from (location of ocdiMaxDateRange) to (|length| of ocdiMaxDateRange)
-->ここまでは年と月でのカレンダーなので
###日付を入れて
(ocidDateComponents's setDay:numDayNo)
set ocidDate to (refNSCalendar's currentCalendar's dateFromComponents:ocidDateComponents)
set ocidWeekDayClender to (refNSCalendar's currentCalendar's components:(refMe's NSWeekdayCalendarUnit) fromDate:ocidDate)
###日付
log ocidDate as date
###日付番号
log numDayNo
set strDayNoZeroSupp to (text -2 through -1 of ("00" & numDayNo)) as text
#####################
###曜日処理
set numWeekDayNo to (ocidWeekDayClender's |weekday|)'s intValue()
###曜日番号
log numWeekDayNo
###曜日テキスト
set strWeekDayJp to (item numWeekDayNo of listWeekDay) as text
###祭日取得用
set strDateText to ("" & numSetYear & "-" & strMonthNoZeroSupp & "-" & strDayNoZeroSupp & "") as text
###祭日取得
set strValue to (ocidHolidayDictionary's valueForKey:strDateText) as text
###祭日処理
if strValue is "missing value" then
set numChkHoliday to 0 as integer
else
set numChkHoliday to 1 as integer
####Cの列
set strCellNoHoliday to ("C" & numCntRows & "") as text
tell application "Microsoft Excel"
tell objWorkBook
tell sheet strSheetName
tell cell strCellNoHoliday
###Cの列に祭日名を入力
set value to (strValue as text)
end tell
end tell
end tell
end tell
end if
###セル番号
set strCellNoDay to ("A" & numCntRows & "") as text
set strCellNoWeek to ("B" & numCntRows & "") as text
set strCellRowsRange to "$" & numCntRows & ":$" & numCntRows & ""
########################
###エクセル処理
tell application "Microsoft Excel"
tell objWorkBook
tell sheet strSheetName
tell cell strCellNoWeek
set value to strWeekDayJp
end tell
tell cell strCellNoDay
set value to strDayNoZeroSupp
end tell
end tell
end tell
#######行処理
tell cell strCellRowsRange
set number format to "@"
#####
tell interior object
if numWeekDayNo = 1 then
set color to {254, 188, 190}
else if numWeekDayNo = 7 then
set color to {212, 243, 255}
else if numChkHoliday = 1 then
set color to {254, 188, 190}
else
set color to {255, 255, 255}
end if
end tell
#####
tell font object
set font size to numFontSize
set name to strFontName
if numWeekDayNo = 1 then
set color to {170, 0, 0}
else if numWeekDayNo = 7 then
set color to {0, 88, 176}
else if numChkHoliday = 1 then
set color to {170, 0, 0}
else
set color to {0, 0, 0}
end if
end tell
tell border id border top
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
tell border id border bottom
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
tell border id border left
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
tell border id border right
set color to {0, 0, 0}
#set color index to color index automatic
set line style to continuous
set weight to border weight thin
#set tint and shade to 0.0
end tell
end tell
end tell
set numCntRows to numCntRows + 1
end repeat
| 固定リンク
