Go to Qiita Advent Calendar 2024 Top
LoginSignup
5
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@query1000

はじめてのGAS

Last updated at Posted at 2019-11-12

ExcelからGoogle Spreadsheetに移行した。
これからは、GAS（Google Apps Script）による自動化をバリバリやってみようと思う。
ここでは、初めてのGASの作成の模様を、自分用メモとして書く。

2020-03-31追記

下の記事では、以下の手法でスクリプトを作っている。

  • スプレッドシートを新規作成
  • [ツール] => [スクリプトエディタ]

記事寄稿時はこれでスクリプトプロジェクトが勝手にスプレッドシートに結びついていた。
つまり、

  • スプレッドシートからスクリプトエディタを開くと前に編集したプロジェクトが開く
  • スクリプトを実行するとスプレッドシートに作用が起こる

このようなありようをコンテナバウンドという。

しかし、2020-03-31に上記のやり方で試してみたら、なんかうまくいかず、スクリプトプロジェクトがスプレッドシートとは独立に出来る。
バインドの仕方もわからない。

しょうがないから、スクリプトエディタから [File] => [New] => [Spreadsheet] を作るという逆の方法を取った。
これで、なんとなくコンテナバウンドなスクリプトができた。
ワークアラウンドだ。
正しい方法は分からない。

目標

以下のようなスプレッドシートがあったとする。
SnapCrab_NoName_2019-11-12_17-47-39_No-00.png

これを

  • １行目の背景：濃い青
  • １行目の文字色：黄色
  • 以下偶数行目の背景：薄い青
  • 以下奇数行目の背景：中くらいの青

のように設定を与えると、ずーっとシマシマに塗っていくというものだ。
こうなって欲しい。
SnapCrab_NoName_2019-11-12_17-49-4_No-00.png
では行ってみよう。

以下はおいらがやりやすいやり方を取っているだけで、この通りやらないといけないわけじゃない。
もっと頭がいい人はエッセンスだけ汲み取ってくれてもいいし、改善点を指摘してくださってもいい。
また、盲目的に以下の手順に従っても、GASのバージョンが変わった、おいらの環境とおたくの環境が違う、おいらの記述に不備があったなどの理由で、うまく動かないかもしれない。
かなりレアケースで、何らかの不利益がもたらされるかもしれないが、そこは自己責任でお願いします。。

0. 作業環境を用意する

おいらの環境は以下の通り。

  1. Windows 10 Pro 64 bit
  2. Google Chrome Canary（会社用は無印Chrome、自分用はCanaryで分けた）
  3. 自分用のGoogleアカウント

1. スプレッドシートを準備する

※この項目にGASの知識は１ミリも出てきません。

  1. Googleホームページで■が３×３個並んでいるアプリメニューをクリックし、ドライブを選択する
    SnapCrab_NoName_2019-11-12_12-49-26_No-00.png

  2. マイドライブ画面で、＋新規をクリックする
    SnapCrab_NoName_2019-11-12_12-49-48_No-00.png

  3. スプレッドシートをクリックする＝＞無題のスプレッドシートが出来る（以下シート画面と呼ぶことがある）
    SnapCrab_NoName_2019-11-12_12-50-3_No-00.png
    SnapCrab_NoName_2019-11-12_12-50-47_No-00.png

  4. 「無題のスプレッドシート」というタイトルをダブルクリックして「GAS Study」などと変名する
    SnapCrab_NoName_2019-11-12_13-6-24_No-00.png

  5. アドレスバーの★ボタンを押してブックマークバーに「GAS Study」としてブックマークする

  6. シート１に色付き前のテストデータを作る。

  7. シート１のシート名を右クリックしてコピーを作成を選ぶ作業を２回行う
    　（テストデータを取っておくため。１回だと間違えて壊すこともあるから）
    SnapCrab_NoName_2019-11-12_13-1-17_No-00.png

  8. この状態で自分に取っての準備完了。
    SnapCrab_NoName_2019-11-12_13-8-7_No-00.png

2. Hello Worldとダイアログ表示する

  1. ツール→スクリプトエディタを選択する（以下スクリプト画面と呼ぶことがある）
    SnapCrab_NoName_2019-11-12_13-19-53_No-00.png
    ＝＞無題のプロジェクトというプロジェクト、コード.gsというファイル、myFunction()という関数が自動で出来ている
  2. myFunctionをhelloDialogと変更する
  3. {}の間に以下のコードを書く。
  Browser.msgBox("Hello World");

※2022-01-18追記：上記のコードを動かすと「Browser.msgbox is not a functionと言われた。
Referenceを見ると、msgboxは非推奨だからui.alertを使えと言われた。
https://developers.google.com/apps-script/reference/base/browser?hl=en#msgBox(String)

