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列に順番に受信日時を書き込んでいます。

結果は

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

Google Apps Script : getInboxThreads()メソッドでの最大数指定時の注意点

Google Apps ScriptでGmailの受信トレイにアクセスする場合、getInboxThreads()メソッドを使います。これにより、受信トレイ のスレッドを得ることができます。

getInboxThreads()メソッドは引数として、開始位置と最大数を指定することができます。書き方は、

var 変数名 = getInboxThreads(開始位置, 最大数);

となりますが、最大数は500に制限されているようです。

引数に開始位置と最大数を指定しないで受信トレイのスレッドを取得したとのスレッド数を調べてみました。

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

function getMaxThreadsNumber() {
    var threads = GmailApp.getInboxThreads();
    Browser.msgBox(threads.length);
}

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

結果は下のように500となりました。

Google Ads | AdSenseの始め方1

Google Ads(AdSense)とは、運営しているWebサイトやブログに、サイトの内容に関連する広告を掲載し、収益を上げることのできサービスです。

Google AdSense

今すぐ開始ボタンを押して次へ進みます。

Python | 指定した文字列を含む行とそれに続く複数行をまとめて削除する

このブログのメインテーマは、タイトルにもあるようにGoogle Apps Scriptなのですが、Pythonの勉強もしているため、時折Pythonの話題も入ります。

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

#!/usr/bin/env python
#-*- coding:utf-8 -*-

def deleteSecureNode():
    datafile = "C:\test\test.txt" # 架空のパスです
    savefile = "C:\test\test2.txt" # 架空のパスです"

    file = open(datafile,"r")
    file_out = open(savefile,"w")

    line_list = file.readlines()

    for number,line in enumerate(line_list):
        if line[0:12] == "SECURE/ NODE":
            del line_list[number:number+3]
            #del line_list[number+1]
            #del line_list[number+2]
            #del line_list[number+3]
        else:
            file_out.write(line)

    file.close()
    file_out.close()

deleteSecureNode()

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

enumerate()の使い方を知るまでと、del文で削除したい行を指定する方法にたどり着くまでに相当な時間を費やしてしまいました。

記事内のスクリプトをハイライトさせて表示する方法

ブログ投稿欄内にソースコードを表示するときに、皆さんが何を使っているのかを今日知りました。

SyntaxHighlighter

しかし、まだBloggerに適用する方法がわかりません。参考になるサイトとしては

BloggerにおけるSyntaxHighlighter Ver.3.x

というのがあるのですが、もう一度HTMLとCSSの勉強をし直さなくては…

Google Apps Script | Spreadsheetの空白行のみを削除する

Gmail受信箱の特定のスレッドの内容をSpredsheetに書き出すためのスクリプトを、以下のサイトを参考(ほぼコピー)にして書きました。

初心者のためのGoogle Apps Script入門

実行したところ、欲しいスレッドの間に他のスレッドが含まれていると、Spreadsheetでも他スレッド分の空白行ができてしまいました。

これらの空白行を削除するスクリプトとして、自分なりに試したもののうまくいかなかったため、以下のサイトを参考(ほぼコピー)にして実行すると、やりたかったことが完璧にできました。

http://googlestyle.client.jp/sheet/class_sheet.html

---以下スクリプト---
function deleteRows() {
    for(var i = lastRow; i > 0 ; --i){
        var cellA = ss.getRange("A" + i)
        var valueA = cellA.getValue();

        if(valueA == "") {
            sheet.deleteRow(i);
        }
        else{
       
        }
    }
}
---以上スクリプト---

自分はSheetの1行目から下に向かってA列をA1→A2→…と検索し、空白であればその行全体を削除するようなスクリプトを書いてみたのですが、削除されない行があってうまくいきませんでした。

参考にさせていただいたサイトの例では、Sheetの最終行から上に向かって検索しているのが特徴です。

Google Apps Script : 参考図書

製本されて出版されている日本語の参考図書は

• Google Apps Scriptクイックリファレンス
• Googleクラウドスクリプティング
• Google Appsで作る仕事便利ツール

の3つが代表的かと思います。

私は『Googleクラウドスクリプティング』を使っています。Spreadsheetの操作に関しては一通りメソッドが網羅されており、Spreadsheetでどんなことをしたいのかが分かっていれば、この本を見ながらスクリプトを書くことができると思います。

Google Apps Script | Line Chart(折れ線グラフ)の表示サイズ指定について

Google Apps Scriptで作成できるチャート(グラフ)は、その表示サイズもスクリプト内で指定することができます。折れ線グラフを作成する場合の記述は

---以下スクリプト---
var lineChart = Charts.newLineChart()
                    .setDataTable(data)
                    .setTitle("グラフのタイトル")
                    .setDimensions(450,300) //( )内の数値を変更すると表示グラフサイズが変わる
                    .build();
---以上スクリプト---

のようになります。ここで、setDataTable(data)のdataは、あらかじめ作成しておいたグラフ作成用データテーブルオブジェクトです。

グラフの表示サイズはsetDimensions(450,300)で指定します。カンマ区切りの左側が横方向、右側が縦方向のサイズになります。

Google Apps Script : DataTableオブジェクトの型について

Google Apps Scriptでチャート(グラフ)をさくせいする場合、まず、グラフに使用するData Table(表)を作成します。このData Tableはスプレッドシートにグラフの基になるデータ一覧を作成済みでも、スクリプトで改めて記述しなければなりません。

スクリプトでのData Table作成手順はざっくりと

1. 列を作成する
2. 行を作成する

なのですが、列に入ることになるデータのタイプが何なのかを、1の段階で指定しておかなければなりません。例えば日付であればDATE、文字列ならSTRING、数値ならNUMBERを指定します。

ここで指定した型と、2の段階で実際にData Tableに適用した値の型が食い違っているとエラーとなります。

Google Apps Script : 配列に格納した値のデータ型について

複数セルの値をまとめて1つの配列に格納した場合、それぞれの値のデータ型はObjectとなるようです。

そのため、他の値と比較して条件判定する場合、「==」の左右でデータ型をそろえてやらないとうまく機能しないようです。