Googleスプレッドシートのシート1にある複数の時系列グラフがある。GASでシート2に直近1時間分のグラフを描かせたい。

Question

実現したいこと

Googleスプレッドシートのシート1に複数の時系列グラフがある。Google Apps Script(GAS)でシート2に直近1時間分のグラフを描かせたい。
シート1の単数グラフの直近一時間のグラフをシート2に描かせるScriptは人に書いて貰って問題なく動くので、これを改造して、複数のグラフを描かせたい。

前提

シート1のA列に時間、B列、C列、D列、E列にデータが入っている。データはGASで行末に十秒に一度追加される。
問題なく動くシート1の単数のグラフの直近一時間のデータをシート2にグラフで書かせる為のGASは以下の通りである。

// 既存のグラフのデータ範囲を更新
function updateChartRange() {
  // 初期設定
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('シート1');
  const sheet2 = ss.getSheetByName('シート2');

  // シート1の最終行を取得し、その60秒×60分÷10秒(1時間)分の360行を遡った行番号を取得
  const lastRow = sheet1.getLastRow();  // 最終行
  const firstRow = lastRow <= 360 ? 1 : lastRow - 360; // 1時間前の行（1時間経過していないなら最初の行）

  // A列の遡った行からB列の最終行までのセル範囲を取得
  const rangeAdd = sheet1.getRange('A' + firstRow + ':B' + lastRow);

  // シート2の既存のグラフを取得
  // ※ グラフは1つしかないが、配列で取得されるので1番目[0]
  let chart = sheet2.getCharts()[0];

  // 既存のグラフをベースとするグラフ作成ツールを取得
  const chartBuilder = chart.modify();

  // グラフ作成ツールからデータのセル範囲を全て取得
  // ※ 実際には、セル範囲は1つしかないが配列で取得される
  const ranges = chartBuilder.getRanges();

  // グラフ作成ツールにより、
  // 設定されているセル範囲を削除し、
  // 新たなセル範囲を追加してからグラフを生成
  chart = chartBuilder
    .removeRange(ranges[0]) // 取得したセル範囲の配列の最初のセル範囲[0]を削除
    .addRange(rangeAdd)     // 前もって取得しておいたセル範囲を追加
    .build();               // 変更を反映したグラフを生成

  // 生成したグラフで既存のグラフを更新
  sheet2.updateChart(chart);
}

// ファイルを開いたときにメニューを追加
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('グラフ')
    .addItem('更新', 'updateChartRange')
    .addToUi();
}

これを改造して、複数のグラフに対応させたい。

試したこと

以下の通り、レンジ指定を変えてA列とB列だけでなく、A列とC列、A列とD列、A列とE列のグラフに拡張しようとスクリプトを改造した。

function updateChartRange() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet1 = ss.getSheetByName('シート1');
const sheet2 = ss.getSheetByName('シート2');
const lastRow = sheet1.getLastRow();
const firstRow = lastRow <= 360 ? 1 : lastRow - 360;
const range0Add = sheet1.getRange('A' + firstRow + ':B' + lastRow);
const range1Add = sheet1.getRange('A' + firstRow + ':A' + lastRow + ',' + 'C' + firstRow + ':C' + lastRow );
const range2Add = sheet1.getRange('A' + firstRow + ':A' + lastRow + ',' + 'D' + firstRow + ':D' + lastRow );
const range3Add = sheet1.getRange('A' + firstRow + ':A' + lastRow + ',' + 'E' + firstRow + ':E' + lastRow );

let chart0 = sheet2.getCharts()[0];
let chart1 = sheet2.getCharts()[1];
let chart2 = sheet2.getCharts()[2];
let chart3 = sheet2.getCharts()[3];

const chartBuilder0 = chart0.modify();
const chartBuilder1 = chart1.modify();
const chartBuilder2 = chart2.modify();
const chartBuilder3 = chart3.modify();

const ranges0 = chartBuilder0.getRanges();
const ranges1 = chartBuilder1.getRanges();
const ranges2 = chartBuilder2.getRanges();
const ranges3 = chartBuilder3.getRanges();

chart0 = chartBuilder0
chart1 = chartBuilder1
chart2 = chartBuilder2
chart3 = chartBuilder3

