毎日更新されるGoogleスプレッドシートの値を定期的に別シートにコピーする方法

9330 ワード
jsonp GoogleSpreadSheet GoogleAppsScript gas jsonp テキストリンク

余命三ヶ月で作成したプログラムが別プログラムに差し替わる事が決定して、資産が消失するのでメモ。

背景

従業員のシフト管理をスプレッドシートで管理していて、
Webサイト上に従業員の出勤有無を出力する事となった。

従業員数が多く、二ヶ月先までシフトが組んであり
サイトに出力するのは直近5日のみ。

前回以下の記事でコケた事もあり。
GoogleAppsScript(GAS)でスプレッドシートの値を取得した時にハマったこと
必要なデータを毎日別シートに抽出して、抽出結果をjsonp形式で配信する設計とした。
配信は前回記事にある処理を流用。Webサイトに出力する処理はJavaScriptでゴリゴリ書いた。
本記事はデータを毎日別シートに抽出する処理のみを記載。

処理イメージ

毎日特定の条件でシートからデータを抽出し、別シートにコピーする。

必要最低限の機能

・同じ名前のシートが存在しなければ作成。
　→誰かの手によってコピー先シートが削除されても問題ない様にする。

・現在日付から5日後まで抽出を対象とする。

抽出処理

function weekCp() {
  //コピー先シート存在確認
  var sheet = set_sheet("直近5日");
  //コピー先シートクリア
  sheet.clearContents();
  //コピー元シートのデータを二次元配列で一括取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var original = spreadsheet.getSheetByName('シート1');
  var var1 = original.getDataRange().getValues();

  //現在日付を取得
  var date = new Date();
  var date_D = new Date(date.getFullYear(),date.getMonth(),date.getDate(),0,0,0);

  //5日後の日付を取得
  var date_range = new Date(date.getFullYear(),date.getMonth(),date.getDate(),0,0,0);
  date_range.setDate(date_range.getDate() + 4);

  //フィルタ後の配列を初期化　ヘッダー情報は固定なため、ベタ
  var data = [
    ["日付", "時間", "場所", "状況", "担当者"]
  ];

  for(var i=1; i < var1.length; i++){
    //当日以前のデータは対象外なため最初に落とす
    if(var1[i][0] >= date_D) {
      //それが5日の範囲内か
      if(var1[i][0] <= date_range) {
        data.push(var1[i]);
      }
    }
  }

  //データの書き込み
  //横(行)
  var lastColumn = data[1].length
  //縦(列)
  var lastRow = data.length
  //開始行　開始列　終了行　終了列
  sheet.getRange(1, 1, lastRow, lastColumn).setValues(data)
}


//同じ名前のシートがなければ作成
function set_sheet(name) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(name)
  if(sheet)
    return sheet

  sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  sheet.setName(name);
  return sheet;
}

トリガー

毎日0時に起動する様にする。

★[編集]　→　[現在のプロジェクトのトリガー]を選択

★トリガーを追加

以上で毎日0時以降に勝手に起動してくれる。

おわりに

余命三ヶ月って悲しいですね。