GAS(Google Apps Script)入門講座① - スプレッドシートを触ってみよう

はじめに

皆さん、GAS(Google Apps Script)って使ってますか？
「名前だけ聞いたことある」とか「EXCEL VBAみたいなもんでしょ？」と思ってる方もいると思います。

が、実はこれめっちゃ便利で、Googleのサービスだけでなくて、TrelloやChatwork、Slackなど、色々なサービスと連携して使えるんです。

なので、GASを使えば、Googleカレンダーから今日の予定を取得して、Slackに予定を送ってくれる秘書botを作ったり、trelloで進捗が遅れてるカード情報を取得して、スプレッドシートに管理票を作ったり、管理者にメールやSlackで通知したり、もうなんでもできますｗ

ちなみにGASはスクレイピング(web上の情報(テキストや画像など)を取ってくる)もできるので、クラウドワークスやLancersでよく見る、「xxxxの情報を持ってきてスプレッドシートに転記してください。1件10円」なんて案件がありますが、最初にプログラムだけ組んでおけばいくらでも稼げます(前にそういうYoutuberの方いましたよねｗ)

今日からは、そのGASを使ってスプレッドシートやGoogleカレンダー、Googleドライブ、Trelloから情報を持ってきて、GmailやSlack/LINE/ChatWorkへ通知する方法を何回かに分けて解説していきます。

楽しくやっていきましょう！

GASを使う準備をする

まず、Googleドライブに移動して、新しくスプレッドシートを作ってみましょう。

適当にシートの中身を埋めて(埋めなくてもOK)、スクリプトエディタを開きます。

とりあえず、▶の実行ボタンを押します

保存しなさいと言われるので、はい

許可を求められるので、「許可を確認」をクリックします

自分のアカウントをクリックします

最初はこの画面になりますが、「詳細をクリックして、先程付けた名前に移動。というリンクをクリック」

今回作ったGASが、あなたのアカウントのスプレッドシートの内容を表示したり、編集(読み書き)したり、シートを作成・削除しますがOKですか？と聞かれてます。
それが目的なので、「許可」をクリックします。

これで準備は万端です！

スプレッドシートを用意する

今回は、営業所別の日別売上データをサンプルとして作ってみました。

これらの値を取得したり、特定のセルに値を入れたりしていきますよー！！

GASからスプレッドシートを操作できるようにする

まず、スプレッドシートには3つのオブジェクトが存在することを覚えておきましょう。

■ SpreadsheetAppオブジェクト
全ての最上位のオブジェクト、SpreadSheetオブジェクトも、Sheetオブジェクトも取得できる
■ SpreadSheetオブジェクト
これが、スプレッドシート全体を操るオブジェクトで、どのシート(Sheetオブジェクト)を扱うかを決めたりする。
■ Sheetオブジェクト
↑の画像でいう、シート1、シート2のそれぞれにSheetオブジェクトがある。
■ Rangeオブジェクト
↑の青い枠で囲った部分、Sheetオブジェクトから【A1】セルの値を操作(値の取得・設定)とか【A1:C16】の範囲の値を操作(取得・設定)したりします。

GASでsheetオブジェクトを取得する

兎にも角にも、今回は売上が書かれた「シート1」を操作したいです。
そのためには「シート1」のsheetオブジェクトを取得しなければいけません。

Sheetオブジェクトを取得する手順は以下の2つを踏みます。

1. SpreadsheetAppからSpreadSheetオブジェクトを取得
2. Spreadsheetオブジェクトから、sheetオブジェクトを取得する

1. SpreadsheetAppからSpreadSheetオブジェクトを取得
これには、3種類あります。

1. geActiveを使う
2. openByUrlを使う
3. openByIdを使う

スプレッドシートからスクリプトエディタを開いた場合、アクティブなスプレッドシート=元のスプレッドシートになるので、ほとんどの場合は1しか使いません。

では実験してみましょう。

コードは以下を使います (これまでJavaScriptではconsole.logを使ってブラウザのコンソールでログを確認してましたが、GASの場合は、Loggerオブジェクトのlogメソッドを使います)

function myFunction() {
 // spreadSheetオブジェクトを取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 let spreadSheetByUrl    = SpreadsheetApp.openByUrl(【スプレッドシートのURL】)
 let spreadSheetById     = SpreadsheetApp.openById(【スプレッドシートのID】)
 
 // スプレッドシートの名前を表示する
 Logger.log(spreadSheetByActive.getName())
 Logger.log(spreadSheetByUrl.getName())
 Logger.log(spreadSheetById.getName())
}

