• ログインログイン
  • 新規登録新規登録

MENU

Google Apps Scriptでプログラミングを学ぶ 第5回

連載Google Apps Scriptでプログラミングを学ぶ

Google Apps Scriptは、開発環境の設定がなくてもすぐにコーディングができる優れもののサービスです。初心者でもすぐに取り掛かれる事例を用いながら、実践に耐えうるレベルまで徐々にステップアップして学習していきます。

この記事は、株式会社らしく様からの寄稿記事となります。前回までのプログラミング学習コラム引き続き、今回は第5回目の勉強内容です。今回からは、オブジェクト操作とメソッドに入っていきます。

GASの便利なところは、シートや範囲などを操作でき、さらに既存のGoogleスプレッドシート関数やJavaScriptの関数を利用できることです。シートを増やしたり、他のスプレッドシートを開いて、そこから値をコピーしたりということも、プログラムを書くことで自動的にできてしまうのです。

Googleスプレッドシートにおいてはオブジェクトの操作こそ基本です。どんなことができるかだけでも知っておかないと、あとあとGASを使ったアプリケーションを作ろうとしたときに、どんなものを作ろうかという想像力が乏しくなってしまいます。

メソッドという言葉がいっばい出てきて、頭が混乱するかもしれないので、詳細は覚える必要はありません。「へえ、こんなことができるのかぁ」程度に知っておいてください。

また、この回で取り扱うメソッドは、GASの豊富なクラスとメソッドの中の一部です。最終回の参考情報の章で、公式のGoogle Apps Script 製品ページの参照方法を解説しています。

セクション1 オブジェクトの操作

スプレッドシートを使ったデータの入力、計算、出力の基本

コンピュータの基本は、データの入力、データの計算、データの出力です。シートの範囲のデータの取得と書き出しは、それぞれ、RangeクラスのgetValue(), setValue()を使用します。

*書式

取得: 変数 = Rangeオブジェクト.getValue();

指定: Rangeオブジェクト.setValue(変数);

データの入出力の例

function getValue() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();

 var range1 = sheet.getRange("B1");
 var range2 = sheet.getRange("B2");
 var input = range1.getValue();
 var output = input * 10;
 Browser.msgBox("計算結果 " + output + " を、出力します。");
 range2.setValue(output);
}

実行結果

 フォント名の操作

RangeクラスのgetFontFamily()、setFontFamily()メソッドを利用して、セルや範囲のフォント名の取得や指定ができます。取得とは、現在指定されているフォント名を知ることで、指定とは自分の好きなフォント名に設定することです。

*書式

取得: 変数 = Rangeオブジェクト.getFontFamily();

指定: Rangeオブジェクト.setFontFamily(“フォント名”)

フォント名の指定の例

function fontFamily() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();

// 選択されているシートを指定

 var sheet = ss.getActiveSheet();

// 範囲を定義

 var range1 = sheet.getRange("A1");
 var range2 = sheet.getRange("A2:A5");

// rangeオブジェクトに対し、フォントを操作

 range1.setFontFamily("Georgia");
 range2.setFontFamily("Verdana");
}

実行結果

フォントの色

RangeクラスのgetFontColor()、setFontColor()メソッドを利用して、セルや範囲のフォントの色の取得や指定ができます。

*書式

取得: 変数 = Rangeオブジェクト.getFontColor();

指定: Rangeオブジェクト.setFontColor(“色”);

function fontColor() {

// 選択されているシートを指定

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();

// 範囲を定義

 var range1 = sheet.getRange("A1");
 var range2 = sheet.getRange("A2:A5");

/* rangeオブジェクトに対し、フォントの色を操作
* 指定の方法は異なるが、結果は同じになる。
*/

 range1.setFontColor("red");
 range2.setFontColor("#ff0000");
}

実行結果

 セルの背景色

RangeクラスのgetBackground()、setBackground()メソッドを利用して、セルの背景色の取得や指定ができます。

*書式

取得: 変数 = Rangeオブジェクト.getBackground();

指定: Rangeオブジェクト.setBackground(“色”);

function background() {

// 選択されているシートを指定

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();

// 範囲を定義

 var range1 = sheet.getRange("A1");
 var range2 = sheet.getRange("A2:A5");

// rangeオブジェクトに対し、フォントの色を操作

 range1.setBackground("gray");
 range2.setBackground("#c0c0c0");
}

