\ 楽天BLACK FRIDAYセール! /

Excel VBAとGAS(スプレッドシート)の構文を比較してみた!!

目次

この記事の目的

じょじお

この記事は、Google Apps Script(以下GAS)でスプレッドシートを操作するときに、ExcelだとあぁやるんだけどGASでどうやるんだっけ・・・、をまとめてみたやつです。

GASを使う前に6分の壁について理解しよう!

はじめてGASを使う方は、「Google Apps Scriptの6分の壁」というものを知る必要があります。簡単にいうと、実行完了に6分以上の時間がかかるスクリプトはエラーになるということです。それ以上時間のかかる処理は、処理を分割するなどの対策が必要になりますのご注意ください。

6分の壁にぶち当たらないように、下記の高速化を意識しましょう。

  • 自分が書いたマクロがどれくらいの処理時間がかかるのかを計測しましょう。
  • APIへのアクセスを最小回数の使用に抑えましょう。
    • ループ内では何度もセルへアクセスせずにセル範囲でまとめて読み込み・書き込みを行う。

こまかなテクニックについてはインターネット上にいっぱい出てくると思うので興味がある方は探してみてください。

オブジェクトの階層構造

じょじお

オブジェクトの階層構造は、下記のようになっています。

ぽこがみさま

名前こそ違いますが、大体同じです。

Microsoft Excel
Google Spreadsheet
  • Application
    • Workbook
      • Sheet
        • Range(cells)
          • Value
  • SpreadSheetApp
    • SpreadSheet
      • Sheet
        • Range(Cells)
          • Value

Google Apps ScriptとVBAのメソッド・構文比較

じょじお

ここから目的別に構文を比較していきます。

ブックを開いて作業シートを指定する

dim bk as workbook
dim st as worksheet
'ブックを開く
set bk = workbook.open("c:\hoge.xlsx")
'シートを取得する
set st = bk.worksheets("Sheet_name")
//ブックを取得1(スプレッドシートIDを使って取得)
var spreadsheet = SpreadsheetApp.openById("SpreadSheet_id"); 
//ブックを取得2(アクティブブックを取得)
var spreadsheet = SpreadsheetApp.getActive(); 
//シートを取得する1(シートインデックスを使って取得)
var st = spreadsheet.getSheetByName("Sheet_name");
//シートを取得する2(アクティブシートを取得)
var st = spreadsheet.getActiveSheet();

セルの範囲の値を取得する

dim var
var = Range("A1").CurrentRegion.Value
// getDataRange()はExcelのusedrangeとほぼ同じ
var vals = st.getDataRange().getValues(); //複数形valuesとすると複数範囲の値を取得
var val = st.getRange("A1").getValue(); //単数形valueとすると1つのセルの値を取得

セルへ値の代入

ActiveSheet.Range("A6").Value = "VALUE"
//複数セルへ”データ”を代入する
getRange(開始セルの行番号, 開始セルの列番号, 範囲の行数, 範囲の列数).setValues(配列);
//例
st.getRange(1,1 ,arr.length, arr.length[0]).setValues(arr);

セルへ”数式”の代入

ActiveSheet.Range("A6").Formula = "=sum(A1:A5)"
sh.getRange(6,1).setFormula("=sum(A1:A5)");

日付の取得

msgbox(year(now)) '年を取得
msgbox(month(now)) '月を取得
msgbox(date(now)) '日を取得
msgbox(hour(now)) '時間を取得

dim mydate as string
mydate = Format(now, "yyyy/mm/dd") 'now は現在日時を取得する関数
msgbox(mydate)
  var date = new Date(); //今日の日付を取得
  Logger.log(date.getFullYear()); //年を取得
  Logger.log(date.getMonth()); //月を取得
  Logger.log(date.getDate()); //日を取得
  Logger.log(date.getHours()); //時間を取得

  date = Utilities.formatDate(date, "JST", "yyyy/MM/dd")
  Logger.log(date);

▲年を取得するにはgetYearもあるが、バグになりやすいのでまずはgetFullYear()を使っておけばいいとおもいます。

日付の計算

  msgbox(DateAdd("d", 2 ,date)) '2日後
  msgbox(DateAdd("yyyy", -1 ,date)) '1年前
  var date = new Date();
  var dateAfterSevendate = new Date(date.getFullYear(), date.getMonth(), date.getDate() + 7); //7日後
  var dateBeforOneYear = new Date(date.getFullYear() -1, date.getMonth(), date.getDate()); //1年前

最終行・最終列を取得する

Dim maxrow As Long, maxcol As Long
With ActiveSheet
    '最終行
    maxrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    '最終列
    maxcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
var st = SpreadsheetApp.getActive(); //アクティブシートを取得
//最終行
var maxrow = st.getLastRow();
//最終列
var maxcol = st.getLastColumn();

連想配列(辞書・ディクショナリー)

連想配列(辞書)の初期化と追加

 '事前バインディングの場合は、参照設定から紐づけが必要
 dim mydic as Dictionary '定義
 set mydic = new Dictionary '初期化
var mydic = {}; //初期化
mydic['key'] = "value"; //辞書へ追加
console.log(mydic['key']); //辞書へ問い合わせ

配列(リスト)

