スクリプトで苦戦したこと
今回は、少しさかのぼって、
GoogleAppsスクリプトで苦戦したことを
話していきたいと思います。
シート更新は、なるべく全体で
最初の頃は、まず効率面など思いつきもしませんでした。
スプレッドシートを操作するスクリプトにおいて、
最初の頃は必要な部分だけを抜き、そしてはめ込む、
つまり何らかの動作を行うたびに、シートにアクセスしていたわけですが、
じつはそれが最も効率の悪い方法だと気付いたのは、しばらくしてからでした。
「getValue()」「setValue(（内容）)」
これはセルの内容を取得・挿入していく関数ですが、
この場合、対象は１つのセルに対してのみ。
広範囲のセルに対して処理を行おうとすると、当然何度もこれを使用する事になります。
しかし、シートを読み取るにも意外と時間がかかるものであり、
それが何度も連続で続いていると、
スクリプトの処理が完全に終了するまでに時間がかかってしまいます。
そこから発生する問題といえば、処理が終了するまでの人の待ち時間、
処理の途中で編集された際の内容の食い違いの発生、
そして、サーバーエラーなどが考えられます。
それを解決したのが、「getValues()」「setValues(（内容）)」の関数で、
一度にシートの全セルを読み取り、その中で処理を行ったものを、
全セルを一度に更新してしまう。
こうする事で、読み書きのアクセスが計２回で済むことになります。
関数も見ての通り、「s」が付いて複数系になっています。
セルの全域を参照する方法としては・・・
「sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).getValues();」
「getRange()」に設定してあるのは、「1,1,」の部分は左上の「A1」セル、
「sheet.getMaxRows(),sheet.getMaxColumns()」では、
シートを定義した「sheet」の最大行数・列数を、それぞれ取得し、
いちばん右下を示すようにしています。
これで取得したデータは、二次配列で格納され、配列の中身を処理したうえで、
「sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns())
.setValues(（内容）);」
で、またシート全域に対して挿入すればいいのです。
つまり、これでやっている事は、
シート全体を指定し、そこから取得したデータを編集、そしてまた戻す・・・
という作業です。
シート全域を更新するため、ほかの部分も上書きされると思うでしょうが、
そこは前のデータが残っているため、問題ありません。
私も現在では、この方法を主流としています。
配列の中身を考える必要はありますが、これだけでも処理が格段に早くなります。
二次配列の罠
「getValues()」で、二次配列で範囲取得を行えるのですが、
これで取得したものは、必ず二次配列で取得されます。
こう言われても、いまいちピンと来ないかもしれませんが、
縦一列の範囲を取得する場合、たとえば「A1:A10」の範囲を取得する場合なら、
作成された配列、「ary」とします。
この場合、データを個別に取り出すには、「ary[0]」「ary[1]」「ary[4]」・・・
のように、一次配列の要領でも取り出す事ができます。
しかし、これに慣れてしまっていると、次に横一行、
たとえば「A1:G1」のように取得した場合に、エラーが起きます。
この場合は「ary[0]」「ary[1]」「ary[4]」のようには取得できないのです。
範囲が横に伸びた場合は、その部分は二次配列の２層目の部分に格納されるため、
「ary[0][0]」「ary[0][1]」「ary[0][4]」のように指定する必要があります。
たとえばシートで一覧みたいなものを作ってみましたが、このような感じです。
スクリプトを触る機会の増えた今なら、じつに単純な事だと思えるようになってきたのですが、
これは実際に自分もハマった事でもあります。
１行だけなんだから、一次配列なんじゃないのか？
と思い込んでいたら、じつは二次配列だった。
これに気付いたのは、関数ガイドの「二次配列で取得される」と書かれた部分を見たときでした。
長くやってる人にとっては、初歩的どころか常識とまで認識してしまうでしょうから、
こういう部分は細かく執筆されていない場合が多いんでしょうね。
しかし、意外にも気付かない人はいると思うのです。この私のように・・・
そして、言うまでもなく（言うのを忘れてたとも言えますが・・・）、
配列は0から始まるため、取得した配列も0から始まります。
配列を挿入する際には、「getValues(（二次配列）)」に、
二次配列をそのままブチ込めばいいのですが、
配列を指定した変数と同じ変数でシートを参照する場合は、
その変数に「+1」して、１セル分ずらす必要があります。
まぁ、今考えると、これら２つの事項もじつに単純な事でもあるのですが、
GoogleAppsスクリプト始めたての頃は、本当に苦戦した部分です。
関数の概要やサンプルスクリプトは検索でいくらでも拾えても、
よくある例とかはないので、実際に自分で失敗してみないと、わからないものです。
