スクリプトプロパティ

今回のプログラムでは、あるデータを支店ごとに分割して、支店ごとに用意されているフォルダにコピーするというのが、ざっくりとした仕様。 GAS（Google）の世界では、フォルダやファイルはIDというユニークな文字列で定義されていて、ナニをするにもIDがないと、どうにもならない。 支店のフォルダにコピーするときも、そのID（フォルダID）が必要。 自分（GASが紐づいているスプレッドシート）で持っていればいいんだけど、支店の統廃合があった場合など、そのデータではコピーできなくなってしまう。 で、コピーするときに実フォルダを検索して、見つかったフォルダのIDを利用する方式を考えたんだけど、えらい時間が掛かってしまう。 そこで、登場するのがスクリプトプロパティ。 取り敢えずは、検索して、見つかったら、支店名をキー、フォルダIDを値にしてスクリプトプロパティにセットとする。 以降は、検索する前に、まずスクリプトプロパティを探してみつかれば、それを使い、見つからないときにだけ、実際のフォルダを探しに行くようにした。 初回は時間が掛かるものの、データが増えれば、だんだん処理時間も短くなる。 同様の仕組みは、スプレッドシートで管理しても出来るんだけど、AI君によると、断然スクリプトプロパティを使った方が早いと教えてくれた。 てか、スクリプトプロパティの存在自体もAI君に教えてもらったんだけどね。
--
のち

解決策

今回の案件、各支店に送ったたデータ、今回はスプレッドシートなんだけど、それを支店がほんとに見てるかどうかを感知して、ある程度時間が経っても見てないようならメールかなんかで注意喚起するというのが、大まかな流れ。 ずっと、支店がオープンしたときに、ノテファイさせて、サーバ（うち）が受ける方式を考えていたんだけど、onOpen イベント（オープン時に起動されるプログラム）には、色々制約があるみたいで、他のファイルとの通信が出来ないことがわかった。 そこで、考えを変えて、サーバ側から支店のスプレッドシートをフェッチして、オープンされたかどうかを調べるようにした。 スプレッドシートには、ドキュメントプロパティというのがあって、そこにオープンした回数なんかを書き込めるんだけど、Google の仕様で、そこには、他（サーバ側）のプログラムからはアクセスできない。 そこならユーザ側には見えない領域なので、お誂えむきだったのだけど、使えないことが分かった。 そこで、仕方なく非表示のシートを1枚作って、オープン回数を保存するようにした。 サーバ側は、支店に送ったスプレッドシートを、適当なタイミングで、こっそりのぞいてオープン回数を見るとう仕組み。 シートを使うのがイマイチどんくさいけど、まあ、これならみんな納得してくれると思われ。 夏休みをずらした甲斐があった。
--

挫折（VBAからGASへのデータ連携）

VBAからメールとGoogleチャットにメッセージを飛ばすためにデータ連携をする方法を考えている。 ボクの知識だとファイルを介してのデータ連携しか思いつかなかったので、もっとスマートな方法はないものかと社内AIに聞いてみた。 そしたら、VBAからHTTPを使って、GASをWebアプリにして、doPostで送る方法を教えてくれた。 GAS側にトリガ設定をする必要もなく、全てのデータをVBAから受け取るので、GAS側でメールアドレスや、チャットのWebhook URLを管理する必要もないし、実装も割と簡単そうだったので、この方法を採用することにした。 サンプルコードをAIに教えてもらい、実行してみると、上手く行かない。 色々調べると、セキュリティの関係でプログラムからのHTTPリクエストをGASが受け取る前にGoogleさんてかWebサーバ？が破棄してることがわかった。 他にも、googleフォームを偽装して、送信する方法も教えてもらったけど、これもやっぱりHTTPなので、届かなかった。 なかなか画期的な方法だと思ったんだけど、結局使えなかった。 仕方がないので、きょうのところは、JSONにしたデータをファイルにしてGoogleドライブに置いて、GAS側は、ポーリングでファイルをスキャンして、見つけたら読み取って、内容によりメールやチャットを飛ばすことにした。 ただ、今までは、ツールごとにVBAとGASでやり取りをしていた（宛先はGAS側で管理）けど、送り先もVBAからもらうようにすることで、GAS側はどのツールからも一元的にこの送信ツールで処理できることになる。 今までよりも、ちょっとはスマートになるかも。 ここに至るまで1日掛かったけど、少しは得るものがあった。
--

