GASでローカルCSVを取り込み、スプレッドシートで請求書を作成する

2020.04.23

Guten Tag! ベルリンより伊藤です。

特殊な請求書を発行するケースがあり、CSVファイルのデータから請求書を作成する必要がありましたので、スプレッドシート + GASで実装しました。

この投稿を書いている中で次々にバグを見つけて修正できました。アウトプット大切!

はじめに

ネットで「CSVを取り込んでスプレッドシートにデータを入力する」といった情報は調べるといくつか出てきますが、普通にコードをコピペして試してみると動かない場合があります。というのも、GASでは2020年2月より V8 ランタイムが導入され、現在デフォルトで有効になっているため、以前のランタイムを元に書かれたコードではエラーとなる可能性があるからです。もちろんそのままで問題ない場合もありますが、この時期より前に紹介されているコードはランタイムが原因で正しく動作しない可能性があるということをご認識おきくださいませ。(私はこれに気づくまでに丸一日消費しましたので...)

対策としては、以下の方法があります。

a. Migrating scripts to the V8 runtime を元にV8ランタイム対応にコードを更新する(日本語で解説しているページでは Google Apps ScriptのV8 Runtime対応を検証してみた - officeの杜 が細かく紹介されていました)

b. プロジェクトで以前のラインタイム(Rhino)を使用するよう切り替える(次の通り、V8の有効・無効はスクリプトエディタのページからいつでも変更可能です)

GASでV8を無効にする

もちろん a. で進める方が良いのでしょうが、今回導入したい、ダイアログ内のフォームからCSVファイルをアップロードする処理については、V8のバグによりHTML内でファイルのアップロードができない疑いがあり、V8を無効にして進めることにしました。

ですので、今回紹介するスクリプトは Rhino ランタイム(V8無効)で動作確認 しています。

なお、「CSVを取り込んでスプレッドシートにデータを入力する」だけでしたら、下記のページが大変分かりやすく、大部分を参考にさせていただきました。(※こちらもファイルアップロードを使うため、デフォルトのV8では動きません)

参考: 連載目次:GASのダイアログでcsvファイルアップローダーを作ろう

今回やったこと

ユーザは下記の流れで請求書を作成できます。このうち 2〜4 の処理をスクリプトで実装します。

  1. レートを入力、必要に応じて請求日などを変更する
  2. ボタンをクリックし、既存のレコードをクリアする
  3. ボタンをクリックし、アップローダで取り込むCSVファイルを選択
  4. ボタンをクリックし、新しいシートに請求書が作成される
  5. 作成されたシートを印刷する

スプレッドシートの準備(非スクリプト)

次の2つのシートを用意します。

※レコードやテンプレートの数字等はサンプルに置き換えてあります。

作成フォーム

このシートでユーザは下記の操作ができます。

  • [CSV取り込み]: アップローダが開かれCSVファイルを選択すると、シート上にデータが貼り付けされる
  • [作成する]: シート上の情報を元に、「テンプレート」シートを複製して請求書が作成される
  • [リセット]: シート上のレートとCSVレコードが消え、請求日を本日にリセットする

請求日はデフォルトで当日、締日や請求書番号は関数に基づいて更新されるようしておき、ユーザはレートのみ外部サイトで確認して手動で入力する必要があります。

ボタンは、「図形描画」を挿入して、「スクリプトを割り当て」で上図の青字の通りそれぞれ関数を割り当てています。どうやら図形描画はコピペができないようなので、グリッドを参考にしつつ同じサイズのものを3つ作りました。

テンプレート

請求書のテンプレをシートで作成し、共通部分をすべて入力して書式なども整えておきます。こちらも、Total (EUR) は =M18*Q18 (Unit Price x Qty.)、Margin は =S24*Q25(Subtotal x 5%)など、あらかじめ関数を仕込んであります。

また、A4の余白標準でちょうどよく印刷が収まるよう、列幅と範囲も調整しておきます。(※分かりやすいようグリッド線を表示させてありますが、印刷時に誤って入らないよう実際には非表示にしておきます。)

