別の関数ネタを書こうと思ってたんですが、ちょうど最近知恵袋で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はあまり使いませんが、コードとしてはいたって普通ですね。

このような質問でした。

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

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

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

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

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

ふっしぎー。

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

さらに以下のように

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

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

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

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

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

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

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

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

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

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

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

そうすると I21 セルの式

=F1/10

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

となりますよね。

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

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

新たな GAS関数の出現

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

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

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

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

先入観による初動ミス

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

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

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

はたまた、取得した 二次元配列に #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;
}

このように / で挟むことで、正規表現として 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では取得できない を解決するのではなく、

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

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

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

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

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

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

こんな感じで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のコードがなんか間違ってるんだろ！ よく見ろ」とか

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

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

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

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

問題解決にあたって

• 先入観を排除する

• 相手の話を部分的にではなく全体をよく聞き理解する

• 目の前だけでなく 問題の元凶を確認し必要に応じて根本から対処する

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

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

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

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

ってお話でした。

次回こそは 関数ネタを。