Googleカレンダー終日予定をスプレッドシートへ表示する

この記事を読むとよい人

GoogleAppsScriptでカレンダーの終日予定を取得してみたい

Googleカレンダーの終日予定をスプレッドシートに反映したい

Googleカレンダーを元に1週間、1か月の予定表を表示したい

はじめに

業務改善においては、既存のシステムをどれだけ利用するかも大切な観点ですよね。

今回は、Googleカレンダーに終日の休みの予定を入れてもらうことで、関係者の休みの状況をスプレッドシートで可視化するGoogleAppsScriptで作成しました。

ネットで検索しても、完成されたものが見つからず、複数のサイトを見ながら自作するということになりました。

コード掲載いたしますが、以下のリンクをクリックすると、即時利用可能で実用性の高いスプレッドシートをコピーし、以下のいくつかの簡単な手順に沿っていただくだけで、利用可能となっています。

細かい仕様や自分でカスタマイズしたい場合には、ダウンロード後にこの記事をよく読んでいただき、自分に合ったカスタマイズをしてみてください。

ダウンロードURL

https://docs.google.com/spreadsheets/d/1zcv_c92817UCkjInWxD5NV8nij9n6DIEjPkoPX8QL6s/

手順

らいちょうさん

ダウンロードURLクリックして、ファイル→コピーを作成をクリック。

らいちょうさん

ドキュメントをコピーで、任意の名前とフォルダを指定してをOK押します。

※今回は、規定で表示された名前とフォルダをそのままでOKを選択しています。

らいちょうさん

拡張機能→AppsScript

らいちょうさん

「appsscript.json」マニフェストファイルをエディタで表示する　にチェックを入れる有効にする。

カレンダーの様に国や地域によって時刻に影響がでるスクリプトを実行するときは、タイムゾーンを正しく指定しておかないと想定と異なる結果になります。スクリプトを実行する環境を日本時間にしておく必要があります。

また、カレンダーについてもどこのタイムゾーンを利用しているかで結果変わったはずですので、確認しておきましょう。

らいちょうさん

マニフェストファイルが有効になっているか確認します。エディタをクリックします。

らいちょうさん

マニフェストファイル「appsscript.json」が表示されますのでクリックして、timezoneを確認し、"Asia/Tokyo"になっていなければ以下のコードをコピペして上書きしてください。

※日本でなければ、いらっしゃる地域にしてください。

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