バグ

件のExcelファイルのリストを元にPDFファイルを作るプログラム。 先週リリースしたんだけど、きょう、クレームが来た。 Excelファイルをスプレッドシートに変換して、読み込むところでエラーになる。 ボクの環境で、おなじExcelファイルをコピーしてやってみたら、なんの問題もなく動いた。 色々調べた結果、Excelファイルを置いてあるフォルダが共有ドライブのせいだいうことが分かった。 ＤriveAPIを使っているのだけど、マイドライブと共有ドライブでは挙動が違うらしい。 Excelファイルを置くフォルダをマイドライブに変えて実験してもらうと、上手く行った。 こんなところでエラーになるとは思っていなかったよ。

■

続 夢千代日記 ▶ 仕事のあと、続 夢千代日記#5（最終回）を見る。 なんとも、切ないエンディングだった。
--

サラダボウル ＃4

昨夜、リアルタイムで見る。 お父さん役は、番宣でやっていた台湾の役者だと思われ。 なんか、切ない話しだった。

■

社内用ChatGPTと会話していて、google.script.runという機能が、サーバサイドからクライアント側のJavaScriptを呼ぶことが出来ることが分かった。 ネットを見ても、クライアント側からサーバサイドの関数（GAS）を呼ぶコードしか出て来なくて、GAS側から、クライアント側の関数を呼ぶコードは見つけられなかったけど。 試してないから、嘘かもしれないけど...。
--

GASで非同期処理

今度は、GASで非同期処理が出来ないか、また社内版 ChatGPT に聞いてみた。 非同期処理は実装されてないけど、トリガを利用して、疑似的に非同期処理を実現できるらしい。 1分後に指定した関数を実行させ、その関数の中で、自分自身のトリガを削除するというもの。 なるほど。 で、それがこのコード。

function setTrigger() {
    ScriptApp.newTrigger('triggeredFunction')
        .timeBased()
        .after(1 * 60 * 1000) // 1 minute
        .create();
}

function triggeredFunction() {
    // この関数が実行されたときに自身のトリガーを削除
    var triggers = ScriptApp.getProjectTriggers();
    for(var i = 0; i < triggers.length; i++) {
        if(triggers[i].getHandlerFunction() == "triggeredFunction") {
            ScriptApp.deleteTrigger(triggers[i]);
        }
    }
    // その他の処理...
}

■

いつも見ている内田樹のブログに、先の米国大統領選のバイデンとトランプの討論について述べたコラムの中に、『アメリカ・ファースト』に触れて、

彼らは欧州の戦争に米国は関与すべきではないと主張した。仮にナチスがヨーロッパ全土を支配して、苛烈な弾圧を行ってもそれは米国には関係のないことだ。 欧州の旧秩序を維持するために米国の若者が血を流す必然性はない、と。 フィリップ・ロスの小説『プロット・アゲンスト・アメリカ』はこの史実を踏まえて、リンドバーグ大佐が１９３６年の大統領選挙でフランクリン・ローズヴェ ルトを破って大統領になった米国を描いた「近過去物語」である。

内田樹の研究室 トランプのアメリア

こんなことが書かれている。 この、 フィリップ・ロス『プロット・アゲンスト・アメリカ』を調べると翻訳されて文庫でも出ていることが分かった。 ちょっと読んでみたいな。
--
いちじ

排他制御

社内版 ChatGPT に、GASてかスプレッドシートの排他制御について質問したら、ロックサービス（LockService）というのがあるらしい。 いま、作っているシステム、複数の拠点から1つのスプレッドシートにアクセスするので、競合が懸念されてはいたんだけど、基本、読むだけなので、大丈夫だろうということで進めてきた。 実際には、頻度は低いものの書き込む処理もあるので、不安はあったのだけど、排他制御が実装できるので大体安心になった。 その ChatGPT が教えてくれたコードがこれ

// ドキュメントロックを取得します
const lock = LockService.getDocumentLock();
// 最大10秒待ってロックを取得します
if(lock.tryLock(10000)) {
  try {
    // ロックが取得できたら、何かの処理を行います
    ...
  }
  finally {
    // 処理が終わったらロックを解放します
    lock.releaseLock();
  }
}

