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

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

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

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

スポンサーリンク

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

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

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

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

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

では、API呼び出し回数減が高速化につながらないのはどのような場合でしょうか。答えは、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呼び出し回数が多いから遅くなるだろうと考えたわけですね。その予想は正しくもありますが、間違いでもあります。

検証用スプレッドシートを開くと上部メニューに『検証メニュー』という項目が表示されます。これをクリックして関数名をクリックすると対象の関数を実行することができます。
初めてクリックすると許可の確認を求められるので、『 無料のGmailアカウントでGASに初回の実行許可を与える方法 』の手順に従ってスクリプトに実行許可を与えます。

許可がされると関数が実行されますが、処理が完了すると、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()

この処理のベストな利用シーンはある程度限定された条件になると思います。

  1. 対象のセルが毎回同じ場所である
  2. 取得する値の数が少ない

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

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

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

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

例えば、検証用のスプレッドシートのように、全体を取得してしまうと膨大なデータ量となってしまう場合でも、範囲を限定してループ処理の中に入れることでそれほど膨大な時間をかけずに値の取得ができるかもしれません。
この処理を利用してもそれほど非効率にならないという唯一の条件は将来的に膨大なデータ量になるシートにおいて、それほど多くない固定的な範囲の値を取得する場合に限られると思います。

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

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

これは、条件分岐するキーワードなどが入っている列のみ値を取得してきて、行の特定をしたりして、必要なセルを効率よくピンポイントで取得するというやり方です。

前述のループ処理で値を一つ一つ取得するにはデータ量が多いが、後述するgetDataRangeで全体を取得するのはデータ量が膨大過ぎるため、逆に非効率になってしまうような場合に有効な手段だと思います。

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

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

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

getDataRange().getValues()

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

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

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

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

管理人が到達した悟りの境地(必見)

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

エンジニアの人からは邪道とか言われるかもしれませんが。。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ぶっこむ』という項目があります。こちらをクリックして、『ぶっこむぞ』をクリックすると上記サンプルコードを実行できます。
初めてクリックすると許可の確認を求められるので、『 無料のGmailアカウントでGASに初回の実行許可を与える方法 』の手順に従ってスクリプトに実行許可を与えます。

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

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

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

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

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

  1. 人がデータを扱いにくい
  2. スプレッドシートの関数と併用できない

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

注意点

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

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

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

まとめ

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

Exit mobile version