実行結果

 色について(色の定数、RGB、Colorlndex)

フォントの色やセルの背景色の設定方法は、あらかじめ決まっている色の名前を指定する方法と、赤(Red)、緑(Green)、青(Blue)の光の3原色(RGB)を16進数で指定する方法の、2つがあります。

色の名前を指定する方法

ブラウザでウェブページを表現する際に使われるHTMLやJavaScriptで使われる色の名前は、基本的にGASでも指定できます。基本となる16色は、次のとおりです。

RGBを16進数で指定する方法

赤、緑、青を、混ぜ合わせることで、実に様々な色を表現できます。例えば、赤と緑を混ぜ合わせることで黄色になります。各色の割合を0〜255の範囲で指定するのですが、その際、10進数表現を16進数表現に変換して表現することになります。たとば、255はffになります。そして、16進数だと言うことを明示するために、始めに”#”を書きます。

6進数で指定する例

#FFFFFF

#00FF00

#000000

黄色

#FFFF00

#FF0000

グレー

#808080

#0000FF

#800080

詳しい説明は、Wikipediaの「HTMLでの色名称」をご参照ください。

行・列の挿入と削除

行や列の挿入はInsertメソッド、削除はDeleteメソッドを使用します。

function insertRowColumn() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();

/* sheetオブジェクトに対し、基準となる列、行を数字で指定します。
* A1記法ではないことに注意してください。
*/

 sheet.insertColumnAfter(1);
 sheet.insertRowAfter(1);
}

実行結果

行・列の非表示と表示

行や列の非表示はhideメソッド、表示はshowメソッドを使用します。

function hideRowColumn() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range1 = sheet.getRange("B1");
 var range2 = sheet.getRange("A2");
 sheet.hideColumn(range1);
 sheet.hideRow(range2);
}

 実行結果

値の入っている最終行・最終列を知る

スプレッドシートは、行単位に決まったデータ項目の集まりが、異なるデータを持った集合で保存されるということがよくあります。例えば、2章で例として示した、テストの生徒毎の成績などです。このとき、for文をつかって、ループ処理をするのですが、1行目から値が入っている最後の行まで処理したい場合、値が入っている最後の行はどうやって求めればよいのでしょう。
sheetオブジェクト(もしくはrangeオブジェクト)の値の入っている最終行はgetLastRowメソッド、sheetオブジェクト(もしくはrangeオブジェクト)の値の入っている最終列は、getLastColumnメソッドを 使って求めることができます。

結果は、数字で返却されます。

function lastRowColumn() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var last_row = sheet.getLastRow();
 var last_column = sheet.getLastColumn();

// メッセージボックスに出力します。

 Browser.msgBox("最終行 = " + last_row + ", 最終列 = " + last_column);
}

実行結果

 シート数のカウント

スプレッドシート内のシートの数を知るには、SpreadsheetクラスのgetNumSheets()メソッドを使用します。

function numSheets() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var num = ss.getNumSheets();
 Browser.msgBox("シート数 = " + num);
}

実行結果

シートの追加

スプレッドシートに新しくシートを追加する場合は、SpreadsheetクラスのinsertSheet()メソッドを使用します。

function insertRowColumn() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.insertSheet();
}

スプレッドシートの新規作成

新しくスプレッドシートを作成する場合は、SpreadsheetAppクラスのcreate()メソッドを使用します。

function insertRowColumn() {

// スプレッドシートの名前を"Sample"とします

 var ss = SpreadsheetApp.create(“Sample”);
}

実行結果

Googleドライブのマイドライブ内に、Sampleという名前のスプレッドシートが作成されます。

Googleスプレッドシート関数の操作

Goolgeスプレッドシートは、豊富な関数を持っています。GASでは、そのGoogleスプレッドシート関数をセルに挿入することで、Googleスプレッドシート関数を使えるようになります。

*書式

取得: 変数 = Rangeオブジェクト.getFormula();

指定: Rangeオブジェクト.setFormula(“Googleスプレッドシート関数”);

GoogleFinance関数を利用した例

function setFinanceFormula() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();

// 最終行を取得

 var last_row = sheet.getLastRow();

// 最終行までのループ処理

 for (var i = 1; i <= last_row; i++) {
 var company = sheet.getRange("A" + i).getValue();

// A列の株価銘柄名から、現在の株価を取得して表示

 var range = sheet.getRange("B" + i);
 range.setFormula('=GoogleFinance("' + company + '","price")');
 }
}

