【GAS】データが正しく取得できないときの対処法｜getValues,getDisplayValueで数式が入ったセルの値がゼロ(0)になる・表示されてる値と違う(getRange.getValue,Google App Script・スプレッドシート・ガス)

スプレッドシート・スプシspreadsheet GAS-prograshi（プロぐらし）-kv SpreadSheet
記事内に広告が含まれていることがあります。

GASのgetValueやgetValues,getDisplayValueなどでスプレッドシートの値を取得しようとしたときに、実際に表示されている値と、取得した値が異なる場合があります。

ここでは、この原因と対処法についてまとめています。


エラー（不具合）内容

この事象はエラーとして報告されるわけではないのですが、実際にスプレッドシートに表示されている値と、取得してきた値が違うというのは開発者からするとエラー以外のなんでもありません。

例えば、スプレッドシート上では以下のように、N15セルの値は「4」となっています。


しかし、sheet.getRange( "N15" ).getValue()やsheet.getRange( "N15" ).getDisplayValue()などで取得した値は「0」になります。

もちろん、.getValues()など配列で取得した場合も同じように０になります。

  let testVal = sheet.getRange( "N15" ).getValue(); 
  let testDisplayVal = sheet.getRange( "N15" ).getDisplayValue(); 
  console.log(testVal);
  console.log(testDisplayVal);


//出力結果
12:16:19	情報	0
12:16:19	情報	0


なお、他のセルでは「7」と表示されているのに、取得した値は「4」となり、表示されている値と取得した値が異なるという事象も発生していました。


原因

この原因はスプレッドシートで複雑な数式や大量の数式を使用していることで、自動計算で数式処理が完了するまでに時間がかかることです。

GASでシートにアクセスすると、その瞬間から自動計算が始まるのですが、その処理が完了する前の計算途中の段階でgetValue()などを行うため、取得した値が「0」になったり、異なる数値になったります。

このため、数式で算出している値を文字列に変えてGASを実行すれば、きちんとその値を取得してきてくれます。


対処法

考えうる対処法はいくつかあります。（きっと他にもたくさんあると思いますが一例です。）

対処法
  1. スプレッドシートの数式処理を軽くする
  2. Utilities.sleep()などでプログラムを待機する。


スプレッドシートを軽くする

スプレッドシートの数式処理や条件付き書式の処理が多すぎて、自動計算が終了するまでに時間がかかっていることが原因なので、スプレッドシートの処理を軽くします。

やることとしては、数式を簡略化する。数式を削除する。行列が多すぎるのであれば削除して減らすなどです。


Utilities.sleep()などでプログラムを待機する

２つ目の対処法はUtilities.sleep()など、プログラムを一時的に待機させる処理を使用することで、対象のスプレッドシートの計算処理が終わってから、getValue()などの処理を走らせることです。


Utilities.sleep()とは何か？

Utilities.sleep(ミリ秒)とは指定した時間（ミリ秒間）処理を一時的に停止するGoogle App Scriptのプログラムです。

UtilitiesとはGASの専用のクラスで、文字列のエンコード/デコード、日付の書式設定、JSON操作などのメソッドが用意されています。

そのUtilitiesクラスの中のsleepメソッドを使用するということです。

例えば、10秒停止する場合は以下のように記述します。

Utilities.sleep(10000);

なお、最大の指定は3000,000ミリ秒（5分）です。

ユーティリティとは何か？

Utilities（ユーティリティ）とは、「便利」や「有用」という意味で、プログラミングで使用する場合は「便利なメソッド」や「便利な関数」といった意味になります。ユーティリティ関数といったりもします。

（参考）Apps Script >リファレンス > Class Utilities
　　　　sleep(milliseconds)


setTimeoutを使う

Utilities.sleep(ミリ秒)以外にも、setTimeout(code, ミリ秒)を使う方法があります。setTimeoutはJavaScriptのメソッドで、指定したミリ秒後に、指定したコードを実行するものです。

処理を関数化している場合はこちらの方が使いやすいかもしれません。

（参考）Mmdn web docs setTimeout()


Utilities.sleep()の注意点

Utilities.sleep()を使う際に注意しなければいけないことがあります。それは、記述する場所です。

GASでコードを記述する場合、処理開始前の準備として、上の方に対象のスプレッドシートやIDなど変数に代入する処理を記述することがあります。

この前処理のタイミングに記載してもあまり意味がありません。例えば、以下のような表記です。

  const targetSsId = "スプシのID番号";

  const ss = SpreadsheetApp.openById(targetSsId);
  const sheet = ss.getSheetByName("★担当別スケジュール");

  let targetStartCol = 13;
  let targetEndCol = 379;

  Utilities.sleep(500); //ここに記述しても意味ない

  sheet.showColumns( targetStartCol, targetEndCol - targetStartCol );

このタイミングでUtilities.sleepを実行してもただ時間が経過するだけです。

Utilities.sleepを記述するのはあくまで、.getRange()などでGAS経由でスプレッドシートにきちんとアクセスしてからにします。

こうすることで、GASが対象のスプレッドシートにアクセスする → 自動計算が行われる → Utilities.sleepで待機する → 自動計算が終わる → getValueなどでデータを取得するといった流れになります。


おそらく、こちらの方法でデータが正しく取得できるかと思いますので、お困りの方はぜひ試してみてください^^



タイトルとURLをコピーしました