VBAデバッグの鬼

Excelでできることはスプレッドシートでもやりたい！VBA対GAS

GAS

【今回の問題】
Excelで作った日報マクロをGoogleのスプレッドシートでも作ってください。

Excelからスプレッドシートに移行するときの勝手の違い

今年6月から新しい職場で頑張っています。在宅ですけどね。

日報がかなりめんどくさくて、VBAで自動化できる余地が十分にあったので、次の作業を自動化させてもらいました。

  • 一週間分の日付を自動入力（VBA）
  • 一週間分のデータを消去する（VBA）
  • ドロップダウンで時間を入力できるようにする（入力規則）

スタッフさんの反応はとても好意的で、結構喜んでもらえました！
そのうえ、次のようなオファーがありました。
「スプレッドシートでできない？」

これは嬉しいオファーでしたね。勤務時間にGAS（Google Apps Script）の学習をやっていいよ！というオファーですからね(^^♪
身に余るほどのやりがいを感じながら、嬉々として取り組ませていただきました。

VBAはVisual Basic GASはJavaScript

Excelもスプレッドシートも機能としては似たようなものなのですが、マクロのような機能を備えさせるとなると、やり方は全然違います。
そもそもマクロを組む言語自体が違います。

Excelはみなさんご存じVBAでマクロを組みますが、スプレッドシートの場合はGASという言語を使います。
GASという言語というと、語弊があるかもしれません。GASはJavaScriptで書かれているので、遠回しに言うと、スプレッドシートのマクロはJavaScriptで書きます。

実際に、VBAで書いたものと全く同じプログラムをスプレッドシートに実装させてみたのですが、あまりの勝手の違いに頭がくるくるなりました💦

例えばA1セルに「Hello World!」と表示させたい場合、ソースはこんなにも違ってきます。

まずはVBAでやってみましょう。

Sub A1テスト()

Cells(1, 1).Value = "Hello World!"

End Sub

簡単簡単♪

ところが、GASだとこのようになるんですね。

function myFunction() {
  const ss = SpreadsheetApp.getActiveSheet();
  ss.getRange(1,1).setValue("Hello World!"); 
}

なんじゃこりゃ～～～？！！

VBAはセルと入力文字だけ入れてあげれば、簡単に「Hello World!」が表示できますが、それだけのためにGASではなんと長々とコードを書かされてしまうのでしょう💦

GASのコードは2行にまたがっていますが、別に定数ssに代入しなくても

function myFunction() {
  SpreadsheetApp.getActiveSheet().getRange(1,1).setValue("Hello World!"); 
}

このように1行で書いてもいいんですね。
ただ、セルを変更するときなどに備えて、SpreadsheetApp.getActiveSheet()は、定数または変数に代入しておいたほうが使い勝手がいいので最初のコードのようにしました。

しかし、この長いコードにも長所はあるもので、必ずシートを指定してあげなければいけないから、VBAのようにあいまいなセル指定で他のシートで誤作動を起こすなんていうことがないんですね。
慣れてくれば、GASも使い勝手のいい言語かもしれません。

VBA→プロシージャ　GAS→関数

コードをみてお分かりの通り、VBAはSub プロシージャ名()とEnd Subでコードを囲みます。
GASの場合は関数を定義して{}の中にコードを書き込んでいきます。

関数と聞くと、アレルギー反応を起こす方もおられると思いますが(笑)要は、これもfunction 関数名(){ と } で囲まれたプロシージャのようなものだと思うととっつきやすいかもです。

おまけ：入力規則の使い勝手がいいスプレッドシート

自動化したものの箇条書きに「入力規則」という技を使っているの気づいてました？
ドロップダウンリストが作れる便利なあれですが、スプレッドシートを使って感動したのが、コピーしてペーストするときに「入力規則を貼り付け」というものがあるんですね。
Excelにはありません。書式が違うと新たに入力規則をまた作らないといけない煩わしさ。
早くExcelのペーストメニューにも、「入力規則を貼り付け」つくってください。