モチベーション
Google Apps Script を使ってスプレッドシートからの処理の自動化〜〜みたいなことをここ数ヶ月でやってみていたのですが、めちゃ便利ですね。入れた締切日に応じてカレンダーに予定を入れたり、アンケートの集計とかも好きなように自動化できます。
ただ、問題となったのが、スプレッドシートは自由度が高く、利用者側の事情でカラムの追加や削除が頻繁に行える必要があることです。カラム番号をScriptにハードコードしちゃうと、カラムの追加・削除があったときにぜんぜん違うカラムを参照・更新されてしまう、という事故が起きるようになりました。
例
こんなかんじで開発工程の管理をしていたとして
ステータスのカラムを集計して、未着手、進行中、完了がそれぞれ何個あるかを毎日メールする、というスクリプトを作るとします。
function sendStatusUpdateMail() {
var sheet = SpreadsheetApp.getActive().getSheetByName("開発工程管理");
var statusValues = sheet.getRange("C:C").getValues().map(function callback(value) {return value[0]}); // C列にステータスが入っているので配列で取り出す。
statusValues.shift(); // 一行目は見出しだから捨てる
statusValues.forEach(function callback(value) {
Logger.log(value);
// この辺で集計して
});
// この辺でメールを送る
}
- このあと誰かが、"担当者" というカラムをB列あたりに挿入したとすると、、
C列は納期になっちゃうので、納期を集計したメールが届いてしまうことになります。
自由度の高さこそスプレッドシートを利用する理由なので、いちいちシートの修正で不便を受けたくはありません。
解決策
Step1 参照するカラムを見出し名で指定できるようにする。
https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns にあるように、 getRange はカラムを番号で受け取ることができるため、「ステータス」というカラムが何カラム目なのかがわかれば、カラム番号指定で getRange することができます。
function sendStatusUpdateMail() {
var sheet = SpreadsheetApp.getActive().getSheetByName("開発工程管理");
var columnNo = sheet.getRange("1:1").getValues()[0].indexOf("ステータス") + 1; // カラム番号は1から始まる
var statusValues = sheet.getRange(2, columnNo, 100, 1).getValues().map(function callback(value) {return value[0]});
// statusValues.shift(); 2行目からRangeを取っているので、shiftは不要。
// 以下同文
}
結果
これだけで、カラム追加による影響は受けなくなりました。
ただ、 sheet.getRange(2, columnNo, 100, 1) の 100 がハードコードで、シートの行数が増えてくると全行が見れなくなるのが微妙です。。
やはり、Rangeはできることなら "C:C" という風に全行を指定したいところです。
※そもそも全行取ってパフォーマンス的に良いの？という疑問はあるので、このあたり良い方法ご存知の方がいたら教えてください・・！
Step2 カラム番号からカラム名(アルファベット)を解決する
こんなコードでいけました！
function getColumnFromIndex(index) {
var alphabet = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
var column = ""
while (index > 0) {
var digit = (index - 1) % alphabet.length;
column = alphabet[digit] + column;
index = (index - 1 - digit) / alphabet.length;
}
return column;
}
A~Zだけ解決しててもしょうがないので、80カラムくらいで動作確認。
function testGetColumnFromIndex() {
for (var i = 1; i <= 80; i++) {
Logger.log(String(i) + ":" + getColumnFromIndex(i));
}
}
実行結果
1:A
2:B
3:C
4:D
5:E
6:F
7:G
8:H
9:I
10:J
11:K
12:L
13:M
14:N
15:O
16:P
17:Q
18:R
19:S
20:T
21:U
22:V
23:W
24:X
25:Y
26:Z
27:AA
28:AB
29:AC
30:AD
31:AE
32:AF
33:AG
34:AH
35:AI
36:AJ
37:AK
38:AL
39:AM
40:AN
41:AO
42:AP
43:AQ
44:AR
45:AS
46:AT
47:AU
48:AV
49:AW
50:AX
51:AY
52:AZ
53:BA
54:BB
55:BC
56:BD
57:BE
58:BF
59:BG
60:BH
61:BI
62:BJ
63:BK
64:BL
65:BM
66:BN
67:BO
68:BP
69:BQ
70:BR
71:BS
72:BT
73:BU
74:BV
75:BW
76:BX
77:BY
78:BZ
79:CA
80:CB
Z以降のカラムもちゃんと連番されていそうです。
これをStep1と合わせれば、以下のように、カラムに割り振った名前で列を取り出すことができます。
function sendStatusUpdateMail() {
var sheet = SpreadsheetApp.getActive().getSheetByName("開発工程管理");
var column = getColumnFromColumnIndexName("ステータス"); // ステータス という見出しのカラムのカラム名
var statusValues = sheet.getRange(column + ":" + column).getValues().map(function callback(value) {return value[0]}); // C列にステータスが入っているので配列で取り出す。
statusValues.shift(); // 一行目は見出しだから捨てる
statusValues.forEach(function callback(value) {
Logger.log(value);
// この辺で集計して
});
// この辺でメールを送る
}
function getColumnFromColumnIndexName(columnIndexName) {
var line = SpreadsheetApp.getActive().getSheetByName("開発工程管理").getRange("1:1").getValues();
var colomunNo = line[0].indexOf(columnIndexName) + 1;
return getColumnFromIndex(colomunNo);
}
function getColumnFromIndex(index) {
var alphabet = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
var column = ""
while (index > 0) {
var digit = (index - 1) % alphabet.length;
column = alphabet[digit] + column;
index = (index - 1 - digit) / alphabet.length;
}
return column;
}
もやん
自分でつけた見出しから取るって、普通にサポートしてくれても良いのになと思ったので、もっとスマートな方法があれば教えていただきたいです！
本件以外でもツッコミがあれば是非是非お願いします。
あと、本件とは直接関係ないのですが、配列は0から始まるのにカラムとか行の指定は1から始まるってなかなか混乱します。。(とはいえいきなり0行目が出現しても混乱だけど)