GASでカレンダーから予定詳細を抽出してスプレッドシートに分割して出力

-
色々なところにメモとか残すのがしんどいのでGoogleカレンダーの予定詳細に以下の様な感じで個人的に問合せ履歴を残したりしています。  

#問合せ内容
#対応内容詳細
#聞いてきた人  

そして時間もとっていたりするのでそれをスプレッドシートに1日1回出力すれば簡単な問合せ一覧表が作れるなぁと思ってGASを用意しました。 ほとんどこれの応用版みたいな感じですが
カレンダーに登録すれば時間も勝手に計算してくれます。(複数日にまたがった対応とかは無理ですが)

スプレッドシートのセルの中に値を埋め込んでそこから値を引いてきています。キーワードは適当に変更すれば別のものにも応用利くかなぁとか思います。タイムゾーンに注意がいります。そこではまりました。

◆ソースコード

//設定値取得
const SHEET_URL = SpreadsheetApp.getActiveSpreadsheet().getUrl();
const ss = SpreadsheetApp.openByUrl(SHEET_URL);
const SETTING_SHEET = ss.getSheetByName('設定')
const CALENDAR_ID = SETTING_SHEET.getRange('B2').getValue();
const SAVE_FOLDER_ID = SETTING_SHEET.getRange('B3').getValue();
const SAVE_FILE_NAME = SETTING_SHEET.getRange('B4').getValue();
const SAVE_SHEET_NAME = SETTING_SHEET.getRange('B5').getValue();
const KEY_WORD = SETTING_SHEET.getRange('D2').getValue();
//セルの中でAlt＋Enterで改行して値を持たせることで項目数は可変
const PADDING_WORD = SETTING_SHEET.getRange('E2').getValue();
const SHEET_TITLE = SETTING_SHEET.getRange('F2').getValue();
const COLUMUN_WIDTH = SETTING_SHEET.getRange('G2').getValue();