■

関東甲信地方、梅雨明けの宣言が出た。 昨夜の天気予報では、まだ先みたいに言ってたのに...。 まあ、ボク的にはおっけーなんだけど。
--

GASでHTMLメールを送信する

スプレッドシートの情報を定期的にメールで送信する必要があって、GASでコードを書いて割と簡単にできた。 メール本文は、テキストなので、列ごとにタブ区切りで出力してるんだけど、文字列によってずっこける。 しょうがないと言えばしょうがないんだけど、あんまりカッコよくない。 なんか列をそろえる方法はないかと思案して、HTMLメールにすれば上手く行くんじゃないかと思い至った。 ネットで調べると、比較的簡単に出来そうなことが分かった。 HTMLで記述したテキストをメールのオプションに突っ込めば良さそう。 シートを出力する前に<table>を出力して、1行ごとに<tr></tr>、出力する項目ごとに<td></td>を入れて、最後に</table>でくくればテーブルが出来上がる。 試してみると、思い通りの表示になった。 インラインスタイルでちょっと装飾も加えて完成。 プログラムでタグを入れるのが面倒といえば面倒だけど、思っていたより簡単に実装出来て良かった。

■

キダタロー氏死去
--
のち

Libraryの罠（GAS）

Googleフォームを使ったシステム。 AさんがフォームAから申請をして、その審査結果をBさんがフォームBから入力する。 それぞれGASはフォームの回答用スプレッドシートにバインド型で実装。 フォームAの回答用スプレッドシートが、どちらかと言えばマスタで、申請時に管理番号を発行してキーにしている。 フォームBの審査結果をフォームAの回答用スプレッドシートにセットする関数を、はじめは、フォームBの回答用スプレッドシートに書いていたんだけど、なんか違う気がして、フォームA側に実装して、ライブラリで公開しておき、フォームB側は、フォームA側の（ライブラリ）関数を呼ぶように変更した。 フォームA側にTPを作って実行すると上手く行ったので、フォームB側に実装して実行するとエラーになる。 あれ？ あちこちにログを吐くようにして、実行してみてやっと原因がわかった。 フォームBから実行すると、フォームBの回答用スプレッドシートを参照している。 ボクの想定では、フォームAの回答用スプレッドシートを参照するはずだった。 見つからないわけだ。 スプレッドシートオブジェクトを拾うのに、『SpreadsheetApp.getActiveSpreadsheet()』を使っていたせいで、動いているフォームB側のコンテクストになっていたからのよう。 『SpreadsheetApp.openById(シートのID)』に変えるとすんなり動いた。 確かに考えてみれば、動いているフォームB側がアクティブなスプレッドシートだよね。 自分だけで動くコードなら、getActiveSpreadsheet()でもopenById()でも変わらないけど、他から動かすことを考えるなら注意しないといけない。
--

いまさら（GASのログ）

GASのデバッグ、ソースを見ながら実行すれば、実行結果（ログ）が画面の下に表示される。 ところが、トリガー実行とかで、ソースを表示しないで実行したときは、ログが見られなくて困っていた。 前に、お手軽ログをGoogleドキュメントに吐き出すコードを書いたことがあるけど、それも、使ってると死んでることがあって、最近は実装してなかった。 試しにネットを調べると実行数画面から確認できることが分かった。 なんだ、ちゃんと用意されてるじゃん！
--

ソートの罠（GAS）

スプレッドシートの範囲をソートしようと、ネットでやり方を調べた。 getRange()で範囲を指定してsort()関数で、カラム位置と昇順降順をセットするだけらしい。 キーが複数のときは、sort関数に喰わせるカラム位置と昇順降順の組を配列で突っ込めば良いみたい。 早速やってみても、全然上手く行かない。 ネットに書いてある通りなのに...。 検索ワードに『上手く行かない』を追加してググってみると、Yahoo！知恵袋に答えが見つかった。 ボクは、getRange()で選択した範囲での列番号だと思っていたら、なんとシート上の列番号を指定するらしい。 getRange()で'D1:G10'を指定して、D列をソートキーにする場合、1ではなく4を指定する必要がある。 最初にネットで見た例は、getRange()の範囲がA列からのものばかりだったからわからなかった。 ボクの感覚からすれば、選択した範囲の何列目を指定するべきだろうと思うのだけど、世間はそうでもないらしい。 シート上の列番号に修正すると、すんなりソートすることが出来た。 例によってコードを記す。

