Power Automateを動画で学ぼう!Udemyキャンペーン中

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

目次

この記事の目的

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

この記事は書きかけのため、みずらくてすみません。。。

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

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

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

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

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

オブジェクトの階層構造

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

Excel
SpreadSheet
  • Application
    • Workbook
      • Sheet
        • Range
          • Value
  • SpreadSheetApp
    • SpreadSheet
      • Sheet
        • Range
          • Value

メソッド・構文

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

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年前

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

var st = SpreadsheetApp.getActive(); //アクティブシートを取得
//最終行
var maxrow = st.getLastRow();
//最終列
var maxcol = st.getLastColumn();
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 mydic = {}; //初期化
mydic['key'] = "value"; //辞書へ追加
console.log(mydic['key']); //辞書へ問い合わせ
 '事前バインディングの場合は、参照設定から紐づけが必要
 dim mydic as Dictionary '定義
 set mydic = new Dictionary '初期化

配列

配列の初期化と追加

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

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

arr.push(”要素”);

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

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

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

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

コメントアウト

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

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

改行

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

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

ログ出力

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

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

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

debug.print str

エラー処理

 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本最強のこちらがオススメ!

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

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

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

ノンプログラマーでも自動化スキルを身につける。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
URLをコピーする
URLをコピーしました!
目次
閉じる