function writeSheet(values, labels, sheetUrl, sheetname, width) {
  /* スプレッドシートに書き出し */
  try {
    if (values.length > 0) {
      // シートを追加する
      let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
      let sheet = set_sheet(sheetUrl,sheetname);
      // 1行目だけヘッダ追加
      let headerCheck = sheet.getLastRow();
      if (headerCheck == 0) {
        sheet.appendRow(labels);
      }
      let lastRow = sheet.getLastRow();
      // 追加したシートに書き出し
      values.forEach(function (value) {
        sheet.appendRow(value);
      });
      const n = labels.length;
      // カラム整形
      let j = 0;
      for (let i = 1; i <= n; i++) {
        //sheet.autoResizeColumns(i,1);
        sheet.setColumnWidth(i, width[j]);
        j =++j;
      }
      return true;
    }
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function set_sheet(sheetUrl,name) {
  /* 同じ名前のシートがなければ作成 */
  try {
    let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
    let sheet = spreadSheet.getSheetByName(name)
    if (sheet) return sheet
    sheet = spreadSheet.insertSheet();
    sheet.setName(name);
    return sheet;
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function getSpreadsheetURL(folderId, fileName) {
  /* スプレッドシートが存在していなければ作成、既に存在していれば開く */
  try {
    let target = findSpreadsheetByName(folderId,fileName)
    if (target==false){
      //新規作成・フォルダ指定がない場合はスクリプトと同じフォルダにファイル作成
      const newSS = SpreadsheetApp.create(fileName);
      const file = DriveApp.getFileById(newSS.getId()); 
      DriveApp.getFolderById(folderId).addFile(file)
      DriveApp.getRootFolder().removeFile(file)
      return file.getUrl();
    }else{
      //既存シートオープン
      return target;
    }
  } catch (e) {
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    return false;
  }
}

function findSpreadsheetByName(id, name) {
  /* ファイル名からスプレッドシート検索 */
  let folder = DriveApp.getFolderById(id);
  let files = folder.getFilesByName(name);
  while (files.hasNext()) {
    let file = files.next();
    if ('application/vnd.google-apps.spreadsheet' == file.getMimeType()) {
      const ss = SpreadsheetApp.openById(file.getId());
      return ss.getUrl()
    }
  }
  return false;
}

function getCalendarEvents(startTime, endTime, calenderId, keyword, detailpading) {
  /* カレンダーからイベントを取得 */
  const calendar = CalendarApp.getCalendarById(calenderId);
  try {
      const events = calendar.getEvents(startTime, endTime);
      const values = [];
      for (const event of events) {
          const wt = getWorkerTime(event.getStartTime(), event.getEndTime());
          const workTime = getJIFUNFormat(wt,'jp');
          const title = event.getTitle();
          let detail = event.getDescription();
          if (detail.length === 0){
             detail = detailpading; 
          }
          const wd = splitDayandTime(event.getStartTime())[0];
          const st = splitDayandTime(event.getStartTime())[1];
          const et = splitDayandTime(event.getEndTime())[1];
          const workmin = (event.getEndTime() - event.getStartTime())/(1000*60);
          if (title.indexOf(keyword) === 0){
              let record = {
                  createday: wd,
                  title: title.replace(keyword,""),
                  detail: detail,
                  starttime: st,
                  endtime: et,
                  workinghours: workTime,
                  workingminutes: workmin
              };
              values.push(record);
          }
      }
      return values;
  } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
  }
}

function formatConversion(values) {
  /* スプレッドシートに書き込み可能なフォーマットに変換 */
  try {
      //2番目の内容の項目を列単位に分解する
      const inquirys = JSON.parse(JSON.stringify(values));
      const results =[];
      const regexp = /^#.+：/g
      const regexp2 = /<("[^"]*"|'[^']*'|[^'">])*>/g
      let details =[];
      for(const inq of inquirys){
          if ( inq.detail.indexOf('
') != -1) {
              details = inq.detail.split('
');
         }else{
              details = inq.detail.split('\n');
         }
         details = details.map(function(x){return x.replace(regexp,'').replace(regexp2,'');});
         const wk = [
              inq.createday,
              inq.title,
              details,
              inq.starttime,
              inq.endtime,
              inq.workinghours,
              inq.workingminutes
          ];
          results.push(wk.flat(1));
        }
      return results;
  } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
  }
}

function getWorkerTime(startTime, endTime) {
  /* 開始時刻と終了時刻から稼働時間算出 */
  try {
      const st = new Date(startTime);
      const et = new Date(endTime);
      const wt = (et - st) / (1000 * 60 * 60);
      return wt;
  } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
  }
}

function getJIFUNFormat(target,returnValue) {
  /* 小数点以下をN時間N分フォーマットに修正して返す */
  try {
      const h = String(target).split(".")[0];
      const b = String(target).split(".")[1];
      const m = 60 * target //小数点表記の分を通常に変換
      switch(returnValue){
        case 'jp':
               if (Number(h) == 0 && Number(b) < 100) {
                 return String(m) + "分";
               } else if (Number(h) >= 1 && m < 60) {
                 return h + "時間" + String(m) + "分";
               } else if (Number(h) >= 1 && (m % 60) > 0) {
                 return h + "時間" + String(m % 60) + "分";
               } else {
                 return h + "時間";
               }
               break;
        default:
               if (Number(h) == 0 && Number(b) < 100) {
                 return "0:" + String(toDoubleDigits(m));
               } else if (Number(h) >= 1 && m < 60) {
                 return h + ":" + String(toDoubleDigits(m));
               } else if (Number(h) >= 1 && (m % 60) >= 0) {
                 return h + ":" + String(toDoubleDigits(m % 60));
               } else {
                 return h + ":00";
               }
               break;
        }         
      
  } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
  }
}

function splitDayandTime(target) {
  /* 日付と時刻に分割して返す */
  try {
      const t = new Date(target);
      const retDay = t.getFullYear() + '-' + toDoubleDigits(t.getMonth() + 1) + '-' + toDoubleDigits(t.getDate());
      const retTime = toDoubleDigits(t.getHours()) + ':' + toDoubleDigits(t.getMinutes());
      r = [retDay, retTime]
      return r;
  } catch (e) {
      Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
  }
}

