初心者でもわかるGoogle Apps Script活用のススメ

【徹底検証】GASでセルから最も効率的に値を取得する方法とは？

サイト管理人

今回はGASを使ってスプレッドシートの処理をする際に処理速度改善において考慮するべき事を紹介しようと思います。
GASでの処理高速化について、他のブログでもよく取り上げられるgetValues()を使って高速化することについては他のブログなどに紹介は任せるとして、本記事では私の経験上有効である考える手法を紹介したいと思います。

この記事ではできるだけ他のブログでは紹介されていないような内容の記事を目指します。

get(set)Valueとget(set)Valuesホントはどっちが早いの？

他のブログに紹介は任せると書きましたが、どうしても書いておきたいことがありましたので、最初に取り上げようと思います。

色々なブログでGASでの処理を高速化するならAPI呼び出し回数を減らせ！ということが書いてあります。私も概ね同じ考えなのですが、状況によっては呼び出し回数を減らすことが高速化につながらない場合もあるということを伝えたいと思います。

インターネットの普及によって、我々はみな同じように様々な情報が入手しやすく、そして発信もしやすくなりました。便利な世の中になった一方で、得られる情報を盲目的に信じてしまうはある意味では危険であると常々感じています。

getValuesやそれと合わせて紹介されることの多いgetDataRangeというメソッドはGASを使った処理において高速化に寄与するということが様々なブログで紹介されてあります。Googleからも公式にgetValueを多様するな！ということが言われていますので、これは間違いではありません。

ただ、私はAPI呼び出し回数減が高速化につながらない場合もあると考えています。それは次のような2つの場合が考えられます。

  1. １つのシートにデータが大量に格納されているスプレッドシート
  2. 処理対象のセル数が少ない処理

このような2つのシチュエーションでは、APIの呼び出し回数減が必ずしも処理の高速化には繋がりません。
このことを検証するためのスプレッドシートとサンプルスクリプトを用意しました。

動作検証

検証用スプレッドシート
検証用スクリプト

上記のリンクで実際に検証を行うことができますので、ご自身で試したい方はスプレッドシートにアクセスして上部メニューの『検証メニュー』をクリックしてスクリプトを実行してみてください。

const sheetUrl = "https://docs.google.com/spreadsheets/d/19RTV39Z6wJQdoa8txs_eSCQf_iA0LoRg9valE594hSQ/"; //スプレッドシートURL
const sheetName = "検証データ" //シート名

//ピンポイントでセルを指定して値を取得する(API呼び出し = 4回)
function getValue() {
  let start = Date.parse(new Date()) / 1000;
  let ss = SpreadsheetApp.openByUrl(sheetUrl);
  let sheet = ss.getSheetByName(sheetName);
  let value = sheet.getRange(51, 4).getValue();
  let end = Date.parse(new Date()) / 1000;
  let duration = end - start;
  Browser.msgBox(value + "( " + duration + " 秒 )");
}

//セル名でセルを指定して値を取得する(API呼び出し = 3回)
function getNamedRangeValue() {
  let start = Date.parse(new Date()) / 1000;
  let ss = SpreadsheetApp.openByUrl(sheetUrl);
  let value = ss.getRangeByName("target").getValue();
  let end = Date.parse(new Date()) / 1000;
  let duration = end - start;
  Browser.msgBox(value + "( " + duration + " 秒 )");
}

//非効率と言われるループ内にgetValueを使って条件と合う行数があればその行数から対象セルの値を取得する(API呼び出し = 105回)
function loopGetValue() {
  let start = Date.parse(new Date()) / 1000;
  let ss = SpreadsheetApp.openByUrl(sheetUrl);
  let sheet = ss.getSheetByName(sheetName);
  let lastRow = sheet.getLastRow();
  for (let i = 1; i <= lastRow; i++) {
    let value = sheet.getRange(i, 2).getValue();
    if (value == 50) {
      value = sheet.getRange(i, 4).getValue();
      let end = Date.parse(new Date()) / 1000;
      let duration = end - start;
      Browser.msgBox(value + "( " + duration + " 秒 )");
    }
  }
}

//キーワード列を最終行まで取得して条件と合う行数があればその行数から対象セルの値を取得する(API呼び出し = 7回)
function getRowValues() {
  let start = Date.parse(new Date()) / 1000;
  let ss = SpreadsheetApp.openByUrl(sheetUrl);
  let sheet = ss.getSheetByName(sheetName);
  let range = sheet.getRange(1, 2, sheet.getLastRow(), 1);
  let data = range.getValues();
  for (let i in data) {
    if (data[i][0] == 50) {
      let value = sheet.getRange(Number(i) + 1, 4).getValue();
      let end = Date.parse(new Date()) / 1000;
      let duration = end - start;
      Browser.msgBox(value + "( " + duration + " 秒 )");
    }
  }
}

