Google Spreadsheet : ゴールシーク機能とソルバ機能について

Googleのサービスは便利なものも多いですが、無料で使える代償として、Googleの都合に振り回されることもあります。バージョンアップに伴う機能の改廃がその1つです。

以前のGoogle Spreadsheetでは、線形問題限定ではあったものの、ソルバ機能がついていました。しかし、今は廃止されています。ゴールシーク(Goal Seek)やソルバ(Solver)は、使い方次第では様々なことができます。 Microsoft Excelのソルバ機能を使った数学計算、工学応用例については、

Excelを用いた科学技術計算

が詳しいです。

同様のことがGoogle SpreadsheetやGoogle Apps Scriptを使って実現できたら、おもしろいだろうな、と思っています。

ということで、ゴールシーク機能をGoogle Apps Scriptで実現できるかどうか、トライしてみたいと思います。

Google Apps Script : セルから取得した値を四捨五入する

Google Spreadsheetのセルから取得した値を整数に変換する方法はJavaScriptの関数を使う方法がいくつかあるようなのですが、ここでは四捨五入して整数にするとします。その場合の記述方法は、

var 変数名 = Math.round(sheet.getRange(セル指定).getValue());

こちらに値の切上げや切り下げを含む他の方法での整数への変換方法が記載されています。

Google Apps Script : 特定の列内の複数の値を並び替える

もちろん列ではなく行の場合でも同じ方法で値を並び替えた配列を取得できます。

書き方は、

配列名.sort();

下のサイトで教えていただきました。ありがとうございます。

配列を逆順(降順)にソートする(JavaScript)

Google Apps Script : 特定の列内の最大値を取得する

とりあえず書き方だけ。

D列の1行目から最終行までの値を1つの配列に入れ、その配列内の最大値をJavaScriptのメソッドを使って取得するようです。

---以下スクリプト---

function getMaxValue() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getActiveSheet();

    var last_row = sheet.getLastRow();

    var columnD = sheet.getRange(1,4,last_row);

    var valuesD = columnD.getValues(); //D列の値が入った配列

    

    Browser.msgBox(Math.max.apply(null,valuesD));

}

---以上スクリプト---

下のサイトから頂戴しました。ありがとうございます。

【Javascript】配列の値の中から、最大値、最小値を求める（配列を引数に展開する）

Google Apps Script : 配列の最初から最後までループを回すときの注意点

Google Apps Scriptを使って、自分のGmail受信トレイから特定のラベルを付けたメールの内容をGoogle Spreadsheetに入力するスクリプトを書いていてはまってしまったので、ここに書いておきます。

まず、はまったスクリプトがこちら。

---以下スクリプト---
function getMessageFromGmail() {

    var label = GmailApp.getUserLabelByName("Label name");
    var threads = label.getThreads();
    row = 1;
   
    for(var n=0;n<=threads.length;n++) {
        var thread = threads[n];
        var msgs = thread.getMessages();
       
        for(m in msgs) {
            var msg = msgs[m];
            var date = msg.getDate();
            var from = msg.getFrom();
            var to = msg.getTo();
            var subject = msgs[m].getSubject();
            var body = msgs[m].getPlainBody();
           
            sheet.getRange(row,1).setValue(date);
            sheet.getRange(row,2).setValue(subject);
            sheet.getRange(row,3).setValue(body);
            row = row + 1;
        }
    }
}
---以上スクリプト---

次に、問題が解決したスクリプトがこちら。

---以下スクリプト---
function getMessageFromGmail() {

    var label = GmailApp.getUserLabelByName("Label name");
    var threads = label.getThreads();
    row = 1;
   
    for(var n=0;n<threads.length;n++) {
        var thread = threads[n];
        var msgs = thread.getMessages();
       
        for(m in msgs) {
            var msg = msgs[m];
            var date = msg.getDate();
            var from = msg.getFrom();
            var to = msg.getTo();
            var subject = msgs[m].getSubject();
            var body = msgs[m].getPlainBody();
           
            sheet.getRange(row,1).setValue(date);
            sheet.getRange(row,2).setValue(subject);
            sheet.getRange(row,3).setValue(body);
            row = row + 1;
        }
    }
}
---以上スクリプト---

違いは1箇所。1つめのforループを回す回数を示すn<threads.lengthが、<=か<だけです。基本を分かっている人にはバカにされ笑われてしまいそうですが、独学だとこんなところにはまってしまいます。

このスクリプトでは、まず、

    var label = GmailApp.getUserLabelByName("Label name");
    var threads = label.getThreads();

のところでGmailの受信トレイから"Label name"という名前のラベルの付いたスレッド(配列)を取得して、threadsという名前をつけています。
次に、配列threadsの中身をforループを使って順番に見ていき、n番目のスレッドを

    var thread = thread[n]
    var msgs = thread.getMessages();

としてthreadという名前の変数に入れ、thread(これも配列)の中身をgetMessages()メソッドを使って取りだしてmsgsという名前をつけています。

上の失敗例を実行すると、関数実行中のメッセージがなかなか消えず、消えたと思ったらvar msgs = thread.getMessages();のところでmessagesがgetできないというエラーメッセージが表示されます。

エラーの理由は、上にも書いた通り、forループの繰り返し回数指定にありました。
Google Apps Script (JavaScriptやPythonでも同様)では、配列(Pythonではリスト)のインデックスは0から始まるため、上のスクリプトのように配列の長さをループの繰り返し回数に指定する場合、

for(var n=0;n<=threads.length;n++)

と書くと、配列の長さより1回分多くループを回してしまうことになります。そのため、配列内の長さを超えたところでgetMessages()メソッドを実行使用してエラーとなっていました。

ものすごく単純なことなのですが、先駆者のみなさんのスクリプトのコピペで勉強を始めた身としては、引っかかって良かったと思える失敗でした。

Google Apps Script : Gmail本文をそのままをスプレッドシートに入力する方法

Google Apps Scriptには、Gmail本文を取得するメソッドとしてGmailAppクラスにgetBody()メソッドがありますが、これを使うとメール本文内の改行を示すHTMLタグである<br />までスプレッドシートに入力されてしまいます。

そこで、<br />タグなしの、本文の見た目そのままを入力したいときはgetPlainBody()メソッドを使います。

---以下スクリプト---

作成中

---以上スクリプト--

参考にさせてもらったサイト

Google Apps Script : getMessages()メソッドで取得できる受信トレイスレッドの順番

GmailAppクラスのgetMessages()メソッドを使うと、自分のGmail受信トレイのスレッドオブジェクトを取得することができますが、どのような順番で取得できるのかがわからなかったのでテストしてみました。

テスト用のスクリプトは

---以下スクリプト---

function getMessageFromGmail() {

    var threads = GmailApp.getInboxThreads();
   
    for(var n=1;n<=5;n++) {
        var thread = threads[n];
        var msgs = thread.getMessages();
              
        for(m in msgs) {
            var msg = msgs[m];
            var date = msg.getDate();
                  
            sheet.getRange(n,1).setValue(date);
        }
    }
}

---以上スクリプト---

Gmail受信トレイの全スレッド(実際には500スレッド)を取得し、それについてforループを5回回してスレッドを取得しています。
さらに取得した5スレッドについて内側のforループで受信日時を取得し、スプレッドシートのA列に順番に受信日時を書き込んでいます。

結果は

となりました。受信日時が新しい順に上から並びましたが、受信トレイには未読のものが残っているため、受信トレイの見た目とは異なることに注意が必要かと思います。