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

CSVファイルからデータを処理して、毎月特定の形式の請求書を発行するというケースがあり、Google SpreadsheetでテンプレートをデザインしてGASでスクリプト処理を実行しました。その方法を紹介します。
2020.07.23

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

CSVファイルのデータから特殊な請求書を発行するケースがあり、スプレッドシート + GASで実装したブログを以前に書きました。

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

データの種類が増えて処理が複雑になりバージョンアップしたので、改めてご紹介します。

(大枠は同じなのでタイトル付けがドラゴンボールみたいに...)

やったこと

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

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

Updated form

前回と違う点は次の通りです。

フォームに「その他の料金」欄とチェックボックスを追加

CSVデータの他に、たまに合わせて請求に含める項目があり、以前は手動で追記していました。チェックボックス形式でフォームに欄を加えて、手動で追記しなくても同様の書式で請求書に追加できるようにしました。

チェックボックスのセルの値は、チェックを入れると "TRUE"、はずすと "FALSE" と同義です。

特別な種類のアカウントに対して異なる割引適用をし、分けて表示

取り込んだCSVデータのうち、「CMPプロジェクト」が「Special」の場合、「Special AWS accounts」として請求書上で別に表示させてサブトータルを計算するようにし、Gross Profitでも異なる粗利率をかけられるようにしています。(次の画像を参照)

請求書テンプレートのデザインを全体的に見やすく

内部的な会計の処理をする上で、計算してあると嬉しい各レベルでのサブトータルを加えました。また、情報が増えても見やすいよう、羅線や文字サイズ、インデント等をもう少し細かく加えました。テンプレートは、以下の通り(実際にはグリッド線は消しておきます)で、あらかじめ関数も定義しておきます。太字の関数はこのまま、細字の関数はスクリプト内で更新されます。

Spreadsheet template

SUMで合計を計算するときには、セルを結合している場合でも1つ目のセル(一番左上のセル)を対象として参照すれば良いので、項目レベルごとに列をずらしてデザインしておくと便利です。例えば、次のように「Special AWS accounts」の合計は、U列を合計範囲としています。

これだと、項目ごとの合計(次図の384.12, -89.11)とアカウントごとの金額(次図の91.34, 292.78, -89.11)とが重複して計算されてしまっているように見えるかもしれません。しかし、項目ごとの合計はT-V列のセルを結合しており、アカウントごとの金額はU-W列のセルを結合しているため、実際にはU列を1つ目のセルとして持つアカウントごとの金額のみが合計として計算されています。   Spreadsheet subtotal

 

※名称や数字等はサンプルに置き換えてあります。

重複となるため、ランタイムに関する解説、フォームやボタンの設置、テンプレートの作成の流れについては前回のブログをご参照ください。

スクリプト

リセット

フォームのシートをリセットする処理です。リセットではチェックを外す動きを加えました。グローバル変数の指定も前回から少し更新しています。

Code.gs language=(1/2)

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

// Form
var FORM_IV_DATE = 'C3'; // 請求日のセル位置
var FORM_RC = 'E12';     // AWS利用料のレコード開始セル
var FORM_COST = 'E4';    // その他の料金の項目の開始セル

function resetForm() {
  var sheet = SpreadsheetApp.getActive().getSheetByName(FORM);
  // AWS利用料の既存レコードをクリア
  var rcRow = sheet.getRange(FORM_RC).getRow();
  var rcCol = sheet.getRange(FORM_RC).getColumn();
  sheet.getRange(rcRow, rcCol, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
  // その他の料金のチェックをはずす
  var costRow = sheet.getRange(FORM_COST).getRow();
  var costCol = sheet.getRange(FORM_COST).getColumn();
  sheet.getRange(costRow, costCol, 5).setValue('FALSE');  //5行
  // 請求日をデフォルトの本日にリセット
  var rngIvDate = sheet.getRange(FORM_IV_DATE);
  rngIvDate.setFormulaR1C1('=TODAY()');
  rngIvDate.setNumberFormat("yyyy/mm/dd");
  // 既存のレートをクリア
  var rngCurRate = rngIvDate.offset(2, 0);
  rngCurRate.clearContent();
}

CSV取り込み

index.html は前回と同じですので、割愛します。こちらもほぼ同じですが、CSVデータの列数を確認する処理を加えました。

Code.gs language=(2/2)

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('Index');
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'CSV取り込み');
}