配列の初期化、配列に要素の追加

//下記の1か2のいずれかで配列を作成(初期化)します。お好きな方をお使いください。

var arr = new Array(); //パターン1
var arr = []; //パターン2


//配列に要素を追加します。
arr.push(”要素”);

多次元配列の各次元の要素数を調べる。

Dim arr(10, 3) As String
Dim arrLenth1 As Long, arrLenth2 As Long
arrLenth1 = UBound(arr, 1) '1次元目の要素数
arrLenth2 = UBound(arr, 2) '2次元目の要素数
MsgBox (arrLenth1 & vbNewLine & arrLenth2)
'msgboxには10と3と表示される
var arr = [];
arr = st.getDataRange().getValues();
var arrLength1 = arr.length;
var arrLength2 = arr.length[0];

シート内の全セルのデータを一括削除する

ActiveSheet.cells.ClearContents '値だけ消す。
ActiveSheet.cells.Clear '書式も含めて値を消す。
st.clearContents();

コメントアウト

'シングルクォーテーションでコメントアウトできます。このように。
//GASのコメントアウトは、Javascriptと同じく"//"スラッシュ2個です。

/*
//このようにすれば複数行改行することも出来ます。
*/

コード内で改行

' VBAは"_"(アンダーバー)で改行です。

'例
thisworksbook.Worksheets("Sheet1").range("A1")  _
= "1234"
//gasは、javascriptと同じく特に改行コードはありません。
//セミコロンがステートメントの終わりを表すので、
//セミコロンの前であれば、スペースが入ってよいところで自由に改行できます。

ログ出力

debug.print str
//1 プログラム作成時のデバッグ時に使うやつ
Logger.log(str) 

//2 トリガー実行時などでも使えるやつ
console.log(str)
console.log
Logger.log
  • スクリプトエディタからログ出力できる。
  • Webアプリやトリガー実行時にログ出力できる。
  • スクリプトエディタからログ出力できる。
  • Webアプリやトリガー実行時にログ出力できない。

Logger.logはWebアプリやトリガー実行時にログ出力してくれないので、console.logを使えばいいかなと思います。

エラー処理

//下記のOn Error~文で「ここからエラーを無視してね」という宣言をします。
On Error Resume Next

//エラーが起きそうところの直後でエラーナンバーをチェックしてエラー処理を書きます。
//エラーナンバーが0で無ければエラーが発生していますのでif文の中にエラー発生時の処理を書きます。
If Err.Number <> 0 Then
  MsgBox Err.Description & vbnewline & "エラーが発生しました。入力値を確認してください。"
  Err.Clear
End If
//Javascriptと同じくtry-catchでエラー処理します。
//tryの中にエラーが起こりそうな処理を書き、catchの中にエラー発生時の処理を書きます。

 try{
    //①必ず実行する処理
    var hogehoge = 処理
  }catch(e){
    //①がエラーだった場合に実行する処理
    hogehoge = e + "というエラーが起きたよ!" ;
  }
    console.log(hogehoge);

GASでよく見るエラーと対処方法

エラー:exception: The parameters (number[]) don’t match the method signature for SpreadsheetApp.Range.setValues. (line #, file hogehoge )

GAS初心者さんが良く躓くポイント。setValuesの引数に普通の配列を渡して上記エラーが発生する。setValuesが受け付けるのは2次元配列です。普通の配列(1次元配列)は上のようなエラーになります。

1次元配列を手っ取り早く2次元配列にするには、1次元配列を[](角カッコ)で囲えばOKです。

Google Apps Script関連記事

▲GASをはじめて作成する前にみてください。

GASのおすすめの本とUdemy講座

書籍で学ぶなら、GAS本最強のこちらがオススメ!

GAS初学者の方がテキスト1冊買うならこれ一択です。幅開く網羅されているので基本が身に付きます。

動画で学ぶならコチラがオススメ!

動画で学ぶならコチラがおすすめです。UdemyのGAS講座はいくつかありますが、他の講座と比較しても圧倒的レビュー数と高評価です。万が一の返金保証があるので安心して購入ができます。

書籍で学ぶなら、GAS本最強のこちらがオススメ!

GAS初学者の方がテキスト1冊買うならこれ一択です。幅開く網羅されているので基本が身に付きます。

動画で学ぶならコチラがオススメ!

動画で学ぶならコチラがおすすめです。UdemyのGAS講座はいくつかありますが、他の講座と比較しても圧倒的レビュー数と高評価です。万が一の返金保証があるので安心して購入ができます。

Google関連のおすすめの本

▲Google Apps Scriptの入門書として間違いのない一冊です。ノンプログラマーの方にもわかりやすく解説されています。V8ランライム対応版にアップデート済みため情報も新しいです。

▲こちらGoogle Apps Scriptの本ではないですが、Google Workspace(旧G Suite)を自動化したりアプリ化するには、Google AppSheetという選択肢もあります。Google AppSheetはノーコードでアプリを作成できます。

▲Google for Educationの使い方にとどまらず実際の運用ででてくる問題への対処方法などもかかれていて面白いです。教師の方達の共著なので現実的な内容となっています。

お役に立てたらシェアお願いします!
  • URLをコピーしました!
  • URLをコピーしました!
目次