【GAS】ピボットテーブル生成時にピボットグループを作成したい

Question

前提

GASで
①ピボットテーブルを自動作成
②ピボット集計した値をコピー
③同ファイル内の別のシートに貼り付け
をさせたいです。

今回、上記①　(②)　で躓いています。

実現したいこと

ピボットテーブル生成の際、割合部はD列とE列+F列の2種で
集計をさせたいです。
E列とF列でピボットグループを作成する場合、手動操作だと、
ピボットテーブル上でE列とF列を選択後、ピボットグループを
作成しますが、どのようにすればこれと同じことをGASで実現
できるかを教えてほしいです。

<元データシートイメージ>

<ピボットテーブルイメージ>

Q1：どのようなコードを書けば、上記が実現できますか?

試したこと(発生している問題)

ピボットテーブル生成時にE列とF列をグループ化する方法が思い
つかなかったため、
・まず、30%(D列)、50%(E列)、100%(F列)の3種でピボット生成
・その後に、別途50%(E列)、100%(F列)を合算し、ピボットシート
H列に追記
という2段階でのアプローチを試みました。

E列とF列の合算は、setFormulaR1C1('組込関数SUM')で算出しよう
としたのですが、SUM範囲を指定する際、終点の行を可変にしよう
とピボットテーブルシートH列で#ERRORとなってしまい、うまく
できません。
終点行を「2」と指定すれば、想定した通りの動きをしてくれます。

Q2：終点行が可変としたい場合、どのようにすればコード修正
すればSUM算出できますか?

【うまくできなかったコード】
function sumPivot() {
  
const ss = SpreadsheetApp.getActiveSpreadsheet();
const detaSheet = ss.getSheetByName("データ");
const summarySheet = ss.getSheetByName("集計");
const S_pivotSheet = ss.getSheetByName('ピボットB');

//▼最終行(lastRow)を取得
const lastRow = S_pivotSheet.getLastRow();
console.log('lastRow ' + lastRow ) //4が正解
const targetRows = lastRow - 2;
console.log('targetRows ' + targetRows ) //2が正解

//★うまくできない★
//▼H列にE列(割合50%)とF列(割合100%)の合算値を補充
//合算値はsetFormulaR1C1('組込関数SUM')で算出
//基準セルから、行方向下に▲行、列方向左方向に●列　>>「R[▲(下方向)]C[-●(左方向)]」
  const sum = S_pivotSheet.getRange(3, 8, targetRows, 1).setFormulaR1C1("=SUM(R[0]C[-3]:R[targetRows]C[-2])");

(参考)ピボットテーブル作成させるコード

function makePivotB() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const mySheet = ss.getActiveSheet();

//▼ファイル内にピボットTBL用の新シートを追加
//スプシ内の左から4番目に新しいシートを作成し、シート名は「ピボットB」に変更
const newSheet = ss.insertSheet('ピボットB',3);　

const pivotSheet = ss.getSheetByName('ピボットB');
const detaSheet = ss.getSheetByName("データ");

//▼ピボットTBLのデータ取得元を指定
const deta = detaSheet.getDataRange(); //「データ」シートからデータを取得する
const pivot = pivotSheet.getRange(1,1).createPivotTable(deta); //ピボットシートにピボットTBLを作成

//▼ピボットTBLの行に表示するデータを指定
pivot.addRowGroup(4).showTotals(false); //ピボット行にD列(所属1)を追加。小計は表示しない
pivot.addRowGroup(5).showTotals(false); //ピボット行にE列(所属2)を追加。小計は表示しない
pivot.addRowGroup(6).showTotals(false); //ピボット行にF列(所属3)を追加。小計は表示しない
pivot.addColumnGroup(11).setDisplayName('割合').showTotals(true); //ピボット列に元データJ列を追加。小計は表示しない


//▼ピボットTBLの値に表示するデータを指定
//B列(社員番号)_COUNTで設定 
const count = pivot.addPivotValue(2,SpreadsheetApp.PivotTableSummarizeFunction.COUNT);　//3が出たら正解

//▼ピボットTBLにフィルタをかける
//フィルタ絞込み条件はH列(兼任)=1のみ表示 
//setVisibleValues(['表示させる値'])
//※変数名は必ずcriteriaを使う?
const criteria1 = SpreadsheetApp.newFilterCriteria().setVisibleValues([1]).build();
//addFilter(●列目,criteria)　
pivot.addFilter(8,criteria1); 

SpreadsheetApp.flush();　//処理がつまらないように強制実行

//▼処理完了Msgを表示
Browser.msgBox("ピボットBTBLを作成しました。",Browser.Buttons.OK);
}

補足情報（FW/ツールのバージョンなど）

非エンジニアビギナーです。
回答は急ぎませんので、次回から自分で解決できるように
なりたいので非エンジニア、ビギナーでも理解できるレベル
のアドバイス、解説をいただけると助かります。
お忙しい中、お手数をおかけして申し訳ありません。
よろしくお願いいたします。

Answer 1

Q1：E列とF列でピボットグループを作成する

質問文中にある「(参考)ピボットテーブル作成させるコード」の20行目を下記のように変えてみて下さい。

// pivot.addColumnGroup(11).setDisplayName('割合').showTotals(true); // 以下のように修正
const pivotGroup = pivot.addColumnGroup(11).setDisplayName('割合').showTotals(true); //ピボット列に元データJ列を追加。小計は表示しない

// 列のうち、E列（50）と F列（100）をグルーピングする
pivotGroup.addManualGroupingRule('50 & 100', [50, 100]);  // 追加

//▼ピボットTBLの値に表示するデータを指定
//B列(社員番号)_COUNTで設定 
... 以降は同じ...

説明

列グループ（または行グループ）を取得し、そのグループに対して

addManualGroupingRule('表示ラベル',[グルーピングしたい値の配列])

を指定することで、グループ化することが可能です。

　

Q2：終了行可変の場合の指定方法

19行目を下記のように変えてみて下さい。

//基準セルから、行方向下に▲行、列方向左方向に●列　>>「R[▲(下方向)]C[-●(左方向)]」
  const sum = S_pivotSheet.getRange(3, 8, targetRows, 1).setFormulaR1C1(`=SUM(R[0]C[-3]:R[${targetRows}]C[-2])`);

説明

文字列中に変数の内容を適用したい場合、${変数}を含む文字列をバッククオート「`」で囲みます（テンプレートリテラル）。

※バッククオート「`」で囲まないとテンプレートリテラルになりません。
「”」や「'」で${}を含む文字列を囲んでもテンプレートリテラルにはなりません。

または

"=SUM(R[0]C[-3]:R[" + targetRows + "]C[-2]"

でも可と思います。

Comments

[donguriko]

qnoirさま
返信が遅くなり申し訳ありません。
お忙しい中、回答ありがとうございます。
レクチャーいただいた内容で想定通りの動きができました。
ありがとうございました!!

▼ピボットグループの作成
addManualGroupingRule('表示ラベル',[グルーピングしたい値の配列])
でグルーピングできるのですね。
勉強になりました。次から自分で使えるように覚えます!!

▼終了行可変の時の指定方法
文字列中に変数の内容を適用したい場合は、
・変数名を${　変数　}　で囲む
・${変数}をさらにバッククオート「`」で囲む

コレ以前にも教えていただいた内容でしたね。。。。
身についていませんでした。大変失礼いたしました。

初めてGASでピボットTBL操作に挑戦してみましたが、qnoirさまの
おかげでなんとかやれそうです。
使いこなせるよう、引き続き精進したいと思います。
いつも丁寧なレクチャー、本当にありがとうございます。(感謝!!)