{

"timeZone": "Asia/Tokyo",

"dependencies": {

"exceptionLogging": "STACKDRIVER",

"runtimeVersion": "V8"

}

らいちょうさん

timezoneの変更が発生した方は、フロッピーディスクのマークをクリックして、保存してださい。

らいちょうさん

一旦、スクリプトと紐づいているスプレッドシートをF5もしくは更新マークで、リロードしてください。

らいちょうさん

リロードしたら、利用できるようにするため、一度GetGoogleCalメニューのgoogleCal To Sp thisをクリックして実行します。

※この時点では、スクリプトはまだ実行されません。スクリプトを実行するための承認作業が完了してから機能するようになります。

らいちょうさん

スクリプトを実行すると、承認が必要と表示されるので、続行をクリック。

らいちょうさん

以下のように表示されますので、ご自身のGoogleアカウントをクリック（どのアカウントで実行するのか）

そのあとに、許可をクリックして準備完了です。

仕様

前提
- このスクリプトを実行するGoogleアカウントに対してカレンダーの閲覧権限を与えられている、かつ他のカレンダーに表示されている必要があります。なお、カレンダー上に表示をするためのチェックボックスはチェックが入っていなくてもよいです。
- listのシートのメールアドレスは、閲覧権限のあるアカウントにしてください。初期のメールアドレスは利用できませんので、削除してください。
シート
- def
  - start day
    - カレンダーの始まりの日を指定できます。25日締めであれば26を入力するとカレンダーに反映されます。
- list
  - このシートにカレンダーに反映させるGoogleアカウントの一覧を追加しておきます。
- last
  - 先月のカレンダー表示
- this
  - 今月のカレンダー表示
- next
  - 来月のカレンダー表示
- exp(Last/This/Next)
  - メニューで対応するスクリプト実行すると、listシートにカレンダー表示したいGoogleアカウントを登録しておく必要があります。
メニューのGetGoogleCal
- googleCal To Sp (last/this/next)
  - last=先月　、this=今月　、next=来月　のカレンダーを取得して反映させることができます。
- targetGoogleCalAddOtherCal
  - 実行者のアカウントのGoogleカレンダーにlistシートに追加したGoogleアカウントを他のカレンダーに追加します。
  - これを最初に実行しておかないとgoogleCal To Sp (last/this/next)を実行しても、他のカレンダーに追加されていない人をスプレッドシートに反映することができません。
AppsScript内ファイル
- cfg.gs
  - この中の設定をかえると、カスタマイズできます。

コード

/**
 * export用2次元配列データをimport用2次元配列に格納する処理
 * 
 * @param array 二次元配列　出力用
 * @param array 二次元配列　入力用
 * 
 * @return array 二次元配列　（出力→入力後）
 * 
 */
function a2ArrayDataImporta2Array(out2Data,in2Data){

  //googleカレンダーデータ
  for(let k in out2Data){

    let outyMd =  Utilities.formatDate(new Date( out2Data[k][0]),"JST", "yyyy/MM/dd");
    Logger.log("out2Data = "+ out2Data[k][4] + " : "+ out2Data[k][0]+ "/ outyMd: "+ outyMd);
    
    // 配列の行移動
    for(let i =0;i< in2Data.length;i++){

      //メールアドレス一致
      if(out2Data[k][4] == in2Data[i][0]){
        
        //配列の列移動
        for(let j =0;j< in2Data[0].length;j++){
          
          //インポート先配列のData　を　文字列にすることで、カレンダーとシートの時間を無視する
          let inyMd =  Utilities.formatDate(new Date( in2Data[0][j]),"JST", "yyyy/MM/dd");
          
          //各DateをString化した日付を比較
          if(outyMd == inyMd){

            //複数日の終日予定を想定したfor 
            for(let holidays = 0;holidays < out2Data[k][7];holidays++){

              //連休数＋日付が配列以内の数値であれば・・・・
                if(j + holidays< in2Data[0].length){
                  in2Data[i][ j + holidays]= out2Data[k][2];
                }
            }
          }
        }
      }
    } 
  }
  Logger.log(in2Data);
  return in2Data;
}



/***
 *  シートのカレンダー範囲を日付も含めて指定する。
 *  
 * @param sheet
 * 
 * @return Object(JSON) 
 *    {
        dtStart: dtStart, 
        dtEnd: dtEnd ,
        targetRange: targetRange,
        targetStartRow: targetRow,
        targetStartCol: targetCol,
        targetRangeRowNum: targetRangeRowNum,
        targetRangeColNum: targetRangeColNum
      };
 */
function stCalRange(st){

  // 検索文字 スプレッドシート配列化の開始位置の文字列を探す準備。
  const textFinder = st.createTextFinder(cfg.findStr);
  
  //　検索対象の文字をシート全体検索　一意であること、また必ず文字があること
  const cell = textFinder.findAll();

  //　検索文字の含まれたセルの行数と列数を取得
  const targetRow = cell[0].getRow();
  const targetCol = cell[0].getColumn();


  //　cfg.findStrの右セルをカレンダー取得日とするためCol + 1 して月初の日付を取得
  let dtStart =(st.getRange(targetRow,targetCol + 1).getValue());
  
  // new Date しないとdtStartの値も変更されます
  let dtEnd = new Date(dtStart);

  //1ヶ月カレンダーの場合
  if (cfg.dispCal == "1month"){
    // 1カ月加算して
    dtEnd.setMonth(dtEnd.getMonth() +1 );
    // 1日減算する
    dtEnd.setDate(dtEnd.getDate() -1 );
  }
  //1週間カレンダー
  if(cfg.dispCal == "1week"){
    //　6日加算する
    dtEnd.setDate(dtEnd.getDate() + 6);
  }

  //配列範囲取り出し範囲指定用　最終列 間の数になってならないように＋１
  var targetColLast = (dtEnd - dtStart)/ (1000 * 60 * 60 * 24) +1;
  Logger.log(targetColLast);

  // シンプルに表示されているMAX行を取得して初期化範囲を設定する
  // 以下も検討したが、対象カレンダー1件だけの場合や、間に空白セルがある場合、きっちり最後の行を文字が入った状態で実行したときなどで
  // 結果がバラバラであったため、シンプルにした
  //st.getRange("A:A").getValues().filter(String).length
  //st.getRange(st.getMaxRows(), targetCol).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
  const targetRowLast = st.getMaxRows();
   
  Logger.log("targetRowLast  = "+ targetRowLast);
  //始点セルからの行数
  const targetRangeRowNum = targetRowLast - targetRow + 1;

  //始点セルからの列数
  const targetRangeColNum = targetColLast + 1;

  //　2次元配列化の　セル範囲を指定する。
  const targetRange = st.getRange(targetRow,targetCol,targetRangeRowNum,targetRangeColNum).getA1Notation();


  return {
            dtStart: dtStart,
            dtEnd: dtEnd ,
            targetRange: targetRange,
            targetStartRow: targetRow,
            targetStartCol: targetCol,
            targetRangeRowNum: targetRangeRowNum,
            targetRangeColNum: targetRangeColNum
          };

}

/**********************************************************
 * 
 * @param st            Googleカレンダー検索結果を出力するシート
 * @param listSt        Googleアカウントがあるリスト
 * @param Object(Date)  カレンダー検索開始日  
 * @param Object(Date)  カレンダー検索終了日
 *
 * @return  Array     ヘッダー部　と2次元配列の[] を除外した範囲を2次元配列化
*/
function getCalTo(st,listSt,startDay,endDay){

  Logger.log("startDay =" +typeof(startDay));
  Logger.log("endDay =" +typeof(endDay));

  /** 単体テスト用変数 */
  if(!startDay || !endDay){
    
    startDay ='2021/01/01 00:00';
    endDay = '2021/01/30 23:59';
  }

  //スプレッドシート　から1次元配列を作成する 良くないけどセル2,2から行方向へ
  let mailaddressListArray = rangesToArray(listSt,cfg.gAccountListStartrow,cfg.gAccountListStartcol);
  

  //要しておいたcfg.g_calをヘッダーにする　オブジェクトのvalueを配列にする
  let header = [Object.values(cfg.g_cal)];
  Logger.log(header);

  //ヘッダ
  st.getRange(1,1,header.length,header[0].length).setValues(header);

  let a2array =[];
  
  //プロパティの値を反復処理
  for( let item of mailaddressListArray){

    //日付、日付、メールアドレス(item)
    let array = planCal(startDay,endDay,item,cfg.searchStr);
    
    a2array.push(array);
  }
  //入れ子2次元をフラットにする [[[]],[]]→[[],[],[]]
  const flat2Array = a2array.flat();


 
  //必要のないので、空の値を削除する
  const cleanArray = flat2Array.filter(String);

  Logger.log('------\n'+cleanArray);
  Logger.log('->'+ cleanArray.length);

  // カレンダー取得データなし（要素なし）[]は0、　0はfalse扱い
  if(cleanArray.length){
    //予定の2次元配列をスプレッドシートに出力する
    st.getRange(2,1,cleanArray.length,cleanArray[0].length).setValues(cleanArray);
  }else{
    Logger.log('カレンダーなし(要素なし)');
  }
  //***************** */
  
  return cleanArray;
}


/*******************************************
 * 
 * 汎用型　スプレッドシート　ー＞　配列
 * 
 * @param st      
 * @param string  range (省略時　=null
 * 
 * @return Array 2次元配列
 */

function stToa2array(st,targetRange=null){
 
  Logger.log(targetRange);

  //第3引数無しの場合(NULL)
  if(!targetRange){
    
    //すべて配列可
    var array = st.getDataRange().getValues();
  
  //第3引数ありの場合（レンジ指定）
  }else{
    //指定範囲の配列化
    var array = st.getRange(targetRange).getValues();
  }
  
  //必要のない空の値を削除する
  let cleanArray = array.filter(String);
  
  // Logger.log(cleanArray);
    return cleanArray;
}



/**
  指定シートの最終行
  シートのデータをgetValuesで取得した2次元配列を1次元配列に変更する

  @param st   スプレッドシート
  @param num　開始行
  @param num　開始列

  @return　array 1次元　メールアドレス
**/
function rangesToArray(st,startRow,startCol){

  //最終行
  const lastR =  st.getLastRow();
  Logger.log(lastR);
  
  //2行3列目から最終行－１行1列分
  
  //2次元配列を取得　
  const arrayBefore = st.getRange(startRow,startCol,lastR - 1,1).getValues();

  //もう使えないflat  const arrayAfter = Array.prototype.concat.apply([],arrayBefore);
  //ES2019から利用可能なflatメソッド　2次元→1次元
  const arrayAfter = arrayBefore.flat();
  Logger.log(arrayAfter);

  return arrayAfter;
}

/**
 *  
 * @param str  /日付
 * @param str  /日付
 * @param array(str) /配列
 * @param array(str) 検索文字列
 * 
 * 
 *  @return array 2次元配列　対象の予定すべて
*/
function planCal(startDay,endDay,targetmailAdd,searchStr){
  
  // ById(対象者のメールアドレス)対象メールアドレスの予定情報を cal に代入
  var cal = CalendarApp.getCalendarById(targetmailAdd);
  
  // 予定が一切無い場合は除外
  if(cal != null){//予定が一切なしでなければ指定期間の予定を取得し、events配列にすべて入れる

    //指定期間内のイベントを取得する
    var events = cal.getEvents(
      new Date(startDay),//開始がyyyy/MM/dd 00:00の仕様の模様
      new Date(endDay)//終了はyyyy/MM/dd-1 の23:59の仕様の模様
    );
  }
  
  //戻り値用　配列
  let array_return =[];

  //events配列の数までiループ for(変数 in オブジェクト)
  for(let i in events){

    //終日の予定であれば　　　チルダで含む場合
    if(events[i].isAllDayEvent() && ~searchStr.indexOf(events[i].getTitle())){

        //イベント終了時間（終日の予定の場合00:00
        cfg.g_cal.sdate= Utilities.formatDate(new Date(events[i].getStartTime()), "JST", "yyyy/MM/dd HH:mm");
        //イベント終了時間（終日の予定の場合翌日00:00
        cfg.g_cal.edate= Utilities.formatDate(new Date(events[i].getEndTime()), "JST", "yyyy/MM/dd HH:mm");
        //表題タイトル
        cfg.g_cal.title= events[i].getTitle();
        cfg.g_cal.lastUpD =     Utilities.formatDate(new Date(events[i].getLastUpdated()), "JST", "MM/dd HH:mm");
        //作成者
        cfg.g_cal.creator  =    events[i].getCreators();
        //終日予定？　true false
        cfg.g_cal.alldayevent= events[i].isAllDayEvent();
        //一意の予定ID
        cfg.g_cal.eventid =    events[i].getId();

        cfg.g_cal.days = parseInt((new Date(events[i].getEndTime()) - new Date(events[i].getStartTime()))/(24*60*60*1000))  ;

      //連想配列を配列へ書き出し用
      let array = [];

      //Object.values(連想配列)で、連想配列の要素を一つずつ書き込まなくても書き出せる
      array.push(Object.values(cfg.g_cal));
              //object.valuesを使わないときの方法
              //すべての要素をstrに書き込み
              // for (var item in p) {
              //   array.push( p[item]);
              // }

      //  Logger.log(array);
      array_return.push(array);
    }
  }
  
  //予定入れていない人のデータの扱いが[]で戻ってくる。別の関数で削除
   Logger.log(array_return);
  //Object.valuesを利用した場合にはflat()にする必要がある。for(var item in p)であればflat()不要
  return array_return.flat();  
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

/**

* export用2次元配列データをimport用2次元配列に格納する処理

* @param array 二次元配列　出力用

* @param array 二次元配列　入力用

* @return array 二次元配列　（出力→入力後）

function a2ArrayDataImporta2Array(out2Data,in2Data){

//googleカレンダーデータ

for(let k in out2Data){

let outyMd = Utilities.formatDate(new Date( out2Data[k][0]),"JST", "yyyy/MM/dd");

Logger.log("out2Data = "+ out2Data[k][4] + " : "+ out2Data[k][0]+ "/ outyMd: "+ outyMd);

// 配列の行移動

for(let i =0;i< in2Data.length;i++){

//メールアドレス一致

if(out2Data[k][4] == in2Data[i][0]){

//配列の列移動

for(let j =0;j< in2Data[0].length;j++){

//インポート先配列のData　を　文字列にすることで、カレンダーとシートの時間を無視する

let inyMd = Utilities.formatDate(new Date( in2Data[0][j]),"JST", "yyyy/MM/dd");

//各DateをString化した日付を比較

if(outyMd == inyMd){

//複数日の終日予定を想定したfor

for(let holidays = 0;holidays < out2Data[k][7];holidays++){

//連休数＋日付が配列以内の数値であれば・・・・

if(j + holidays< in2Data[0].length){

in2Data[i][ j + holidays]= out2Data[k][2];

}

Logger.log(in2Data);

return in2Data;

}

/***

* シートのカレンダー範囲を日付も含めて指定する。

* @param sheet

* @return Object(JSON)

* {

dtStart: dtStart,

dtEnd: dtEnd ,

targetRange: targetRange,

targetStartRow: targetRow,

targetStartCol: targetCol,

targetRangeRowNum: targetRangeRowNum,

targetRangeColNum: targetRangeColNum

};

function stCalRange(st){

// 検索文字スプレッドシート配列化の開始位置の文字列を探す準備。

const textFinder = st.createTextFinder(cfg.findStr);

//　検索対象の文字をシート全体検索　一意であること、また必ず文字があること

const cell = textFinder.findAll();

//　検索文字の含まれたセルの行数と列数を取得

const targetRow = cell[0].getRow();

const targetCol = cell[0].getColumn();

//　cfg.findStrの右セルをカレンダー取得日とするためCol + 1 して月初の日付を取得

let dtStart =(st.getRange(targetRow,targetCol + 1).getValue());

// new Date しないとdtStartの値も変更されます

let dtEnd = new Date(dtStart);

//1ヶ月カレンダーの場合

if (cfg.dispCal == "1month"){

// 1カ月加算して

dtEnd.setMonth(dtEnd.getMonth() +1 );

// 1日減算する

dtEnd.setDate(dtEnd.getDate() -1 );

}

//1週間カレンダー

if(cfg.dispCal == "1week"){

//　6日加算する

dtEnd.setDate(dtEnd.getDate() + 6);

}

//配列範囲取り出し範囲指定用　最終列間の数になってならないように＋１

var targetColLast = (dtEnd - dtStart)/ (1000 * 60 * 60 * 24) +1;

Logger.log(targetColLast);

// シンプルに表示されているMAX行を取得して初期化範囲を設定する

// 以下も検討したが、対象カレンダー1件だけの場合や、間に空白セルがある場合、きっちり最後の行を文字が入った状態で実行したときなどで

// 結果がバラバラであったため、シンプルにした

//st.getRange("A:A").getValues().filter(String).length

//st.getRange(st.getMaxRows(), targetCol).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()

const targetRowLast = st.getMaxRows();

Logger.log("targetRowLast = "+ targetRowLast);

//始点セルからの行数

const targetRangeRowNum = targetRowLast - targetRow + 1;

//始点セルからの列数

const targetRangeColNum = targetColLast + 1;

//　2次元配列化の　セル範囲を指定する。

const targetRange = st.getRange(targetRow,targetCol,targetRangeRowNum,targetRangeColNum).getA1Notation();

return {

dtStart: dtStart,

dtEnd: dtEnd ,

targetRange: targetRange,

targetStartRow: targetRow,

targetStartCol: targetCol,

targetRangeRowNum: targetRangeRowNum,

targetRangeColNum: targetRangeColNum

};

}

/**********************************************************

* @param st Googleカレンダー検索結果を出力するシート

* @param listSt Googleアカウントがあるリスト

* @param Object(Date) カレンダー検索開始日

* @param Object(Date) カレンダー検索終了日

* @return Array ヘッダー部　と2次元配列の[] を除外した範囲を2次元配列化

function getCalTo(st,listSt,startDay,endDay){

Logger.log("startDay =" +typeof(startDay));

Logger.log("endDay =" +typeof(endDay));

/** 単体テスト用変数 */

if(!startDay || !endDay){

startDay ='2021/01/01 00:00';

endDay = '2021/01/30 23:59';

}

//スプレッドシート　から1次元配列を作成する良くないけどセル2,2から行方向へ

let mailaddressListArray = rangesToArray(listSt,cfg.gAccountListStartrow,cfg.gAccountListStartcol);

//要しておいたcfg.g_calをヘッダーにする　オブジェクトのvalueを配列にする

let header = [Object.values(cfg.g_cal)];

Logger.log(header);

//ヘッダ

st.getRange(1,1,header.length,header[0].length).setValues(header);

let a2array =[];

//プロパティの値を反復処理

for( let item of mailaddressListArray){

//日付、日付、メールアドレス(item)

let array = planCal(startDay,endDay,item,cfg.searchStr);

a2array.push(array);

}

//入れ子2次元をフラットにする [[[]],[]]→[[],[],[]]

const flat2Array = a2array.flat();

//必要のないので、空の値を削除する

const cleanArray = flat2Array.filter(String);

Logger.log('------\n'+cleanArray);

Logger.log('->'+ cleanArray.length);

// カレンダー取得データなし（要素なし）[]は0、　0はfalse扱い

if(cleanArray.length){

//予定の2次元配列をスプレッドシートに出力する

st.getRange(2,1,cleanArray.length,cleanArray[0].length).setValues(cleanArray);

}else{

Logger.log('カレンダーなし(要素なし)');

}

//***************** */

return cleanArray;

}

/*******************************************

* 汎用型　スプレッドシート　ー＞　配列

* @param st

* @param string range (省略時　=null

* @return Array 2次元配列

function stToa2array(st,targetRange=null){

Logger.log(targetRange);

//第3引数無しの場合(NULL)

if(!targetRange){

//すべて配列可

var array = st.getDataRange().getValues();

//第3引数ありの場合（レンジ指定）

}else{

//指定範囲の配列化

var array = st.getRange(targetRange).getValues();

}

//必要のない空の値を削除する

let cleanArray = array.filter(String);

// Logger.log(cleanArray);

return cleanArray;

}

/**

指定シートの最終行

シートのデータをgetValuesで取得した2次元配列を1次元配列に変更する

@param st スプレッドシート

@param num　開始行

@param num　開始列

@return　array 1次元　メールアドレス

**/

function rangesToArray(st,startRow,startCol){

//最終行

const lastR = st.getLastRow();

Logger.log(lastR);

//2行3列目から最終行－１行1列分

//2次元配列を取得　

const arrayBefore = st.getRange(startRow,startCol,lastR - 1,1).getValues();

//もう使えないflat const arrayAfter = Array.prototype.concat.apply([],arrayBefore);

//ES2019から利用可能なflatメソッド　2次元→1次元

const arrayAfter = arrayBefore.flat();

Logger.log(arrayAfter);

return arrayAfter;

}

/**

* @param str /日付

* @param array(str) /配列

* @param array(str) 検索文字列

* @return array 2次元配列　対象の予定すべて

function planCal(startDay,endDay,targetmailAdd,searchStr){

// ById(対象者のメールアドレス)対象メールアドレスの予定情報を cal に代入

var cal = CalendarApp.getCalendarById(targetmailAdd);

// 予定が一切無い場合は除外

if(cal != null){//予定が一切なしでなければ指定期間の予定を取得し、events配列にすべて入れる

//指定期間内のイベントを取得する

var events = cal.getEvents(

new Date(startDay),//開始がyyyy/MM/dd 00:00の仕様の模様

new Date(endDay)//終了はyyyy/MM/dd-1 の23:59の仕様の模様

);

}

//戻り値用　配列

let array_return =[];

//events配列の数までiループ for(変数 in オブジェクト)

for(let i in events){

//終日の予定であれば　　　チルダで含む場合

if(events[i].isAllDayEvent() && ~searchStr.indexOf(events[i].getTitle())){

//イベント終了時間（終日の予定の場合00:00

cfg.g_cal.sdate= Utilities.formatDate(new Date(events[i].getStartTime()), "JST", "yyyy/MM/dd HH:mm");

//イベント終了時間（終日の予定の場合翌日00:00

cfg.g_cal.edate= Utilities.formatDate(new Date(events[i].getEndTime()), "JST", "yyyy/MM/dd HH:mm");

//表題タイトル

cfg.g_cal.title= events[i].getTitle();

cfg.g_cal.lastUpD = Utilities.formatDate(new Date(events[i].getLastUpdated()), "JST", "MM/dd HH:mm");

//作成者

cfg.g_cal.creator = events[i].getCreators();

//終日予定？　true false

cfg.g_cal.alldayevent= events[i].isAllDayEvent();

//一意の予定ID

cfg.g_cal.eventid = events[i].getId();

cfg.g_cal.days = parseInt((new Date(events[i].getEndTime()) - new Date(events[i].getStartTime()))/(24*60*60*1000)) ;

//連想配列を配列へ書き出し用

let array = [];

//Object.values(連想配列)で、連想配列の要素を一つずつ書き込まなくても書き出せる

array.push(Object.values(cfg.g_cal));

//object.valuesを使わないときの方法

//すべての要素をstrに書き込み

// for (var item in p) {

// array.push( p[item]);

// }

// Logger.log(array);

array_return.push(array);

}

//予定入れていない人のデータの扱いが[]で戻ってくる。別の関数で削除

Logger.log(array_return);

//Object.valuesを利用した場合にはflat()にする必要がある。for(var item in p)であればflat()不要

return array_return.flat();

}

/**
 * スプレッドシートをブラウザで開いたとき、スプレッドシートに独自メニューを表示する
 */
function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //表示したメニューの中に表示するメニュー
  var menu = [
    {name: "googleCal To Sp last",functionName: "lastPeriod"},
    {name: "googleCal To Sp this",functionName: "thisPeriod"},
    {name: "googleCal To Sp next",functionName: "nextPeriod"},
    {name: "targetGoogleCalAddOtherCal",functionName: "addOtherCal"}
  ];
  //メニューに表示する
  ss.addMenu("GetGoogleCal",menu);
}

