VBA→GASに移植する際に調べたり試したりしたこと

はじめに

こんにちは、D2Cデータアナリストの髙橋です。

以前、社内で管理していたExcelマクロファイルをGoogleスプレッドシートへ移行するという取り組みがあり、初心者ながら色んなサイトを参考に移植作業を行ったので、その際に調べたり試したりしたことを共有したいと思います。
拙い文章ではございますが、何かしらのお役に立てれば幸いでございます。

VBAとGASの違い

ご存じの通り、VBAのコードをそのままGoogleスプレッドシートで使用する事は出来ません。
GoogleスプレッドシートのマクロではGoogle Apps Script（GAS）という言語を使ってコードを作成する必要があり、GASはJavaScriptがベースとなるので、VBAのVisual Basicとはまた異なったコードの書き方となります。

JavaScriptに触れたことのない私は一瞬怯みましたが、
VBAとGASの構文やオブジェクトの違いをまず把握する事によって参考サイトのサンプルコードを解読出来るようになったので、いきなりGASのコードを読むよりこのようなワンクッションを挟むことを個人的におすすめします。

手始めに、Googleスプレッドシートにあるマクロの記録を使い、
GASがどのように生成されるかを試してみました。
『A1セルの値を同シート内のB1セルにコピーする』という操作で生成されたコードが以下となります。

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('A1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

• VBAではSub～で始まるところ、GASではfunction ～
• {}内に実行したい内容を書く
• 変数宣言はvar
• セル取得はgetRange、コピペはcopyTo

マクロの記録なので冗長なコードになっているかと思いますが、
一旦は上記の様な事が分かり、未知であったGASの構文やメソッドのイメージがなんとなく掴めました。

また、当たり前にはなりますが、VBAとGASではオブジェクトが違うので、
オブジェクト階層構造も併せて把握しておくとサンプルコードが読みやすくなるかと思います。
（以下 https://qiita.com/jooji/items/19bbd4d8b2dadf9583be からの引用）

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

実際にGASを書いてみた

GASについてのイメージがなんとなく掴めてきたので、
VBAで組んでいたマクロの内容を実際にGASへ書き換えてみる事にしました。

対象は案件情報の管理用ドキュメントです。
Templateシートに内容記入後、シート複製をした際にSummaryシートに案件項目を書き出す、
といったマクロを組んでおりました。

『1. Templateシートの複製』イメージ

なので今回GASで実行したかった事としては大きく分けて以下3項目となります。

1. Templateシートの複製
2. Templateシートの記載内容削除
3. 既存のSummaryシートに案件項目を書き出し

function make_new() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var tmpsheet = spreadsheet.getSheetByName('Template');
  var sheetnum = tmpsheet.getRange("F2").getValues();
  var sumsheet = spreadsheet.getSheetByName('Summary');

  // シート末尾にTemplateシートのコピーを作成
  var cpsheet = tmpsheet.copyTo(spreadsheet).setName(sheetnum);

  //Templateシートに記載していた内容を削除
  tmpsheet.getRange('F2:F7').clearContent();
  tmpsheet.getRange('B10').clearContent();

  //Summaryシートに案件項目を追加
  var vals = cpsheet.getRange('F2:F7');
  var lastlow = sumsheet.getLastRow();
  vals.copyTo(sumsheet.getRange(lastlow + 1, 2, 1, 6), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);

  //ハイパーリンクをつける
  var cpsheetId = cpsheet.getSheetId();
  var cpurl = "#gid=" + cpsheetId;
  var sumlink = sumsheet.getRange(lastlow + 1, 8);
  sumlink.setFormula('=HYPERLINK("' + cpurl + '","' + "リンク" + '")');
  sumsheet.getRange(lastlow + 1, 2, 1, 7).setBorder(true, true, true, true, true, true);
  sumsheet.activate();
}

以下、各項目ごとのコード説明となります。まずは『1. Templateシートの複製』です。

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var tmpsheet = spreadsheet.getSheetByName('Template');
  var sheetnum = tmpsheet.getRange("F2").getValues();
  var sumsheet = spreadsheet.getSheetByName('Summary');

  // シート末尾にTemplateシートのコピーを作成
  var cpsheet = tmpsheet.copyTo(spreadsheet).setName(sheetnum);

sheet.copyTo(Spredsheet)がシートの複製となるので、tmpsheet.copyTo(spreadsheet)で同スプレッドシート内にTemplateシートのコピーを作成しています。
setNameはシート名の変更となるので、引数sheetnumでTemplateシートのF2セルに記載されている項目（今回でいうと案件番号）が入るよう指定しています。

次に『2. Templateシートの記載内容削除』です。

  //Templateシートに記載していた内容を削除
  tmpsheet.getRange('F2:F7').clearContent();
  tmpsheet.getRange('B10').clearContent();

今後Templateシートを雛形として使用したいので、
Templateシートに記載した内容を削除するコードです。
削除対象のセルをgetRangeで取得し、clearContentで値のみ削除しています。

最後に『3. 既存のSummaryシートに案件項目を書き出し』を以下コードで実行しています。

  //Summaryシートに案件項目を追加
  var vals = cpsheet.getRange('F2:F7');
  var lastlow = sumsheet.getLastRow();
  vals.copyTo(sumsheet.getRange(lastlow + 1, 2, 1, 6), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);

複製したTemplateシートから案件項目を取得し、
Summaryシートのどこに記載するかをgetRange(lastlow + 1, 2, 1, 6)で指示しています。
getRange（行番号, 列番号, 行数, 列数）となりますので、
lastlow + 1 でSummaryシートのデータが入力されている最終行の次の行を指定し、列番号2でB列、行数1で1行、列数6でB列～G列を指定しています。

  //ハイパーリンクをつける
  var cpsheetId = cpsheet.getSheetId();
  var cpurl = "#gid=" + cpsheetId;
  var sumlink = sumsheet.getRange(lastlow + 1, 8);
  sumlink.setFormula('=HYPERLINK("' + cpurl + '","' + "リンク" + '")');
  sumsheet.getRange(lastlow + 1, 2, 1, 7).setBorder(true, true, true, true, true, true);

Summaryシートから各案件のシートに遷移出来るよう、ハイパーリンクを設定しました。
複製したTemplateシートのIDは毎回ユニークとなる為、都度IDを取得するようなコードを書く必要があります。
複製したTemplateシートのIDをgetSheetIdで取得し、
HYPERLINK関数で#gid=取得したシートIDに遷移するリンクを設定しています。
最後の行は、Summaryシートに記載したい項目が全て埋まったのでシートに罫線を引くコードになっています。

『3. 既存のSummaryシートに案件項目を書き出し』イメージ

おわりに

本記事では、VBAとGASの違いと、簡単なGASコードについて解説させて頂きました。
『GASってなんだか難しそうだな～』と思っている方にとって、何らかの取っ掛かりになれば幸いです！
最後までお読み頂きありがとうございました。

参考

https://qiita.com/jooji/items/19bbd4d8b2dadf9583be
https://blog.synnex.co.jp/google/gas-select-range-of-spreadsheet/