function processForm(formObject) {
  try {
    var blob = formObject.myFile;
    var csvText = blob.getDataAsString("sjis");
    var values = Utilities.parseCsv(csvText);
  } catch (err) {
    Logger.log('Error occurred while loading uploaded file:' + err);
  }
  if (values[0].length != 9) {
    Browser.msgBox('CSVのフォーマットが違います。列数が9であることを確認してください。');
    return;
  }
  // 既存レコードをクリアし、CSVのレコードを貼り付ける  
  var sheet = SpreadsheetApp.getActive().getSheetByName(FORM);
  var rcRow = sheet.getRange(FORM_RC).getRow();
  var rcCol = sheet.getRange(FORM_RC).getColumn();
  sheet.getRange(rcRow, rcCol, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
  sheet.getRange(rcRow - 1, rcCol, values.length, values[0].length).setValues(values);  
}

作成する

こちらも大まかな流れは同じですが、フォームの値の空欄チェックを加え、失敗した場合にはちゃんとエラーを表示させるようになりました。

Main.gs language=(1/5)

function createInvoice() {
  var ss = SpreadsheetApp.getActive();
  var sheetForm = ss.getSheetByName(FORM);
  var sheetTemplate = ss.getSheetByName(TMPL);
  // フォームの空欄を確認
  var check = checkForm();
  if (!check) {
    Browser.msgBox('フォームの入力値に空欄があります。');
    return;
  }
  // 対象の年月(請求日の前月)を取得し、新しい請求書のシート名を定義
  var newSheetName = getPeriodName("sheet"); // YYYY月MM日
  // 実行確認、または同じシート名がある場合のキャンセル確認をし、キャンセルなら処理を中断
  var cancel = cancelCreate(newSheetName);
  if (cancel) {
    Browser.msgBox('新しい請求書の作成をキャンセルしました。');
    return;
  }
  // シートを複製
  var errDuplicateSheet = duplicateSheet(sheetTemplate, newSheetName);
  if (errDuplicateSheet) {
    Browser.msgBox('シートの複製中にエラーが発生しました:' + errDuplicateSheet);
    return;
  }
  // 作成したシートにレコードを整形して貼り付け
  var sheetNew = ss.getSheetByName(newSheetName);
  var errSetHeader = setHeader(sheetForm, sheetNew);
  if (errSetHeader) {
    Browser.msgBox('ヘッダーの指定中にエラーが発生しました: ' + errSetHeader);
    return;
  }
  var errPasteRecords = pasteRecords(sheetForm, sheetNew);
  if (errPasteRecords) {
    Browser.msgBox('レコードの貼り付け中にエラーが発生しました: ' + errPasteRecords);
    return;
  }
  // 請求書番号の連番を更新
  try {
    var rngIvDate = sheetForm.getRange(FORM_IV_DATE);
    var num = rngIvDate.offset(1, 0);
    var nextNum = Number(num.getValue()) + 1;
    num.setValue(nextNum);
  } catch (err) {
    Logger.log('Error occurred while updating the invoice series number:' + err);
    Browser.msgBox('請求書番号の更新に失敗しました(請求書の作成は完了しました): ' + err);
    return;
  }
  // 完了メッセージ
  Browser.msgBox("作成が完了しました。",Browser.Buttons.OK);
  Logger.log('Process has been completed.');
};

 

各関数の定義は次の通りです。

*cancelCreate()、duplicateSheet()、shortMonth()、addMonths() はほぼ更新していませんので、前回のブログの「main.gs(3/3)」をご参照ください。

フォームの空欄チェック、対象期間の年月を取得

請求日やレートなど、フォームの記入欄に空欄がある場合にはFalseを返し、処理を中止します。

また、請求書の対象期間を、シート名には「YYYY年MM月」の形式、請求書の本文内では「MMM YYYY」形式で表示させるため、その値を返す関数を用意しました。

Main.gs language=(2/5)

/**
 * Check if the form is filled out.
 *
 * @return {boolean}  false if any element is blank.
 */
function checkForm() {
  var rng = SpreadsheetApp.getActive().getSheetByName(FORM).getRange(FORM_IV_DATE);
  for (var i = 0; i < 5; i++) {
    var cell = rng.offset(i,0).getValue();
    if (cell == "") return false;
  }
  return true;
}

/**
 * Return the period of the AWS usage
 * either for name of the new sheet or for the description in the sheet.
 *
 * @param {string} type  "sheet" or "description"
 * @return {string}      Usage year and month ("YYYY年MM月" or "MMM YYYY")
 */
function getPeriodName(type) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(FORM);
  var ivDate = sheet.getRange(FORM_IV_DATE).getValue();
  var descDate = addMonths(ivDate, -1);
  var descYear = descDate.getFullYear();
  if (type == "sheet") {
    var descMonth = descDate.getMonth() + 1; // getMonth() returns 0 to 11.
    var newSheetName = descYear + "年" + descMonth + "月";
    return newSheetName;
  } else {
    // var descMonth = descDate.toLocaleString("en-US", { month: "short" });  // V8 Runtimeの場合
    var descMonth = shortMonth(descDate);
    var descPeriod = descMonth + " " + descYear;
    return descPeriod;
  }
}

 