/**

* スプレッドシートをブラウザで開いたとき、スプレッドシートに独自メニューを表示する

function onOpen(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

//表示したメニューの中に表示するメニュー

var menu = [

{name: "googleCal To Sp last",functionName: "lastPeriod"},

{name: "googleCal To Sp this",functionName: "thisPeriod"},

{name: "googleCal To Sp next",functionName: "nextPeriod"},

{name: "targetGoogleCalAddOtherCal",functionName: "addOtherCal"}

];

//メニューに表示する

ss.addMenu("GetGoogleCal",menu);

}

/**************************************************************
 作成 oreish.com
動作内容：
　日本時間にする場合は、マニフェストを表示してappsscript.jsonを"timeZone": "Asia/Tokyo",
  にする必要があります。

**************************************************************/

function lastPeriod(){
  //falseはシートコピーなし
  main(cfg.rosterStNameLast,cfg.exportGoogleCalStLast,false);
}

function thisPeriod(){
  //falseはシートコピーなし
  main(cfg.rosterStNameThis,cfg.exportGoogleCalStThis,false);
}
  
// }
function nextPeriod(){
  //falseはシートコピーなし
  main(cfg.rosterStNameNext,cfg.exportGoogleCalStNext,false);
}


/**
 * メイン関数
 * 
 * @param string    勤務表シート名
 * @param string    googleカレンダー結果出力シート名
 * @param Boolean   処理後コピーシート出力の有無
 * 
 * @return なし
 */