ダメなコード
    // セルの範囲を取得する
    let range = sheet.getRange('K3:Q10');
    
    // 並べ替える
    range.sort([
      {column:1, ascending:true},
      {column:2, ascending:true},
      {column:3, ascending:true},
      {column:4, ascending:true},
    ]);


上手く行くコード
    // セルの範囲を取得する
    let range = sheet.getRange('K3:Q10');
    
    // 並べ替える
    range.sort([
      {column:11, ascending:true},
      {column:12, ascending:true},
      {column:13, ascending:true},
      {column:14, ascending:true},
    ]);

--
のち

翌月を求める（GAS）

今回のシステム、作業実績の管理表をつくっている。 グループ長があるボタンをクリックすると、グループ内のメンバの翌月の作業実績入力表（月次のスプレッドシート）を作るようになっている。 その部分は、とっくに出来上がっていて、てか、この前のバグのところ。 きょう、翌月のシートを作ってみると、2月ではなくて3月のシートが出来る。 をいをい、翌月は2月じゃないのかい？とつっこんでみても、３月が出来てしまう。 このまえは、上手く（色は違うがちゃんと翌月が出来ていた）いってたのに、なんでだろう？ コードは、Date()関数で、今日の日付をnewして、getMonth関数で、今日の月をゲットして、その月に1を足して、setMonth関数で月をセットしている。 デバッグしてみると、月を+1して書き戻すと、それまで1月だったのが3月になっている。 どうも、単純に月を+1する訳ではなく、今日に30日だか31日だかを足しているよう。 なるほど。 ということは、今日じゃなくて、今月の1日にしてから1（月）を足してやれば上手く行くんじゃないかと思い修正すると、ちゃんと翌月になった。 ボク的には、違う気（日付に関係なく単純に月を+1するのが正しい）がするけど、まあ、世間とボクの考えが違うことはよくあることなので、仕方ない。 というわけで、コードを記しておく。

ダメなコード
  let date = new Date();
  let month = date.getMonth();	// 今日の月を得る
  month = month + 1;
  date.setMonth(month); 	// 翌月にならないこともある

上手く行くコード
  let date = new Date();
  date.setDate(1);		// きょうではなく今月の1日にする
  let month = date.getMonth();
  month = month + 1;
  date.setMonth(month); 	// 翌月になる

--

バグ発見

出来たと思っていた今回のシステム。 ちょっと動かしてみると様子がおかしい。 1シート1ヶ月で作業実績を入力するのだけど、営業日と休日でセルの色を変える仕様。 今日新しいシートを作ってみると、平日が休日の色になっている。 あれ？おかしいなあ？ いままで上手く行ってると思っていたけど...。 いろいろ調べると、NETWORKDAYS関数で営業日と休日を判断して条件付き書式でセルの色を変えているのだけど、その関数が入っているセルは結合されていて、老番のセル番号を使っていた。 そうすると、どうやらNETWORKDAYS関数が正しい値を返さない模様。 そこで、若番のセル番号にすると正しい値が返ってきた。 ビルドインの関数はほとんど使わないので、原因がなかなか分からなかったよ。
R列とS列（T列とU列...）を結合した状態で

	=NETWORKDAYS(S$2,S$2,'カレンダー'!$A$2:$A$56) 
	だと正しい値が返ってこないけど
    
	=NETWORKDAYS(R$2,R$2,'カレンダー'!$A$2:$A$56) 
	だと上手くいく。

セルを結合すると、いろいろ問題が起きるんであんまりやりたくないんだけど、元の仕様が1日に（作業）項目数と時間の2項目を入力するようになっていて、しかたなく。 いずれにしても、解決して良かった。
--
のち

サイドバーでお手軽プログレスバー

Googleスプレッドシートに表示させたサイドバーからGASを実行して処理をする場合、UI的にはナニもないので、実際動いているのかと止まっているのかわからない。 ちょっと思いついて、プログレスバーが表示できないかとググってみた。 ホントの進捗をリアルタイムに表示するのは、やっぱり大変そうだ。 でも、動いているのがわかるようにするだけなら意外と簡単そうに出来ることがわかった。 たとえば、こんなページ。 で、作ってみたコードはこんな感じ。