ヘッダーの指定

前回は請求日の上に請求書番号を表示させるデザインとしていましたが、「Invoice」のタイトルに続くように表示させることにしました。これは、なぜかPDF出力した際に、どうしても以前のデザインの位置だと請求書番号が文字列選択ができないという挙動に見舞われ、不便だったためデザイン変更しました。なぜだったんだろう...

Main.gs language=(3/5)

/**
 * Set a header of the duplicated sheet.
 *
 * @param {sheet} sheetForm  form sheet to paste from.
 * @param {sheet} sheetNew  new sheet to paste to.
 */
function setHeader(sheetForm, sheetNew) {
  var tmplIvDate = 'V10';
  var tmplTitle = 'A13';
  // フォームの各入力値の位置を定義
  var rngIvDate = sheetForm.getRange(FORM_IV_DATE);
  var rngIvDue = rngIvDate.offset(3, 0);
  var rngIvNum = rngIvDate.offset(4, 0);
  try {
    // 新規シートの請求日、締切日を入力
    var rngNewIvDate = sheetNew.getRange(tmplIvDate);
    var rngNewIvDue = rngNewIvDate.offset(1, 0);
    rngIvDate.copyTo(rngNewIvDate, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    rngIvDue.copyTo(rngNewIvDue, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    // タイトルに請求書番号を指定
    var rngNewTitle = sheetNew.getRange(tmplTitle);
    var ivTitle = rngNewTitle.getValue() + rngIvNum.getValue();
    rngNewTitle.setValue(ivTitle);
  } catch (err) {
    var msg = 'Error occurred while copying and pasting headers:' + err;
    Logger.log(msg);
    return msg;
  }
}

*前回のブログの処理ではCSVデータ貼り付け以外の部分、レート貼り付け等もここでやっていましたが、今回の処理では請求書のヘッダーとなる部分のみに絞っています。

 

レコードの貼り付け

メインの処理でとても長いですが、次のような流れです。

  1. 1行目に、先の getPeriodName 関数を使って取得した "Jan 2020" のような対象年月を追記。
  2. 2行目に、1USDあたりのEURのレートを貼り付け。
  3. 取り込んだCSVデータをfor文で1行ずつ読み込み、getRecord 関数を使って、Specialのアカウントの場合は splValues、Specialのアカウントの割引の場合は splDiscValues、それ以外は regValues にそれぞれ代入。また、レコードに含まれる「RI等」や「割引」といった日本語は、replaceメソッドで置き換え。
  4. 3行目以降に、3のregValuessplValuessplDiscValuesの貼り付けと、そのサブトータルとしてスプレッドシートのSUM関数の範囲を更新。Specialアカウントのレコード(splValues)、またはその割引レコード(splDiscValues)がない場合、テンプレの該当行を削除。
  5. Gross Profitの下に、その他の料金を入力。for文で1行ずつ、チェックボックスにチェックがある(TRUE)かどうか確認し、ある場合は3行を追加、1行目に項目名・単価・個数、2行目に備考を貼り付け。

3の割引レコードの判定には、CSVデータの「明細項目名」の文字列の一部に「割引」を含むかどうかをチェックしています。古いランタイム(*)ではincludesメソッドは使えないので、indexOfメソッドで '-1' ならば存在しないと処理しています。なお、ランタイムV8ではincludesメソッドが使えます。

*古いランタイムを使っている理由は、前回のブログをご参考ください。

また、各レコードには請求書上で項番を振っていますが、「RI等」のようなレコードは1つのアカウントの2つ目のレコードとなるデータの仕様だったため、次のように "アカウントの数" が正確に表示されるよう「RI等」を含むレコードには項番を振らないようにしています。この処理のために、numReg のようにレコードの数を数える変数と regNo のようにアカウントの数を数える変数を分けて持っています。

/**
 * Copy records from a "form" sheet and paste accordingly to the new sheet.
 *
 * @param {sheet} sheetForm  form sheet to paste from.
 * @param {sheet} sheetNew  new sheet to paste to.
 */
function pasteRecords(sheetForm, sheetNew) {
  const tmplFirstRecord = 'A17';
  const tmplH1PriceCol = 16;
  const tmplH1QtyCol = 21;
  const tmplH2DescCol = 2;
  const al1 = "  ";
  try {
    var rngNewRecord = sheetNew.getRange(tmplFirstRecord);
    const r1 = rngNewRecord.getRow();
    const c1 = rngNewRecord.getColumn();
    // 1行目に利用費の対象年月を指定
    var descPeriod = getPeriodName("description"); // MMM YYYY
    var firstDesc = al1 + "AWS Service Charges for " + descPeriod;
    rngNewRecord.setValue(firstDesc);
    // 2行目にレートを貼り付け
    var rngCurRate = sheetForm.getRange(FORM_IV_DATE).offset(2, 0);
    var rngNewCurRate = sheetNew.getRange(r1+1, 8);       // レートのセル列
    rngCurRate.copyTo(rngNewCurRate, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  } catch (err) {
    var msg = 'Error occurred while updating the first line or a currency rate:' + err;
    Logger.log(msg);
    return msg;
  }
  // 3行目以降のAWSレコードを取得してReg/Splそれぞれの配列に入れる
  try {
    const r2 = r1 + 2;
    const c2 = tmplH2DescCol;
    var rngFormAws = sheetForm.getRange(FORM_RC);
    const awsRow = rngFormAws.getRow();
    const awsCol = rngFormAws.getColumn();
    var regValues = [], splValues = [], splDiscValues = [];
    var numReg = 0, numSpl = 0, numSplDisc = 0,
        regNo = 0, splNo = 0, splDiscNo = 0;
    var numRecords = sheetForm.getLastRow() - awsRow + 1;
    for (var y = 0; y < numRecords; y++) {
      var row = awsRow + y;
      var prj = sheetForm.getRange(row, awsCol+2).getValue();  // AWS利用料のプロジェクト
      var desc = sheetForm.getRange(row, awsCol+5).getValue(); // AWS利用料の利用明細
      if (prj != "Special") {
        if (desc.indexOf("RI等") == -1) regNo++;
        numReg++;
        getRecord(row, regNo, desc, regValues);
      } else {
        if (desc.indexOf("(special割引)") == -1) {
          if (desc.indexOf("RI等") == -1) splNo++;
          numSpl++;
          getRecord(row, splNo, desc, splValues);
        } else {
          if (desc.indexOf("RI等") == -1) splDiscNo++;
          numSplDisc++;
          desc = desc.replace("割引", " discount");
          getRecord(row, splDiscNo, desc, splDiscValues);
        }
      }
    }
  } catch (err) {
    var msg = 'Error occurred while getting the AWS records:' + err;
    Logger.log(msg);
    return msg;
  }
  // 3行目以降にAWSレコードを貼り付け
  try {
    // Regular
    setRecords(sheetNew, r2+1, c2, numReg, regValues);
    setTotal(sheetNew, r2, numReg);
    // Special
    var rSpl = r2 + numReg + 3;
    if (numSpl > 0) {
      setRecords(sheetNew, rSpl+2, c2, numSpl, splValues);
      setSplTotal(sheetNew, rSpl+1, numSpl);
      if (numSplDisc > 0) {
        setRecords(sheetNew, rSpl+numSpl+3, c2, numSplDisc, splDiscValues);
        setSplTotal(sheetNew, rSpl+numSpl+2, numSplDisc);
        var rProfit = rSpl + numSpl + numSplDisc + 5;
      } else {
        sheetNew.deleteRows(rSpl+numSpl+2, 2);
        var rProfit = rSpl + numSpl + 4;
      }
      setTotal(sheetNew, rSpl, numSpl+numSplDisc+2);
      var numAws = numReg + 5;
      var rCost = rProfit + 4;
    } else {
      sheetNew.deleteRows(r2+numReg+1, 7);
      sheetNew.deleteRow(r2+numReg+5);
      var numAws = 2;
      var rProfit = r2 + numReg + 3;
      var rCost = rProfit + 3;
    }
    // Total
    var awsTotal = sheetNew.getRange(r1, tmplH1PriceCol);
    var newAWSFormula = "=SUM(R[2]C[3]:R[" + numAws + "]C[3])";
    awsTotal.setFormulaR1C1(newAWSFormula);
  } catch (err) {
    var msg = 'Error occurred while pasting the AWS records:' + err;
    Logger.log(msg);
    return msg;
  }
  // Gross Profitの下にその他の料金を入力
  try {
    var rngFormCost = sheetForm.getRange(FORM_COST);
    var cost = rngFormCost.getValue();
    var costRow = rngFormCost.getRow();
    const costCol = rngFormCost.getColumn();
    for (var y = 0; y < 5; y++) {
      if (cost) {
        // 新しいシートで3行を追加して書式をコピー
        sheetNew.insertRowsBefore(rCost, 3);
        var frRng = sheetNew.getRange(r1, 1, 2, sheetNew.getMaxColumns());
        var toRng = sheetNew.getRange(rCost, 1, 2, sheetNew.getMaxColumns());
        frRng.copyTo(toRng, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
        // 1行目の配列を作りデータを入れてから貼り付ける
        var numCol = tmplH1QtyCol - c1 + 1;
        var numBlankCol1 = tmplH1PriceCol - c1 - 1;
        var numBlankCol2 = tmplH1QtyCol - tmplH1PriceCol - 1;
        var costValue = new Array(numCol);
        costValue[0] = al1 + sheetForm.getRange(costRow, costCol+1).getValue();   // その他の料金の項目
        for (var i = 0; i < numBlankCol1; i++) costValue[i+1] = "";
        costValue[numBlankCol1+1] = sheetForm.getRange(costRow, costCol+3).getValue();  // その他の料金の単価
        for (var i = 0; i < numBlankCol2; i++) costValue[i+numBlankCol1+2] = "";
        costValue[numBlankCol1+numBlankCol2+2] = sheetForm.getRange(costRow, costCol+4).getValue(); // その他の料金の個数
        var costValues = [costValue];
        var rngCostRecords = sheetNew.getRange(rCost, c1, 1, numCol);
        rngCostRecords.setValues(costValues);
        // 2行目のセルをクリア、結合してから、データを貼り付ける
        sheetNew.getRange(rCost+1, 1, 1, sheetNew.getMaxColumns()).clearContent();
        var rngCostNote = sheetNew.getRange(rCost+1, c1+1, 1, tmplH1PriceCol-c1);
        rngCostNote.mergeAcross();
        var costNote = sheetForm.getRange(costRow, costCol+5).getValue(); // その他の料金の備考
        rngCostNote.setValue(costNote);
        rngCostNote.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
        rCost += 3;
      }
      // 次の変数を指定
      costRow++;
      cost = sheetForm.getRange(costRow, costCol).getValue();
    }
  } catch (err) {
    var msg = 'Error occurred while pasting the additional costs:' + err;
    Logger.log(msg);
    return msg;
  }
  sheetNew.getRange('A1').activate();
}

 

以下は、上記の処理に含まれる関数です。

上記のその他の料金の"1行目の配列"で似たような処理(117行目以降)をしていますが、getRecord 関数でも、レコード1行分の配列を作り、データを入れています。こちらでは、さらに1行分の配列 rec を、配列 arrayregValuessplValuessplDiscValues)に毎行追加して二次元配列を作っています。

最終的には setRecords 関数内で setValues で貼り付けますが、この際に選択した範囲の行列数と貼り付けるデータの行列数は一致している必要があります。また今回のように、貼り付け先のセルの一部が結合されている場合でも、列数は変わりません。

'1.,3551-5259-XXXX Account SAMPLE,,,,,,,,,,,,,,,0

例えば、1行目のレコードは上のような値で配列 rec に格納され、配列 regValues の1つ目に追加されています。貼り付け先のB-Rの17列のうちC-P列のセルが結合されていますが、結合されていない場合と同様に17列分の値が必要です。そのため、スクリプトの20行目のようにfor文で空の値の代入を繰り返し、この,,,,,の部分を実現しています。

setTotal、setSplTotal 関数では、冒頭のテンプレートの画像の細字で示したサブトータルのSUM関数について、対象範囲を更新しています。setFormulaR1C1 メソッドの指定は、例えば =SUM(R[1]C[2]:R[3]C[2]) の場合、SUMを行うセルに対して、下1行・右2列のセルから、下3行・右2列のセルまで、といった感じになります。なので、S19であれば、=SUM(U21:U23)となります。

/**
 * Add a single line record of Reg/Spl/SplDisc into each array.
 *
 * @param {num} row   line number of form records.
 * @param {num} no   series no. of Reg/Spl/SplDisc.
 * @param {num} desc  description of records.
 * @param {array} array  array of Reg/Spl/SplDisc.
 */
function getRecord(row, no, desc, array) {
  var sheetForm = SpreadsheetApp.getActive().getSheetByName(FORM);
  var numCol = 18 - 2 + 1; // No.からUSDまでの列数
  var numBlankCol = numCol - 3;
  var rec = new Array(numCol);
  rec[0] = "'" + no + ".";
  if (desc.indexOf("RI等")+1) {
    desc = desc.replace("RI等", "Prepayment of RI/R53 or Marketplace");
    rec[0] = "";
  }
  rec[1] = desc;
  for (var i = 0; i < numBlankCol; ++i) rec[i+2] = "";
  rec[numCol-1] = sheetForm.getRange(row, 8).getValue(); // AWS利用料の外貨金額の列
  array.push(rec);
}
/**
 * Insert rows under the first row of records, copy the style,
 * and set the array of values.
 *
 * @param {sheet} sheet   target sheet.
 * @param {num} row       the first row of records / original row to copy the style from.
 * @param {num} col       the first column of records.
 * @param {num} num       the number of records.
 * @param {array} values  an array of Reg/Spl/SplDisc values.
 */
function setRecords(sheet, row, col, num, values) {
  if (num > 1) {
    sheet.insertRowsAfter(row, num-1);
    var frRng = sheet.getRange(row, 1, 1, sheet.getMaxColumns());
    var toRng = sheet.getRange(row, 1, num, sheet.getMaxColumns());
    frRng.copyTo(toRng, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  }
  var rngRecords = sheet.getRange(row, col, num, values[0].length);
  rngRecords.setValues(values);
}
/**
 * Set the formula of the total (EUR) of Reg/Spec AWS accounts.
 *
 * @param {sheet} sheet  target sheet.
 * @param {num} row      the row of the 'total' cell.
 * @param {num} num      the number to specify the 'total' range.
 */
function setTotal(sheet, row, num) {
  var total = sheet.getRange(row, 19);  // H2レベルのトータル列
  var newFormula = "=SUM(R[1]C[2]:R[" + num + "]C[2])";
  total.setFormulaR1C1(newFormula);
}
/**
 * Set the formula of the total (USD) of Special AWS accounts and the Special discounts.
 *
 * @param {sheet} sheet  target sheet.
 * @param {num} row      the row of the 'total' cell.
 * @param {num} num      the number to specify the 'total' range.
 */
function setSplTotal(sheet, row, num) {
  var total = sheet.getRange(row, 16);  // H3レベルのUSDトータル列
  var newFormula = "=SUM(R[1]C[2]:R[" + num + "]C[2])";
  total.setFormulaR1C1(newFormula);
}

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

前回含めていた、印刷用に調整する処理も更新していました。末尾の Subtotal〜Total 部分が改ページされる位置にある場合、見やすさのために、ページをまたがないよう行を追加する処理です。

しかし、テンプレートで1ページ目は45行となっていたものの、今回のデザインでは冒頭の一部の行幅を広く/狭くしたため2ページ目以降では1ページあたりの行数が異なること、そして、セルを結合・テキストの折り返しを有効にしたことでさらに行幅が変動する可能性があったため、この45というのはあんまり意味がない処理だなということでお蔵入りしました。

ということで、調整するのは諦めましたが、行幅が均一な場合には有効な処理です。

function alignPrint(sheet) {
  // 45行で1ページ;Subtotal が42〜45行目にある場合、Subtotal前に行を追加する
  var subtotalRow = sheet.createTextFinder('Subtotal').findNext().getRow();
  var x = subtotalRow % 45;
  if (x > 41 && x < 46 ) {
    var addNum = 46 - x;
    sheet.insertRowsBefore(subtotalRow-1, addNum);
  }
}

なお、前回のブログでも書きましたが、マクロを活用すると GAS でのメソッドを探す手間が省けます!

GAS初心者がスプレッドシートでマクロからスクリプトを作ってみた

以上、前回から大枠は変わり映えありませんが、似たようなことをする際にサンプルの一つとして参考にしてもらえたらと思います!