ui.alertのリファレンスがコチラ。
https://developers.google.com/apps-script/guides/dialogs?hl=en#alert_dialogs

コードは以下のようになる。

function helloDialog(){
  var ui = SpreadsheetApp.getUi(); // Same variations.
  ui.alert("Hello World");
}

＝＞未保存の編集がある場合はコード.gsタブ名の左横にダーティサイン（赤い＊）が表示されている。
SnapCrab_NoName_2019-11-12_13-26-6_No-00.png
4) Ctrl+Sで保存しようとしたら、プロジェクト名の命名を求められたので、GAS Studyとした。
SnapCrab_NoName_2019-11-12_13-25-11_No-00.png
SnapCrab_NoName_2019-11-12_13-26-47_No-00.png
＝＞保存が起こり、ダーティサインが消え、プロジェクト名が変わった。
5) シートのタブとスクリプトのタブを分離し、左右に並べた。
SnapCrab_NoName_2019-11-12_13-30-8_No-00.png
※実際に現象が起こるシート画面とスクリプト画面を分けたほうが見やすいため
6) エディタ画面の虫アイコン（デバッガー）の右横で、helloDialogという現在唯一の関数が表示されていることを確認する
SnapCrab_NoName_2019-11-12_13-30-8_No-00.png
7) 虫の左横のプレイボタン（▶）をクリックする＝＞初回だけAuthorization requiredというダイアログが表示される
8) 「許可を確認」をクリックする
foobar.png
9) 「アカウントの選択」が表示されるので、自分のアカウントを選択する
10) 「▲このアプリは確認されていません」と警告されるので「詳細」をクリックする
11) 下に表示される「GAS Study（安全でないページ）に移動」をクリックする
12) 「アカウントへのアクセスをリクエストしています」と表示される。下の方の「許可」をクリックする
＝＞エディタ画面に「Running function helloDialog...」と表示され、シート画面に「Hello World」と表示される。やったー
SnapCrab_NoName_2019-11-12_13-38-43_No-00.png
13) シート画面でOKをクリックしてダイアログを閉じる

3. 現在のセルの背景色を塗る

いよいよ実のあるプログラミングを行う。
やりたいことをググりまくって、いろんなページからコピペしまくって、試行錯誤でコードを書いた。
参考のページはリンクを貼るのがスジだが、あまりにもいろんなページを渉猟しまくって訳が分からなくなってしまったし、（あくまで好みの問題で）しょうじき紹介するには微妙なページもあるので割愛。
スミマセン。

ま、最終的には公式のリファレンスを参考にすることになるだろう。
https://developers.google.com/apps-script/reference/spreadsheet

まず、現状カーソルがあるセルを青く塗ってみる。

  1. エディタ画面に以下のように入力する。
function paintHereBlue () {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  cell.setBackground("blue");
}
  1. Ctrl+Sでダーティサインを消す
  2. 虫の右の関数名をpaintHereBlueに変える
    SnapCrab_NoName_2019-11-12_13-47-5_No-00.png
  3. シート画面で塗りたいセルをクリックする（左ではA1をクリックした）
    SnapCrab_NoName_2019-11-12_13-47-36_No-00.png
  4. エディタ画面でプレイボタンをクリックする
    ＝＞セルが青くなる。やったー
    SnapCrab_NoName_2019-11-12_13-48-38_No-00.png

プログラムの解説を試みる

ここでプログラムの解説を試みる。
おいらGASもJavaScriptもシロートだから真に受けないでください。

function paintHereBlue () {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  cell.setBackground("blue");
}

paintHereBlueは関数名だ。
()には引数が書けるけど上では何も渡していない。

varは変数を宣言する。
ここではアクティブシートをsheetという変数に入れることにした。
=は代入を行う。

SpreadsheetAppは組み込みのクラス名だ。
人のプログラムを見ていて分からないものが出てきたら、公式リファレンスで虫眼鏡（🔎）アイコンのついた検索ボックスで検索しておくと良い。
SnapCrab_NoName_2019-11-12_14-5-58_No-00.png
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

getActiveSheet()はSpreadsheetAppクラスのアクティブシートを得るメソッドだ。
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactivesheet

setBackground()はrangeオブジェクトで背景色を塗るメソッドだ。
https://developers.google.com/apps-script/reference/spreadsheet/range#setBackgrounds(String)

色は"red"、"green"などのフレンドリーな名前で指定することも、#ffffff（真っ白）のようなRGB値で指定することも出来る。

