GAS 大切なことは目に見えないんだよってはなし

mir

2023年9月10日 12:01

別の関数ネタを書こうと思ってたんですが、ちょうど最近知恵袋でGAS関連の回答をした時に 少しハマったんで記録しておこうかなと。

これは実際の知恵袋での質問を元にした フィクションです。登場するコード・質問のセル位置、内容など実際の質問とは異なる箇所がございます。

要はスクールウォーズみたいなもんですｗ

ちなみに「大切なことは目に見えないんだ」は、サンテグジュペリの著書「星の王子様」に登場する名言ですね。

前回の note。2週にわたって AIを使いながら GASを使った Googleスプレッドシートの行削除の繰り返し処理を学びました

なぜかGASでコピペするとエラーになる？

function _1(){
  //アクティブシートの取得
  const sheet = SpreadsheetApp.getActiveSheet();

  //I2:I23をK2セルに値のみ貼り付け
  sheet.getRange('I2:I23').copyTo(sheet.getRange('K2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

今回の質問に掲載されていたコード。

GASに慣れると値を貼り付けで copyToはあまり使いませんが、コードとしてはいたって普通ですね。

Googleスプレッドシートで、これを実行すると I22 と I23のセルのデータが、コピー先の K22 K23 で #VALUE! となってしまいます。

どのように記述したら、 #VALUE! ではなく、値がコピーされますか？

このような質問でした。

元データが #VALUE!になっているわけではないとのこと。

手動ではコピペできるのにGASだとエラーに？？

コードは特に問題なく動作してますね。

ちなみに I22,I23 には数式が入っていますが、シート上ではエラーになっているわけではなく、きちんと数値が表示されています。

でも、確かに値貼付けした右側 K列では K22、K23だけ #VALUE! になっちゃいますね。

ふっしぎー。

もちろん、これは解決して原因がわかってるから再現できてるのであって、リアルタイムの知恵袋の質問では、他の要因やシートの構成を探りながら原因を究明していく形なので、なかなか大変でした。

さらに以下のように

手作業でコピペ（値を貼付け）した場合は、エラーにならず元のセルに表示されている通りに値が貼付けできるというのも謎。

GASでのコピー値貼付けだけで発生するこの問題

いったいなにが原因なのか？？

見えないものを見ようとして～♪

copyToメソッドがダメなら、getValues() 、いやむしろ getDisplayValues() で取得してみたらどうか？

うーん、貼り付けるまでもなく取得した段階で #VALUE!エラーです。

getDisplayValue / getDisplayValues は、シート上で表示されてるままの（表示形式を反映させた）値を取得することが出来るメソッドで、日付を文字列として取得したい時には超便利なんですが・・・。

今回は通用しませんでした。

しかし、 #VALUE!エラーが出るのはなんでなんでしょうか？

そもそも、このエラーが発生するのは、

こんな感じで、テキストを四則演算子と組み合わせて計算に使用している時が多いんですが・・・。

そうすると I21 セルの式

=F1/10

で参照している F1が怪しいのでは？

となりますよね。

推理モノで、容疑者の生まれ育った故郷に犯行の動機や心情を探るヒントを求めて赴く流れですねw

古谷一行だったら、行った先の混浴温泉で女子大生3人組と出くわして、「なんだ、また君たちか～」というサービスカットが入りますねｗ

新たな GAS関数の出現

聞き込み調査を進めると、なんと F1には

=STOCKPRICEJP(5243)

といった関数が使われているとのこと。

こんなシート関数は存在しないので、GASによる自作関数と思われます。おそらく関数名からして 日本の株価を取得する関数じゃないかなと。

ちなみに 5243は、こちらの note ですｗ

先入観による初動ミス

GASによる自作関数ってことで、このコピペのGASを実行した際に再計算が走って、その結果の取得に時間がかかる（ローディング中にコピペ処理が進んでしまう）せいで、エラーになるに違いない！

なら、処理を遅延させてから取得する

//5秒待つ
Utilities.sleep(5*1000);

スプレッドシートを更新させてから値を取得する

SpreadsheetApp.flush();

はたまた、取得した二次元配列に #VALUE! が含まれていたら、再度取得させるという whileループ でエラーがなくなるまで取得を繰り返す

この辺りで解決するだろ ってことで、元のコードに上のコードを入れてみたり、以下のようなコードを書いて試してみました。

function _1(){
  //アクティブシートの取得
  const sheet = SpreadsheetApp.getActiveSheet();

  let values;
  
  //#VALUEエラーが含まれている場合は再取得
  do{
    values = sheet.getRange('I2:I23').getDisplayValues();
    console.log(values);
  }while(values.flat().includes("#VALUE!")); 
}

do whileは 1回はやってみて評価する while文です

配列の中に含まれるかを true / falseで得るなら includes が簡単。

というわけで、これらを試してみましたが・・・。

いずれもダメ。

最後のループ処理は3分くらい待ったけど終わらないので、途中でキャンセルｗ

さんざんな結果となりました。

「よし、わかった！」でおなじみの元祖金田一シリーズの等々力警部（もしくはコナンだと毛利小五郎）のごとく、豪快に推理ミスをしてしまったわけです。

日本株の株価を返すGAS自作関数 STOCKPRICEJP

自分の初動ミスを反省し、改めて原点であるF1セルに入っている STOCKPRICEJP というGASで作成された自作関数が、どのようなコードで、どのような値を返しているのかを確認することに。

確認すると、コードは以下のようなものでした。

function STOCKPRICEJP(code){
  let url = "https://www.google.com/finance/quote/" + code + ":TYO";
  let html = UrlFetchApp.fetch(url).getContentText();
  let stockPrice = Parser.data(html)
    .from("<div class=\"YMlKec fxKbKc\">")
    .to("</div>")
    .build();
  return stockPrice;
}

いくつか同じ関数を紹介してるサイトがありましたが、元祖はここかな。（違ったらすいません）

HTMLを扱うのに便利な Parserライブラリ

コードの中でポイントとなるのが、

  let stockPrice = Parser.data(html)
    .from("<div class=\"YMlKec fxKbKc\">")
    .to("</div>")
    .build();

この Parser ってやつです。出典元のサイトを見れば記載がありますが、上のコードはただコピペしただけではそのまま使えません。

「Parser なんて定義されてないぞゴルァ！」

とエラーになります。

使う場合は、上のサイトをよく読んでライブラリを追加してあげましょう。

GASでストレイピングとかしている人にはお馴染みの Parserライブラリです。こちらを入れることで、HTMLから特定の箇所を簡単に抜き出すことが出来ます。

今回の場合は、

この金額を取得したいわけですから、 HTMLの中から

<div class="YMlKec fxKbKc">　ココが欲しい　</div>

この間を取得すれば良いわけですね。

この抽出処理に Parser を使ってるわけです。

Parserライブラリを導入方法や使い方は以下のサイトあたりを参考に。

正規表現と match でも出来る

余談ですが、この程度なら Parser入れるまでもなく正規表現と match を使った記述に置き換えることで、ライブラリの追加なしでも対応できます。

function STOCKPRICEJP2(code){
  let url = "https://www.google.com/finance/quote/" + code + ":TYO";
  let html = UrlFetchApp.fetch(url).getContentText();
  let stockPrice = html.match(/<div class="YMlKec fxKbKc">(.+?)<\/div>/)[1];
  return stockPrice;
}

/<div class="YMlKec fxKbKc">(.+?)<\/div>/

このように / で挟むことで、正規表現として matchメソッドを使った抽出ができます。

一点だけ注意点があって、 </div> の / は正規表現において特殊な意味合いをもつ メタ文字ってやつなんで、エスケープさせる必要がある為 <\/div> と前に \ （バックスラッシュ）をつけています。

(.+?)　で取り出した株価の部分（キャプチャグループ1） は matchの配列の1番目を取り出すことで取得できるので、最後に [1] としています。

今回はエスケープ箇所が1カ所だけだったんで正規表現で取得するのも簡単でしたが、HTMLのタグは結構エスケープが必要なんで、Parserで処理した方が簡単ってケースも多いのです。

STOCKPRICEJP の結果を確認する

これを実際にシート上でセルに入れて使ってみると、このように株価が取得できているのがわかります。

ちなみに STOCKPRICEJPは、シート上でセルに入れて自作関数として使う分には承認は不要ですが、エディタ上で実行すると スクリプトの承認が必要になります。

で、このSTOCKPRICEJP の返り値ですが・・・

左寄せになってるし、¥マークついてるし、GAS関数から出力されているのは完全に文字列（テキスト）ですね。

セル上の値のタイプを判別するときは TYPE関数が使えます。TYPE関数はExcelにもある関数です。

このF1の結果がテキストってのが、事件を解決するキーなのでは！？

シート上の計算結果とサーバー側の結果が違う

で、そろそろ気づいた人もいるかと思いますが、今回のトラブルの原因はこの通り シート上でテキストが計算できちゃうことにあります。

シート上では文字列である ¥461.00 を四則演算子で計算すると、気をきかせて 461.00 という数値として見てくれて、計算結果が返ります。

つまり、シート上ではテキストデータの F1セル ¥461.00 を

=F1/10

と計算に使った際は数値としてみなしてくれて

= 461.00 / 10 = 46.1

と計算結果を返してくれているので、すっかりこの 46.1が GASでも取得できるつもりでいました。

GASではシート上と同じ計算結果が取得できない

実はサーバー側（GASで取得できる値）は同じようにいきません。

残念ながら GASからはこの 46.1は見えず、GASから取得できるサーバー側の結果は 厳密に¥461.00 をテキストと区分し

= "¥461.00" / 10 ・・・テキスト（異物）が混じってる！

ってことで #VALUE! というエラー結果が見えていたわけです。

ちなみにセルに直接

="¥461.00"/10

と入れた場合もセル上では数値として処理され計算結果が返りますが、GASから取得した場合は #VALUE! エラーとなります。getDisplayValueを使っても同様。

シート側としては「あなたの為を思って」親切心でテキストを数値化して計算結果を返してくれていたのですが、厳格なサーバー（GAS）側とのすれ違いが生んだ悲しい事件でした。。（コナンでよくあるパターン）

実はスプレッドシートで少し変わったことをしようとすると、この 見えているもの（シート上の結果）、見えないもの（サーバー側の結果）の違いに悩まされたり、気づかずにハマることが結構あります。

このズレは 関数でスピンボタンを作成した回にも登場しましたね。

とりあえず今回はこの段階で気づけて良かったんですが、残念ながらこの見えている値は、どうやってもGASで取得することができません。（もしかしたら特殊なやり方があるのかもしれませんが、mirは見つけられません）

こんなに近くにいるのに、手を伸ばしても届かない～ ♪という、どっかで聞いたことあるようなラブソングの歌詞状態です。

原因はわかったけど、どうにもならないのか？解決策はないのか？

過去に戻って元を断つ！

このシート側の計算結果を取得する方法で頭を悩ませても答えがでないので、今回は早々に原因の元を断つプランに変えました。

つまり

GAS関数 → 結果が文字列で返る → 文字列だけどシート上では計算結果が返る → GASでは取得できない

この最後の段階の GASでは取得できない を解決するのではなく、

GAS関数
→ ✖ ~~結果が文字列で返る~~　
　○ 結果を数値で返す

このように初期段階、つまり STOCKPRICEJP 側のコードを修正して、結果を数値で返せるようにすれば、

GAS関数 → 結果は数値で返る → シート上で計算結果が返る → GASで取得できる

とシンプルに解決できるわけです。

「これはオレの人生の”リベンジだ”」で過去にタイムリープする展開です。

金額表記を数値に変換するコード修正

では、日本円の 金額表記の文字列を数値に変換するにはどうすればよいか？

邪魔な ¥マーク、そして 三桁の , を削除して、Number() に入れて数値化が一番丁寧・確実でしょう。

stockPrice = Number(stockPrice.replace(/[¥,]/g, ''));

※ ¥ さえついていなければ数値として見なしてくれるんで、ここまで丁寧でなくても本当はOK

こんな感じでreplace を使って、 ¥または , を "" に置き換える処理でいけそうです。

桁が大きく, が複数ある可能性もあるので、 gフラグでマッチした全てを対象とするとすることを忘れずに。

というわけで数値を返す修正版の STOCKPRICEJP関数はこんな感じ。

function STOCKPRICEJP_NUM(code){
  let url = "https://www.google.com/finance/quote/" + code + ":TYO";
  let html = UrlFetchApp.fetch(url).getContentText();
  let stockPrice = Parser.data(html)
    .from("<div class=\"YMlKec fxKbKc\">")
    .to("</div>")
    .build();
  stockPrice = Number(stockPrice.replace(/[¥,]/g, ''));
  return stockPrice ;
}

これでマイキーくんの闇落ち（ #VALUE! エラー）を回避できるのか！？

結果の確認

修正版のコードで確認してみましょう。

まず、修正をしたことで、このように株価の結果が数値で返りました。

これだと株価っぽくないって場合は、セルの表示形式で対応すれば良いですね。

元凶を直せば、コピペ処理の方は元々の copyToのコードそのままでまったく問題ないです。

function _1() {
  //アクティブシートの取得
  const sheet = SpreadsheetApp.getActiveSheet();

  //I2:I23をK2セルに値のみ貼り付け
  sheet.getRange('I2:I23').copyTo(sheet.getRange('K2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

↓ このように #VALUE! エラーは発生せず、K22,K23のエラーが解消された未来になりました～。

途中、方向性を間違えて迷宮入りするところでしたが、無事この事件を解決することが出来ました。

大切なことは目に見えないんだよ

今回のトラブル、

手動でコピーして値貼付けだと問題ないのに、GASで実行すると copyToだろうが getDisplayValuesだろうが一部が #VALUE!エラーになる。

これ、リアル職場で忙しい時に質問されたら下手すると

「そんなわけないだろ！」とか

「GASのコードがなんか間違ってるんだろ！よく見ろ」とか

「モルダーあなた疲れてるのよ」

で終わってしまう事案じゃないでしょうか？

今回のようなコピペ程度のコードでそんなエラー出るわけないし、そもそも手動とGASで結果が違うわけがないだろっていう先入観があるからです。

でも、Googleスプレッドシートにおいて見えているシート（ローカル）側とGASで取得できるサーバー側で 値が違うことがある ってことを知っていて、しっかり話を聞いてコードや表で使われている値や式など 全体を確認すれば、原因を見つけ正しい解決方法を提示することも出来るわけです。

問題解決にあたって

先入観を排除する
相手の話を部分的にではなく全体をよく聞き理解する
目の前だけでなく問題の元凶を確認し必要に応じて根本から対処する

こんな学びがあったので紹介させていただきました。

皆さんの職場にいる見た目は元気そうで成果を出している社員は、実は心の中で #VALUE!エラーが出てたりしませんか？

見えているものが全て正しいわけじゃなくって

大切なことは目に見えないんだよ

ってお話でした。

次回こそは関数ネタを。

この記事が気に入ったらサポートをしてみませんか？