#この記事の目的
この記事は、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 |
メソッド・構文
#ブックを開いて作業シートを指定する
//ブックを取得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を特定するためのブック固有の文字列
dim bk as workbook
dim st as worksheet
'ブックを開く
set bk = workbook.open("c:\hoge.xlsx")
'シートを取得する
set st = bk.worksheets("Sheet_name")
#セルの範囲の値を取得する
// getDataRange()はExcelのusedrangeとほぼ同じ
var vals = st.getDataRange().getValues(); //複数形valuesとすると複数範囲の値を取得
var val = st.getRange("A1").getValue(); //単数形valueとすると１つのセルの値を取得
dim var
var = Range("A1").CurrentRegion.Value
##セルへ値の代入
//複数セルへ”データ”を代入する
getRange(開始セルの行番号, 開始セルの列番号, 範囲の行数, 範囲の列数).setValues(配列);
//例
st.getRange(1,1 ,arr.length, arr.length[0]).setValues(arr);
##セルへ”数式”の代入
sh.getRange(6,1).setFormula("=sum(A1:A5)");
ActiveSheet.Range("A6").Formula = "=sum(A1:A5)"
日付の取得
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(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();
var dateAfterSevendate = new Date(date.getFullYear(), date.getMonth(), date.getDate() + 7); //7日後
var dateBeforOneYear = new Date(date.getFullYear() -1, date.getMonth(), date.getDate()); //1年前
msgbox(DateAdd("d", 2 ,date)) '2日後
msgbox(DateAdd("yyyy", -1 ,date)) '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と３と表示される
##シート内の全セルのデータを一括削除する
st.clearContents();
ActiveSheet.cells.ClearContents '値だけ消す。
ActiveSheet.cells.Clear '書式も含めて消す。
#コメントアウト
//GASのコメントアウトは、Javascriptと同じく"//"スラッシュ2個です。
/*
このようにすれば複数行改行することも出来ます。
*/
'シングルクォーテーションでコメントアウト
#改行
//gasは、javascriptと同じく特に改行コードはありません。
//セミコロンがワンステートメントの終わりを表すので、
//セミコロンの前であれば、スペースが入ってよいところで自由に改行できます。
' VBAは"_"（アンダーバー）で改行です。
'例
thisworksbook.Worksheets("Sheet1").range("A1") _
= "1234"
ログ出力
//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/
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関連で、わたしが良く見返す記事
-
JavaScriptの配列の使い方まとめ。要素の追加,結合,取得,削除。
-
Google Apps Scriptで二次元配列の行と列を入れ替える方法とその革命的な効果