請求書テンプレート

スクリプトを実行した時に、作成日やCSVデータに応じて図のオレンジ部分が更新されます。

前述の作成フォームのCSVサンプルデータから作成を実行すると、このテンプレートを複製してシート「2020年3月」が作成され、印刷すると、次のようになります。

作成された請求書のサンプル

スクリプト

大まかな記述の仕方などは、Google App Script 公式のチュートリアルを参考にしています。

リセット

冒頭でグローバル変数としていくつかセル位置を定義し、レートのセル位置は offset を使って請求書の位置を元に範囲を特定しています。

main.gs(1/3)

var FORM = '作成フォーム';
var TMPL = 'テンプレート';

var IV_DATE = 'C2';  // 作成フォームの請求日のセル位置
var RC_ROW = 10;     // 作成フォームのレコード開始行
var RC_COL = 1;      // 作成フォームのレコード開始列
var IV_ROW = 20;     // テンプレートのレコード開始行
var IV_COL = 3;      // テンプレートのレコード開始列

/**
 * This method clears the ranges of existing records and currency rate
 * when a user clicks the "Reset" button.
 */
function resetForm() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(FORM);  
  
  // フォームの既存レコードをクリア
  clearRecords(RC_ROW, RC_COL, sheet);
  
  // 既存のレートをクリア
  var ivDate = sheet.getRange(IV_DATE);
  ivDate.offset(2, 0).clearContent();
  
  // 請求日をデフォルトの本日にリセット
  ivDate.setFormulaR1C1('=TODAY()');
  ivDate.setNumberFormat("yyyy/mm/dd");
}

function clearRecords(row, col, sheet) {  
  sheet.getRange(row, col, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
}

CSV取り込み

スクリプトエディタで main.gs に加えて index.html を用意します。流れとしては、ボタンをクリックしたら、openDialog からシート上に htmlのダイアログが開かれ、そこでファイルを選択して送信すると processForm が実行されます。

骨子は公式ドキュメントのForms冒頭でも紹介したページ(いつも隣にITのお仕事)を参考にしていますので、詳細はそちらをご参考ください。

main.gs(2/3)

/**
 * This method opens Index.html in a dialog which includes a file uploader
 * when a user clicks the "Import CSV" button.
 */
function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('Index');
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'CSV取り込み');
}

/**
 * This method is called by Index.html when the user uploads a file in a dialog.
 */
function processForm(formObject) {
  var blob = formObject.myFile;
  var csvText = blob.getDataAsString("sjis");
  var values = Utilities.parseCsv(csvText);

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(FORM);
  
  // 既存レコードをクリアし、CSVのレコードを貼り付け
  clearRecords(RC_ROW, RC_COL, sheet);
  sheet.getRange(RC_ROW - 1, RC_COL, values.length, values[0].length).setValues(values);  
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      // Prevent forms from submitting.
      function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
          forms[i].addEventListener('submit', function(event) {
            event.preventDefault();
          });
        }
      }
      window.addEventListener('load', preventFormSubmit);

      function handleFormSubmit(formObject) {
        google.script.run
          .withSuccessHandler(google.script.host.close)
          .withFailureHandler(function () {
            alert('アップロードに失敗しました');
            google.script.host.close();
        })
          .processForm(formObject);
       }
    </script>
  </head>
  <body>
    <form id="myForm" onsubmit="handleFormSubmit(this)" enctype="multipart/form-data">
      <input name="myFile" type="file" />
      <input type="submit" value="取り込み" />
    </form>
 </body>
</html>

作成する

ボタンをクリックすると、createInvoice を実行し、実行確認、テンプレシートの複製、請求日や番号の貼り付け、レコードの貼り付け・計算の修正、印刷用に改行を調整、請求書番号の連番を更新といった流れで処理します。

