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

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

CSVファイルからデータを処理して、毎月特定の形式の請求書を発行するというケースがあり、Google SpreadsheetでテンプレートをデザインしてGASでスクリプト処理を実行しました。処理が複雑となったため、以前書いたブログからの更新部分を解説します。
#JavaScript
#デザイン
#Google Apps Script
Ito ma

Ito ma

facebook logohatena logotwitter logo
Clock Icon2020.07.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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

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

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

CSV内で扱うデータの種類が増えて処理が複雑になり、全体的に刷新しましたので、本ブログでは更新部分のみをご紹介します。処理の基本的な流れ（ユーザがやること）は前回から変わりありませんので、まずは上の記事をご参考ください！

今回やったこと

作成フォームの見栄えが変わっていますが、ユーザ側が行うことはほぼ前回から変わらずで、下記の流れで請求書を作成できます。

(1) レートを入力、必要に応じて請求日などを変更する
(2) （任意）「フォームのリセット」ボタンをクリックし、既存のレコードをクリアする
(3) （任意）その他の料金があれば、表内に記入してチェックを入れる (4) 「CSV取り込み」ボタンをクリックする
(5) アップローダのダイアログが開くので、取り込むCSVファイルを選択する
(6) ダイアログが閉じCSVデータが表に貼り付けられるので、「新しい請求書を作成する」ボタンをクリック
(7) 新しいシート「YYYY年M月」に請求書が作成される
(8) 作成されたシートを印刷する

Updated form

前回と違う点は次の3つです。

スクリプトでは、これらの実現に加えてエラーハンドリングも加えたため、なんらかの問題が発生した時にどこでエラーが発生したかメッセージが表示されるようになりました。

フォームに「その他の料金」欄を追加

CSVデータの他に、不定期に請求に追加する課金があります。以前は印刷前に手動で追記していましたが、作成フォーム上に入力欄を加えてチェックボックス形式とすることで、手動で追記しなくても請求書に追加されるようにしました。

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

特別な種類のレコードに対して、表示と計算を区別

取り込んだCSVデータのうち、「CMPプロジェクト」が「Special」であるAWSアカウントの場合、「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

 

フォームやボタンの設置、テンプレートの作成の流れについては前回のブログへどうぞ。

スクリプト

リセット

フォームのシートをリセットする処理です。前回から全体的にマイナーチェンジしてますが、基本的には「その他の料金」のチェックを外す動きを加えただけです。

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

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

function resetForm() {
  var sheet = SpreadsheetApp.getActive().getSheetByName(FORM);
  // AWS利用料の既存レコードをクリア
  clearRecords(sheet);
  // その他の料金のチェックをはずす
  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();
}

function clearRecords(sheet) {
  var rcRow = sheet.getRange(FORM_RC).getRow();
  var rcCol = sheet.getRange(FORM_RC).getColumn();
  sheet.getRange(rcRow, rcCol, sheet.getLastRow(), sheet.getLastColumn()).clearContent();
}

CSV取り込み

index.html は前回と同じですので割愛します。以下もほぼ同じですが、CSVデータの列数をチェックする処理を加えました。

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);
  clearRecords(sheet);
  sheet.getRange(rcRow - 1, rcCol, values.length, values[0].length).setValues(values);  
}

作成する

こちらも大まかな流れは同じですが、フォームの値の空欄チェックやエラーハンドリングを加え、失敗した時の原因調査が簡単になりました。

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日
  
  // 既存シートの削除確認 or 作成の実行確認をし、キャンセルなら処理を中断
  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）」をご参照ください。

それ以外の定義は次で補足します。

各関数の処理

checkForm()、getPeriodName()

  • checkForm()：請求日やレートなど、フォームの記入欄に空欄がある場合に、処理を中止させるためFalseを返す
  • getPeriodName()：請求書の対象期間である年月を取得（シート名では「YYYY年MM月」、請求書の本文内では「MMM YYYY」形式）