なお、varキーワードと;の間に、複数の変数定義と代入を入れることも出来る。
その場合、各定義の末尾には,を書く。
上のコードはこうも書けた。

function paintHereBlue () {
  var sheet = SpreadsheetApp.getActiveSheet(),
      cell = sheet.getActiveCell();
  cell.setBackground("blue");
}

どっちがいいんだろ。

スクリプトエディタのエラー

なお、スクリプトエディタはなかなかインテリジェントで、行頭でタブを押すと正しくインデントされるし、ユーザー変数は最初は鮮やかな青で２回目からはくすんだ青など、工夫をこらして表示されるので、間違いは少ない。
文法レベルで間違ったコードを保存しようとすると、怒られて保存が出来ない。
下は;と間違えて:と書いて保存しようとしたところ。
SnapCrab_NoName_2019-11-12_14-22-6_No-00.png
正しく書き直してCtrl+Sを押すとエラーが消える。

4. 現在のセルの文字色を変える

同様に文字色を変えるのはsetなんとかじゃないだろうかと思ってrangeオブジェクトのメソッドを見回していたら、setFontColorだった。

function paintHereBlueAndYellow () {
  var fontColor = "yellow",
      background = "blue",
      sheet = SpreadsheetApp.getActiveSheet(),
      cell = sheet.getActiveCell();
  cell.setBackground(fontColor);
  cell.setFontColor(background);
}

２度目以降のテストを行う場合、シート画面でCtrl+Zを押すと、Undoされて、元通りの白バックに黒文字に変わる。
エディタ画面でプレイボタンがグレイになる場合は、虫の右が「関数を選択」となっているので、実行したい関数（ここではpaintHereBlueAndYellow）に変えてやる。
実行。

■

スクショを取り忘れたので割愛するが、できでた。
なお、上のプログラムでは見やすさのために色の設定を行うfontColorとbackgroundという変数を用意して、先頭に出してみた。

スクリプトエディタの補完

fontColorなどという長いつづりをいちいち打ち込んだり、コピペするのは大儀である。
スクリプトエディタでは、fonとか途中まで書いてAlt+/を押すと、Emacsライクな動的補完が起こり、fontColorと書いてくれる。
便利！

5. 背景色を知りたい

これまではblue、yellowという色で察しがついたけど、他の色は英語で何ていうんだろう。
リファレンスの検索ボックスで「blue yellow white black red」と検索してみると、偉いものでEnum Colorという項目に当たった。

ううーん、でもなんか足りないなぁー。
もっと淡い色に塗りたい。
そこで、まずセルを塗ってから現在のセルの背景色を知るマクロを作る。

function showBackgroundHere () {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  Browser.msgBox(cell.getBackground());
}

明細Ａ１をちょっと薄い色にして選択し、上の関数を走らせた。
またスクショをとりはぐれたが、「#cfe2f3」だと分かった。
同様に明細Ａ２を少し濃い目にして選択し、「#9fc5e8」だと分かった。

6. 最終セルを知りたい

最終セル（データが入っている行数と列数の最大値）を知りたい。

ところで、完全な閑話だが、日本語のエクセル（および行列演算）用語で横の並び（row）を行という。
縦の並び（column）を列という。
これは行の右のテみたいなところに注目して、横に線が走ってるから行と言う。
同様に列の右のリみたいなところに注目して、縦に線が走ってるから列と言う。
そうやって覚えればいいと高校の先生に習った。
そんなことはどうでもいい。

function showLastCell () {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastrow = sheet.getLastRow();
  var lastcolumn = sheet.getLastColumn();
  Browser.msgBox("行" + lastrow + "列" + lastcolumn);
}

SnapCrab_NoName_2019-11-12_15-44-55_No-00.png
5行3列であると分かった。

7. セルの範囲を塗りたい

これまでは単一のセルを塗っていたが、範囲、たとえば4行1列から、4行3列までの3セルを塗りたいときはどうするか。
試行錯誤は省略するが、引数4つのgetRange()を使う。

function paintRowPale () {
  var background = "#cfe2f3",
      row = 4,
      column = 3,
      sheet = SpreadsheetApp.getActiveSheet(),
      range = sheet.getRange(row, 1, 1, column);
  cell.setBackground(fontColor);
}

8. 組み立てる

さあ、さすがに材料が揃っただろう。

  • １行目の背景：濃い青
  • １行目の文字色：黄色
  • 以下偶数行目の背景：薄い青
  • 以下奇数行目の背景：中くらいの青

のように設定を与えると、ずーっとシマシマに塗っていくスクリプトを書く。