<script>

  // グローバル変数
  var val;                // プログレスバーの進捗値
  var intervalID;         // 一定間隔で処理を行うintervalのIDを保持

  function moveCompletedProject() {
  //
  //  @brief  開発済み案件を各グループの実績シートに移動する
  //  @note   
  //  @param  
  //  @date   Sep ??, 2022
  //..............................................................

    // 50msおきにプログレスバーを更新する
    val = 0;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
    intervalID = setInterval("updateProgress()", 50);
   	
    // GAS側に処理を依頼する
    google.script.run
      .withSuccessHandler(function(data) {
        // ここはCallback関数
        // プログレスバーの後始末（表示をクリアしてインターバルを解除）
        document.getElementById("myProgress").value = 0;
        clearInterval(intervalID);

        if(data == 0) {
          alert("開発済み案件はありませんでした。");          
        }
        else {
          alert(data + "件の開発済み案件を移動しました。");
        }
      })
      // ここが実際のGAS側の関数
      .moveCompletedProject();
  }

  function updateProgress() {
  //
  //  @brief  プログレスバーの進捗値を更新し、プログレスバーに反映させる
  //  @note   
  //  @param  
  //  @date   Sep 30, 2022
  //..............................................................
    
    val += 1;
    document.getElementById("myProgress").value = val;
    document.getElementById("myProgress").innerText = "なんちゃって" + val + "%";
    console.log("progress:", val, "%");

    // 最大値まで達したら振り出しに戻る
    if (val == 100) {
      val = 0;
    }
  }
</script>

<body>
  <div class="sidebar">

	--ここは省略して --

    <!-- プログレスバーを表示 -->
    <div>
      <progress id="myProgress" value="0" max="100">0%</progress>
    </div>
  </div>
</body>

--

サイドバーに配置したテキストエリアのデータを得る

サイドバー上に配置したテキストエリアに入力されたデータをGAS側で得る方法について書いてあるページがほぼ見つからない。 そんなことは常識なのかしら？ 逆にGAS側からサイドバーにデータを返す方法は詳しく説明しているページはあるんだけど。 というわけでサンプルコードはこんな感じ。 テキストエリアの内容をdocument.getElementById()で変数に入れて、それを引数にすればおっけー。

■ サイドバー側

<html>
  <head>
    <script>
    function hogehoge() {
      var inputText = document.getElementById("idInputText");
      google.script.run.hoge(inputText .value);
    }
    </script>
  </head>
  <body>
   <label>hogehoge</label>
   <input type="text" id="idInputText">
   <button onclick="hogehoge()">ほげほげ</button>
  </body>
</html>


■ GAS側

function hoge(input) {
  if(input == 'OK') {
    ..... OKの処理
  }
  else {
    ..... OK以外の処理
  }
}

--

Googleスプレッドシートのサイドバーを消す（続編）

また、スプレッドシートに表示したサイドバーを追加したメニューから消そうと思いググってみた。 相変らず、それらしいページは見当たらない。 そういえば、昔、同じコードを書いたようなと思い出し、ここの『GasTips』タグを探してみると、でてきた。 同じコードを書いて動かしてみても上手く行かない。 自分が表示したサイドバーは消えるのだけど、その下に素のサイドバーみたいのが表示されたまま。 おかしいなあと思いながら、更にググってみると、別の方法が出ていたのでやってみると上手くできた。 前、やったときは素のサイドバーが表示された記憶はないのだけど、今回、自分のサイドバーが消えた後、一瞬、素のサイドバーが表示されてから、消えるようになっている。 いろいろやってみたけど、改善しないので諦めることにする。

■ ダメなコード（以前のコード）

function closeSidebar() {
    var html = HtmlService.createHtmlOutput("");
    SpreadsheetApp.getUi().showSidebar(html);
}

// anywhere on the server-side script (or code.gs)
closeSidebar();


■ 上手く行ったコード

function closeSidebar() {
    var html = HtmlService.createHtmlOutput(
                         "<script>google.script.host.close();</script>");
    SpreadsheetApp.getUi().showSidebar(html);
}

