Google Apps Script(GAS) 超入門① - スプレッドシートの値をとってみる

かるく背景

非エンジニアでも簡単に、サーバーも無しで無料で使える超便利なGoogle Apps Scripts。通称GAS。
非常に敷居は低いですが、やっぱり初めて「スクリプトエディタ」を見ると"おえっ"という感じになる人は多いハズ。

そこで、超簡単にGASの使い方のイメージをつかんで読者さんが自分でも触れるようにしよう！
という目的で記事書いてます。

目標

• GASを使うための、スプレッドシートへのアクセス方法を知る
• GASを使ってスプレッドシートの値を好きなように取れるようにする

スプレッドシートを使うときのイメージ

GASでスプレッドシートを使うとき、この3つの概念を頭に入れておくと、超イメージ湧きやすいです。

• SpreadSheetオブジェクト
  • スプレッドシートを扱う
  • スプレッドシートに対して、シートを作ったり消したりするときに使います
• sheetオブジェクト
  • シート(「シート1」とか「シート2」)を扱う
  • シートの中身に対して範囲を指定するとか、行の追加・削除とかに使います
• Rangeオブジェクト
  • 範囲 (普段関数で使うA2とかA2:B3みたいなものです)
  • 特定のセルの値を取得したり、入力したりします

オブジェクトって何？って思うかも知れませんが、あまり気にしなくても使うことは出来ますし、難しく考えすぎるとまた敷居が高くなります。
今はシートから値を取り出すために必要なもの。くらいに考えておきましょう。

使い方

基本的に、SpreadSheetオブジェクト > sheetオブジェクト > Rangeオブジェクトとアクセスしてきます。
これは普通にスプレッドシートを使ってるときと同じ考え方です。
例えば、「営業日報を入力する」シーンを考えてみましょう。

• まず「営業日報」というスプレッドシートを開きますね
  • これが、SpreadSheetオブジェクトです
• 次に、「山本」という自分の名前が書かれたシートを選択します
  • これがSheetオブジェクトです
• 最後に、今日に日付の行の3列目(C4とか)に今日の受注件数を入力します
  • これがRangeオブジェクトです
  • Rangeオブジェクトを使って、C4というセルに(セルの情報を取得)、受注件数を入力します(セルに値をセット)

簡単な気がしてきますね。
じゃあ、実践していきましょう。

1. シートを使えるようにします

まず、スプレッドシートを作ります。
こんな感じで、B列に名前、C列に生年月日が入ってるものでも用意しておきましょう。

そして、「ツール」>「スクリプトエディタ」をクリックして、Google Apps Scriptsを開きましょう。

この画面が出たらOKです。

まずは、シートを使える状態にしてみましょう。
sheetオブジェクトを使える状態にするためには、shpreadSeetオブジェクトからsheetオブジェクトを作ります。

手始めに、B3セルの「大谷」を取得するスクリプトを書いてみます(↓をコピペしてください)

function myFunction() {
  // スプレッドシートオブジェクトを作る
  var spreadSheetByActive = SpreadsheetApp.getActive();

  // シートにアクセスする
  var sheet = spreadSheet.getActiveSheet();
  
  var name = sheet.getRange('B3').getValue();
  Logger.log(name);  
}

スプレッドシートからGASファイルを作った場合はこの方法が１番シンプルです。

早速実行してみる

一回作ったらまずは使ってみたいですよね。
最初は承認とかいろいろ必要なので画像で説明していきます。

1. Ctrl+Sでファイルを保存

適当に名前をつけて「OK」

2. 実行！！

画面の上部にある「再生ボタン」みたいなやつクリック

3. 承認してく

最初この画面が出るので、「許可を確認」をクリック

自分のGoogleアカウントをクリック

警告が出るので、「詳細を表示」をクリック

xxxxに移動をクリック

これはGASがスプレッドシートにアクセスして読み書きする事を許可しますか？
と聞いてるので、「許可」をクリック(これで名前が取得出来ます)

4. 実行結果を見てみる

「表示」>「ログ」をクリックすると、ログのダイアログが開きます。

ちゃんと、B3列に表示されてる大谷が取得出来てます。

もうこれでやりたい事は出来ましたね！！

スプレッドシートへのアクセスの仕方

スプレッドシート(今回は「gas入門①」というスプレッドシート)にアクセスするには、今回使ったgetActive以外にも4つの方法があります。

function myFunction() {
  // スプレッドシートオブジェクトを作る
  var spreadSheet         = SpreadsheetApp.getActiveSpreadsheet(); // ① アクティブなシートを指定
  var spreadSheetByActive = SpreadsheetApp.getActive(); // ② アクティブなシートを指定
  var spreadSheetById     = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxx'); // ③ スプレッドシートのIDで指定
  var spreadSheetByUrl    = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx/edit#gid=0'); // ④ スプレッドシートのURLで指定
}

アクセスの仕方	説明
① アクティブなシートを選択	このGASファイルを作ったスプレッドシート(今回はGAS入門①)を選択
② アクティブなシートを選択	このGASファイルを作ったスプレッドシート(今回はGAS入門①)を選択 (①同じです)
③ スプレッドシートのIDで指定	スプレッドシートのURLのeditより前の文字列でアクセス
④ スプレッドシートのURLで指定	スプレッドシートのURLをまるごと指定してアクセス

