2020年4月13日 / 最終更新日 : 2020年7月11日 acromediaGAS スプレッドシート

スプレッドシートでスクリプトを使うための基本

はじめに

今回はスプレッドシートをより便利に使用するための技術を紹介します。

スプレッドシートはGoogle社が提供する表計算ソフトですね。関数を設定できたり、自分以外の人と共有をすることができたりするので重宝します。同じく表計算ソフトのエクセルと機能や使い方が似ており比較されることが多く感じます。

では「Google Apps Script」という言葉を聞いたことはあるでしょうか。「Google Apps」というのはスプレッドシートを始め、ドキュメントやドライブ、ハングアウトなどのGoogle社が提供するサービスのことを指します。「Google Apps Script」とはそれらのGoogleのサービスをJavaScriptで操作するための環境を指します。Google Apps Script(以下GAS)を使用するにはスクリプトエディタというものを利用し、コードを記述していく必要があります。どのようなコードを書くとスプレッドシートで便利に使用できるのか以下に紹介します。

コードを書く準備～スクリプトエディタの起動～

まずはスクリプトエディタを起動します。

手順：スプレッドシートを開き、上部のメニューから「ツール」を選択→「スクリプトエディタ」をクリック

すると別タブでエディタが開きます。もし何か記録しているマクロがある場合は、ここに表示されるので確認してみてください。わざと何か事前に登録しておいて、エディタ上ではスクリプトとしてどのように書かれているのか見てみるのも良いでしょう。

ちなみに何も登録されていない場合は「コード.gs」という項目が表示され、下記のような空の関数だけ用意されています。

function myFunction() {}

ここに自分でコードを書いて肉付けしていきます。

別途テキストエディタを用意したり、環境構築のために何かをインストールしたりする必要がないので大変手軽です。スクリプトエディタは煩わしい段階も踏まず何回かクリックしていると開くので、軽い気持ちで始められるのが好印象です。

これでコードを書く準備は整いました。次からは先ほどの空の関数の中にコードを書いていくこととします。(関数名は適宜変更してください。)

セル番地の指定(getRange)

まずはスプレッドシートの中で操作する対象となるセルがどこにあるのか、スクリプトで表現する方法を紹介します。セルの位置を指定するにはgetRangeメソッドを使用します。以下に書式をいくつかまとめました。

①sheet.getRange(“セル番地”)
②sheet.getRange(“セル番地（開始）: セル番地（終了）”)
③sheet.getRange( 行番号 , 列番号 )
④sheet.getRange( 行番号 , 列番号 , 行数 )
⑤sheet.getRange( 行番号 , 列番号 , 行数 ,列数 )

①、②のセル番地にはA1やC3のように明確なセル1つの番地が入ります。②の場合はセル番地を2つ指定して、複数セルの範囲を表します。例えばA1:C3となっていた場合A1～C3の9つのセルを示すことになります。③④⑤にある行番号と列番号にはそれぞれ当てはまる行や列の番号が入ります。例えば③に当てはめて考えると(1,3)とした場合、1行目の3列目のセルになるのでC1を指すことになります。

セルの値を取得する(getValue、getValues)

セル番地の指定方法が分かったところで次は、様々な場面で必要になるであろうセルの値の取得についてご説明します。スプレッドシートのどのセルの値を取得するのか、先ほどのセル番地の指定も使用しながら、スクリプトエディタで書いて実行してみます。

指定したセルに値を入力するメソッドの書式は以下の通りです。

var 戻り値=range.getValue();
(例) A1のセルの値を取得するコード
var sheet=SpreadsheetApp.getActiveSheet();
var value=sheet.getRange(“A1”).getValue();

1行目は決まり文句のようなもので、現在アクティブになっているシートに対しての処理であることを示しています。2行目からが実際にセルの値を取得するコードです。Rangeは先ほど紹介した通りセルの番地を示すものです。では、例を以下のように変更するとどうなるでしょうか。