openByUrlの引数に指定してる【スプレッドシートのURL】は、ブラウザで開いてるスプレッドシートのURLを指定してください。
openByIdの引数の【スプレッドシートのID】はURLの以下の部分(d/と/editの間の文字列)です。

https://docs.google.com/spreadsheets/d/【この部分の文字列】/edit#gid=0

では実行です。

この再生ボタンで実行してみてください。

ログは以下のように「表示→ログ」を見るとログを表示するダイアログが表示されます。
(※ Macの人はCommand+Enterでログのダイアログを表示できます)

3つとも今回のシート「無題のスプレッドシート」(名前付けてないのでｗ)が取れてるのが分かります。

では、ここからsheetオブジェクトを取得してみましょう。
これも2つの方法があります。

1. getActiveSheetを使う方法
2. getSheetByNameを使う方法​

一般的には先程のSpreadsheetオブジェクトを取得した時と同じで、1番でOKです。
が、例えばGASの中で複数のシートからデータをとってくる時なんかはgetSheetByNameを使います。これは文字通り、シート名を指定して持ってきます。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得方法
 let sheetByActive = spreadSheetByActive.getActiveSheet()
 let sheetByName   = spreadSheetByActive.getSheetByName("シート1")
 
 // sheet名を表示
 Logger.log(sheetByActive.getName())
 Logger.log(sheetByName.getName())
}

これもまた実行してログを見てみましょう。

両方とも「シート1」が取れてますね。一応、シート名から別のシートのsheetオブジェクトを取得してみましょうか。

【営業店別売上】というシートを作ってみたので、これを取得してみます。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得方法
 let sheetByActive = spreadSheetByActive.getActiveSheet()
 let sheetByName   = spreadSheetByActive.getSheetByName("営業店別売上")
 
 // sheet名を表示
 Logger.log(sheetByActive.getName())
 Logger.log(sheetByName.getName())
}

ちゃんと、getActiveSheetではシート1が取得できていて、getSheetByNameの方は指定した「営業店別売上」のシートが取得できてるのが分かります。

スプレッドシートの値を取得してみる

では、シートへのアクセスはできたので、シートの値を取得していみましょう。

シートの値の取得は、sheetオブジェクトのgetRangeメソッドを使うのですが、これは引数の値によって取れるデータが変わります。

1. セル名を指定する方法
2. セルの範囲で指定する方法
3. 行列で指定する方法
4. セルと取得する行数を指定する方法
5. セルと取得する行数、列数を指定する方法

これ結構分かりづらいですよねｗ
多分コード見ても微妙だと思います。。が一応コード。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得
 let sheetByActive = spreadSheetByActive.getActiveSheet()
 
 // rangeオブジェクトの取得
 
 // 1. セル名を指定する方法
 let ragetByCellName = sheetByActive.getRange("A1")
 
 // 2. セルの範囲で取得する方法(A1 〜 C3まで)
 let rangeByCellRange = sheetByActive.getRange("A1:C3")
 
 // 3. 行列で指定する方法(A1を指定する)
 let ragetByRowColumn = sheetByActive.getRange(1,1)
 
 // 4. セルと取得する行数を指定する方法(A1から3行分取得)
 let rangeByRowColumnNumRows           = sheetByActive.getRange(1, 1, 3)

 // 5. セルと取得する行数・列数を指定する方法(A1から3列分、3行分取得)
 let rangeByRowColumnNumRowsNumColumns = sheetByActive.getRange(1, 1, 3, 3)
 
 Logger.log(ragetByCellName.getValues()) 
 Logger.log(rangeByCellRange.getValues())    
 Logger.log(ragetByRowColumn.getValues())
 Logger.log(rangeByRowColumnNumRows.getValues())
 Logger.log(rangeByRowColumnNumRowsNumColumns.getValues())
}

結果はこんな感じになります。

非常に分かりづらいですよねｗ

図解してみます。

1. セル名を指定する方法

A1とかで取得するのは、普通にEXCELでも使うので直感的ですよね。

2. セルの範囲で指定する方法