見て分かる通り、①と②は、スプレッドシートからGASファイルを作った場合に使います。
ただ、①と②は同じ結果なので、②のgetActiveのほうが短くて良きです。

また、③と④はスプレッドシートに紐付けずにGASファイルを作った場合に使います。
「カレンダーの情報を取得して、特定のスプレッドシートにカキカキ」とかしたくなったら使うので今度説明します。

シートへのアクセスの仕方

じゃあ、スプレッドシートにアクセスする方法が分かったので、次はシートにアクセスする方法を学びましょう。
シートってのは、今回は「シート1」を使いましたが、「シート2」を使うときとかにどうするか？という話ですね。

余談. なんでgetActiveSheetが使えたのか

↑の例では、getActiveSheet と書いたら「B3」の値として「大谷」が取得出来ました。
これは「今アクティブなシート」 = 「今選択中のシート」という意味だったわけです。
たまたま、「シート1」を選択してる状態で実行したので「大谷」が取れてたので、「シート2」を選択した状態で実行するとどうなるか見てみましょう。

一旦スプレッドシートに戻って、「シート2」を選択して、もう一度スクリプトエディタから「実行」してみましょう。

シート２のB3は何も書かれてないので、ログには「大谷」が表示されません。

じゃあgetActiveSheetだと何か危ない気がしますね。他にどんな方法があるのでしょうか。
取得方法は2つで、今回のgetActiveSheetかシート名で指定するgetSheetByNameの2択です。

function myFunction() {
  // スプレッドシートオブジェクトを作る
  var spreadSheet = SpreadsheetApp.getActive();
   

  // シートにアクセスする
  var sheet       = spreadSheet.getActiveSheet(); // ① アクティブなシートを指定
  var sheetByName = spreadSheet.getSheetByName("シート1"); // ② シート名で指定
  
  // getActiveSheetで指定したときの結果  
  var name = sheet.getRange('B3').getValue();
  Logger.log("getActveSheetの結果 => " + name); 

  // getSheetByNameで指定したときの結果  
  var name = sheetByName.getRange('B3').getValue();
  Logger.log("getSheetByNameの結果 => " + name);   
}

これで、「シート2」を選択した状態で実行してみましょう。

getActiveSheetで取得すると「シート2」のB3を取りにいくので結果が空です。
ただ、getSheetByNameで取得した場合は「シート1」のB3を取りに行くので「大谷」が取れてます。

スプレッドシートの値を取得する

実はもう「大谷」で取得してましたが、もう少し最初のSpreadSheetオブジェクトとSheetオブジェクト、Rangeオブジェクトに分けて考えてみましょう。

function myFunction() {
  // スプレッドシートオブジェクトを作る
  var spreadSheet = SpreadsheetApp.getActive();
   
  // シートにアクセスする
  var sheet       = spreadSheet.getActiveSheet();
  
  // 範囲(Range)にアクセスする
  var range = sheet.getRange('B3');
  
  var name = range.getValue();
  
  Logger.log(name); 
}

この例ではこんな感じでした。

簡単に解説しておくと

• SpreadSheetApp.getActive()でスプレッドシートオブジェクトを作って
• そのスプレッドシートオブジェクトから、getActiveSheetでシートオブジェクトを作って
• シートオブジェクトから、getRangeでRangeオブジェクトを作って

で、最後にその値を取得してました。

もっといろんな値を取得してみる

今回、値はB3を直接指定しましたが、この取得方法も2種類あります。
一つは、getRange("セルの番号")とする方法。
もう一つは、`getRange("行の番号", "列の番号")とする方法。

後者の方は、行の番号に何行目、列の番号に何列目かを入れるので、B3を取得したい場合は、 3行目の2列目(B列は左から数えて2列目なので)`を指定すれば同じ結果が取得出来ます。

function myFunction() {
  // スプレッドシートオブジェクトを作る
  var spreadSheet = SpreadsheetApp.getActive();
   
  // シートにアクセスする
  var sheet       = spreadSheet.getActiveSheet();
  
  // 範囲(Range)にアクセスする
  var range      = sheet.getRange('B3');
  var rangeByNum = sheet.getRange(3, 2); // 3行目の2列目(B3)にアクセス 
  
  // B3の取得結果
  var name = range.getValue();  
  Logger.log("B3の取得結果　=> " + name); 
  
  
  // 3,2と指定した取得結果
  var name = rangeByNum.getValue();
  Logger.log('3,2の取得結果 => ' + name);
}

同じ結果が得られましたね。

次回予告

今回のでスプレッドシートの値を自在に取得できるようになったので、自分でもいろいろできる気がしてきましたね
例えば、スプレッドシートの値を取得(営業の数値とか)して、その結果を毎朝8時にチャットする。とかも出来ますよ。

さて、次回はもっと柔軟に使っていくために、スプレッドシートの値を複数一気に取得する方法と、スプレッドシートに値を入れる方法を紹介していきます。