var sheet=SpreadsheetApp.getActiveSheet();
var value=sheet.getRange(“A1:A3”).getValue();

先ほどとの違いは2行目のセル番地を指定している部分になります。この例のコードではA1のセルからA10までのセルを範囲指定しており、その間の値を取得できる。ような気がしませんか？ですが、実際にこの処理を実行するとA1の値しか取り出すことができていません。getValue()では一つの値を取り出すことしかできないのです。このように範囲を指定した場合でも指定された範囲の1番左上(この場合A1)の値しか取得できないのです。

そこで登場するのがgetValues()です。複数形になっていますね。getValues()は値を配列に入れて取り出します。よって、実行すると指定した範囲全ての値を取得できるのです。

セルに値を入力する(setValue,setValues)

値の取得が出来たところで、次にセルへの値の入力の仕方を説明します。

指定したセルに値を入力するメソッドの書式は以下の通りです。

var 戻り値=Rangeオブジェクト.setValue(入力値);
(例)15行目2列目のセルにみかんという文字列が入力されるコード。
var sheet=SpreadsheetApp.getActiveSheet();
sheet.getRange(15,2).setValue(“みかん”);

getとsetの違いなので分かりやすいです。sheet.getRange(15,2)ここではセルの番地を指定しています。上記にセルの番地指定の紹介をしているので理解しやすいかと思いますが、この例の場合15行目の2列目を指定していることになります。

setValue(“みかん”)ここでは何を入力するかを指示しています。引数の中に表示したい文字を入力します。この例の場合はみかんという文字列を挿入することになります。

setValuesと複数形に変更すると配列で一気に入力したい値を指定することも出来ます。

セルの表示形式を指定する(setNumberFormat, setNumberFormats)

スプレッドシートでは多くのデータをまとめるために表を用いることが多いでしょう。表は数値や日付など、様々な表示形式のデータが混在する場合が多くあります。そんなときに役立つ指定したセルの表示形式を指定できるメソッドを紹介します。書式は以下の通りです。

range.setNumberFormat(“フォーマット”)

フォーマットとなっている部分には表示形式に合わせた文字列を指定します。スクリプトには標準で紹介しきれないぐらいたくさんあるので、ここでは一部の使用頻度が高いであろう表示形式の指定方法について紹介していきます。

yyyy/mm/dd　←　2020/03/04(/を変更すると区切る記号も変更可能です。)
hh:mm A/P”.M.”　←　06:15 PM
####.#　←　小数点以下第一位まで表示します(例)5673.37の場合5673.3となります。
#,###　←　,でケタ区切りを行います。(例)123456の場合123,456となります。

ちなみに、ここまで紹介してきたメソッド達と同じくsetNumberFormatsと複数形に変更すると、複数セルの形式を文字列の配列で一気に指定することが出来ます。

独自の関数を作成してみよう

上記のように最初から様々な便利な関数が用意されているスプレッドシート関数ですが、GASを使用して独自の関数を作成することもできます。例えば、金額を表す数値を税込み表示にすしたり、西暦と和暦を変換したり、入力された日付が事前に登録しておいた期間に当てはまるかどうか判断したり、と目的に応じて作成し分けることができます。次からはそのカスタム関数について作成する方法を学んでいってみましょう。

まずはプロジェクトを追加します。スクリプトエディタを開いて左上のプロジェクト名(初期は「無題のプロジェクト」になっている箇所)を編集して任意のプロジェクト名をつけてください。次にスクリプトエディタにスクリプトを書いていきます。カスタム関数を作成する場合は、デフォルトで書かれている空の関数は削除してしまってOKです。今回は例として、入力された金額を表す数値を税込み金額にするという関数を書いてみます。関数名は自分で分かりやすいものをつけてください。例ではSYOHIZEIという関数を作成します。

function SYOHIZEI(price){
const taxProp = 0.1;
return price * (1 + taxProp);
}