--

一瞬の夏

このところ不順な天気が続いていたけど、昨日は、夏が戻ってきたような良い天気だった。 きょうはクレーム対応が2件。 1件は、件のセルの値の先頭の0が消える問題。 また再発した模様。 TPを作って調べたのだけど、上手くいく。 コピー先の表示形式を値をコピーしてから設定しても上手く反映されない。 先に表示形式を設定してからコピーすると上手く行くのだけど、今回の問題は、既に表示形式が設定されているセルに上書きするので、これが原因とも思えない。 コード的にはコピーの後、設定していたので、先に表示形式を設定してからコピーするように修正した。 これで様子を見てもらうことにした。 もう1件は、出力側に値が設定されていないというもの。 よくよく話を聞いてみると、入力側の値をコピーすることになっていたのを、ボクが忘れててか、コピーすることを知らなかったのでコピーしていなかった。 コピーするコードを追加してリリースした。
--
のち

GASクレーム対応 getDisplayValues()

スプレッドシートに入力しているデータの先頭の'0'が、いつの間にか消えてしまうとクレームが来た。 スプレッドシートのデータは処理速度の問題で、一旦2次元配列（メモリ）に取り出して、処理を終えると書き戻すと云うのが、GASの標準的な作法になっている。 ずっとこの方法を取っていたのだけど、 書式なしのテキスト表示（書式設定）している先頭に’0’が付いたデータを配列に取り出し、シートに書き戻すと先頭の’0’が消えてしまうことがわかった。 最初、なぜ’0’が消えるのかわからなくて、書式設定が書き換わるせいかと思っていたのだけど、どうもそうではないことに気づいた。 ググってみると、getDisplayValues()という関数があることが分かった。 ずっとgetValues()という関数しか使っていなかったし、書式が無視されて配列に格納されることを知らなかった。 どうやらgetDisplayValues()を使うと、セルに表示されている見た目通りの値が配列に格納されるらしい。 デバッグのときは、getValues()で上手くいったたのに、リリース後に起きたのが腑に落ちないけど、getDisplayValues()に修正したのでこれで大丈夫だと思われ。
--
のち いちじ

気を取り直し

対応を考える。 大文字、あるいは小文字に揃えるのではなく、大文字でも小文字でも同じ文字だと判断できれば良いことに思い至る。 早速ネットをググると、『メソッドを使ったアルファベットの大文字小文字を無視した文字列の検索』というページが見つかった。 まさしくこれだよと思いながらページを見る。 ちょっと改造すれば使えそうなことがわかり、テストプログラムを書いて試してみた。 すると思い通りの結果になったので、本チャンのプログラムに実装。 本チャンでもちゃんと動くことを確認した。 正規表現が使えれば、すぐに対応できるのは分かっていたけど、恥ずかしながら、未だに自分では書けない。
書いたコードはこんな感じ。

function countMatchCharacters(string, passCode) {
//
//  @brief  文字列を比較し同じ文字数を返す
//  @note   大文字小文字を区別しない
//  @param  
//  @date   Jul 05, 2022
//..........................................................................

  let num = 0;

  for(let i=0; i<passCode.length; i++) {
    let regExp = new RegExp(passCode[i], 'i');    
    if(regExp.test(string)) {
      num++;
    }
  }

  console.log(num);
  Logging('T', 'countMatchCharacters() Exit' , 'Match count = ' + num);
  return num;
}

--
いちじ

急な修正

今日の仕事も、そろそろ終わりの時間になって、修正の依頼が来た。 Googleフォームから入力されたパスワードをマスタデータと比較してるところで、大文字小文字を区別しないでくれという。 早速、ネットを調べるとtoUpperCaseというメソッドで、小文字を大文字にすることが出来るようなので、両方を大文字にして比べるればいいやと思い実装した。 上手く行ったと思ったんだけど、入力されたパスワードが数字のみの場合、toUpperCaseでエラーになりスクリプトが止まってしまうことが分かった。 そしたら、数字のみか英数字混在かで処理を分ければいいと思い、今度は、それをどうやって判断するかググってみた。 いろいろ出てはいるんだけど、どうも上手く動かない。 結構時間を食ってしまい、今日はここまでで、明日、仕切り直すことになった。
--
いちじ