function main(rosterStName,exportGoogleCalStName,output){

  // スプレッドシート指定
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  //let ss = SpreadsheetApp.openByUrl(cfg.ssUrl);

  // カレンダー出力先シート
  let rosterSt = ss.getSheetByName(rosterStName);

  //googleカレンダーの情報を反映させるスプレッドシートの範囲と期間を取得
  let stCalInfo = stCalRange(rosterSt);
  Logger.log('stCalInfo =' + JSON.stringify(stCalInfo,null,'\t'));
  //出力先stの対象範囲の値を消去 日付とメールアドレスを消さないので、行列ともに＋１ 範囲を行は　－１、範囲列は日付の月による差があるため、３１で固定
  if(cfg.dispCal == "1month"){
    rosterSt.getRange(stCalInfo.targetStartRow + 1,stCalInfo.targetStartCol + 1,stCalInfo.targetRangeRowNum-1,cfg.rosterClearColNum).clearContent();
  //1week
  }else{
    rosterSt.getRange(stCalInfo.targetStartRow + 1,stCalInfo.targetStartCol + 1,stCalInfo.targetRangeRowNum-1,stCalInfo.targetRangeColNum-1).clearContent();
  }
  
  //googleカレンダーのデータを反映させるシートの範囲を配列化
  let stRangeData = stToa2array(rosterSt,stCalInfo.targetRange);
   
  Logger.log(stRangeData);

  //  対象シート
  // カレンダー出力先シート
  let exportGoogleCalSt = ss.getSheetByName(exportGoogleCalStName);
  
  //シートをクリア
  exportGoogleCalSt.clear();

  //googleアカウント一覧のスプレッドシート
  let GoogleAccountlistSt = ss.getSheetByName(cfg.googleAccountlistStName);
  
  //Googleカレンダーの情報を取得し2次元配列化
  let calData = getCalTo(exportGoogleCalSt,GoogleAccountlistSt,stCalInfo.dtStart,stCalInfo.dtEnd);
  
  //スプレッドシートカレンダー表に反映させる2次元配列を変数名で扱う
  let processedData = a2ArrayDataImporta2Array(calData,stRangeData);
  
  //header部の最初の配列（日付）を削除
  processedData.splice(0,1);
  
  //スプレッドシートカレンダー表の日付部を上書きしないように列を一つ下へ下げて、セル範囲も一つ削る範囲指定
  rosterSt.getRange(stCalInfo.targetStartRow+1,stCalInfo.targetStartCol,stCalInfo.targetRangeRowNum-1,stCalInfo.targetRangeColNum).setValues(processedData);
  
  //true
  if(output){
  
  //予定表シートを　コピーして、現在時刻のシートで複製する
    let nowStr = Utilities.formatDate(new Date(),"JST", "yyyy/MM/dd HH:mm:ss");
    let copySt = rosterSt.copyTo(ss);
    copySt.setName(rosterStName + " " + nowStr );
    
    let copyExpGooCalSt =ss.getSheetByName(exportGoogleCalStName).copyTo(ss);
    copyExpGooCalSt.setName(exportGoogleCalStName + " " + nowStr );
  }
}