当初、レコードを貼り付ける際に for 文の中で setValue を記述して、一行ずつ値を入力していたのですが、GASでローカルCSVインポート - Qiita を参考に、for文では配列に値を入れ、setValuesでまとめて貼り付けるようにすると、処理が一瞬で終わるようになりました。ただしsetValuesのセル数には限界(251?)があるそうなのでデータ量が増える場合は注意が必要です。

なお、この参考ページでもCSVファイル取り込みが行なわれていますが、ダイアログに使用されているUiApp(UI Service)は現在は廃止されており、代わりに本稿のようにHtmlServiceを使用することが推奨されています。

その他、それぞれ下記ページも参考にしました。

addMonths(): JavaScript function to add X months to a date - Stack Overflow

shortMonth(): Get the Name of the Current Month in Vanilla JavaScript - Coder Rocket Fuel

main.gs(3/3)

/**
 * This method creates a new invoice sheet by duplicating a template sheet
 * and by copying and fixing original records on the form sheet,
 * when a user clicks the "Create" button.
 */
function createInvoice() {
  var ss = SpreadsheetApp.getActive();
  var sheetForm = ss.getSheetByName(FORM);
  var sheetTemplate = ss.getSheetByName(TMPL);
  
  var ivDate = sheetForm.getRange(IV_DATE);  // 請求日のセル位置
    
  // 対象の年月(請求日の前月)を取得し、新しい請求書のシート名を定義
  var descDate = addMonths(ivDate.getValue(), -1);
  var descYear = descDate.getFullYear();
  var descMonth = descDate.getMonth() + 1;   // getMonth() returns 0 to 11.
  var newSheetName = descYear + "年" + descMonth + "月";

  // 既存シートの削除確認 or 作成の実行確認をし、キャンセルなら処理を中断
  var cancel = cancelCreate(newSheetName);
  if (cancel) {
    Browser.msgBox('新しい請求書の作成をキャンセルしました。');
    return;
  }
  
  // シートを複製
  dublicateSheet(sheetTemplate, newSheetName);
  
  // 作成したシートにヘッダーを入力
  var sheetNew = ss.getSheetByName(newSheetName);
  setHeader(sheetNew, ivDate, descDate);
  
  // 作成したシートにレコードを整形して貼り付け
  pasteRecords(sheetForm, sheetNew, RC_ROW, RC_COL, IV_ROW, IV_COL);

  // subtotal〜Totalのブロックが印刷1ページ目と2ページ目の改ページをまたぐ場合、調整する
  alignPrint(sheetNew);

  // 請求書番号の連番を更新
  var num = ivDate.offset(1, 0);
  var nextNum = Number(num.getValue()) + 1;
  num.setValue(nextNum);

  Browser.msgBox("作成が完了しました。",Browser.Buttons.OK);
};


/**
 * Check if a sheet with the same year and month already exists
 * and prompts to delete it and continue the process.
 *
 * @param {string} sheetName  a name String of the new sheet.
 * @return {boolean}      true if user chose not to continue the process.
 */
function cancelCreate(sheetName) {
  var ss = SpreadsheetApp.getActive();
  var exSheet = ss.getSheetByName(sheetName);
  if (exSheet != null) {
    // 既存のシートがある場合、削除確認
    var delPrompt = Browser.msgBox("同じ年月のシートが既に存在します。削除して作成し直しますか?",Browser.Buttons.OK_CANCEL);
    if (delPrompt == 'ok') {
      // OKの場合、既存のシートを選択して削除し、処理を続ける
      ss.setActiveSheet(exSheet, true);
      ss.deleteActiveSheet();
      Logger.log('Existing sheet has been deleted.');
      return false;
    } else {
      // そうでない場合、true を返す
      Logger.log('User declined to delete the existing sheet.');
      return true;
    }
  } else {
    var conPrompt = Browser.msgBox("入力した請求日、レート、レコードで請求書を作成してもよろしいですか?",Browser.Buttons.OK_CANCEL);
    if (conPrompt == 'ok') {
      // OK の場合、処理を続ける
      Logger.log('User confirmed to create a new invoice.');
      return false;
    } else {
      // そうでない場合、true を返す
      Logger.log('Invoice creation was canceled by user.');
      return true;
    }
  }
}

