【GAS】 Google Apps Scriptを使って給与支払報告書を作成する(2)〜データ入力、PDF保存〜

はじめに

前回、GASを使って給与支払報告書のデータを作成するところまでやりました。
今回は以下をやりたいと思います。

今回やること

• 帳票を作成する。(給与支払報告書)
• 帳票の中でデータを入力する位置(行番号、列番号)、データkeyを特定する。
• セル位置とkeyの配列を用意する。(setData)
• FormSheetクラスの作成。(savePdfメソッド)
• scriptの作成
  内容：前回作ったデータとsetDataをもとに帳票にデータを入力していく。１人分のデータを入力し終わったら、そこでPDF化して保存する。
  上記を人数分繰り返す
  注意）前回の使ったbirthデータは、入力しやすいようにgengo,year,month,dayに分割したデータに変わっています。

帳票を作成して、データを入力する位置(行番号、列番号)とデータkeyを特定する

作成した帳票: 給与支払報告書
セル位置とデータkeyは次のとおり

これを元にsetData(配列)を作成する。

const setData = [
    ['行番', '列番', 'key',],
    [5, 14, 'address',], 
    [5, 56, 'myNumber',],
    [7, 50, 'furigana',],
    [8, 50, 'name',],
    [11, 20, 'nenkanKyuyo',],
    [52, 57, 'gengo',],
    [52, 68, 'year',],
    [52, 72, 'month',],
    [52, 76, 'day',],
  ];

FormSheetクラスの作成

pdf化して保存するコードは、別でクラスにしました。【コピペでOK！】GASで現在のシートをPDF化する方法を参考にさせていただきました。
このクラスを使うことにより、スプレッドシートIDとシート名を指定すれば、savePdfメソッドを使えるようになります。

'use strict'

class FormSheet {

  constructor(ssId, shName,) {
    this.ssId = ssId;
    this.shName = shName;
    this.setSs = SpreadsheetApp.openById(this.ssId);
    this.setSheet = this.setSs.getSheetByName(this.shName);
  }
  
  // シートをpdf化して、指定したフォルダに指定したファイル名で保存する
  savePdf(folderId, fileName) {
    const setSsId = this.setSs.getId();
    const setSheetId = this.setSheet.getSheetId();

    //PDFを作成するためのベースとなるURL
    let baseUrl = "https://docs.google.com/spreadsheets/d/"
      + setSsId
      + "/export?gid="
      + setSheetId;

    //★★★自由にカスタマイズしてください★★★
    //PDFのオプションを指定
    let pdfOptions = "&exportFormat=pdf&format=pdf"
      + "&size=A5" //用紙サイズ (A4)
      + "&portrait=true"  //用紙の向き true: 縦向き / false: 横向き
      + "&fitw=true"  //ページ幅を用紙にフィットさせるか true: フィットさせる / false: 原寸大
      + "&top_margin=0.25" //上の余白
      + "&right_margin=0.25" //右の余白
      + "&bottom_margin=0.10" //下の余白
      + "&left_margin=0.25" //左の余白
      + "&horizontal_alignment=CENTER" //水平方向の位置
      + "&vertical_alignment=TOP" //垂直方向の位置
      + "&printtitle=false" //スプレッドシート名の表示有無
      + "&sheetnames=false" //シート名の表示有無
      + "&gridlines=false" //グリッドラインの表示有無
      + "&fzr=false" //固定行の表示有無
      + "&fzc=false" //固定列の表示有無;

    //PDFを作成するためのURL
    let url = baseUrl + pdfOptions;

    //アクセストークンを取得する
    let token = ScriptApp.getOAuthToken();

    //headersにアクセストークンを格納する
    let options = {
      headers: {
        'Authorization': 'Bearer ' + token
      }
    };

    //PDFを作成する
    let blob = UrlFetchApp.fetch(url, options).getBlob().setName(fileName + '.pdf');

    //PDFの保存先フォルダー
    //フォルダーIDは引数のfolderIdを使用します
    let folder = DriveApp.getFolderById(folderId);

    //PDFを指定したフォルダに保存する
    folder.createFile(blob);
  }

}

スクリプト作成

• 給与支払報告書のシートをFormSheetクラスでインスタンス化する
• 前回作ったkyuyoHoukokuObjをmapメソッドで回しながら、その中でsetDataを回して指定したセルにkeyで指定されたvalueを入力していく
  最後のデータを入力し終わった時点でPDF化して指定したフォルダに保存する
• これを人数分繰り返す

// 帳票シートのインスタンス化
  const kyuyoHoukokuForm = new FormSheet('1bCERdqmCKFHt68-xoezqLx3YtIgWtw2HfxQBGNbBpfY', '給与支払報告書');
  const setSheet = kyuyoHoukokuForm.setSh; //setSheetの取得
  const setData = [
    ['行番', '列番', 'key',],
    [5, 14, 'address',], // 給与支払報告書[1]
    [5, 56, 'myNumber',],
    [7, 50, 'furigana',],
    [8, 50, 'name',],
    [11, 20, 'nenkanKyuyo',],
    [52, 57, 'gengo',],
    [52, 68, 'year',],
    [52, 72, 'month',],
    [52, 76, 'day',],
  ];
  // 保存するフォルダID
  const folderId = '**************'; // 任意のフォルダを指定してください

  // kyuyoHoukokuObj,setDataを元にした繰り返し処理
  kyuyoHoukokuObj.map((element) => {         // mapメソッドでobjectを処理する
    const last_element = setData.length - 1  // setData要素数(見出し配列を除くため-1する)
    for (let i = 1; i < setData.length; i++){ // setDataの要素ごとに繰り返し処理をする
      const e =setData[i];
      // データの流れと値の確認用
      console.log(`last_element=${last_element} i=${i} setSheet => セル位置:${e[0]},${e[1]} ${e[2]}:${element[e[2]]}`);
      setSheet.getRange(e[0], e[1]).setValue(element[e[2]]); // スプレッドシートへのデータ入力
      SpreadsheetApp.flush(); // スプレッドシートの再描画 => これがないと描画が遅れてエラーになることがある
      if (i === last_element){　// 最後の要素の処理が終了したところでPDF化して指定のフォルダに保存する
        console.log('最後のデータが入力された後、pdf化して保存');
        kyuyoHoukokuForm.savePdf(folderId, `給与支払報告書_${element['name']}_${response}`);
      }
    }
  });

データの流れと値の確認用のconsole.log()は、次のように出力されます。

情報  last_element=9 i=1 setSheet => セル位置:5,14 address:岐阜県
情報  last_element=9 i=2 setSheet => セル位置:5,56 myNumber:1234-1234-1234
情報  last_element=9 i=3 setSheet => セル位置:7,50 furigana:ヤマダ　タロウ
情報  last_element=9 i=4 setSheet => セル位置:8,50 name:山田太郎
情報  last_element=9 i=5 setSheet => セル位置:11,20 nenkanKyuyo:80000
情報  last_element=9 i=6 setSheet => セル位置:52,57 gengo:平成
情報  last_element=9 i=7 setSheet => セル位置:52,68 year:15
情報  last_element=9 i=8 setSheet => セル位置:52,72 month:9
情報  last_element=9 i=9 setSheet => セル位置:52,76 day:2
情報  最後のデータが入力された後、pdf化して保存

帳票シートを眺めると順番にデータが更新されていくのが確認できます。

まとめ

スプレッドシートとスクリプトを共有しましたので、試してみてください。
スプレッドシート
スクリプト(github)