.removeRange(ranges0[0])
.removeRange(ranges1[0])
.removeRange(ranges2[0])
.removeRange(ranges3[0])

.addRange(range0Add)
.addRange(range1Add)
.addRange(range2Add)
.addRange(range3Add)

.build();
sheet2.updateChart(chart0);
sheet2.updateChart(chart1);
sheet2.updateChart(chart2);
sheet2.updateChart(chart3);

}
// ファイルを開いたときにメニューを追加
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('グラフ')
    .addItem('更新', 'updateChartRange')
    .addToUi();
}

上のスクリプトでは、sheet1.getRangeで('A' + firstRow + ':B' + lastRow);
として、もし現在のデータ数が660個だとすると、グラフの範囲(A300:B660)を指定している。C列のデータ範囲が'シート1'!A300:A360,'シート1'!C300:C360なので、指定にsheet1.getRange('A' + firstRow + ':A' + lastRow + ',' + 'C' + firstRow + ':C' + lastRow )としてみたが、こういう使い方は Range not foundといわれるので書式が良くないらしい。

他にもBuild周りでちゃんと動かない気がする。

どうやったら、複数のグラフでシート2にグラフを書かせることが出来るでしょうか？
よろしくお願いします。

Answer 1

前回と異なり、複数のグラフでB列以外ではセル範囲が連続していないので、
B列も含め、セル範囲を2つ(X軸とY軸の1系列)設定してグラフが作成されていることが前提になります。
4つのグラフを個別にではなく、繰り返し処理でセル範囲を更新しています。
なお、4つのグラフの更新中に10秒後のデータがシート1に追記された場合
最新のセル範囲にならず、その後自動的にセル範囲が広がっていくことには
ならない場合があります。（厳密には前のグラフ1つの例でもあり得ました。）

// X軸にA列と系列としてB-E列の2つの列がセル範囲に
// 設定されたグラフがあることを前提としているので
// 既存のグラフのセル範囲が'シート1'!A1:B360なら
// X軸:'シート1'!A1:A360
// 系列（Y軸）:'シート1'!B1:B360 と設定して
// セル範囲を'シート1'!A1:A360;'シート1'!B1:B360
// とするグラフに変更しておく必要があります。
// C列、D列,E列のグラフも同様です。
function updateChartRange() {
  // 初期設定
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('シート1');
  const sheet2 = ss.getSheetByName('シート2');
  const xAxis = 'A';  // X軸の列
  const yAxes = [ // 系列（Y軸）の列
    'B',  // グラフ1
    'C',  // グラフ2
    'D',  // グラフ3
    'E',  // グラフ4
  ];

  // 最終行と1時間分遡った行を取得
  const lastRow = sheet1.getLastRow();
  const firstRow = lastRow <= 360 ? 1 : lastRow - 360;

  // X軸のセル範囲
  const rangeX = sheet1.getRange(xAxis + firstRow + ':' + xAxis + lastRow);

  // Y軸のセル範囲を順次配列に蓄積
  const rangeAdd = [];
  for (const yAxis of yAxes) {
    rangeAdd.push(sheet1.getRange(yAxis + firstRow + ':' + yAxis + lastRow));
  }

  // シート2のグラフを全て取得
  const charts = sheet2.getCharts();

  // それぞれのグラフごとに繰返し処理
  const newCharts = []; // 生成したグラフを蓄積する配列
  for (let chart of charts) {
    // グラフ作成ツールを取得
    const chartBuilder = chart.modify();

    // 設定されているセル範囲を取得
    const ranges = chartBuilder.getRanges();

    // セル範囲を全て削除
    for (const range of ranges) {
      chartBuilder.removeRange(range);
    }

    // X軸及びY軸のセル範囲を追加してグラフを生成し配列に蓄積
    chart = chartBuilder
      .addRange(rangeX)
      .addRange(rangeAdd.shift())
      .build();
    newCharts.push(chart);
  }

  // タイミングをあわせるため更新処理は分離
  for (const chart of newCharts) {
    // 生成したグラフで既存のグラフを更新
    sheet2.updateChart(chart);
  }
}

// ファイルを開いたときにメニューを追加
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('グラフ')
    .addItem('更新', 'updateChartRange')
    .addToUi();
}

Comments

[Aki1000]

ありがとうございました！！
本当に助かりました。