実行結果

 日付の加算

日付の扱いは、JavaScriptのメソッドを使って行うことができます。また、Utilitiesクラスには、日付のフォーマットを行うメソッドが用意されています。

現在時刻の取得

現在時刻の生成、時刻の取得や指定は、JavaScriptのメソッドを利用して行います。

*書式

現在時刻の生成: 変数 = new Date();

取得:    変数.getTime();

指定:    変数.setTime(“ミリ秒”);

function date() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range1 = sheet.getRange("B1");
 var range2 = sheet.getRange("B2");
 var range3 = sheet.getRange("B3");
 var date = new Date();

// 現在時刻を生成

 var formattedDate = Utilities.formatDate(date, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");

// グリニッチ標準時で指定フォーマットに編集

 var tomorrow = new Date();

// 時刻を生成し、24時間をミリ秒に変換して加算

 tomorrow.setTime(date.getTime() + (1*24*60*60*1000));
 range1.setValue(date);
 range2.setValue(formattedDate);
 range3.setValue(tomorrow);
}

実行結果

 文字列の操作

GASは、JavaScriptをベースに作られているため、JavaScriptのメソッドが利用できます。JavaScriptの文字列操作は、stringクラスのメソッドやプロパティを用いて行います。ここでは、概念を理解してもらうことを目的に、2つほど例を示します。

文字列の文字数を求める

JavaScriptで文字数を求める場合、文字列のlengthプロパティを確認します。

*書式

取得:    文字列.length

lengthプロパティを使用した例

function strLength() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var last_row = sheet.getLastRow();
 for (var i = 1; i <= last_row; i++) {

// A列の文字を取得

 var string = sheet.getRange("A" + i).getValue();

// lengthプロパティを使用して文字列の長さを確認

 var length = string.length;

 sheet.getRange("B" + i).setValue(length);
 }
}

実行結果

文字列を切り出す

ある文字列の一部を切り出すには、stringクラスのsliceメソッドを使用します。

*書式

取得:    文字列.slice(開始インデックス, [終了インデックス])

対象の文字列の開始インデックスの位置にある文字から終了インデックスの位置にある文字の前の文字までの部分文字列を新しい文字列として返します。インデックスは、配列で学んだインデックスと同様に、0からカウントを始めます。

sliceメソッドを使用した例

function strSlice() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var last_row = sheet.getLastRow();
 for (var i = 1; i <= last_row; i++) {
 var string = sheet.getRange("A" + i).getValue();
 var length = string.slice(2,4);

 sheet.getRange("B" + i).setValue(length);
 }
}

実行結果参考として、stringクラスのメソッドとプロパティの主要なものを示します。

メソッド、プロパティ

説明

stringメソッド(コンストラクタ関数)

文字列の生成

lengthプロパティ

文字列の長さを取得

toStringメソッド

文字列の文字列表現

valueOfメソッド

文字列の基本データ型の値

concatメソッド

文字列を連結

sliceメソッド

開始位置と終了位置を指定して部分文字列を取得

substringメソッド

開始位置と終了位置を指定して部分文字列を取得

substrメソッド

開始位置と長さを指定して部分文字列を取得

charAtメソッド

指定の位置の文字を取得

charCodeAtメソッド

指定の位置の文字コードを取得

fromCharCodeメソッド

文字コードを指定して文字列を作成

toLowerCaseメソッド

大文字を小文字に変換した文字列を取

toUpperCaseメソッド

小文字を大文字に変換した文字列を取得

indexOfメソッド

指定の文字列が含まれる位置を検索

lastIndexOfメソッド

指定の文字列が含まれる位置を最後から検索

計算を行う

Mathクラスでは数学に関する演算を行う上で便利な定数と関数が定義されています。ここでは、乱数や桁の切り上げなどを例に取り説明します。

乱数の発生や四捨五入などを使う

ここでは、改めて、サイコロを振る場合の例を説明します。乱数の発生や、数字の桁の四捨五入、切り上げ、切り捨て、などは、JavaScriptのMathクラスのメソッドが使えます。

*書式

乱数の発生:   変数 = Math.random(); 0以上1未満の数値を生成する。

四捨五入: 変数 = Math.round();

切り上げ: 変数 = Math.ceil();

切り捨て: 変数 = Math.floor();

サイコロをふる例

