mtdtks
10/3/2016 - 3:57 AM

Utility for GoogleSpreadSheet

Utility for GoogleSpreadSheet




/*
var util{ name : function(){}}に集約したい。。。
*/
var util = {
  /*
   * シート名で指定して、カラム番目の最後の行の取得
   * @usecase getLastRowNumbySheetName(sheetName, column)
   */
  getLastRowNumbySheetName : function(sheetName, column){
      "use strict";
      var obj = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = obj.getSheetByName(sheetName);
      var last_row = sheet.getLastRow();
      //var last_row = sheet.getMaxRows();  getValue()を多様し過ぎるとエラー
      Logger.log("last_row : " + last_row);
     for(var i = last_row; i >= 1; i--){
          var t = sheet.getRange(i, column); 
        if(t.getValue() != ''){//column列目の最後を検索
              Logger.log("lastrow : " + i);
           break;
        }
      }
      Logger.log("SheetName : " + sheetName);
      Logger.log("column : " + column);
      return i;
  },
  //移行中、仮実装
  /*
   * シート名とカラム番目を指定して最後の行にカーソルやるだけ?
   */
  selectLastCell_ColumnA : function(sheetName, column){

   var obj = SpreadsheetApp.getActiveSpreadsheet();
   var sh = obj.getSheetByName(sheetName);
   var last_row = sh.getLastRow();

   for(var i = last_row; i >= 1; i--){
    var rng = sh.getRange(i, column);
    if(rng.getValue() != ''){
     rng.activate();
        Logger.log("i=" + i);
     break;
    }
   }
  },
 /**
  * @usecase getSheets(int)
  * @ int番目のシートを取得する
  */
  getSheetbyNum : function(sheetNum){//シートを番号で取得
  
   var bk = SpreadsheetApp.getActiveSpreadsheet();
   var arr_sh = bk.getSheets();
    var i =  arr_sh[sheetNum];
    return i;
  },
  
  /**
   * 名前でシートを取得するサンプル
   * @usecase getSheetbyName(void)
   * @return {object(sheet)}
   */
  getSheetbyName : function(SheetName){ //getSheetbyName(SheetName)で使うと(bが小文字)直接シートオブジェクト取得
  var bk = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = bk.getSheetByName(SheetName);
  return sheet;
  }
};

/*
 * シート名とカラム番目を指定して最後の行にカーソルやるだけ?
 */
function sample_selectLastCell_ColumnA(){

 var bk = SpreadsheetApp.getActiveSpreadsheet();
 var sh = bk.getActiveSheet();
 var last_row = sh.getLastRow();

 for(var i = last_row; i >= 1; i--){
  var rng = sh.getRange(i, 3);
  if(rng.getValue() != ''){
   rng.activate();
      Logger.log("i=" + i);
   break;
  }
 }
}

/*
function getLastRowNumber_A(sheetNum){ //人別合計に使用
//最終行取得
 var sh = getSheets(sheetNum);
  var last_row = sh.getLastRow();
 for(var i = last_row; i >= 1; i--){
  if(sh.getRange(i, 1).getValue() != ''){//1列目の最後を検索
   //Browser.msgBox("lastrowA" + i);
   break;
  }
 }
  return i;
}
*/

/**
@usecase getSheets(int)
@ int番目のシートを取得する
*/
function getSheets(sheetNum){//シートを番号で取得

 var bk = SpreadsheetApp.getActiveSpreadsheet();
 var arr_sh = bk.getSheets();
  var i =  arr_sh[sheetNum];
 //Browser.msgBox(arr_sh[sheetNum].getRange("A1").getValue());
  return i;
}

/**
名前でシートを取得するサンプル
 * @usecase getSheetbyName(void)
 * @return {object(sheet)}
 *
 *  var ss = SpreadsheetApp.getActiveSpreadsheet();
 *   var sheet = ss.getSheetByName('test');
 */
function getSheetbyName(SheetName){ //getSheetbyName(SheetName)で使うと(bが小文字)直接シートオブジェクト取得
  var bk = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = bk.getSheetByName(SheetName);
  return sheet;
}