これもEXCELで使うので大丈夫そうですね。
(GASがデフォルトで日付のアウトプットが文字列じゃないので、上のログだとMon Jun 01 00:00:00 GMT+09:00 2020と表示されちゃってます)

3. 行列で指定する方法

ここから少し分かりづらいですね、getRange(行の番号, 列の番号)と指定してます。今回は1行目の1列目を指定してるので【日付】が取得できてます。

4. セルと取得する行数を指定する方法

これは、getRange(行, 列, 取得する行数)と指定します。
今回は、1行目の1列目から、3行分バーっと取得する。って意味ですね。

5. セルと取得する行数、列数を指定する方法

最後は、getRange(行, 列, 取得する行数, 取得する列数)と指定します。
今回は、1行目の1列目から、3行分、3列分をバーっと取得する。って意味です。

普通にセルの値(A1とか、C1とか、A1:C3とか)なら分かりやすいのになんでわざわざ数字で取得する必要があるのでしょうか？

例えば、行数が毎回入力する人によって変わるとします。
その場合、A1:C3のような指定はできません。
(もしかしたら4行目以降が入ってくるかもしれないですからね)

よく使うのが、2行目から、存在する最後の行までとってくる。という指定です。

sheetオブジェクトには、「getLastRow() = そのシートの最後の行数」「getLastColumn() = そのシートの最後の列数」というメソッドがあるので、これを使います。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得
 let sheetByActive = spreadSheetByActive.getActiveSheet()
 
 // rangeオブジェクトの取得 (2行目の2列目から、最終行、最終列まで取得)
 // 横浜、¥235500 ~ 15行分とってきてほしい
 let range = sheetByActive.getRange(2,2,sheetByActive.getLastRow(), sheetByActive.getLastColumn())
 
 Logger.log(range.getValues()) 
}

最初の[20-06-25 22:06:57:149 JST]ってのは実行時間なので気にしないでください。

ちょっと気になるのが、全ての配列に[横浜, 2355000, ]と、最後に謎のカンマが入ってるのと、最後の表示に[,,]という空の配列が入ってますよね。
これは、このシートのgetLastColumnは3を返して、getLastRowは16を返します。

すると、2列目から3列分。(B列からD列まで)
2行目から16行分(2行から17行まで)
を取得しちゃいます。

今回、D列と、17行目は無いですよね？だから空が入ってしまってるのです。

以下のように、はじめる行数・列数分を引く必要があります。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得
 let sheetByActive = spreadSheetByActive.getActiveSheet()
 
 // rangeオブジェクトの取得 (2行目の2列目から、最終行、最終列まで取得)
 // 横浜、¥235500 ~ 15行分とってきてほしい
 let range = sheetByActive.getRange(2,2,sheetByActive.getLastRow() - 1 , sheetByActive.getLastColumn() - 1)
 
 Logger.log(range.getValues()) 
}

できましたね。

これはよく使う(ハマる)ので覚えておきましょう！

スプレッドシートに値を挿入してみる

さて、ではシートに値を挿入するにはどうしたら良いでしょうか？

挿入には、rangeオブジェクトのsetValueを使います。

1. 値を入れたいセルのrangeオブジェクトを取得して
2. そのrangeオブジェクトにsetValueで値を入れる

と、覚えておけばOKです。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得
 let sheetByActive = spreadSheetByActive.getActiveSheet()
 
 // rangeオブジェクトの取得 (1行目の3列目 = 売上)  
 let range = sheetByActive.getRange(1,3)
 
 // 売上だったセルを「売上2」にする
 range.setValue("売上2")
 
 // A4のセルに売上3を挿入してみる
 let rangetD4 = sheetByActive.getRange(1, 4).setValue("売上3")
}

こんな感じですね。setValueは、一度入れた変数に指定もできますし、getRagen(1,4).setValue("売上3")のように連続して指定することもできます。

無事に上書きも挿入もできましたね。

rangeオブジェクトにはsetValuesを使って、例えばA2:C3の値を一気に挿入する。ということも可能です。

この辺は次回以降勉強していきましょう！

さいごにお願い

今後のモチベーションのためにもお願いします！！

もしこのnoteが参考になったら....

■ 最後にたった2つのお願い
① このnoteを「スキ」してください 🙇‍♂️
サポートしてくれたら泣いて喜びます！直接DMでお礼させていただきます！！
② このnoteの感想、何でも良いので @riman_skillplus 宛にください 🙇‍♂️