楽をして早く帰ろう！ GASで面倒くさい請求書、見積書などを自動化する方法とは：働く価値を上げる“VBA／GAS術”（6）（3/3 ページ）

[高橋宣成，プランノーツ]

領収書を作成するスクリプト

　では次に、関数「createReceipt」を解説します。この関数の大まかな流れは以下のようになっています。

1. スプレッドシート「領収書ひな型」を「template」として開く
2. 「領収書リスト」のアクティブシートを「sheet1」として取得する
3. 以下の処理をsheet1の2行目から4行目まで繰り返し
   1. sheet1から氏名、品目、金額のデータを作成する
   2. templateに名前を付けてコピーし、その1番目のシートを「sheet」として取得する
   3. sheetに氏名、品目、金額のデータを転記する

IDでスプレッドシートを開く

　スプレッドシート「領収書ひな型」はバインドされていないので、アクティブなシートとして開けません。その場合、「スプレッドシートID」を把握し、以下書式の「openById」メソッドを使うことで、目的のスプレッドシートを開けます。

SpreadsheetApp.openById(スプレッドシートID)

　openByIdメソッドは、開いたスプレッドシートをSpreadSheetオブジェクトとして返します。なおスプレッドシートIDは、目的のスプレッドシートをブラウザで開いた際のURLで分かります。下記URLの{-----スプレッドシートID-----}部分に記されているアルファベットと数値、記号の組み合わせによる文字列が該当します。

https://docs.google.com/spreadsheets/d/{-----スプレッドシートID-----}/edit

　これによりスプレッドシート「領収書ひな型」を開いて、変数templateにセットしているのが、以下のステートメントとなります。

var template = SpreadsheetApp.openById('-----スプレッドシートID-----');

アクティブシートの各レコードを繰り返す

　続いて、以下の部分を見ていきます。

var sheet1 = SpreadsheetApp.getActiveSheet();
for(var i = 2; i <= 4; i++) {
  //処理    
}

　スクリプトはスプレッドシート「領収書リスト」にバインドしているので、「getActiveSheet()」メソッドで取得できるSheetオブジェクトは、そのアクティブなシートである「Sheet1」になります（領収書リストにはシートが1つしかありませんので、確実にSheet1が「アクティブシート」になります）。またfor文は、sheet1の2行目から4行目について、繰り返し処理するためのものです。

データの取得・生成と文字列の連結

　forループ内の以下の部分では、「領収書リスト」の各レコードから、氏名、品目、金額といった各データを生成または取得する処理になります。

var name = sheet1.getRange(i, 1).getValue() + ' '
  + sheet1.getRange(i, 2).getValue(); //姓 名
var description = "但 " + sheet1.getRange(i, 3).getValues() 
  + " として上記領収いたしました。"; //品目
var amount = sheet1.getRange(i, 4).getValue(); //金額

　Sheetオブジェクトに対する「getRange()」メソッドでセルの行番号、列番号を指定して、目的のセルをRangeオブジェクトとして取得し、さらに「getValue()」メソッドで、そのセルの値を取得します。

　また、JavaScriptで文字列を連結する場合は、以下のように「+」演算子を用います。

文字列1 + 文字列2

スプレッドシートを複製する

　次に、forループ内の以下の部分を見ていきましょう。

var ssName = '領収書(' + name + ')';
var sheet = template.copy(ssName).getSheetByName('領収書');

　まず参加者ごとの領収書をスプレッドシートとして作成するので、nameを利用して、スプレッドシート名を生成。それをssNameに代入します。さらに、そのssNameを引数として、SpreadSheetオブジェクトの複製する「copy()」メソッドを使用しています。書式は以下の通りです。

SpreadSheetオブジェクト.copy(スプレッドシート名)

　copy()メソッドで複製されたスプレッドシートは、Google ドライブのマイドライブ直下に追加されます。以上により、指定したスプレッドシート名にて、対象のスプレッドシートをコピーします。

シート名でシートを取得する

　続く、「getSheetByName()」メソッドは、シート名を指定してSheetオブジェクトを取得するメソッドです。

