2014年3月10日月曜日

Google Apps Script - スプレッドシートで各列の最終行を求めるScript

スプレッドシートで各列の最終行を求めるScript

通常の場合は、getLastRow() を使えば、最終行の値を得ることはできるのですが、それは、そのシート全体の範囲としての最終行になってしまいます。

例えば、行数が各列ごとに違う場合などは、一番大きい値のみが得られ、不便なことがあります。

つまり、スプレッドシート関数のCOUNTA()を、Google Apps Script で書いてみました。


A列に記入されているデータの個数を取得するScript

// getting the number of data contained in the column A
function getLastRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A:A").getValues();
  Logger.log(range);
  var lastRow = range.filter(String).length;
  Logger.log(lastRow);
}

(A:A) という表記が、列全体を表す書き方です。
上のScriptでは、A列にある値をすべて取得し、その範囲で値が文字列であるものだけを取得するのが、filter(String)です。
さらに、その値の数を、length で取得すれば、スプレッドシート関数のCOUNTA(A:A)と同じになります。

ただ、この表記のままだと、Script で扱いづらい!!
Script では、(2, 5) などの表記が扱いやすいので、この表記で処理して、最終的に、(A:A)のような表記に直す方法を調べました。

.getA1Notation()

例えば、

(A1:A10)

のような表記を作りたいときは、

var from = sheet.getRange(1, 1).getA1Notation();
Logger.log(from);  // A1
var to = sheet.getRange(10, 1).getA1Notation();
Logger.log(to);  // J1

と変換してくれます。

その後に、

var width = from + ":" to;
Logger.log(width); // A1:A10

こうすれば、(A:A)という表記を使わずとも、スプレッドシートの各列の範囲をA1のような表記で得ることができます。

こうするメリットは、forなどでLoopさせるときに、(1, i) などの表記で処理を行ってから、A1表記に直せることにあります。



スプレッドシートで各列の最終行(各列に記入されているデータの個数)を求めるScript:

function getLastRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range, i;

  var lastCol = sheet.getLastColumn();
  //Logger.log(col);

  for(i = 1; i <= lastCol; i++) {
    var from = sheet.getRange(1, i).getA1Notation();
    var to = sheet.getRange(sheet.getLastRow(), i).getA1Notation();
    //Logger.log(from, to);
    var width = from + ":" + to;
    //Logger.log(width);
  
    var range1 = sheet.getRange(width).getValues().filter(String).length;
    Logger.log(range1);
  }
}



メールの一斉送信のScriptでもこれを利用しています。
http://code-ur-life.blogspot.jp/2014/03/google.html



参考サイト:
https://productforums.google.com/forum/#!topic/docs/kgDmjZ-zhMY

http://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column-google-apps-script


HTH, Peace.

0 件のコメント:

コメントを投稿