//シートの全データを取得してキーワード列の条件と合う行数があればその行数から対象セルの値を取得する(API呼び出し = 4回)
function getDataRangeValues() {
  let start = Date.parse(new Date()) / 1000;
  let ss = SpreadsheetApp.openByUrl(sheetUrl);
  let sheet = ss.getSheetByName(sheetName);
  let data = sheet.getDataRange().getValues();
  for (let i in data) {
    if (data[i][1] == 50) {
      let value = data[i][3];
      let end = Date.parse(new Date()) / 1000;
      let duration = end - start;
      Browser.msgBox(value + "( " + duration + "秒 )");
    }
  }
}

検証方法

用意した検証用スプレッドシートは、D51セルの背景を赤色にしています。
今回は、4つの異なる方法を使ってこの値を取得するのにかかる所要時間を比較をしてみたいと思います。
キーワード列の値を検証する処理を行っている処理もありますが、キーワード列はB列とし、B列の値が50になる行のD列が対象であると言う条件で検証をしてみたいと思います。

  1. getValue − コードにD51セルをピンポイントで指定して値を取得する方法
  2. loopGetValue − ループ処理でB列の値を一つずつ取得して条件に一致する行のD列の値を取得する方法
  3. getNamedRangeValue − 名前付きセルから値を取得する
  4. getRowValues − キーワード列を一括で取得し、条件に一致する行のD列の値を取得する方法
  5. getDataRangeValues − シートの全データを一括で取得し、B列が条件に合う行のD列の値を取得する方法

APIの呼び出し回数を軸に考えると、loopGetValueだけが回数がめちゃめちゃ多いですが、それ以外の関数についてはAPI呼び出し回数はほとんど変わりません。処理速度が早い順に並べるとどうなると思いますか？

おそらく、他のブログを見た方の多くはloopGetValueが一番遅くなると考えると思います。API呼び出し回数が多いから遅くなるだろうと考えたわけですね。
その予想は正しくもありますが、間違いでもあります。

検証用スプレッドシートを開くと上部メニューに『検証メニュー』という項目が表示されます。これをクリックして関数名をクリックすると対象の関数を実行することができます。
初めてクリックすると許可の確認を求められるので、下記の記事を参考にGASに実行許可を付与してください。

あわせて読みたい
無料のアカウントでGASに初回の実行許可を与える方法【超入門】 今回と次回の投稿では、Google Apps Scriptでその他のG Suiteサービスの操作を行う際に必要なアクセス許可について説明します。Googleアカウントには無料のGoogleアカウ…

許可がされると関数が実行され、処理が完了するとD51セルの値（150）と処理時間がメッセージボックスで表示されます。どれが早いのかご自身で体験してみてください。

結論

結果はどうだったでしょうか？
105回APIの呼び出しをしているloopGetValue関数が一番遅い関数ではなかったと思います。

API呼び出し回数順に関数を並べると、

[少ない] getNamedRangeValue ＜ getValue ＝ getRowValues ＜ getDataRange ＜ loopGetValue [多い]

ですが、速度順に並べると、

[速い] getValue ＝ getNamedRangeValue ＜ loopGetValue ＜ getRowValues ＜ getDataRange [遅い]

上記の結果のように、GASではAPIの呼び出し回数が必ずしも処理速度に比例するわけではないということが検証の結果わかりました。

この検証結果からわかることは2つあります。

  1. getValuesはセルの指定範囲のデータ量によって処理速度が大きく変わる
  2. 盲目的にgetDataRange().getValues()を使うべきではない

ここでは、使用する状況によってベストな処理方法は変わるということを認識してもらえれば良いかと思います。

ちなみに、loopGetValueは検証用コードでは51行目までしかループしませんでしたが、もっと下の行数までループさせると、結果は全く異なり、処理速度はものすごく遅くなります。ですので、API呼び出し回数が速度に影響しないと言いたいわけではないのです。

セルから値を取得するベストプラクティス

それでは、次にどの関数がどういったシチュエーションに適しているのかを考えていきます。ちなみに、下記の例ではgetValueとgetValuesを説明に用いていますが、setValueとsetValuesでも考え方は同じですので、頭の中で読み替えて頂ければ良いかと思います。

getRange(row, column).getValue()

この処理のベストな利用シーンはある程度限定された条件になると思います。
私の場合は、この処理を使う場合は下記の条件でのみ利用すると思います。

取得対象のセルが毎回同じ場所にあり、対象のセルが少ないのであれば、getDataRangeを使ってデータの全体を取得対象とするよりも処理をスピーディーに行うことができます。
条件分岐等の処理とはあまり相性が良くないと思いますので、特に難しいことを考えずに特定のセルにある値を取得ができる場合はこの処理になるのかなお思います。
まずはデータ全体を取得する必要があるのかを一度考えて必要がなければ処理的に一番シンプルなgetRange(row, column).getValue()を利用するのがいいと思います。