function blueStripe () {
  var background_header = "blue",
      fontcolor_header = "yellow",
      background_even = "#cfe2f3",
      background_odd = "#9fc5e8",
      sheet = SpreadsheetApp.getActiveSheet(),
      lastrow = sheet.getLastRow(),
      lastcolumn = sheet.getLastColumn();
  sheet.getRange(1, 1, 1, lastcolumn).setBackground(background_header);
  sheet.getRange(1, 1, 1, lastcolumn).setFontColor(fontcolor_header);
  for (var row = 2; row <= lastrow; ++row) {
      var background;
      if (row % 2 == 0) {
         background = background_even;
      } else {
         background = background_odd;
      }
      sheet.getRange(row, 1, 1, lastcolumn).setBackground(background);
  }
}

SnapCrab_NoName_2019-11-12_16-17-5_No-00.png

9. ピンクバージョンを作る

  • １行目の背景：濃い赤
  • １行目の文字色：白
  • 以下偶数行目の背景：薄いピンク
  • 以下奇数行目の背景：中くらいのピンク

のように設定を与えると、ずーっとシマシマに塗っていくスクリプトを書く。

まず、blueStripeを関数化する。
なお、中間変数rangeを省いて、メソッドを数珠つなぎにしてみた。

function colorStripe (background_header, fontcolor_header, background_even, background_odd) {
  var sheet = SpreadsheetApp.getActiveSheet(),
      lastrow = sheet.getLastRow(),
      lastcolumn = sheet.getLastColumn();
  sheet.getRange(1, 1, 1, lastcolumn).setBackground(background_header);
  sheet.getRange(1, 1, 1, lastcolumn).setFontColor(fontcolor_header);
  for (var row = 2; row <= lastrow; ++row) {
      var background;
      if (row % 2 == 0) {
         background = background_even;
      } else {
         background = background_odd;
      }
      sheet.getRange(row, 1, 1, lastcolumn).setBackground(background);
  }
}

次に、それを呼び出す関数を作る。

function pinkStripe () {
  var background_header = "red",
      fontcolor_header = "white",
      background_even = "#ead1ac",
      background_odd = "#d5a6bd";
  colorStripe(background_header, fontcolor_header, background_even, background_odd);
}

一時変数のbackground_headerとか要らなくて、いきなりリテラル引数でcolorStripe()を呼べばいいような気がするけど、使い方リファレンスを兼ねているので分かりやすい変数名つきで書いてもいい気がする。
実行。
SnapCrab_NoName_2019-11-12_16-26-58_No-00.png
でけた。
色は事前に適当なセルを塗って、showBackgroundHereで調べたけど、塗ってみるとちょっと肌色っぽくてキショイ感じがする。

10. メニューから実行する

以下のコードを貼り付ければオッケー。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('GAS Study');
  menu.addItem('青のシマシマに塗る', 'blueStripe');
  menu.addItem('ピンクのシマシマに塗る', 'pinkStripe');
  menu.addToUi();
}

addItem()の第1引数は表示されるメニューアイテム、第2引数は呼び出される関数だ。
onOpen()はスプレッドシートを開いた瞬間に実行される関数だ。

一度Chromeを閉じて開いてみると、出来てた。
SnapCrab_NoName_2019-11-12_16-35-12_No-00.png

（※スクショではStudyをSrudyって間違えてるけど許してくれ。。）

実行して青く塗り直してみた。
SnapCrab_NoName_2019-11-12_16-35-28_No-00.png
ヤッター

X. その他いろいろ

上記では紹介できなかったことを書く。

  • Browser.msgBox()の代わりにLogger.log()を使うと表示→ログで出てくる実行ログにメッセージが出てくる
  • プログラムの実行速度を測るためには、開始直後と終了直前にLogger.log()でメッセージを出すと、タイムスタンプが出るので大体分かる
  • ループで時間が掛かっている場合、ある条件で周回を飛ばすといいことがある。continueを使う
  • range.getBackground(background)は16進を文字列で返すので、"white"とかと比較してもうまくいかない。"#ffffff"と比較する。"#FFFFFF"もダメ
  • encodeURI()という関数を使うと文字列をURLエンコード出来る
  • メニューは階層化できる

Z. 今後の課題

  • claspというのを使うとローカル（Emacs）で開発が出来る
  • フォームと連動させるとウェブリケーションが出来る
  • 現状自分ひとりで使っているが、人に使わせるにはデプロイということを行う
  • Webスクレイピングも出来る

★★★

GASはスプレッドシートを使えばデータ構造も出来るし、UIもどきも出来るし、JavaScriptでモダンっていうの？に書けていい感じだ。

（この項おわり）

5
3
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?