GAS (Google Apps Script)で数値を文字としてsetValueする方法についてあれこれ模索した結果

昨年末に委員会で作ると約束してしまった事務的ツール。

事務方が使うのでExcelVBAがいいんじゃないかな。。。と思ってだいたい作り終わって２ヶ月ほど放置していました。

新年度のバタバタも収束しつつあるので再始動です。

最近、個人的にGoogle Docsを多用するようになっていまして、どうせならば、このツールもGoogle Apps Script（以降、GAS）でいいんじゃないかな。。。と。

で、２日前くらいから勉強し作り始めました。

一から作るのは面倒だったので作りかけのVBAを移植する感じで進めました。

VBAほどではありませんが、ググるとGASは日本語の情報もチラホラあるので、つまったら参照しながら進めてきました。

いくつか問題があったのですが、その中の２つをご紹介。知っている人にとっては何をいまさら。。。かもしれませんが。

１．エディタのフォントでは全角と半角の違いがわかりにくい

GASはブラウザ上で開発するのですが、たまに以下のようなエラーが表示されるんですね。

該当する行を見ても、変な文字は入っていない。。。

で、しらみつぶしに変えてみるのですが。。。非常に効率が悪い。どれくらい見分けがつかないのかの例が以下です。これは行末のセミコロンが全角になっていてエラーが表示されたんですが、見た目、ほとんど変わりませんね。。。

２．数値を文字としてsetValueする方法についてあれこれ模索した結果

今、作りかけのデータに職員コードがあって、そのコードをもとにデータを紐つけようとするのですが、コードが０はじまりの数値になるケースがあり、その値をそのままワークシートにsetValueすると数値になってしまうんですね。。。例えば、０１２３をsetValueすると１２３となる感じです。キーとして使えなくなるわけです。

で、いろいろ調べたのが以下です。まず、実験用スクリプトです。その下に解説を書きました。よろしければご覧くださいませ。

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');

  sheet.clear(); //内容も書式もクリア
  
  sheet.getRange("A1").setValue("0123"); //(1)NG

  sheet.getRange("A2").setNumberFormat('@');
  sheet.getRange("A2").setValue("0123"); //(2)OK
  
  sheet.appendRow(["0123"]); //(3)NG

  var lastRow = sheet.getLastRow() + 1;
  var range = sheet.getRange(lastRow, 1);
  range.setNumberFormat('@');
  range.setValue("0123"); //(4)OK

  var lastRow = sheet.getLastRow() + 1;
  var range = sheet.getRange(lastRow, 1, 1, 3);
  range.setNumberFormat('@');
  range.setValues([["0123", "0234", "0345"]]); //(5)OK
}

clear()メソッドで全セルの値と書式をクリアした状態がスタートです。あとワークシートには手動で（[表示形式]ー[数字]ー[自動]）を選択している状態です。

コード中の（１）では文字列をそのままセットします。これは失敗します。ワークシートの初期状態が手動で（[表示形式]ー[数字]ー[書式なしテキスト]）を選択していれば（１）でもうまくいきます（0123と入力できる）

プログラムで書式なしテキストにするのにsetNumberFormat('@')を使えばいいらしいので、それをやったのが（２）の例です。これはうまくいきました。つまりsetValueを実行する前にセルの表示形式をテキストにしておけば数値に見える数字も文字列として入力ができるということです。VBAでよくある話ですね。

で、本当にやりたかった、というかこれを調べ始めたのは（３）がうまくいかなかったことからなんですね。シート内の最終行を自動的に検索して行追加してくれるメソッドがappendRow()なわけですが、これで"0123"のようなデータをいれると数値になってしまう。。。という問題。これはぐぐってもあまり書かれていなくて、昨夜は諦めてしまったくらいでした。

で、これの対応が要は（２）をすればいいということです。それが（４）になります。シート内のデータが入っている最終行を探してくれるgetLastRow()を使い、その次の行のRangeにsetNumberFormat('@')を適用して、さらにsetValueする。。。という流れです。これが正しいのかよくわかりませんが、とりあえずできました。

さらにappendRow()のように複数カラムを含む行を追加する例として（５）を確かめました。これもOKでした。

GASを使いこなして意図したアプリを作るには、まずJavaScriptの動的型づけとSpreadsheetAppの特性をうまく見極める経験が必要です。

上記のスクリプトの実行結果は以下の通りです。

GAS、１つの仕事用ツールとして普通に使えますね。