スプレッドシートのセルを操作

前回に引き続きスプレッドシートでXML情報を取得して表形式に出力する方法について。今回は表に出力する編。

セルの操作手順

セルの値を取得したり変更したりする場合、以下のステップを踏みます。

  1. スプレッドシートオブジェクトから対象シートのオブジェクトを取得
  2. 対象シートのセル範囲を指定したオブジェクトを取得
  3. 指定セル範囲の値を取得・変更できる

対象シート→対象セルの順番でオブジェクトを取得してようやくセルへのアクセスが出来る状態になります。

セルにアクセスできるようになるまで

まず最初にSpreadsheetAppClassのgetActiveSheetメソッドで現在開いているシートのオブジェクトを取得します。

var sheet = SpreadsheetApp.getActiveSheet();

次にgetRangeメソッドで値を取得・変更したい対象のセルを指定します。

var range = sheet.getRange(1, 1);

getRangeメソッドはSheetクラスのメソッドで以下のように設定します。

getRange(row, column)
NameTypeDescription
rowIntegerthe row of the cell to return
columnIntegerthe column of the cell to return

セルA1を指定する場合は、getRange(1, 1)と指定することになります。これで手順は理解したので、XMLデータを取得してセルに表示してみます。

RangeクラスのsetValueメソッドで値をセット

RangeクラスのsetValueメソッドを使って値をセットします。前回のXMLデータから記事タイトルと投稿者をセルに表示してみます。

function parseXml() {
  // フィードのURL設定
  var feedURL = "https://www.terakoya.work/feed/";

  // フィードを取得
  var response = UrlFetchApp.fetch(feedURL);

  // XMLをパース
  var xml = XmlService.parse(response.getContentText());

  // 名前空間
  var namespace = XmlService.getNamespace("dc", "http://purl.org/dc/elements/1.1/");

  // 各データの要素を取得
  var entries = xml.getRootElement().getChildren("channel")[0].getChildren("item");

  // 要素数を取得
  var length = entries.length;

  // アクティブシート取得
  var sheet = SpreadsheetApp.getActiveSheet();

  // 取得したデータをループさせる
  for(var i = 0; i < length; i++) {
    // 記事タイトル
    sheet.getRange(i + 1, 1).setValue(entries[i].getChildText("title"));

    // 投稿者
    sheet.getRange(i + 1, 2).setValue(entries[i].getChildText("creator", namespace));
  }
}
setValueの実行結果

とれた！＼(^o^)／………が何か遅くないか？たかだか20件ぐらいのデータを取得するのに何秒かかるんだ？ちょっと時間計測してみよう。

処理にかかる時間をログに出力してみる。

スクリプトの処理の開始時に現在の日時を取得して変数に保存しておきます。

var startTime = new Date();

スクリプト処理に最後にまた現在の日時を取得して、開始時間との差分を計算してログに表示します。

var endTime = new Date();
var execTime = (endTime - startTime) / 1000;
Logger.log('処理時間:' + execTime);

準備完了！いざ実行！

実行にかかった時間

ろ、6.694秒だと……？遅いー！これ100件、1,000件と件数増えたらどんだけ遅いんだ？これはAPIが遅いのか？何か速くする方法はないものか？うーん。

RangeクラスのsetValuesメソッドでまとめて値をセット

RangeクラスにsetValuesという配列の値をまとめてセットするメソッドがありました。これならいけそう。ということで、プログラムの改修。

〜〜省略〜〜

// setValuesメソッドに渡す配列定義
var data = [];

// 取得したデータをループさせる
for(var i = 0; i < length; i++) {
  // ワーク配列
  var args = [];

  // 記事タイトル
  args.push(entries[i].getChildText("title"));

  // 投稿者
  args.push(entries[i].getChildText("creator", namespace));

  // setValues用配列にpush
  data.push(args);
}

// getRangeで範囲設定（開始行, 開始列, 終了行, 終了列）
sheet.getRange(1, 1, length, 2).setValues(data);

getRangeメソッドもrow, columnだけの設定でなく、開始行, 開始列, 終了行, 終了列を設定して配列をセットする範囲を指定します。これでまとめて表示できました。計測時間は、6.122秒と少しだけ速くなりましたが、5,000件ぐらいのデータを実際に試してみたら30秒近く差が出ました。ソースコード全文貼り付け。

function parseXml() {
  // 処理時間計測開始
  var startTime = new Date();

  // フィードのURL設定
  var feedURL = "https://www.terakoya.work/feed/";

  // フィードを取得
  var response = UrlFetchApp.fetch(feedURL);

  // XMLをパース
  var xml = XmlService.parse(response.getContentText());

  // 名前空間
  var namespace = XmlService.getNamespace("dc", "http://purl.org/dc/elements/1.1/");

  // 各データの要素を取得
  var entries = xml.getRootElement().getChildren("channel")[0].getChildren("item");

  // 要素数を取得
  var length = entries.length;

  // アクティブシート取得
  var sheet = SpreadsheetApp.getActiveSheet();

  // setValuesメソッドに渡す配列定義
  var data = [];

  // 取得したデータをループさせる
  for(var i = 0; i < length; i++) {
    // ワーク配列
    var args = [];

    // 記事タイトル
    args.push(entries[i].getChildText("title"));

    // 投稿者
    args.push(entries[i].getChildText("creator", namespace));

    // setValues用配列にpush
    data.push(args);
  }

  // getRangeで範囲設定（開始行, 開始列, 終了行, 終了列）
  sheet.getRange(1, 1, length, 2).setValues(data);

  // 処理にかかった時間を計測する
  var endTime = new Date();
  var execTime = (endTime - startTime) / 1000;
  Logger.log('処理時間:' + execTime);
}