LoginSignup
20
24

More than 3 years have passed since last update.

@jooji(joji o)

ExcelVBAとGAS(スプレッドシート)の構文比較

Last updated at Posted at 2020-04-29

この記事の目的

この記事は、ExcelのアレってGASでどうやるんだっけ・・・、をまとめているものです。
随時更新中。

GASを使う前に知っておくこと

6分の壁

まずGASを使うにあたり「Google Apps Scriptの6分の壁」というものがあるため、
常にスピード、APIアクセスの回数について意識しなければなりません。

参考「いつも隣にITのお仕事」
https://tonari-it.com/gas-spreadsheet-speedup/

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

  • 自分が書いたマクロがどれくらいの処理速度なのかを知る
    • 計測方法：計測開始したい場所と計測終了したい場所にconsole.logする。
    • あるいは開始と終了にnew Date();して、最後にconsole.logする。
  • APIへのアクセスを最小回数の使用に抑える。特にループ内では。

こまかなテクニックについては、上記のサイト様などにも書かれているので見てみると勉強になります。

オブジェクト

オブジェクトの名称と階層は、両者を比較すると下記のような理解でいます。

　言葉　 　VBA　 　GAS　
アプリケーションオブジェクト　 Application　 SpreadsheetApp　
ブックオブジェクト Workbook SpreadSheet
シートオブジェクト Sheet Sheet
セルオブジェクト Range,Cell Range

メソッド・構文

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

gas
//ブックを取得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();

SpreadSheet_idは下図参照ください。SpreadSheetを特定するためのブック固有の文字列
image.png

VB
dim bk as workbook
dim st as worksheet
'ブックを開く
set bk = workbook.open("c:\hoge.xlsx")
'シートを取得する
set st = bk.worksheets("Sheet_name")

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

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

セルへ値の代入

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

セルへ”数式”の代入

gasでセルへ数式を代入する
sh.getRange(6,1).setFormula("=sum(A1:A5)");
VBAで複数セルへデータを代入する
ActiveSheet.Range("A6").Formula = "=sum(A1:A5)"

日付の取得

gas
  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()を使っておけばいいとおもいます。

vb
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)

日付の計算

gas
  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年前
vb
  msgbox(DateAdd("d", 2 ,date)) '2日後
  msgbox(DateAdd("yyyy", -1 ,date)) '1年前

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

gas
var st = SpreadsheetApp.getActive(); //アクティブシートを取得
//最終行
var maxrow = st.getLastRow();
//最終列
var maxcol = st.getLastColumn();
vb
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

連想配列（辞書）

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

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

配列

配列の初期化と追加

gas
//初期化　下記のパターン1or2

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

arr.push(”要素”);

多次元配列の各次元の要素数を知る

gas
var arr = [];
arr = st.getDataRange().getValues();
var arrLength1 = arr.length;
var arrLength2 = arr.length[0];
VBA
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と３と表示される

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

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

コメントアウト

gas
//GASのコメントアウトは、Javascriptと同じく"//"スラッシュ2個です。

/*
このようにすれば複数行改行することも出来ます。
*/
VBA
'シングルクォーテーションでコメントアウト

改行

gas
//gasは、javascriptと同じく特に改行コードはありません。
//セミコロンがワンステートメントの終わりを表すので、
//セミコロンの前であれば、スペースが入ってよいところで自由に改行できます。
VBA
' VBAは"_"（アンダーバー）で改行です。

'例
thisworksbook.Worksheets("Sheet1").range("A1")  _
= "1234"

ログ出力

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

//2 トリガー実行時などでも使えるやつ
console.log(str)

上記のどちらを使えばいいか、については、Logger.logはWebアプリやタイマー実行のプログラムのGASでのログ出力ができない。このため、console.logだけ使っておけばいいという認識。（違ったらごめんなさい。）

console.logについては、下記サイト様が勉強になります。

いつも隣にITのお仕事 - Apps Scriptダッシュボードでconsoleクラスのログ出力を確認する方法とメリット
https://tonari-it.com/gas-console-log/

VBA
debug.print str

エラー処理

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

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

  • xception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues. (line #, file hogehoge )
    • setValuesに1次元配列を渡していないか？[]で囲って2次元配列にしてみよう。

参考になるサイト・記事

この記事の補足としてだったり、GAS関連で、わたしが良く見返す記事

20
24
2

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
20
24