/**
 * @usecase getLastRowNumber_lastMonday()
 * @ 最後の月曜日の行番号を返す
 * @return {string}
 */
function getLastRowNumber_lastMonday(){
//最後の月曜日を取得する
  var sh = getSheets(0);
  var last_row = sh.getLastRow();
  for(var i = last_row; i >= 1; i--){
  if(sh.getRange(i, 3).getValue() == '月'){ //下から検索して’月’を探す
   //Browser.msgBox("lastMondy" + i);
   break;
  }
 }
  return i;
}

/**
 * @usecase getLastRowNumber_lastDay()
 * @ 最後の月初めの行番号を返す
 * @return {number}
 */
function getLastRowNumber_lastDay1(){
//最後の月初めを取得
  var sh = getSheets(0);
  var last_row = sh.getLastRow();
  for(var i = last_row; i >= 1; i--){
  if(sh.getRange(i, 2).getValue() == '1'){ //下から検索して’1’を探す
   //Browser.msgBox(i);
   break;
  }
 }
  return i;
}
/**
 * @desc 今日の日付YYYYMMDDYobiを返す
 * @usecase getDate()
 * @return {string}
 */
function getDate(){ //日付取得
  var todayGet = new Date();
  var year = todayGet.getFullYear();
  var month = todayGet.getMonth() + 1;
  var week = todayGet.getDay();
  var day = todayGet.getDate();
  
  var yobi = new Array("日","月","火","水","木","金","土");
  //Browser.msgBox(year+"年"+month+"月"+day+"日 "+yobi[week]+"曜日");
  var now = year+"年"+month+"月"+day+"日 "+yobi[week]+"曜日";
  //Browser.msgBox(now); //test
  return now;
}


/**
 * @desc sheetNum番目のシートの最終行ののcolumn列目の最終行の数値を返す
 * @usecase getLastRowVal(int,int)
 * @return {number}
 */
function getLastRowVal(sheetNum,column){ //人別合計に使用
//最終行取得
  //sheetNum = 0;  //test
  //column = 25;  //test
 var sh = getSheets(sheetNum);
  var last_row = sh.getLastRow();
  var num;
 for(var i = last_row; i >= 1; i--){
  if(sh.getRange(i, column).getValue() != ''){//1列目の最後を検索
    //Browser.msgBox("lastrowVal :" + i);
    //Browser.msgBox(sh.getRange(i, column).getValue());
   num = sh.getRange(i, column).getValue();
      break;
  }
 }
  return num;
}

/**
 * @desc sheetNum番目のシートの最終行ののcolumn列目の最終行の番号か値を返す
 * @usecase getLast(int,int).RowNum
 * @usecase getLast(int,int).RowVal
 * @return {number}
 */
function getLast(sheetNum,column){
  //最終行取得
  //sheetNum = 0;  //test
  //column = 1;  //test
 var sh = getSheets(sheetNum);
  var last_row = sh.getLastRow();
  var num;
  for(i = last_row; i >= 1; i--){
  if(sh.getRange(i, column).getValue() != ''){//1列目の最後を検索
    //Browser.msgBox("lastrowVal :" + i);
    //Browser.msgBox(sh.getRange(i, column).getValue());
   num = sh.getRange(i, column).getValue();
      break;
  }
 }
  //Browser.msgBox(i);
  return {RowNum: i, RowVal:num};
}


/*test
function test(){
  Browser.msgBox(getLast(0, 1).RowVal);
}
*/


/**
@usecase getSheets(int)
@ int番目のシートの1列目の最終行を返す
*/
function getLastRowNumber_A(sheetNum){ //人別合計に使用
//最終行取得
 var sh = getSheets(sheetNum);
  var last_row = sh.getLastRow();
 for(var i = last_row; i >= 1; i--){
  if(sh.getRange(i, 1).getValue() != ''){//1列目の最後を検索
   //Browser.msgBox("lastrowA" + i);
   break;
  }
 }
  return i;
}


/**
@usecase getSheets(int)
@ int番目のシートの2列目の最終行を返す
*/
function getLastRowNumber_B(sheetNum){
//最終行取得
 var sh = getSheets(sheetNum);
  var last_row = sh.getLastRow();
 for(var i = last_row; i >= 1; i--){
  if(sh.getRange(i, 2).getValue() != ''){//2列目の最後を検索
   //Browser.msgBox("lastrowB" + i);
   break;
  }
 }
  return i;
}


