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); // 最初の引数と同じなので計算済みの結果が使われる。
*/