記述したら忘れずに保存してください。スクリプトエディタはスプレッドシートや他のサービスと違って自動的に保存されません。スクリプトエディタはWeb上で編集していますが、通常Webを閲覧しているときと違って、ショートカットキーのCtrl+Sでもコードを保存することができます。関数の説明に戻ります。

SYOHIZEIという関数の引数priceに数値を渡します。taxPropという変数に税率10%を表す0.1を格納し、3行目でpriceに入れた数値を使って計算した結果を返しています。

この2行目で出てきたconstというのは、定数を表します。これはGAS独自ではなく、ベースとなっているJavaScriptと同じ書き方になります。

実際に作成したカスタム関数をスプレッドシートで使ってみよう

今度は実際にスプレッドシートで作成した関数を呼び出して使ってみましょう。

実際に関数を使用するには、数値を合計するSUM関数などと同じで関数名を指定して使用します。上記の入力された数値を税込み金額に直す関数を例に説明していきます。

まず前提として、「A列に数値を入力→B列でA列の数値を税込み金額に直す」という動きにします。

なので関数を入力するのはB列になります。まずA1のセルに任意で数値を入力しておきます。そして、B1には「=SYOHIZEI(A1)」という式を挿入します。A1の部分にはA列の数値を変更したいセル番地を入れます。もしA1に100を入力していたとしたら、B1には110と表示されているはずです。

ちなみにA列の数値を入力する前提の箇所に文字列を入力するとエラーになります。実際に計算できない文字列などを入力してそちらも確認してみてください。もしこのように関数でエラーが発生した場合にエラーを表示したくない場合は、IFやIFERRORなどの関数と組み合わせて使用し、表示される値を操作してみてください。例えば取引先と共有しているドキュメントだった場合や、印刷する書類の場合はそういった気遣いのある処理で見やすさや操作の仕方に違いが出てきます。整然とした書類の方が使うときも閲覧するときも都合がいいです。

ドキュメンテーションコメントを使用する

いきなりこれまで使用していない単語が出てきましたが、これはスプレッドシート側で実際に関数を使用する際に便利になるワザです。ドキュメンテーションコメントとは「/**」ではじまり「*/」で終わるコメントのことを指し、その関数を説明するためのコメントになります。ドキュメンテーションコメントは関数を宣言する直前に記載します。先ほど記載したSYOHIZEI関数を例に記載方法を説明します。

/**
* 金額を税込にする関数SYOHIZEI
*
* @param {Number} 金額
* @return {Number} 税込金額
* @customfunction
*/
function SYOHIZEI(price){
//中身は同様の為割愛します。
}

このようにコメントの中に@で始まるタグを指定します。「@customfunction」のタグを追加することで、スプレッドシートで関数を入力しようとした際に補完の候補にあがるようになります。さらに、入力中にタブキーを押下するとドキュメンテーションコメントに入力した内容の一部を参照できるようになります。関数の作成者意外がスプレッドシートで関数を使おうとしたときに補助的な役割をしてくれるので圧倒的に使いやすいものになります。ドキュメンテーションコメントの書き方ですが、これもJavaScriptと共通で、JSDocがベースになっています。しかしGASで使用できるタグには制限があり以下の3つになっています。

@param：引数のデータ型と説明
@return：戻り値のデータ型と説明
@customfunction：スプレッドシートのカスタム関数として使う

ドキュメンテーションコメントを記載することは、何か共同で作成する場合などメンバー間での共有がしやすくなりますし、メンテナンスの際にも内容がすぐに分かるので効率が良くなります。

最後に

スプレッドシートに最初から備わっている関数に関して一部の基本的なメソッドしか紹介できませんでしたが、GASにはまだまだデフォルトでたくさんのメソッドが備わっています。公式のサイトで確認できますので、目的に応じて探してみてください。
また、独自の関数の作成は、作業を自動化するためには切っても切り離せないものになるので文法などしっかり覚えて活用していきたいです。

カテゴリー: スプレッドシート