Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@koichiro-h(Koichiro Hirayama)

GAS(GoogleAppsScript)を使ってみる　#003

Last updated at Posted at 2018-04-16

概要

　予定通りSpreadsheet with GASの第三回です。え？　前回で完結だったんじゃなかったかって？　ええ、実行時にズッコケました。しかしこれはもちろん計画の範囲内です。ブログの記事を増やすための策略なのです！(;`･ω･)

　と、いう事で今回はスクリプト失敗の監視アラートと実行結果を掲載します。これで本当に完結です。

GAS(GoogleAppsScript)を使ってみるシリーズ一覧

#001. Calendar.getEvents(startTime, EndTime)の挙動がおかしい報告
#002. リハビリ出社記録表作成支援ツールの総括
いまここ☞#003. トリガーの失敗例とソース修正
#004. セルの書式設定の細かいお話
#005. jQueryとTimePickerをユーザプロンプトとして出力するお話
#006. リファクタリングとクラス設計のお話

エラー発生

　前回#002でセットした毎週月曜日のトリガーが失敗したメールは下記の様に届きました。
error.png

　setHolizontalAlignments(string)が見つかりません。

ほう。

Range.setHolizontalAlignmentsを調べてみると、、、

setHorizontalAlignments(alignments)

Parameters
| Name Type Description
+------------------------------------------
| alignments Object[][] A two-dimensional array of alignments, either 'left', 'center' or 'normal'; a null value resets the alignment.

良く読んでなかった…引数は2次元配列だと…orz

【使い方例】（以下リファレンス本文より抜粋）

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 // The size of the two-dimensional array must match the size of the range.
 var horizontalAlignments = [
   [ "left", "right", "center" ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setHorizontalAlignments(horizontalAlignments);

なるほど、Range配列に応じた2次元配列が必要と…メンドクサイ…

どうせアロー式使えないGASなので下記の様に匿名関数にした。予め必要な配列 8 * 9の表の分だけ準備する。
Array.from( new Array(8), function(){ return new Array(9) } );

しかしArray.fromが見つからないと…orz...
多分参照設定でどうにかするんだろうけど、クライアント毎の設定にならないよね？？テストするかリファレンス探してみるか...

何か段々面倒になってきたのでこれで言いやあ(;´Д`)と代替したのがこれ

  var arr = [
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "center", "center", "center", "center", "center", "center", "center"],
    ["center", "center", "left", "left", "left", "left", "left", "left", "left"]
  ];

いや、カッコ悪いなあぉぃ…

あれでもそうすると、setHolizontalAlignment(string)の方は単一の値を複数Rangeセットできそうな気がする。

予想通り、単数の場合はstring、そして複数セルも一括でセット出来た。これがVBAのRangesオブジェクトとGASのRangeクラスの一番大きな違いだと思う。Rangesであればその配下のプロパティは要素数の違い等を気にする必要はなく一気に変更が出来るのだが、GASだとこれが一旦配列化しなければならず、相対位置でのアクセスになるのが少し煩わしいなと感じた。

そんなこんなでこの様な変更を施しました。取り敢えず前述のようにアホみたいにプリミティブ増やさないで済んだ(-ω-;)

