【背景】
予実推移表など、項目ごとに金額の入力と計算が行われているスプレッドシートにおいて、数値や計算ロジックが整合していることは重要な前提である。
【対応方針】
入力されている数値が「ベタ打ち」、「計算結果」、「他シートの参照」なのかがわかっていると整合の確認がしやすいため、これらが自動で色分けされる仕組みを作った。
色の使い分けが重要な理由として、
- 数値の依存関係がわかりやすくなる
- 共通認識を持っていれば複数人で編集しやすくなる
などが挙げられる。コンサルティング会社が行っているような仕組みを想定している。
【対応方針へのアプローチ】
結果として色分けされていれば上記の目的は達成できるため、シートへの数値、関数などを入力後にスクリプトを実行すると条件に対応する色に変化するスクリプトを書いた。条件は下記のとおりである。
| 色 | テキストの状態 |
|---|---|
| 青 | ベタ打ち |
| 黒 | 同シートでの計算結果 |
| 緑 | 他シートからの参照 |
スクリプトを実行すると、「この行はベタ打ちなので青……」といった確認をして手動で色を変えていた作業を自動化し、効率化する。
留意点として、下記のように、参照関数が記載されたセル内で計算を行った場合は緑となる。
='他のシート'!A1+B2
上記に関しては、黒になるのではないかという意見も考えられるが、シートを管理する側の意見として参照と計算を同一セルで行うのはそもそも避けるべきであることと、同一シート内で情報が完結していないことを明示するために緑とした。
【行わなかったアプローチ】
行もしくは列ごとに入力制限を設ける。
行わなかった理由として、予実推移表などは頻繁に集計単位や期間が変更される。この頻度に比例して行列の追加が行われるため、セルに対する入力制限は、将来的に運用が破綻する可能性が髙いと判断した。フォーマットが固定されている場合は、このアプローチに検討の余地があると思う。
【コード】
function colorCellsBasedOnContentAndCountExcludingSheet() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheets = spreadsheet.getSheets();
let greenCellsCount = 0;
let blackCellsCount = 0;
let blueCellsCount = 0;
const excludedSheetName = "入力ルール"; // このシートは処理から除外
sheets.forEach(sheet => {
if (sheet.getName() === excludedSheetName) {
// "入力ルール"シートはスキップ
return;
}
const range = sheet.getDataRange();
const formulas = range.getFormulas();
const values = range.getValues(); // 数値またはパーセンテージのチェック用
range.setFontColor(null); // 全セルのフォントカラーをリセット
for (let i = 0; i < formulas.length; i++) {
for (let j = 0; j < formulas[i].length; j++) {
const formula = formulas[i][j];
const value = values[i][j].toString();
const cell = sheet.getRange(i + 1, j + 1);
// 数式が他のシートを参照しているかどうかのチェック
if (formula && formula.startsWith("=")) {
if (formula.includes("!")) {
cell.setFontColor("#008000"); // 彩度を落とした緑色
greenCellsCount++;
} else {
cell.setFontColor("#000000"); // 黒色: 他のシートを参照していない数式
blackCellsCount++;
}
} else if (typeof value === 'string' && (value.trim() !== "") && isNaN(parseFloat(value))) {
// 日本語テキスト含む場合は黒色、但し空文字は除外
cell.setFontColor("#000000"); // 黒色
blackCellsCount++;
} else if (!isNaN(value) || (!isNaN(parseFloat(value)) && value.toString().endsWith("%"))) {
cell.setFontColor("#0000FF"); // 青色: 数値またはパーセンテージ
blueCellsCount++;
}
}
}
});
console.log("Green cells count: " + greenCellsCount); // 他のシートを参照する数式が含まれるセルの数
console.log("Black cells count: " + blackCellsCount); // 黒色にすべきセルの数
console.log("Blue cells count: " + blueCellsCount); // 青色にすべきセルの数
}
【スクリプトの説明】
これいる？
- 「入力ルール」シートの存在
- 上記スクリプトでは、「入力ルール」シートには効果が及ばないようにしている。このシートに、上記のルールやそれ以外のルールを記載することを想定している。
【今後】
現在はすべてのシートに対してスクリプトを実行しているが、実行時間が長くなった場合などを踏まえ、該当のシートのみスクリプトが実行されるように変更してもよいと思う。
Discussion