function toDoubleDigits(num) {
  /* 月と日付パディング */
  num += "";
  if (num.length === 1) {
      num = "0" + num;
  }
  return num;
}

function main() {
  try {
    //探索対象フォルダIDチェック
    if(CALENDAR_ID === ''){
      Browser.msgBox('メールアドレスを設定してください。');
      SpreadsheetApp.getActiveSheet().getRange("B2").activate();
      return;
    }
    const id = CALENDAR_ID;
    //フォルダ名(フォルダID指定がなければスプレッドシートの存在するフォルダのIDを取得)
    let folderId = ''
    if(SAVE_FOLDER_ID === ''){
      const ssId = ss.getId();
      const parentFolder = DriveApp.getFileById(ssId).getParents();
      folderId = parentFolder.next().getId();
    }else{
      folderId = SAVE_FOLDER_ID;
    }
    //ファイル名(指定がなければ固定名称)
    let fileName = '';
    if(SAVE_FILE_NAME === ''){
      fileName = "問合せ履歴";
    }else{
      fileName = SAVE_FILE_NAME;
    }
    let sheetUrl = getSpreadsheetURL(folderId, fileName) 
    //シート名(指定がなければファイル名を設定)
    let sheetName = ''
    if(SAVE_SHEET_NAME === ''){
      sheetName = fileName;
    }else{
      sheetName = SAVE_SHEET_NAME
    }
    //パラメータ設定
    const title = SHEET_TITLE.split('\n');
    const width = COLUMUN_WIDTH.split('\n');
    const keyword = KEY_WORD;
    const paddingValue = PADDING_WORD;
    //開始日および終了日指定(通常は日次)
    /*
    const StartTime = new Date('2020/10/1')
    const EndTime = new Date('2021/3/2')
    */
    const n = -1;
    const d = new Date();
    const EndTime = new Date(d.getFullYear(), d.getMonth(), d.getDate(), 0, 0, 0);
    const yd = d.setDate(d.getDate() + n, d.setHours(0), d.setMinutes(0), d.setSeconds(0));
    const StartTime = new Date(yd);
    //カレンダーデータを加工したものを取得
    const inquiryCal = getCalendarEvents(StartTime, EndTime, CALENDAR_ID, keyword, paddingValue);
    //フォーマット加工
    let data = formatConversion(inquiryCal);
    //スプレッドシート出力
    writeSheet(data, title, sheetUrl, sheetName, width)
  } catch (e) {
    let msg = e.message;
    Logger.log("message:" + e.message + "\nlineNumber:" + e.lineNumber + "\nstack:" + e.stack);
    console.log('エラーが発生しました\\n'+ msg);
  }
}


◆参考サイト

コメント

コメントを投稿

このブログの人気の投稿

GASでGoogleDriveのサブフォルダとファイル一覧を出力する

-
 タイトルのまんまです。 以前、EXCELで似たようなこと をしていたのでGASではどうするのかなぁと思ってやってみました。まぁほとんど丸パクリです。とりあえず、また使うかもしれないので自分用にメモです。しかしGASでGoogleDriveのファイル一覧嘗め回そうとすると3分かかってしまう。数が多いと6分制限に引っかかるのでこれでは使えない。せいぜい100ファイルとか数が少ないところで使う分にはいいけどダイナミックな場合は何回かに分割してやらないと少し厳しいかもしれない。 ◆GAS //設定値取得 const SHEET_URL = SpreadsheetApp.getActiveSpreadsheet().getUrl(); const ss = SpreadsheetApp.openByUrl(SHEET_URL); const SETTING_SHEET = ss.getSheetByName('設定') const TARGET_FOLDER_ID = SETTING_SHEET.getRange('B2').getValue(); const SAVE_FOLDER_ID = SETTING_SHEET.getRange('B3').getValue(); const SAVE_FILE_NAME = SETTING_SHEET.getRange('B4').getValue(); const SAVE_SHEET_NAME = SETTING_SHEET.getRange('B5').getValue(); function writeSheet(values, labels, sheetUrl, sheetname, width) { /* スプレッドシートに書き出し */ try { if (values.length > 0) { // シートを追加する let spreadSheet = SpreadsheetApp.openByUrl(sheetUrl); let sheet = set_sheet(sheetUrl,sheetname); // 1行目だけヘッダ追加 let he...