/**
 * 
 * カレンダー情報を取得するには、プログラム実行者の他のカレンダーに追加されている必要がある。
 * 
 */
function addOtherCal() {

  try {

    // スプレッドシート指定
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    //const ss = SpreadsheetApp.openByUrl(cfg.ssUrl);// URLで指定したいときはこちらを利用

    // カレンダーを取得したいgoogleアカウント一覧のスプレッドシート
    const GoogleAccountlistSt = ss.getSheetByName(cfg.googleAccountlistStName);

    //スプレッドシート　から1次元配列を作成する 良くないけどセル2,2から行方向へ
    let mailaddressListArray = rangesToArray(GoogleAccountlistSt ,cfg.gAccountListStartrow,cfg.gAccountListStartcol);
    
    //スクリプト実行者のカレンダーに他のカレンダーを追加するときの条件
    const options = {
      hidden: false,  // カレンダーは、ユーザインタフェースの中に隠されているかどうか（デフォルト： false）
      selected: false // カレンダーを追加したときに、カレンダー上に反映させない（チェックボックスにチェックを入れない）
    }
    
    //対象のアカウントのメールアドレスがなくなるまで実行する
    for( let item of mailaddressListArray){

      if(item){
        var errEmail = item;
        Logger.log(item);
        // 他アカウントのカレンダーを追加
        CalendarApp.subscribeToCalendar(item, options);
        // 実行を少し待機
        Utilities.sleep(0.5 * 1000);
      }
    }
  }catch(e){

    Browser.msgBox("エラー：" + e.message +' errEmail :'+errEmail +' は存在しません。一度確認してください。');
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

/**************************************************************

作成 oreish.com

動作内容：

　日本時間にする場合は、マニフェストを表示してappsscript.jsonを"timeZone": "Asia/Tokyo",

にする必要があります。

**************************************************************/

function lastPeriod(){

//falseはシートコピーなし

main(cfg.rosterStNameLast,cfg.exportGoogleCalStLast,false);

}

function thisPeriod(){

//falseはシートコピーなし

main(cfg.rosterStNameThis,cfg.exportGoogleCalStThis,false);

}

// }

function nextPeriod(){

//falseはシートコピーなし

main(cfg.rosterStNameNext,cfg.exportGoogleCalStNext,false);

}

/**

* メイン関数

* @param string 勤務表シート名

* @param string googleカレンダー結果出力シート名

* @param Boolean 処理後コピーシート出力の有無

* @return なし

function main(rosterStName,exportGoogleCalStName,output){

// スプレッドシート指定

let ss = SpreadsheetApp.getActiveSpreadsheet();

//let ss = SpreadsheetApp.openByUrl(cfg.ssUrl);

// カレンダー出力先シート

let rosterSt = ss.getSheetByName(rosterStName);

//googleカレンダーの情報を反映させるスプレッドシートの範囲と期間を取得

let stCalInfo = stCalRange(rosterSt);

Logger.log('stCalInfo =' + JSON.stringify(stCalInfo,null,'\t'));

//出力先stの対象範囲の値を消去日付とメールアドレスを消さないので、行列ともに＋１範囲を行は　－１、範囲列は日付の月による差があるため、３１で固定

if(cfg.dispCal == "1month"){

rosterSt.getRange(stCalInfo.targetStartRow + 1,stCalInfo.targetStartCol + 1,stCalInfo.targetRangeRowNum-1,cfg.rosterClearColNum).clearContent();

//1week

}else{

rosterSt.getRange(stCalInfo.targetStartRow + 1,stCalInfo.targetStartCol + 1,stCalInfo.targetRangeRowNum-1,stCalInfo.targetRangeColNum-1).clearContent();

}

//googleカレンダーのデータを反映させるシートの範囲を配列化

let stRangeData = stToa2array(rosterSt,stCalInfo.targetRange);

Logger.log(stRangeData);

// 対象シート

// カレンダー出力先シート

let exportGoogleCalSt = ss.getSheetByName(exportGoogleCalStName);

//シートをクリア

exportGoogleCalSt.clear();

//googleアカウント一覧のスプレッドシート

let GoogleAccountlistSt = ss.getSheetByName(cfg.googleAccountlistStName);

//Googleカレンダーの情報を取得し2次元配列化

let calData = getCalTo(exportGoogleCalSt,GoogleAccountlistSt,stCalInfo.dtStart,stCalInfo.dtEnd);

//スプレッドシートカレンダー表に反映させる2次元配列を変数名で扱う

let processedData = a2ArrayDataImporta2Array(calData,stRangeData);

//header部の最初の配列（日付）を削除

processedData.splice(0,1);

//スプレッドシートカレンダー表の日付部を上書きしないように列を一つ下へ下げて、セル範囲も一つ削る範囲指定

rosterSt.getRange(stCalInfo.targetStartRow+1,stCalInfo.targetStartCol,stCalInfo.targetRangeRowNum-1,stCalInfo.targetRangeColNum).setValues(processedData);

//true

if(output){

//予定表シートを　コピーして、現在時刻のシートで複製する

let nowStr = Utilities.formatDate(new Date(),"JST", "yyyy/MM/dd HH:mm:ss");

let copySt = rosterSt.copyTo(ss);

copySt.setName(rosterStName + " " + nowStr );

let copyExpGooCalSt =ss.getSheetByName(exportGoogleCalStName).copyTo(ss);

copyExpGooCalSt.setName(exportGoogleCalStName + " " + nowStr );

}

/**

* カレンダー情報を取得するには、プログラム実行者の他のカレンダーに追加されている必要がある。

function addOtherCal() {

try {

// スプレッドシート指定

const ss = SpreadsheetApp.getActiveSpreadsheet();

//const ss = SpreadsheetApp.openByUrl(cfg.ssUrl);// URLで指定したいときはこちらを利用

// カレンダーを取得したいgoogleアカウント一覧のスプレッドシート

const GoogleAccountlistSt = ss.getSheetByName(cfg.googleAccountlistStName);

//スプレッドシート　から1次元配列を作成する良くないけどセル2,2から行方向へ

let mailaddressListArray = rangesToArray(GoogleAccountlistSt ,cfg.gAccountListStartrow,cfg.gAccountListStartcol);

//スクリプト実行者のカレンダーに他のカレンダーを追加するときの条件

const options = {

hidden: false, // カレンダーは、ユーザインタフェースの中に隠されているかどうか（デフォルト： false）

selected: false // カレンダーを追加したときに、カレンダー上に反映させない（チェックボックスにチェックを入れない）

}

//対象のアカウントのメールアドレスがなくなるまで実行する

for( let item of mailaddressListArray){

if(item){

var errEmail = item;

Logger.log(item);

// 他アカウントのカレンダーを追加

CalendarApp.subscribeToCalendar(item, options);

// 実行を少し待機

Utilities.sleep(0.5 * 1000);

}

}catch(e){

Browser.msgBox("エラー：" + e.message +' errEmail :'+errEmail +' は存在しません。一度確認してください。');

}

/**
 * パラメータ設定
 * 
 * カスタマイズする場合には、ここのパラメータ変更対応できる内容もあると思います。
 * 
 */
var cfg ={
  
  //ssUrl : "https://docs.google.com/spreadsheets/d/************************/edit#gid=000000000000000",//特定のスプレッドシートを指定したい場合
  exportGoogleCalStLast: 'expLast',//googleカレンダー検索結果出力シート
  exportGoogleCalStThis: 'expThis',//googleカレンダー検索結果出力シート
  exportGoogleCalStNext: 'expNext',//googleカレンダー検索結果出力シート
  googleAccountlistStName  : 'list',//googleカレンダー検索対象者一覧シート
  gAccountListStartrow : 2,//listシートのGoogleアカウントの最初の行の開始位置（1の場合ヘッダなし）
  gAccountListStartcol : 1,//listシートのGoogleアカウントの列を指定
  rosterStNameLast:'last',//googleカレンダー情報の出力先　last勤務表シート
  rosterStNameThis:'this',//googleカレンダー情報の出力先　this勤務表シート
  rosterStNameNext:'next',//googleカレンダー情報の出力先　next勤務表シート
  
  rosterClearColNum:31,//勤務表を３１まで削除
  findStr: "email@",//勤務表シートの処理始点セル位置特定要文字列
  dispCal :"1month",//一週間は　"1week"  1ヶ月　"1month"
  searchStr : ['有休','公休','早退','遅刻'],//対象となるカレンダータイトル（完全一致）ここ追加してもOK
  
  //カレンダーeventsの必要なデータを連想配列で取り扱い、配列に引き渡す
  g_cal :{  
    sdate : "sdate",//イベント終了時間（終日の予定の場合00:00
    edate :  "edate" ,//イベント終了時間（終日の予定の場合翌日00:00
    title :   "title", //表題タイトル
    lastUpD :  "lastupdate"   ,
    creator  :  "creator"  ,//作成者
    alldayevent :"alldayevent" ,//終日予定？　true false
    eventid :     "eventid",//一意の予定ID
    days : "days"
  }
}

/**

* パラメータ設定

* カスタマイズする場合には、ここのパラメータ変更対応できる内容もあると思います。

var cfg ={

//ssUrl : "https://docs.google.com/spreadsheets/d/************************/edit#gid=000000000000000",//特定のスプレッドシートを指定したい場合

exportGoogleCalStLast: 'expLast',//googleカレンダー検索結果出力シート

exportGoogleCalStThis: 'expThis',//googleカレンダー検索結果出力シート

exportGoogleCalStNext: 'expNext',//googleカレンダー検索結果出力シート

googleAccountlistStName : 'list',//googleカレンダー検索対象者一覧シート

gAccountListStartrow : 2,//listシートのGoogleアカウントの最初の行の開始位置（1の場合ヘッダなし）

gAccountListStartcol : 1,//listシートのGoogleアカウントの列を指定

rosterStNameLast:'last',//googleカレンダー情報の出力先　last勤務表シート

rosterStNameThis:'this',//googleカレンダー情報の出力先　this勤務表シート

rosterStNameNext:'next',//googleカレンダー情報の出力先　next勤務表シート

rosterClearColNum:31,//勤務表を３１まで削除

findStr: "email@",//勤務表シートの処理始点セル位置特定要文字列

dispCal :"1month",//一週間は　"1week" 1ヶ月　"1month"

searchStr : ['有休','公休','早退','遅刻'],//対象となるカレンダータイトル（完全一致）ここ追加してもOK

//カレンダーeventsの必要なデータを連想配列で取り扱い、配列に引き渡す

g_cal :{

sdate : "sdate",//イベント終了時間（終日の予定の場合00:00

edate : "edate" ,//イベント終了時間（終日の予定の場合翌日00:00

title : "title", //表題タイトル

lastUpD : "lastupdate" ,

creator : "creator" ,//作成者

alldayevent :"alldayevent" ,//終日予定？　true false

eventid : "eventid",//一意の予定ID

days : "days"

}

最後に

email@を基準にしてシートをクリアしているので、この仕様を理解していただければ、左列、上行追加してもらって、名前を入れたりして利用も可能です。

また、自動実行のトリガーを設定しておくと、めちゃくちゃ便利に利用できます。

あと、雑なコードになっていますが、ご了承ください。

はじめに

手順

仕様

コード

最後に

人気記事ベスト5

妊娠中と出産後に必ず食べたいおすすめの食べ物