function printFormatOfWeek() {
  var range = sh.getRange( sh.getDataRange().getLastRow() + 2, 1, 8, 9 ); // 出力先のRange
  range.setBorder( true, true, true, true, true, true );

  var horizontalAlignments = [ //所感行の水平文字位置
    [ "center", "center", "left", "left", "left", "left", "left", "left", "left" ]
  ];
  range.offset( 8, 0, 1, 9 ).setHorizontalAlignments( horizontalAlignments ); //所感部分のみカスタム

//range.setHorizontalAlignments( 'center' );
  range.setHorizontalAlignment( "center" );      // 表は所感以外は中寄せ
  
  var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
  youbi.merge();
  youbi.setValue( '曜日' );
  
  youbi.offset( 0, 2 ).setValue( '日' );
  youbi.offset( 0, 3 ).setValue( '月' );
  youbi.offset( 0, 4 ).setValue( '火' );
  youbi.offset( 0, 5 ).setValue( '水' );
  youbi.offset( 0, 6 ).setValue( '木' );
  youbi.offset( 0, 7 ).setValue( '金' );
  youbi.offset( 0, 8 ).setValue( '土' );

  var tsukihi = youbi.offset( 1, 0 ); // '月日'
  tsukihi.merge();
  tsukihi.setValue( '月日' );
  
  var yotei = tsukihi.offset( 1, 0, 2, 1 );
  yotei.merge();
  yotei.setValue( '予定' );
  
  yotei.offset( 0, 1, 1, 1 ).setValue( '出社時間' );
  yotei.offset( 1, 1, 1, 1 ).setValue( '退社時間' );

  var zisseki = yotei.offset( 2, 0, 2, 1 );
  zisseki.merge();
  zisseki.setValue( '実績' );

  zisseki.offset( 0, 1, 1, 1 ).setValue( '出社時間' );
  zisseki.offset( 1, 1, 1 ,1 ).setValue( '退社時間' );

  var taichomen = zisseki.offset( 2, 0, 1, 2 );
  Logger.log( taichomen.getA1Notation() );
  taichomen.merge();
  taichomen.setValue( '体調面' );
  
  var shokan = taichomen.offset( 1, 0, 1, 2 );
  Logger.log( shokan.getA1Notation() );
  shokan.merge();
  shokan.setValue( '所感' );

  var horizontalAlignments = [ //所感行の水平文字位置
    [ "center", "center", "left", "left", "left", "left", "left", "left", "left" ]
  ];
  shokan.offset( 0, 0, 1, 9 ).setHorizontalAlignments( horizontalAlignments ); // 所感行は複合
  
  return range;
}

エラーではないがついでにうっかりしていた部分を直す。
Date型による返却を忘れていて、ただの日にち数値を返してしまっていた…

/**************
/* printDate is printing date in range into 2nd rows. (2nd rows into) first cell is column by '月日'.
/* @param range is Range Instance for printing table Area.
/* @param date is Date Instance for be expected printing.
/***************/
function printDate( range, date ) {
  var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
  var tsukihi = youbi.offset( 1, 1 ); // '月日'
  var date_array = [[,,,,,]];
  
  for ( i = 0; i < 7; i++ ) {
//    tsukihi.offset(0, 0, 1, 7).setValues( date.getDate() + i );
    date_array[0][i] = new Date( date.getYear(), date.getMonth(), date.getDate() + i );
  }
  
//  Logger.log( date_array );
  tsukihi.offset(0, 1, 1, 7).setValues( date_array );
  tsukihi.offset(0, 1, 1, 7).setNumberFormat("m/d");
}

小ボケはまだまだ続く。間違い探しどうぞっ(Logger以外のコメントアウトが修正点)

/**************
/* printFormatHoliday is printing layouts of holidays.
/* @param range is Range Instance for printing table Area.
/* @param date is Date Instance for be expected printing.
/***************/
function printFormatHoliday( range, date ) {
  var calHoliday = CalendarApp.getCalendarById( 'ja.japanese#holiday@group.v.calendar.google.com' );
  var endDate = new Date( date.getTime() + 6 * 86400000 );

  Logger.log( 'start = ' + date.getTime() + '\tend = ' + endDate.getTime() );
  var ranges = range.getValues();
  
  // 表内検索
  for ( i = 0; i < ranges.length; i++ ) {

    // 土日のセルカラーのセット
    if ( ranges[i][0] === '曜日' ) {
      var cSunday = ranges[i].indexOf( '日' );
      var cSaturday = ranges[i].indexOf( '土' );
//     range.offset( 0, cSunday, range.getNumRows(), 1 ).setBackGrounds( '#f4cccc' );
//     range.offset( 0, cSaturday, range.getNumRows(), 1 ).setBackGrounds( '#9fc5e8' );	
      range.offset( 0, cSunday, range.getNumRows(), 1 ).setBackground( '#f4cccc' );
      range.offset( 0, cSaturday, range.getNumRows(), 1 ).setBackground( '#d0e0e3' );
     
    }
    
    // 表内祝日のセルカラーのセット
    if ( ranges[i][0] === '月日' ) {
      for ( j = 1; j < ranges[i].length; j++) {
        if ( calHoliday.getEventsForDay( new Date( ranges[i][j] ) ).length > 0 ) {
//          Logger.log( ranges[i][j] + ' is Holiday!' );
//          Logger.log( 'RowOffset:' + i + '\tColumnOffset:' + j );
//          Logger.log( range.offset(0, j, range.getNumRows(), 1).getA1Notation() );
//          range.offset(0, j, range.getNumRows(), 1).setBackGrounds( '#f4cccc' );
          range.offset(0, j, range.getNumRows(), 1).setBackground( '#f4cccc' );
        }
      }
      // 月日の行を青色に戻す
//      range.offset(i, 0, 1, range.getNumColumns() ).setBackGrounds( '#9fc5e8' );
      range.offset(i, 0, 1, range.getNumColumns() ).setBackground( '#9fc5e8' );
//      Logger.log( range.offset(i, 0, 1, range.getNumColumns() ).getA1Notation() );
    }
  }
}