/**
 * Dublicate a template sheet and rename the new sheet.
 *
 * @param {sheet} sheet  "template" Sheet to duplicate.
 * @param {string} name  name String of the new sheet.
 */
function dublicateSheet(sheet, name) {  
  var ss = SpreadsheetApp.getActive();

  ss.setActiveSheet(sheet, true);
  var sheetNew = ss.duplicateActiveSheet();
  sheetNew.setName(name);
  sheetNew.setTabColor(null);
};

/**
 * Set a header of the duplicated sheet.
 *
 * @param {sheet} sheet   duplicated Sheet.
 * @param {range} ivDate  a cell Range of input invoide date.
 * @param {date} descDate a Date of the previous month of invoice date.
 */
function setHeader(sheet, ivDate, descDate) {
  // フォームの各入力値の位置を定義
  var ivDue = ivDate.offset(0, 2);
  var ivNum = ivDate.offset(1, 2);
  var curRate = ivDate.offset(2, 0);

  // 作成した新しいシートを選択し、請求日、締切日、請求書番号、レートを順に貼り付け
  sheet.getRange('S10:V10').activate();
  ivDate.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet.getRange('S11:V11').activate();
  ivDue.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
  sheet.getRange('R9:V9').activate();
  ivNum.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  sheet.getRange('H19:I19').activate();
  curRate.copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // 利用費の対象期間として請求日前月の年月を指定
  // var descMonth = descDate.toLocaleString("en-US", { month: "short" });  // V8 Runtimeの場合
  var descMonth = shortMonth(descDate);
  var descYear = descDate.getFullYear();

  var desc = sheet.getRange('A18').getValue() + " " + descMonth + " " + descYear;
  sheet.getRange('A18').setValue(desc); 
};

/**
 * Copy records from a "form" sheet and paste accordingly to the new sheet.
 *
 * @param {sheet} sheet1  form Sheet to paste from.
 * @param {sheet} sheet2  new Sheet to paste to.
 * @param {int} r1  First row Number of record to copy.     10
 * @param {int} c1  First column Number of record to copy.   1
 * @param {int} r2  First row Number of record to paste.    20
 * @param {int} c2  First column Number of record to paste.  3
 */