この処理は、ループの中に入れてしまうと、処理が急激に遅くなる可能性が高いので、利用に気をつけてください。

ループ処理の中のgetRange(row, column).getValue()

直前でループ処理の中ではあまり使わないほうが良いと言ったばかりですが、ループ処理の回数がそれほど多くないのであればループの中で使うということも悪くはない選択肢かもしれません。

例えば、getDataRangeを使って全体を取得してしまうと膨大なデータ量となってしまう場合でも、取得範囲を必要最低限に限定（例えばD列のデータのみなど）してループ処理の中に入れることで全体を取得するよりも時間をかけずに値の取得ができるかもしれません。

この処理を利用してもそれほど非効率にならないという唯一の条件は将来的に膨大なデータ量になる可能性のあるシートにおいて、それほど多くない固定的な範囲の値を取得する場合に限られると思います。

正直そういったシチュエーションはあまり思い浮かばないので、ループ処理 × getRange(row, column).getValue()はあまり使わない方が良いというのは他の方々が主張している通りです。

getRange(row, column, sheet.getLastRow(), 1).getValues()

これは、条件分岐するキーワードなどが入っている列のみ値を取得してきて、行の特定をしてから検索対象のデータのみを効率よくピンポイントで取得するというやり方です。

ループ処理で値を一つ一つ取得するにはデータ量が多いが、getDataRangeで全体を取得するのはデータ量が膨大過ぎる場合に有効な手段だと思います。

検証用コードではgetRowValuesという関数がこの処理に当たります。
キーワードとなるB列のみ最終行まで値取得して、ループ処理で条件に合致する行を探します。
行数が特定できたら、その行のほしい列の値をピンポイントに取得すると言ったやり方です。

膨大な量のデータがすでにシートにある場合や、将来的に膨大な量になることが予想されるシートに対しては、この処理がとても有効であると思います。

getDataRangeでシートすべてのデータを取得する場合、データ量がある一定水準を超えると値の取得時間がめちゃくちゃかかってしまうようになるからです。
検証用コードのように、場合によっては数分かかる場合もあるため、取得後の処理がとても単純な処理だとしても時間がかなりかかってしまうのです。

getDataRange().getValues()

最後は様々なブログでよく取り上げられているgetDataRangeを使った値取得になります。
前項に書いたように、getDataRange().getValues()はある一定レベル（明確にどのレベルかは自分で経験して判断してほしい）を超えるデータ量のシートからデータを取得する場合に処理効率が著しく低下します。
そのため、どの程度のデータを処理することになるのかを考えた上で、採用するかを考えることをオススメします。

GASでは処理上限時間が非常に短く設定されていますので、極力時間のかかる処理は省いていきたいものです。

getDataRange().getValues()を使えるシチュエーションは、それほど多くないデータを扱っているシートに限定するべきだと考えます。
そのためには、データの入れ方の設計などは十分に行った上でコードを書き始めることをおすすめします。

何も考えずにデータを全部取ってこれるので、とても便利な関数なんですけどね。使い所を間違えると痛い目に遭います。

Sheet APIを利用したデータの取得と更新

今までは、SpreadsheetAppというGASで一般的に利用できるクラスでの話に終始していましたが、スプレッドシートを扱う処理には他にもSheets APIというものがあります。

Google for Developers
Google Sheets API  |  Google for Developers

こちらもSpreadsheetAppと同じようなことができるのですが、少し仕様が異なる部分もありますので、場合によってはSheets APIを活用するというのも効率的に処理ができる選択肢の一つだと思います。

SpreadsheetAppよりもSheets APIを使ったほうがいい場合

  • 同じスプレッドシート内の複数のシートに対して処理をする場合
  • データ量が多いシートに対して処理をする場合

他にもSpreadsheetAppではできないこともできたりしますので、場合によって使い分けることがオススメです。

Sheets APIがSpreadsheetAppよりも使いづらい点

  • コードの書き方が少し複雑
  • Sheets APIの公式ドキュメントが英語で内容も不親切
  • 参考になるブログがあまりない

上記のような理由から個人的にはあまり注目をされていないような気がするのですが、使えるようになると処理速度が劇的に向上する可能性があります。

Sheets APIについては、別の機会に記事を書こうと思っていますので、後ほどご紹介したいと思っています。

管理人が到達した悟りの境地

仰々しく見出しを書いてしまいましたが、GASにおいて、処理上限が6分間と短めに設定されていることから、値の取得に時間をかけてしまうのはかなり致命的です。なぜなら、値の取得は往々にしてメインの処理ではないからです。
それを少しでも解消するべく、データの入れ方を色々と考えたのですが、たどり着いた管理人のベストプラクティスを紹介しようと思います。