function searchTest(){ //拾いもの@未使用
  var statusColumnIndex = 1; //ステータスを保持する列のインデックス
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var finishedRowIndexes = []; //ステータスが完了済みの行数を保持する

  for (var i = 0; i < data.length; i++) {
    if (data[i][statusColumnIndex] === '201508') {
      finishedRowIndexes.push(i + 1);
    }
  }

  for (var i = 0; i < finishedRowIndexes.length; i++) {
    // 完了済みの行に色を塗る
    sheet.getRange(finishedRowIndexes[i], 1, 1, sheet.getLastColumn()).setBackgroundColor('#eeeeee');
  }
}


/**
csvファイルをSPLITTERで配列に代入する
CSVToArray(OBJECT,SPLITTER)
@type {object,string}
@return {object[行][列]}
@[行番号][列番号]の2次元配列で帰ってくる
*/
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.
function CSVToArray( strData, strDelimiter ){
    // Check to see if the delimiter is defined. If not,
    // then default to comma.
    strDelimiter = (strDelimiter || ",");
    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp(
        (
            // Delimiters.
            "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
            // Standard fields.
            "([^\"\\" + strDelimiter + "\\r\\n]*))"
        ),
        "gi"
        );
    // Create an array to hold our data. Give the array
    // a default empty first row.
    var arrData = [[]];
    // Create an array to hold our individual pattern
    // matching groups.
    var arrMatches = null;
    // Keep looping over the regular expression matches
    // until we can no longer find a match.
    while (arrMatches = objPattern.exec( strData )){
        // Get the delimiter that was found.
        var strMatchedDelimiter = arrMatches[ 1 ];
        // Check to see if the given delimiter has a length
        // (is not the start of string) and if it matches
        // field delimiter. If id does not, then we know
        // that this delimiter is a row delimiter.
        if (
            strMatchedDelimiter.length &&
            (strMatchedDelimiter != strDelimiter)
            ){
            // Since we have reached a new row of data,
            // add an empty row to our data array.
            arrData.push( [] );
        }
        // Now that we have our delimiter out of the way,
        // let's check to see which kind of value we
        // captured (quoted or unquoted).
        if (arrMatches[ 2 ]){
            // We found a quoted value. When we capture
            // this value, unescape any double quotes.
            var strMatchedValue = arrMatches[ 2 ].replace(
                new RegExp( "\"\"", "g" ),
                "\""
                );
        } else {
            // We found a non-quoted value.
            var strMatchedValue = arrMatches[ 3 ];
        }
        // Now that we have our value string, let's add
        // it to the data array.
        arrData[ arrData.length - 1 ].push( strMatchedValue );
    }
    // Return the parsed data.
    return( arrData );
}


/**
@usecase 新規シートを作成する@動かない
*/
function createSheet() {
  var sheet = SpreadsheetApp.create("hoge");
  sheet.getRange("A1").setValue("test1");
}




//==================={文字列操作}=====================

/**
 @desc +00005050などの文字列を数値に変換する
 @usecase toNum(String)
 @return {Number}
 */
function toNum(num){ //めちゃ簡単!!!
  num = num.slice(1);
  return parseInt(num, 10);
}


/**
 *@desc 配列の合計を算出する
 *@usecase sumArray(Array)
 *@return {Number}
 */
function sumArray(a) {
  return a.reduce(function(x, y) { return x + y; });
}



/**
 *@desc 計算済の結果をメモして高速化する関数の見本
 */
/*
var complex = function() {
	var memo = [];
	var comp = function(n) {
		var result = memo[n];		// 計算済みの結果があればそれを使う
		if (typeof memo[n] === 'undefined') {
			// 時間のかかる計算
			result = ...
			memo[n] = result;		// 計算した結果を保存しておく
		}
		return result;
	};
	return comp;
}();

var a = complex(10);
var b = complex(20);
var c = complex(10);	// 最初の引数と同じなので計算済みの結果が使われる。

*/