読みたくないんですが、自己満足回答のために、57行目に提案入れてます。ただ日付や時刻が空白だともっと手前でエラーになるので、微妙です。
が、日付が空白だとエラーになります、という質問ではないので。
javascript
1 /* カレンダーへイベントを登録する */
2 function getsheet ( ) {
3
4 /*-前準備-*/
5
6 //シートの項目を以下変数定義
7 var sht , i , eventday , eventname , place , start , end , added ;
8
9 //shtを定義
10 sht = SpreadsheetApp . getActiveSpreadsheet ( ) . getSheetByName ( "sheet1" ) ;
11
12 //シートの2行目〜最終行まで処理を繰り返す
13 for ( i = 2 ; i <= sht . getLastRow ( ) ; i ++ ) {
14
15 /*-前準備ここまで-*/
16
17 /*--スプレッドシートの値を取得して変数へ格納--*/
18
19 //i行1列目の値(イベント日)をeventdayへ格納
20 eventday = sht . getRange ( i , 1 ) . getValue ( ) ;
21
22 //開始日をUtilities.formatDateでフォーマットしてbへ格納
23 var b = Utilities . formatDate ( eventday , "JST" , "yyyy/MM/dd" ) ;
24
25 //i行2列目の値(イベントの名前)をeventnameへ格納
26 eventname = sht . getRange ( i , 2 ) . getValue ( ) ;
27
28 //i行3列目の値(イベントの場所)をplaceへ格納
29 place = sht . getRange ( i , 3 ) . getValue ( ) ;
30
31 //i行4列目の値(開始時刻)をstartへ格納
32 var starttime = sht . getRange ( i , 4 ) . getValue ( ) ;
33
34 var H = starttime . getHours ( ) ; //starttimeの時間を取得してHへ格納
35 var M = starttime . getMinutes ( ) ; //starttimeの時間を取得してMへ格納
36 var S = starttime . getSeconds ( ) ; //starttimeの時間を取得してSへ格納
37
38 //new Dateメソッドで開始日時「yyMMdd hh:mm」をstartへ格納
39 var start = new Date ( b + " " + H + ":" + M + ":" + S ) ;
40
41 //i行5列目の値(終了時刻)をendへ格納
42 var endtime = sht . getRange ( i , 5 ) . getValue ( ) ;
43
44 var H1 = endtime . getHours ( ) ; //endtimeの時間を取得してH1へ格納
45 var M1 = endtime . getMinutes ( ) ; //endtimeの分を取得してM1へ格納
46 var S1 = endtime . getSeconds ( ) ; //endtimeの秒を取得してS1へ格納
47
48 //new Dateメソッドで終了日時「yyMMdd hh:mm」をendへ格納
49 var end = new Date ( b + " " + H1 + ":" + M1 + ":" + S1 ) ;
50
51 /*--カレンダーへ登録--*/
52
53 //i行6列目の値(イベント登録有無)をaddedへ格納
54 added = sht . getRange ( i , 6 ) . getValue ( ) ;
55
56 //addedの値が空白でなかったらスキップ
57 if ( added !== "" || ! eventname || ! start || ! end || ! place ) { continue ; }
58 Cal = CalendarApp . getCalendarById ( 'hiro0615taka@gmail.com' ) ; //<---ここを自分のgmailアドレスへ変更する
59
60 //指定のカレンダーIDへインベント登録
61 Cal . createEvent ( eventname , start , end , { location : place } ) ; //createEvent(タイトル、開始日時、終了日時、オプション)
62
63 //カレンダー登録が終わったイベントのaddedへ「登録完了」を記入
64 sht . getRange ( i , 6 ) . setValue ( "登録完了" ) ;
65 } //forを閉じる
66 } //functionを閉じる
67
以下、自己満足回答です。検証してないので動かないかも。
javascript
1
2 function driveOnEdit ( ) {
3 const targetSheet = SpreadsheetApp . getActive ( ) . getSheetByName ( "sheet1" ) ;
4 const activeCell = targetSheet . getActiveCell ( ) ;
5 const r = activeCell . getRow ( ) ;
6 const c = activeCell . getColumn ( ) ;
7 const topLeftRange = targetSheet . getRange ( r , c ) ;
8 const e = { range : topLeftRange } ;
9 onEdit ( e ) ;
10 }
11 function onEdit ( e ) {
12 const targetSheet = "sheet1" ;
13 const ownerEmail = "hiro0615taka@gmail.com" ;
14 const sheet = e . range . getSheet ( ) ;
15 if ( sheet . getName ( ) !== targetSheet ) { return ; }
16 const c = e . range . getColumn ( ) ;
17 if ( c > 5 ) { return ; }
18 const r = e . range . getColumn ( ) ;
19 const val = sheet . getRange ( r , 1 , 1 , 5 ) . getValues ( ) [ 0 ] ;
20 if ( val . filter ( function ( e ) { return ! e ; } ) . length > 0 ) { return ; }
21 const cal = CalendarApp . getCalendarById ( ownerEmail ) ;
22 const startTime = new Date ( val [ 0 ] ) . setHour ( val [ 3 ] . getHours ( ) ) . setMinuts ( val [ 3 ] . getMinutes ( ) ) ;
23 const endTime = new Date ( val [ 0 ] ) . setHour ( val [ 4 ] . getHours ( ) ) . setMinuts ( val [ 4 ] . getMinutes ( ) ) ;
24 const id = upsertEvent ( cal , val [ 5 ] , val [ 1 ] , startTime , endTime , undefined , val [ 2 ] ) ;
25 sheet . getRange ( r , 6 ) . setValue ( id ) ;
26 }
27 function upsertEvent ( calendar , id , title , startTime , endTime , description , location , guests , sendInvites ) {
28 if ( ! id ) { return createEvent ( calendar , title , startTime , endTime , description , location , guests , sendInvites ) ; }
29 const evt = calendar . getEventById ( id ) ;
30 if ( ! evt ) { return createEvent ( calendar , title , startTime , endTime , description , location , guests , sendInvites ) ; }
31 updateEvent ( evt , title , startTime , endTime , description , location , guests ) ;
32 return id ;
33 }
34 function updateEvent ( event , title , startTime , endTime , description , location , guests ) {
35 if ( title && event . getTitle ( ) !== title ) { event . setTitle ( title ) ; }
36 if ( startTime && event . getStartTime ( ) !== startTime ) { event . setStartTime ( startTime ) ; }
37 if ( endTime && event . getEndTime ( ) !== endTime ) { event . setEndTime ( endTime ) ; }
38 if ( description && event . getDescription ( ) !== description ) { event . setDescription ( description ) ; }
39 if ( location && event . getLocation ( ) !== location ) { event . setLocation ( location ) ; }
40 if ( ! guests ) { return ; }
41 const current = event . getGuestList ( false ) . map ( function ( e ) { return e . getEmail ( ) ; } ) ;
42 current . filter ( function ( e ) { return guests . indexOf ( e ) === - 1 ; } ) . forEach ( function ( e ) { event . removeGuest ( e ) ; } ) ;
43 guests . filter ( function ( e ) { return current . indexOf ( e ) === - 1 ; } ) . forEach ( function ( e ) { event . addGuest ( e ) ; } ) ;
44 }
45 function createEvent ( calendar , title , startTime , endTime , description , location , guests , sendInvites ) {
46 const option = buildEventOption ( description , location , guests , sendInvites ) ;
47 return calendar . createEvent ( title , startTime , endTime , option ) . getId ( ) ;
48 }
49 function buildEventOption ( description , location , guests , sendInvites ) {
50 const args = { description : description , location : location , guests : guests , sendInvites : sendInvites } ;
51 const keys = [ "description" , "location" , "guests" , "sendInvites" ] . filter ( function ( e ) { return args [ e ] ; } ) . reduce ( function ( a , c ) { a [ c ] = args [ c ] ; return a ; } , { } ) ;
52 }