/**
 * 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 the 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;
  }
}

 

setHeader()

  • setHeader()：請求日や締日、請求書番号を入力

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

/**
 * 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;
  }
}

pasteRecods()

  • pasteRecords()：
    • 1行目に対象年月（eg. "Jan 2020"）、2行目にレートを貼り付け。
    • CSVで取り込まれたレコードをfor文で1行ずつ読み込み、後述のgetRecord()を使ってSpecialのアカウントは splValues、Specialアカウントの割引は splDiscValues、それ以外は regValues にそれぞれ代入。レコードに含まれる日本語はreplaceメソッドで英語に置き換え。（※）
    • 3行目以降にregValues、splValues、splDiscValuesを貼り付けし、それらのサブトータルやトータルであるシート上のSUM関数の範囲を更新。Specialアカウント(splValues)やその割引(splDiscValues)のレコードがない場合は、テンプレの該当行を削除。
    • 「その他の料金」をfor文で1行ずつ読み込み、チェックボックスにチェックがある(TRUE)場合は3行を追加、1行目に項目名・単価・個数、2行目に備考を貼り付け。

※補足１

Specialなどの種類を区別するために、レコードの「明細項目名」に特定の文字列を含むかどうかチェックしていますが、古いランタイムではincludesメソッドは使えないので、ここではindexOfメソッドで '-1' ならば存在しないと処理しています。（ランタイムについては前回のブログ参照）

参考1：Alternative to array.includes() in google apps script? - Stack Overflow
参考2：JavaScript｜「含むか」の判定(配列, 文字列) - わくわくBank

※補足２

numReg と regNo のような似た変数を使っていますが、前者はレコードの数を数える変数、後者はアカウントの数を数える変数です。これは、レコードで「RI等」（英語置換後は「Prepayment of RI〜」）を含むものは1つのアカウントの2つ目のデータで、下記のように請求書上で項番を振るときに、アカウントの数が重複しないようにしたかったためです。

/**
 * 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;
  }
  // レコードを取得して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行目以降にレコードを貼り付け
  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();
}

 

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

getRecord()、setRecords()

  • getRecord()：レコード1行分の配列 rec にデータをまとめて入れて、配列 array（regValues、splValues、またはsplDiscValues）に毎行追加して二次元配列を作る
  • setRecords()：二次元配列の値を指定範囲に setValuesメソッドでまとめて貼り付け

setValues メソッドでは、貼り付け先の行列数と貼り付けデータの行列数が一致している必要があります。今回のようにセルの一部が結合されている場合でも、列数は通常通り数えます。

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

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

/**
 * 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);
}

setTotal()、setSplTotal()

  • setTotal()、setSplTotal() ：冒頭のテンプレートの画像の細字で示したサブトータルのSUM関数について、対象範囲を更新

setFormulaR1C1 メソッドの指定は、例えば =SUM(R[1]C[2]:R[3]C[2]) の場合、SUM関数を指定するセルに対して、下1行・右2列のセルから、下3行・右2列のセルまで、といった感じになります。なので、S19へこれを指定すると、=SUM(U21:U23)が入力されます。

参考: setFormulaR1C1(formula) - Class Range  |  Google Developers

/**
 * 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);
}

おわりに

今回に関しては、正直言ってユースケースが特殊すぎて、読んでいても訳がわからないんじゃないかと思います...

処理の中で使っている一部だけでも、何かどこかでお役に立てば幸いです！

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

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

Share this article

facebook logohatena logotwitter logo

関連記事

ใช้ HTML＋JavaScript เรียก API Gateway กับ Lambda ที่สร้างด้วย REST API: Serverless Web App บน AWS : Part 6

ใช้ HTML＋JavaScript เรียก API Gateway กับ Lambda ที่สร้างด้วย REST API: Serverless Web App บน AWS : Part 6

User avatar
Supanut Akaram
2024.11.11
【React 19】サクッと理解するuseActionState

【React 19】サクッと理解するuseActionState

User avatar
戸田駿太
2024.10.11
[Transcribe] 動画内でしゃべった言葉をテキスト検索可能にする

[Transcribe] 動画内でしゃべった言葉をテキスト検索可能にする

User avatar
中村 修太
2024.10.10
[Bun] JavaScriptでC言語のファイルを簡単に実行する

[Bun] JavaScriptでC言語のファイルを簡単に実行する

User avatar
中村 修太
2024.09.26
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.