目的
会社で工数を管理するツールを作成したい。
前提・実現したいこと
① GoogleAppScriptでスプレッドシートの指定の列の背景色カウント
発生している問題・エラーメッセージ
①の背景色のカウントについては実現できましたが、
「onEdit」などを使って、
該当のソースコード
GoogleAppScript
1 function getBackgroundColor(rangeSpecification) {
2 var sheet = SpreadsheetApp.getActiveSheet();
3 var cell = sheet.getRange(rangeSpecification);
4 return cell.getBackground();
5 }
6
7 function sumWhereBackgroundColorIs(color, rangeSpecification) {
8 var sheet = SpreadsheetApp.getActiveSpreadsheet();
9 var range = sheet.getRange(rangeSpecification);
10 var x = 0;
11 for (var i = 1; i <= range.getNumRows(); i++) {
12 for (var j = 1; j <= range.getNumColumns(); j++) {
13 var cell = range.getCell(i, j);
14 if(cell.getBackground() == color)
15 x += parseFloat(cell.getValue());
16 }
17 }
18 return x;
19 }
20
21 function countCellsWithBackgroundColor(color, rangeSpecification) {
22 var sheet = SpreadsheetApp.getActiveSpreadsheet();
23 var range = sheet.getRange(rangeSpecification);
24 var x = 0;
25 for (var i = 1; i <= range.getNumRows(); i++) {
26 for (var j = 1; j <= range.getNumColumns(); j++) {
27 var cell = range.getCell(i, j);
28 if(cell.getBackground() == color)
29 x++;
30 }
31 }
32 return x;
33 }
34
35 //スプレッドシートに独自メニューボタンを追加
36 //https://tonari-it.com/gas-spreadsheet-addmenu-onopen/#toc3
37 function onOpen(e){
38 //メニュー配列
39 var myMenu=[
40 {name: "工数再計算", functionName: "recalculate"},
41 ];
42
43 //メニューを追加
44 SpreadsheetApp.getActiveSpreadsheet().addMenu("再計算",myMenu);
45 }
46
47 //選択したシートを再計算させる
48 function recalculate(){
49 var activeRange = SpreadsheetApp.getActiveRange();
50 var originalFormulas = activeRange.getFormulas();
51 var originalValues = activeRange.getValues();
52
53 var valuesToEraseFormula = [];
54 var valuesToRestoreFormula = [];
55
56 originalFormulas.forEach(function(outerVal, outerIdx){
57 valuesToEraseFormula[outerIdx] = [];
58 valuesToRestoreFormula[outerIdx] = [];
59 outerVal.forEach(function(innerVal, innerIdx){
60 if('' === innerVal){
61 //The cell doesn't have formula
62 valuesToEraseFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
63 valuesToRestoreFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
64 }else{
65 //The cell has a formula.
66 valuesToEraseFormula[outerIdx][innerIdx] = '';
67 valuesToRestoreFormula[outerIdx][innerIdx] = originalFormulas[outerIdx][innerIdx];
68 }
69 })
70 })
71
72 activeRange.setValues(valuesToEraseFormula);
73 activeRange.setValues(valuesToRestoreFormula);
74 }
75
76 function onInstall(e) {
77 onOpen(e);
78 }
以下の関数をExcelのH11に挿入しています。
Excel
1
2 =countCellsWithBackgroundColor(getBackgroundColor("G11"), "B1:B31")
3 業務一覧（昼休憩）↑ ↑範囲（月曜日の列）
このサンプルの画像の場合、
試したこと
・H11のセルをコピーまたは切り取りしてそのままH11にペーストしても値は更新されない似ている質問 があったので
onEditのメソッドの挙動は問題ないと思います。
GoogleAppScript
1 function onEdit(e){
2 var range = e.range;
3 if(range.getColumn() == 1){
4 range.setNote('Last modified: ' + new Date());
5 }
6 }
・以下記事を参考に関数を実行Google Sheetで選択したセルを再計算させるApps Script
参考URL
今回こちらのScriptを組む上で参考にしたURL集です。
・全体の構成webapps.stackexchange.com しゃのんあどべんとかれんだー 2日目 (忘年会幹事のちょっとしたお悩みをちょっと解決してくれるかもしれない GAS)
・スプレッドシートへのアクセス方法【Qiita】Google Apps Script で Spreadsheet にアクセスする方法まとめ
・トリガーGoogle公式ドキュメント GAS入門 スプレッドシートのセル編集時のイベントトリガーまとめ Google Spreadsheetのトリガーの「編集時」と「値の変更」の違いを検証してみた
・Tetatail内の似ている質問【GoogleAppsScript】OnEditを特定の範囲においてのみ動作させる方法
Q&A
ベストアンサー
