（個人的）（現状での）ベストプラクティス
#他の人がシート側を弄っても壊れないGASを書きたい！
こんにちわ、私は元事務員なのを活かして(？)社内事務で使われているオフィスアプリの連携とか効率化に勤しんでいます。(G WorkSpace関連とかSlackとか)
そして、やはりExcelやスプレッドシートの自動化は事務作業効率化の華ではないでしょうか。
全部を自動化できなかったとしても、少しの補助機能で面倒臭さやミスをグッと減らすことが出来たりします。
要するにそういうことをボソボソとやってるのですが、
自分以外の人間が主に使用するスプレッドシートに使うスクリプトで大切なこと、
それは**「列や行の新規挿入」「シート名・項目名の変更」といったシート構造の変更に柔軟に対応出来るようにすること**ですよね！
もちろんある程度取り決めを作ったり、必要部分に最低限の人の編集権限を与えるなどで防ぐことも大事ですが、
取り決めは破られるためにあるし、自分に編集権限を集めると、自分のメンテナンス業務量が増えることでもあるので、スクリプト側で出来る限り対応したいものです。
あとまぁ純粋に変更を依頼された時に対応が楽です。
#実際によくあるスプレッドシート例
社外との取引を申請したい社員が個々で入力してもらうタイプの管理表です。
こういうリストは特に無法地帯化しやすので記入内容のバリデーションなど色々改善点はありますが、とりあえず少人数で使うシートでも運用中によく起きる変更が下記です。
- リスト上部に記入方法や注意書きが挿入され、その内容は更新され増減する
- 例えば「承認日」欄が「金額」欄の後に必要になり、列数が増える
上記が発生しても基本的に今実装している機能に影響を与えないためには、スクリプト内で列数や行数を固定値で記述しないことが重要です。
そのために色々と工夫してきましたが、シートや各行(record)をハンドリングするclassを作ってあれこれするのが一番分かりやすいなという結論に達しました。
#実際のclass
下で解説しますが、前提として下記を使用した場合のスクリプトです
- claspを使って.tsファイルに記述
- classを使うにあたってruntimeはV8でもRhinoでも大丈夫っぽいけど自分は普通にv8使っています
const THIS_SPREAD_SHEET = SpreadsheetApp.getActiveSpreadsheet()
class SheetData {
sheet: GoogleAppsScript.Spreadsheet.Sheet; dataTable: any; lastRow: number; lastCol: number; sheetTable: any[][]; labels: any[][]; labelIdx: any; firstLabel: any;
constructor(shName, firstLabel) {
this.sheet = this._getSheetByName(shName);
this.firstLabel = firstLabel
this.sheetTable = this.sheet.getDataRange().getValues()
this.labelIdx = this._getLabelsIdx(firstLabel)
this.labels = this.sheetTable[this.labelIdx]
this.dataTable = this.sheetTable.filter((r, i) => { return i > this.labelIdx })
}
set setNewSheetTable(newSheetTable) {
this.sheetTable = newSheetTable
this.labelIdx = this._getLabelsIdx(this.firstLabel)
this.labels = this.sheetTable[this.labelIdx]
this.dataTable = this.sheetTable.filter((r, i) => { return i > this.labelIdx })
}
_getSheetByName(shName) {
const sh = THIS_SPREAD_SHEET.getSheetByName(shName);
if (!sh) {
throw new Error("シート名からシートを取得できませんでした");
}
return sh
}
_getLabelsIdx(firstLabel) {
let labelIdx = null
this.sheetTable.forEach((record, idx) => {
if (record[0] === firstLabel) { labelIdx = idx }
});
if (labelIdx < 0) {
throw new Error("ヘッダー行のインデックスを取得できませんでした");
}
return labelIdx
}
updateData() {
this.setNewSheetTable = this.sheet.getDataRange().getValues()
}
getIdxOf(label) {
let idx = this.labels.indexOf(label)
if (idx < 0) { throw new Error("ヘッダーにその名前の項目はありません") }
return idx
}
getNewRecordRange(recordValues) {
const lastRow = this.sheet.getLastRow();
return this.sheet.getRange(lastRow + 1, 1, 1, recordValues.length)
}
appendNewRecord(recordValues) {
const newRecordRange = this.getNewRecordRange(recordValues)
newRecordRange.setValues([recordValues])
}
}
class RecordData {
recordId: any; matchRecordData: { matchRecord: any; rowNum: any; }; record: any; rowNum: any; companyIdIdx: any; sheetData: any; idLabel: any;
constructor(idLabel, recordId, sheetData) {
this.sheetData = sheetData
this.recordId = recordId.trim()
this.idLabel = idLabel
this.matchRecordData = this._getMatchRecord()
this.record = this.matchRecordData.matchRecord
this.rowNum = this.matchRecordData.rowNum
}
_getMatchRecord() {
let rowNum = null
const idIdx = this.sheetData.getIdxOf(this.idLabel)
const matchRecord = this.sheetData.dataTable.find((record, idx) => {
rowNum = this._getRowNum(idx)
return record[idIdx] === this.recordId
})
if (!matchRecord) {
throw new Error("番号に一致する取引がありません");
}
return { matchRecord, rowNum }
}
_getRowNum(idx) {
return idx + 1 + this.sheetData.labelIdx + 1
}
getValOf(label) {
const idx = this.sheetData.getIdxOf(label)
return this.record[idx]
}
}
#使い方
function handlingSheet() {
const sheetData = new SheetData("取引管理", "記入日")
//項目名の配列を取得したい
console.log(sheetData.labels) // ->[ '記入日', '取引番号', '申請者', '取引先', '取引内容', '金額', '発注日', '納品日', '請求確認日' ]
//実際リストとして入力されている部分のgetValues()の結果を取得したい(二元配列)
console.log("before", sheetData.dataTable)
//特定の項目名の配列内のインデックスを取得したい
console.log(sheetData.getIdxOf("取引先")) // ->3
//新しいレコードをスクリプト側から最下行に追加したい
sheetData.appendNewRecord(["2020/11/11", "A-021", "山田", "○○商事", "機材レンタル"])
//シートデータの再取得
sheetData.updateData()
console.log("after", sheetData.dataTable) // .appendNewRecord()で追加されたレコードも以後データに含まれる
}
function handlingRecord() {
const sheetData = new SheetData("取引管理", "記入日")
const recordData = new RecordData("取引番号","A-001", sheetData)
//取引番号に一致するレコードの取得
console.log(recordData.record) // -> [ Sat Nov 21 2020 00:00:00 GMT+0900 (日本標準時),'A-001','山田','○○商事',...]
//取得したレコードのシート内での行番号（レコードの削除や書き換え時に使用）
console.log(recordData.rowNum) // -> 10
//取得したレコード内で、指定した項目名の値を取得
console.log(recordData.getValOf("申請者")) // -> 山田
}
#classの解説
クラスの中の処理、どういったシート側の変更に強く、何に弱いのかを説明します。
##SheetData
class SheetData {
constructor(shName, firstLabel) {
this.sheet = this._getSheetByName(shName);
this.firstLabel = firstLabel
this.sheetTable = this.sheet.getDataRange().getValues()
this.labelIdx = this._getLabelsIdx(firstLabel)
this.labels = this.sheetTable[this.labelIdx]
this.dataTable = this.sheetTable.filter((r, i) => { return i > this.labelIdx })
}
//以下メソッド定義....
constructor内での処理ですが、
まずthis.sheetTableにシート内に値の入っている範囲全てをgetValuesした結果を格納します。
この時点ではリスト上部の説明文なども全てが含まれています。
次にthis.labelIdxにA列の中で**firstLabel=「記入日」**と値が一致する行数を格納します
（実際には取得した二元配列内でのインデックスなので、行番号-1の値ですが）
this.labelsは上で取得したインデックスからシート内の項目名行の値の配列を格納しています。
this.dataTableが管理リストの純粋なレコード部分の二元配列になります。実際に扱いたいデータしか入っていないので、気軽にmapとかfilterとかで取得操作できます。
this.sheetTableから項目名行を含めた部分までの行を取り除くことで取得しています。
set setNewSheetTable(newSheetTable) {
this.sheetTable = newSheetTable
this.labelIdx = this._getLabelsIdx(this.firstLabel)
this.labels = this.sheetTable[this.labelIdx]
this.dataTable = this.sheetTable.filter((r, i) => { return i > this.labelIdx })
}
updateData() {
this.setNewSheetTable = this.sheet.getDataRange().getValues()
}
classのインスタンスを作成した後、その時点で取得されたシートの中身を参照しながら以降の処理を行うため、処理の途中で実際のシートに新規行を追加したり並べ替えを行うと多分おかしなことになります。
なのでそういった処理を途中に行った場合はシート全体のデータを再取得する必要があります。
sheetData.updateData()でコンストラクタ内の全ての情報を更新できます。
####強い変更
- リストの上の説明部分が増えても減っても影響を受けない
- 一番左の行を動かさなければ、列の増減、入れ替えが値の取得に影響を与えない
####弱い変更
- 一番左の項目名を変えられるとお終いになる
- 既存の項目名の名前を変更されるとsheetData.getIdxOf(label)が値を取得できなくなる
※上記の変更があった場合はerrorが投げられるようになっています。
##RecordData
特に説明ないです
レコードのidとして渡された値が見つからない場合はerrorが投げられます。
#補足
- アンダーバーから始まるメソッドは、クラス内でしか使わないメソッドです、という意味です。
- たまに見栄えの為にA列を空列にしてB列からリストを作る人がいますが、許可を取ってA列を消しましょう。
どうしてもB列から始めたい場合は_getLabelsIdxの中のrecord[0]をrecord[1]にすれば大丈夫、多分…
###シート名の変更について
classの初期化にシート名を使っています。シート名を勝手に変えられると、元のが無くなればまだエラーになるので良いのですが、別のシートに名前だけすり替わるとスクリプトはそれに気づけない可能性があります。
私はシート名の変更については実際にシートを使う人と取り決めをして、編集権限を付けて対応しているのでシート名を使っていますが、そこも信用できない場合はシートIDで指定する方法にすると安心だと思います。
この方法はコードの可読性が落ちるのもあってあまり使いたくないですが…
###実行速度の向上を求めた工夫に付いて
GASをよく使う人には常識だと思いますが、GASの実行速度はSpreadSheetAppクラス等にアクセスする回数や処理をいかに減らせるかにかかっています。
なのでclassの中で一度シートの全部の値を二元配列で取得した後は基本的にその配列に対して操作を行い、実際のシートにアクセスするのは値を追加・更新する場合に絞っています。そして実際に追加が行われたら再度シートデータを更新するメソッドを呼び出します。
しかしこの「実際のシートに変更を加えたらシートデータを更新する必要がある」というのが曲者で、というか私がすぐ忘れてしまう…
処理速度を考えると最下行getLastRow()や最右列getLastColumn()など複数個所で使うことの多い値もコンストラクタで定義する方が効率的ですが、特に最下行は処理途中で変わることが多いので、
つどつどsheetData.sheet.getLastRow()で呼び出すことにしています。
まぁ結局更新するのを忘れてるとおかしなことになることが多いので忘れないようにしなきゃいけないんですけど...
appendNewRecord()を処理の途中に使うことが確定していたらメソッド内の最後にupdateData()を呼び出すようにしてしまっても良いですね。
#最後に
ベストプラクティスとか言ってますが、ここまで整理して大分使い易くなったなぁ程度のものです。
今後も効率的かつ分かりやすい書き方を模索していきます。