// Main Function
function main() {
  Logger.clear();
  
  var date = getLastDate();           // 表で使用されている最後の日付を取得
  date.setDate( date.getDate() );  // 最後の日の翌日を取得
  var range = printFormatOfWeek();    // 新しい表の範囲をセット
  printDate( range, date );           // 最終日の翌週を新たな表にセット
  printFormatHoliday( range, date );  // 土日祝祭日のレイアウトをセット  
}

結果

　↓実行前
before.png

　↓実行後
after.png

所感

　良いか悪いか別としてExcelVBAには無かったRange(複数範囲セル)の個別値を配列としてセット出来るところは、使い馴れると便利かなと思いました。ただ、あくまでもセル個別に動的な値を扱う場合は、Range(単一セル)と単数セット系のコマンドをループ処理させるか、ケースによってはメタプログラミングが必要になってくるかな...

完成ソース紹介

/*************
/* @Title   : MakeCalendarFormat Project
/* @author  : k_hirayama@ap-com.co.jp
/* @update  : 2018/04/11
/* @version : 1.0
/************/

var sh = SpreadsheetApp.getActiveSheet();

// Main Function
function main() {
  Logger.clear();
  
  var date = getLastDate();           // 表で使用されている最後の日付を取得
  date.setDate( date.getDate() );  // 最後の日の翌日を取得
  var range = printFormatOfWeek();    // 新しい表の範囲をセット
  printDate( range, date );           // 最終日の翌週を新たな表にセット
  printFormatHoliday( range, date );  // 土日祝祭日のレイアウトをセット  
}

/****
/* getLastDate is return date string that displayed date format as like 'M/D', 'M/DD', 'MM/D', 'MM/DD' with SpreadsheetApp.
/* @return Date object for used in SpreadSheet.
/********/

function getLastDate() {
  var year = new Date( Date.now() ).getYear();
  var range = sh.getDataRange();
  var regex = new RegExp('^[0-9]{1,2}/[0-9]{1,2}$');
  
  // gets date values into '月日' rows.
  var filterd = range.getDisplayValues().filter( function(cells) {
    for( var i = 0;  i < cells.length; i++ ) {
        if ( regex.test( cells[i] ) ) {
          return true;
        }
    }
  } );
  
  return new Date( year + '/' + filterd.pop().pop() );

}

/****
/* printFormatOfWeek is output default template table view on the SpreadSheet.
/* @return Range Object for new Table Area.
/***********/