function pasteRecords(sheet1, sheet2, r1, c1, r2, c2) {
  // 新しいシートにレコードを入力する行を追加
  var numRows = sheet1.getLastRow() - r1 + 1; // レコード行数 30-10+1=21
  sheet2.insertRowsAfter(r2, numRows - 1);    // 1行目の下に20行を追加

  // 1行目をコピーして追加した行に貼り付け
  var frRng = sheet2.getRange(r2, 1, 1, sheet2.getMaxColumns());               // コピー元の範囲 20:20 (20,1,1,max)
  var toRng = sheet2.getRange(r2 + 1, 1, numRows - 1, sheet2.getMaxColumns()); // コピー先の範囲 21:40 (21,1,20,max)
  toRng.activate();
  frRng.copyTo(sheet2.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // 二次元配列の変数を定義
  var acctDescValues = [];
  var acctUsdValues = [];

  // レコードの行数だけ繰り返す
  for (var i = 0; i < numRows ; ++i) {
    var row1 = r1 + i;
    var row2 = r2 + i;

    // アカウントの説明文
    var acctDescValue = [sheet1.getRange(row1, c1 + 5).getValue()];
    acctDescValues.push(acctDescValue);

    // アカウントの利用費(USD)
    var acctUsdValue = [sheet1.getRange(row1, c1 + 3).getValue()];
    acctUsdValues.push(acctUsdValue);
  }

  // 貼り付け先にそれぞれ値を入力
  var acctDescRng = sheet2.getRange(r2, c2, numRows, 1);
  var acctUsdRng = sheet2.getRange(r2, c2 + 13, numRows, 1);  
  acctDescRng.setValues(acctDescValues);
  acctUsdRng.setValues(acctUsdValues);

  // Unit Price に合計のSUM式を入力
  var unitPrice = sheet2.getRange(r2 - 2, c2 + 10);
  var sumForm =
      "=SUM(R[" + 2 + "]C[" + 6 
      + "]:R[" + (2 + numRows - 1) + "]C[" + 8 + "])";
  unitPrice.setFormulaR1C1(sumForm);

  sheet2.getRange('A1').activate();
}

/**
 * Add rows to display the Total Amount block in one page.
 *
 * @param {sheet} sheet  new Sheet to paste to.
 */
function alignPrint(sheet) {
  // Subtotal が41〜45行目にある場合、Subtotal前に行を追加する
  var subtotalRow = sheet.createTextFinder('Subtotal').findNext().getRow();
  if (subtotalRow > 40 || subtotalRow > 46 ) {
    var addNum = 46 - subtotalRow;       // 2ページ目の1行目から引いた数
    sheet.insertRowsBefore(subtotalRow, addNum);
  }
}

/**
 * Get a short name of the month from date.
 *
 * @param {date} date  original Date.
 * @return {string}    3-digit month name.
 */
function shortMonth(date){
  const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  var monthNum = date.getMonth();
  var monthName = monthNames[monthNum];
  return monthName;
}

/**
 * Add the number of month to date.
 *
 * @param {date} date  original Date.
 * @param {int} months  number of month to add.
 * @return {date}      result date.
 */
function addMonths(date, months) {
    var d = date.getDate();
    date.setMonth(date.getMonth() + months);
    if (date.getDate() != d) {
      date.setDate(0);
    }
    return date;
}

冒頭でせっかくセル位置を定義しているんですが、setHeader における請求日、請求書番号などの貼り付け先は、関数内でベタ書きにしてしまいました。

また、印刷で Total のブロックが途切れないよう調整する alignPrint は、現状では1,2ページの間の処理しか対応していないので、件数が増えた場合のことは考えていません。

などなど長く運用する上での不備はありますが、社内利用ということで...

結局使わなかったけど参考メモ

「1234-5678-XXXX Account Name」といった説明文の部分ですが、当初レコードでそのままあるのに気づかず、スクリプトで書いた版です。

参考: Google Apps Script(GAS)で文字列を切り出し・抽出(slice,substring,substr)の解説 | AutoWorker〜Google Apps Script(GAS)とSikuliで始めるRPA入門

    // アカウントの説明文
    var acctNum = zeroPad(sheet1.getRange(row1, c1).getValue(), 12);
    var acctName = sheet1.getRange(row1, c1 + 1).getValue();
    var acctDesc = acctNum.slice(0,4) + "-" + acctNum.slice(4,8) + "-XXXX " + acctName;

上記で使われている zeroPad は下記のゼロパディングを参考にしました。

参考: JavaScriptで数値の桁数を合わせる(ゼロパディング)方法 - SMART 開発者のためのウェブマガジン

/**
 * Add zero to increase the length. This is no longer needed.
 *
 * @param {int} NUM  original number.
 * @param {int} LEN  length of the number.
 * @return {string}  zero-added number added.
 */
function zeroPad(NUM, LEN){
  return ( Array(LEN).join('0') + NUM ).slice( -LEN );
}

おわりに

かなり特殊なケースにはなりますが、CSVを使って作るというめんどくさい作業がスクリプトで簡単にできるようになりました!

なお、スクリプトを作っていると、「スプレッドシート上でこの処理はなんていうメソッドなんだろう?」という場面があります。そういう時、もちろんググっても見つけられると思うんですが、マクロを使うと便利です!今回でいうと例えば、シートの色を変更したい場合に、「マクロを記録」で試しにシートの色を変更し、記録されたコードから setTabColor() を使うことがわかりました。マクロについては、以前GAS初心者としてマクロからスクリプト記述した方法をこちらのブログで紹介しましたので、ぜひご参考ください。

Auf Wiederlesen!