function dice() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 for(var i = 0; i < 10; i++) {

// 乱数の発生

 var num = Math.random() * 6/10 *10;

// 桁の切り上げ

 var dice = Math.ceil(num);
 sheet.getRange("A" + (i+1)).setValue(dice);
 }
}

実行結果

公開されているデータを取得する

Google、Facebook、Twitter、AppleなどのアメリカのIT企業はもちろん、日本でもヤフー、楽天、リクルート、食べログ、などの企業は、積極的にプログラムがアクセスしやすい形式で、データを公開しています。

GASは、簡単にそれらのデータを取得して、スプレッドシートに展開できます。その後、Googleスプレッドシートの関数を利用してデータを加工できるので、とても便利です。

ここでは、AppleのiPhoneやiPod, iPad用アプリのマーケットである、iTunesが公開しているデータを使用することを例にとります。

この説明のコンセプト図は、以下のとおりです。

まずは、GASから、少しはなれて、iTunes Storeを触ってみます。iTunes Storeで、右上の検索窓に、”google”と入力して実行してみます。すると、”google”というワードが、アプリのタイトルや説明に含まれているアプリの一覧を表示します。

iTunes Storeでは、このように人間がブラウザ画面から検索する方法の他に、プログラムが検索する方法をサポートしています。このように、プログラムがが互いにやりとりするのに使用する方法や約束事などを、API(アプリケーション・プログラミング・インターフェイス)と言います。

iTuens APIの詳細の説明はしませんが、以下のURLに検索条件を付加して、GASでリクエストをし、返されるデータをGASで、利用することになります。

htt://itunes.apple.com/search?term=検索ワード&country=&entity=種類oftware&limit=検索結果の上限数
例: https://itunes.apple.com/search?term=google&country=jp&entity=software&limit=5

※詳しい解説は、こちらをご覧ください。

ブラウザのアドレス入力窓で、上記の例を入力して実行すると、ブラウザ画面に以下のようなデータが表現されます。

(何がなんだかわからないと思いますが、)Googleのアプリに関するデータが表示されているのが、かろうじてわかると思います。

これを、JSON LINTというサービスできれいに整形してみます。先ほどのGoogleのアプリに関するデータを、まるごとコピーし、JSON LINTの画面にペースとして、Valiateをクリックします。

 これは第4回で学んだオブジェクトのデータ形式になっており、階層的に表現されています。このようなデータ形式はJSONとよばれ、ウェブ開発ではもちろん、APIでのデータ構造として、広く普及しています。

urlFetchAppクラスのfetchメソッドを利用して、データを取得します。

*書式

取得:   変数 = urlFetchApp.fetch(“url”);

iTunes APIを利用して、”google”というワードを含むアプリデータを5件取得して表示

function getAppData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();

// iTunes APIにリクエストするURL

 var url = "https://itunes.apple.com/search?term=google&country=jp&entity=software&limit=5";

// responseにオブジェクトを格納

 var response = UrlFetchApp.fetch(url);

// エラーコードを判定し、エラーであれば、エラーメッセージを表示

 if(response.getResponseCode() != 200) {
 throw "error: response code=" + response.getResponseCode();
 }
 var json = response.getContentText();
 var objects = eval('(' + json + ')');
 var counts = objects.resultCount;
 //Browser.msgBox("counts = " + counts);
 for(var i = 0; i < counts; i++){

/* 扱いたいオブジェクトのプロパティを変数にセット
* ここでは、アプリの名前(trackName)とアプリの画像URL(artworkUrl60)を取得
*/

 var app_title = objects.results[i].trackName;
 var app_icon = objects.results[i].artworkUrl60;

 var j = i+1;

 sheet.getRange("A" + j).setValue(j);
 sheet.getRange("B" + j).setValue(app_title);
 sheet.getRange("C" + j).setFormula('=image("' + app_icon + '", 3)');
 sheet.setRowHeight(j, 60);
 }
}

実行結果

今回は以上となります。次回からは、実践的な文法を書いてみるところを解説していきます。

株式会社らしくのインターン応募はこちら

オススメ記事一覧

もっと見る
完全無料!

1で登録完了!

エンジニアの仕事・年収や選考ノウハウ記事が読めるほか、
会員にはプログラミング講習やES・面接対策などリアルな無料サポートも充実。
ここだけの求人情報も多数。

今すぐ新規会員登録
Page Top