function printFormatOfWeek() {
  var range = sh.getRange( sh.getDataRange().getLastRow() + 2, 1, 8, 9 ); // 出力先のRange
  range.setBorder( true, true, true, true, true, true );
  var horizontalAlignments = [
    [ "center", "center", "left", "left", "left", "left", "left", "left", "left" ]
  ];
  range.setHorizontalAlignment( "center" );
  range.offset( 8, 0, 1, 9 ).setHorizontalAlignments( horizontalAlignments );
  
  var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
  youbi.merge();
  youbi.setValue( '曜日' );
  
  youbi.offset( 0, 2 ).setValue( '日' );
  youbi.offset( 0, 3 ).setValue( '月' );
  youbi.offset( 0, 4 ).setValue( '火' );
  youbi.offset( 0, 5 ).setValue( '水' );
  youbi.offset( 0, 6 ).setValue( '木' );
  youbi.offset( 0, 7 ).setValue( '金' );
  youbi.offset( 0, 8 ).setValue( '土' );

  var tsukihi = youbi.offset( 1, 0 ); // '月日'
  tsukihi.merge();
  tsukihi.setValue( '月日' );
  
  var yotei = tsukihi.offset( 1, 0, 2, 1 );
  yotei.merge();
  yotei.setValue( '予定' );
  
  yotei.offset( 0, 1, 1, 1 ).setValue( '出社時間' );
  yotei.offset( 1, 1, 1, 1 ).setValue( '退社時間' );

  var zisseki = yotei.offset( 2, 0, 2, 1 );
  zisseki.merge();
  zisseki.setValue( '実績' );

  zisseki.offset( 0, 1, 1, 1 ).setValue( '出社時間' );
  zisseki.offset( 1, 1, 1 ,1 ).setValue( '退社時間' );

  var taichomen = zisseki.offset( 2, 0, 1, 2 );
  Logger.log( taichomen.getA1Notation() );
  taichomen.merge();
  taichomen.setValue( '体調面' );
  
  var shokan = taichomen.offset( 1, 0, 1, 2 );
  Logger.log( shokan.getA1Notation() );
  shokan.merge();
  shokan.setValue( '所感' );
  
  return range;
}

/**************
/* printDate is printing date in range into 2nd rows. (2nd rows into) first cell is column by '月日'.
/* @param range is Range Instance for printing table Area.
/* @param date is Date Instance for be expected printing.
/***************/
function printDate( range, date ) {
  var youbi = sh.getRange( range.getRow(), range.getColumn(), 1, 2 ); // '曜日'
  var tsukihi = youbi.offset( 1, 1 ); // '月日'
  var date_array = [[,,,,,]];
  
  for ( i = 0; i < 7; i++ ) {
    date_array[0][i] = new Date( date.getYear(), date.getMonth(), date.getDate() + i );
  }
  
//  Logger.log( date_array );
  tsukihi.offset(0, 1, 1, 7).setValues( date_array );
  tsukihi.offset(0, 1, 1, 7).setNumberFormat("m/d");
}

/**************
/* printFormatHoliday is printing layouts of holidays.
/* @param range is Range Instance for printing table Area.
/* @param date is Date Instance for be expected printing.
/***************/
function printFormatHoliday( range, date ) {
  var calHoliday = CalendarApp.getCalendarById( 'ja.japanese#holiday@group.v.calendar.google.com' );
  var endDate = new Date( date.getTime() + 6 * 86400000 );

  Logger.log( 'start = ' + date.getTime() + '\tend = ' + endDate.getTime() );
  var ranges = range.getValues();
  
  // 表内検索
  for ( i = 0; i < ranges.length; i++ ) {

    // 土日のセルカラーのセット
    if ( ranges[i][0] === '曜日' ) {
      var cSunday = ranges[i].indexOf( '日' );
      var cSaturday = ranges[i].indexOf( '土' );
      range.offset( 0, cSunday, range.getNumRows(), 1 ).setBackground( '#f4cccc' );
      range.offset( 0, cSaturday, range.getNumRows(), 1 ).setBackground( '#d0e0e3' );
    }
    
    // 表内祝日のセルカラーのセット
    if ( ranges[i][0] === '月日' ) {
      for ( j = 1; j < ranges[i].length; j++) {
        if ( calHoliday.getEventsForDay( new Date( ranges[i][j] ) ).length > 0 ) {
          range.offset(0, j, range.getNumRows(), 1).setBackground( '#f4cccc' );
        }
      }
      
      // 月日の行を青色に戻す
      range.offset(i, 0, 1, range.getNumColumns() ).setBackground( '#9fc5e8' );
    }
  }
}


//////////////// below are TestFunctions

function testPrintFormatHoliday() {
  Logger.clear();
  var range = sh.getRange( "A57:I64" );
  var date = new Date( '2018-03-18' );
  printFormatHoliday( range, date );
}

function testGetLastDate() {
  Logger.clear();
  var date = getLastDate();
  Logger.log( date );
}

function testPrintDate() {
  Logger.clear();
  var range = sh.getDataRange();
  var date = getLastDate();
  printDate( range, date );
}
2
0
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?