SpreadSheetオブジェクト.getSheetByName(シート名)

　今回のスクリプトでは、複製されたスプレッドシートの「領収書」シートをsheetに取得している、ということになります。

セルに値を入力する

　最後に、複製したスプレッドシートの該当セルに氏名、品目、金額を転記する処理が以下の部分です。

sheet.getRange('A4').setValue(name + ' 様');
sheet.getRange('B9').setValue(description);
sheet.getRange('C7').setValue(amount);

　まず、「getRange()」メソッドは引数として、行番号と列番号のカンマ区切りだけでなく、「A4」「B9」といったセルのアドレスを文字列形式で直接指定できます。これで、転記先のセルをRangeオブジェクトとして取得します。書式は以下の通りです。

Sheetオブジェクト.getRange(アドレス)

　用途に応じて、2つのセルの指定方法を使い分けるとよいでしょう。また、セルに値を入力するには、Rangeオブジェクトに対して「setValue()」メソッドを使います。書式は以下の通りです。

Rangeオブジェクト.setValue(値)

　引数で与えた値を、対象のRangeオブジェクトが表すセルに入力できます。

スクリプト全体を確認

　解説は以上です。再度、関数「createReceipt()」の内容を確認しておきます。

function createReceipt() {
  var template = SpreadsheetApp.openById('1l_SwuMbMH-UGZ2Rn3H7czyMo9IsbvTsjzhDZ4jT7PZQ');
  var sheet1 = SpreadsheetApp.getActiveSheet();
  
  for(var i = 2; i <= 4; i++) {
    
    //データの作成
    var name = sheet1.getRange(i, 1).getValue() + ' '
      + sheet1.getRange(i, 2).getValue(); //姓 名
    var description = "但 " + sheet1.getRange(i, 3).getValues() 
      + " として上記領収いたしました。"; //品目
    var amount = sheet1.getRange(i, 4).getValue(); //金額
    
    //ひな型をコピー
    var ssName = '領収書(' + name + ')';
    var sheet = template.copy(ssName).getSheetByName('領収書');
    
    //データを転記
    sheet.getRange('A4').setValue(name + ' 様');    
    sheet.getRange('B9').setValue(description);
    sheet.getRange('C7').setValue(amount);
  }
}

次回はExcel VBAでデータを集めるマクロを作成します

　以上、GASでデータの作成や転記を駆使して、自動で書類を作成するスクリプトを紹介しました。配列を用いるなどにより処理速度を向上できますが、今回は初心者でも作れるように、比較的易しい作り方をしています。とはいえ、SpreadSheet、Sheet、Rangeといったオブジェクトの主要な操作が多く含まれていますので、ぜひ使いこなせるようにしましょう。

　前回と今回は「1カ所のデータを複数の場所に転記する」ものでしたが、次回はその逆で「複数の場所にあるデータを1カ所にまとめる」処理を、Excel VBAで組んでいきます。どうぞお楽しみに。

著者プロフィール

高橋宣成

プランノーツ 代表取締役

「ITを活用して日本の『働く』の価値を高める」をテーマに、ExcelやVBA、G Suite、Google Apps Script、クラウドなどによる企業または個人事業主向けのシステムおよびツールの開発やコンサルティング、セミナー講師などを務める。「IT×働き方」をテーマに運営するブログ「いつも隣にITのお仕事」は月間60万PV達成。

書籍紹介

ExcelVBAを実務で使い倒す技術

高橋宣成著 秀和システム 1800円（税別）

動くコードが書けたその先、つまり「ExcelVBAを実務で使う」という目的に特化した実践書。ExcelVBAを楽に効果的に使いこなし続けるための知恵と知識、そしてそのためのビジョンと踏み出す勇気を提供する1冊。

詳解！ Google Apps Script完全入門 〜Google Apps & G Suiteの最新プログラミングガイド〜

高橋宣成著 秀和システム 2600円（税別）

Google Apps Scriptの完全入門書として、JavaScriptの基本から自作ライブラリまでを徹底解説。これ一冊だけで基礎から実践まで体系的にマスターできます。