エンジニアの人からは邪道とか言われるかもしれませんが。。GASに最適化したデータの入れ方ということで大目に見てください。

検証により、GASではデータ量が多いほどgetValuesするのに時間がかかることがわかりましたが、そうなると処理を早くするにはデータ量（セル数/文字数など）を減らす必要があることが考えられます。どのようにデータ量を減らすのがベストでしょうか。

私が考えたベストプラクティスは、1つのセルに出力データを全部詰め込んでしまう方法です。通常1セルにつき1データを入れていると思いますが、このやり方では項目数が増えれば増えるほど列が増えていきます。1列増えるごとにデータ量に乗算されますので、仮に1万行のデータがあったとすると、1列増えるとセル数が1万増えてしまいます。
これをコンパクトにするには1セルにデータをぶっこむしかないと考えました。

1セルに入れられる文字数の上限は50,000文字までですので、実はスプレッドシートは１セルでかなり大量のデータが格納できるのです。
複数項目のデータを1セルにどうやって入れたらいいかをいろいろ考えましたが、プログラム的に扱いやすいのはJSON形式なので、JSONで1セルにデータをぶっこむことにします。

const sheetUrl = "https://docs.google.com/spreadsheets/d/1RIcqoxjRerkEbg-b-tMnkiJi8j3zFXavqb9dwPGnAJw/edit#gid=0";
const sheetName = "JSONでぶっこむ";

function setValueAsJson() {
  let ss = SpreadsheetApp.openByUrl(sheetUrl);
  let sheet = ss.getSheetByName(sheetName);
  let object = {
    "title": "初心者でもわかるGoogleAppsScript活用のススメ",
    "post": "【徹底検証】GASでセルから最も効率的に値を取得する方法とは？",
    "sample": "JSONでぶっこみたい",
    "category": "処理の高速化",
    "misc": "邪道処理"
  }
  sheet.appendRow([JSON.stringify(object)]);
}
JSONぶっこみサンプルスプレッドシート

上記スプレッドシートにアクセスすると上部メニューに『JSONぶっこむ』という項目があります。こちらをクリックして、『ぶっこむぞ』をクリックすると上記サンプルコードを実行できます。
初めてクリックすると許可の確認を求められるので、下記記事の手順に従ってスクリプトに実行許可を与えてください。

あわせて読みたい
無料のアカウントでGASに初回の実行許可を与える方法【超入門】 今回と次回の投稿では、Google Apps Scriptでその他のG Suiteサービスの操作を行う際に必要なアクセス許可について説明します。Googleアカウントには無料のGoogleアカウ…

実行が完了すればJSON形式でデータが追加されていると思います。もし入っていなかったら一度リロードしてみてください。

JSON形式で出力することのメリット

メリットはいくつもあると思っていますが、いくつか思い浮かんだものを。

  • 列数を少なくできるため、処理が高速化される
  • データの増減に柔軟に対応できる
  • プログラムでデータを扱いやすい

JSON形式で出力することのデメリット

  1. 人がデータを扱いにくい
  2. スプレッドシートの関数と併用できない
  3. スプレッドシートにデータをもたせる意味があまりない

メリットもデメリットもありますが、私の場合はそもそもシステム処理されるデータと人が触るデータは混在させないようなシステム設計をしているため、デメリットはあまり影響がありません。
しかし、この記事を読んでいる人の中にはシステムと人が協働しながら業務を進めている人もいると思います。そういった方にはあまりうまく使えないと思います。

JSON形式で出力時の注意点

JSON形式でスプレッドシートに格納する場合、JSONオブジェクトのままシートに出力はできません。シートに出力する前にJSON.stringifyしてJSONデータを文字列化しましょう。
データを取り出すときには逆に文字列ではオブジェクトとして機能しませんので、JSON.parseしてオブジェクトに戻してから処理しましょう。

行数が増えてくると、JSON.parseする量も増えて処理が遅くなる可能性もありますので、場合によってはgetRowValuesと併用して、キーワードのみ別の列で参照しやすくして、JSON.parseを最小限に抑えるといった処理もできると思います。

だいぶ前からこの形式でデータを処理していますが、データ項目が増えても列を増やさなくていいのはまじでめちゃくちゃ重宝しています。
50,000文字を超えるとしても、JSONを文字列化して50,000文字ごとに分割してセルに出力してしまえば、いくらでもデータを保存できますので、怖いものなしです。

まとめ

今回は、GASでスプレッドシートの処理をする際の高速化についてまとめてみました。
他のブログには載っていない内容を入れようと思って、今まで公開していなかった私独自のデータ出力方法も書いてみました。
いろいろな意見があると思いますが、私としてはこのやり方が自分の中でのベストプラクティスであると思っています。もし他にも高速化のヒントなどあれば共有ください。

Exit mobile version