続きを読む

マクロ経済学(IS-LM分析)

-
イメージ
危うく3日坊主になりそうでした。とりあえず今日は 「IS-LM分析」 です。 テキストでは 簡便4象限法 とかいうのの説明が多いです。なのでさっぱり わけわからん状態ですが、それなりに頑張ってみます。 1: IS曲線 の導出 財市場の均衡を表す。Iは投資を表し、Sは貯蓄を表す。 ・IS曲線導出の材料 I→利子率の関数、S→所得の関数で「I=S」で財市場を表す。 ・ 投資関数 利子率の減少関数、I (投資関数)=I(r)　※rは利子率 ・政府活動の導入 裁量的な拡張的な財政政策はIS曲線をシフトさせる。 IS曲線の右シフト=政府支出の増大や減税等の有効需要を増加させる要因による 2: LM曲線 の導出 利子率の低下が国民所得の増大につながる因果関係を明らかにする目的 ・LM曲線導出の材料 貨幣需要(MD)=L、L=取引的動機+予備的動機(L1)+投機的動機(L2) MD=L1+L2 貨幣供給(Ms)=名目貨幣供給/物価(P) ・LM曲線のシフト 金融緩和策・物価下落：実質貨幣供給(MS)が増大→右(下)へシフト 金融引締策・物価上昇：実質貨幣供給(MS)が減少→左(上)へシフト 独立投資拡大→IS曲線が右にシフト 利子率がr1→r2に下落する。 IS曲線、LM曲線の均衡点がE→Fに移動する。 国民所得がY1→Yfとなって増大する。 3:特殊なLM曲線 ・ 流動性の罠 のLM曲線 金融政策は無効→LM曲線をシフトさせても均衡点Eは変わらない IS曲線を右にシフトさせる拡張的な財政政策を実施すると均衡点が E→E´となり国民所得がY1→Y2に増加するので財政政策は有効となる。　　　 ・ 投資の利子率に対する弾力性ゼロ 金融政策は無効→LM曲線を右にシフトさせても国民所得が増えない。 流動性の罠と同じくIS曲線を右にシフトさせる拡張的な財政政策を実施すると 均衡点がE→E´となり国民所得がY1→Y2に増加するので財政政策は有効となる。　　　　　　 4: クラウディング・アウト 政府が市場に介入して公共投資を行うと民間の投資が締出しを受ける ・古典派による財政政策の無効論 政府支出を拡大すると利子率が上昇してしまい。投資が減少する。 政府の支出がクラウディングアウトを引き起こして支...
続きを読む

IEのセキュリティ設定をいじくるバッチ

-
まぁかなり頻出ですがIEでアプリ用に設定しないといけなくて クリックOnlyで処理したかったので色々と調べてみて作ってみた。 ローカルイントラネットの設定いじくるのとActiveX関連をいじくるのと 互換表示設定をいじくっています。 というよりそういう設定しないと動かないアプリの組み方辞めてください。 頼むから・・・。 挙動が微妙に他と違うのでIEとかEdgeには苦しめられます。 @echo off set REGROOT="HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap" reg add %REGROOT% /f /v "AutoDetect" /t REG_DWORD /d "0" reg add %REGROOT% /f /v "IntranetName" /t REG_DWORD /d "1" reg add %REGROOT% /f /v "ProxyBypass" /t REG_DWORD /d "1" reg add %REGROOT% /f /v "UNCAsIntranet" /t REG_DWORD /d "1" rem IEのローカルイントラネット設定(*ドメイン指定) set REGROOT="HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Ranges\Range1" set REGROOT="HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\対象ドメイン" reg add %REGROOT% /f /v "*" /t REG_DWORD /d "1" rem スクリプトを実行しても安全だとマークされていない activex コントロールの初期化とスクリ...
続きを読む