質問編集履歴

目的、サンプル画像の追加。実現したいこと、試した事の修正。

2020/05/13 02:33

投稿

Risney

スコア148

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -1,21 +1,26 @@
+### 目的
+会社で工数を管理するツールを作成したい。
+![イメージ説明](e393ae10dfabf1a87b02acda4402d2e9.png)
+サンプルのスプレッドシートの図を確認して頂ければイメージが付きやすいと思うのですが、
+B列の月曜日のセルに右の業務一覧から、業務を選択してコピペをします。
+そうするとH列にB列内の背景色をカウントした数が表示されます。
+背景色の参照をしているのはG列の業務一覧のセルです。
 ### 前提・実現したいこと
 ① GoogleAppScriptでスプレッドシートの指定の列の背景色カウント
-② シートの値をどこか編集したのをトリガーにシート全体の値を更新したい
+② 「工数再計算」という独自のボタンをトリガーに指定セルを更新したい
+↳更新したいのはH列の関数が入っているセルです
 ### 発生している問題・エラーメッセージ
 ①の背景色のカウントについては実現できましたが、
 ②でつまずいております。
-「onEdit」を使えば、
+「onEdit」などを使って、
-ユーザーがスプレッドシートの値を変更したときに実行されるので
+スプレッドシートを更新しても、値は更新されず、
-「onEdit」のメソッド内に処理をかけば良い。
-というところまで理解したのですが、
-**どんな処理を書くか？**が分からなくて困っています。
+毎回セルに関数を張りなおさないと数字が更新されない状態です。
-なので現在、毎回セルに関数を張りなおさないと数字が更新されない状態です。
 ### 該当のソースコード
 ```GoogleAppScript
@@ -53,26 +58,72 @@
   return x;
 }
-// onEdit Event
+//スプレッドシートに独自メニューボタンを追加
+//https://tonari-it.com/gas-spreadsheet-addmenu-onopen/#toc3
-function onEdit(event) {
+function onOpen(e){
+  //メニュー配列
+  var myMenu=[
+    {name: "工数再計算", functionName: "recalculate"},
+  ];
-  // ここに編集したシートを更新する処理をいれる？
+  //メニューを追加
-  }
+  SpreadsheetApp.getActiveSpreadsheet().addMenu("再計算",myMenu);
 }
+//選択したシートを再計算させる
+function recalculate(){
+  var activeRange = SpreadsheetApp.getActiveRange();
+  var originalFormulas = activeRange.getFormulas();
+  var originalValues = activeRange.getValues();
+  var valuesToEraseFormula = [];
+  var valuesToRestoreFormula = [];
+  originalFormulas.forEach(function(outerVal, outerIdx){
+    valuesToEraseFormula[outerIdx] = [];
+    valuesToRestoreFormula[outerIdx] = [];
+    outerVal.forEach(function(innerVal, innerIdx){
+      if('' === innerVal){
+        //The cell doesn't have formula
+        valuesToEraseFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
+        valuesToRestoreFormula[outerIdx][innerIdx] = originalValues[outerIdx][innerIdx];
+      }else{
+        //The cell has a formula.
+        valuesToEraseFormula[outerIdx][innerIdx] = '';
+        valuesToRestoreFormula[outerIdx][innerIdx] = originalFormulas[outerIdx][innerIdx];
+      }
+    })
+  })
+  activeRange.setValues(valuesToEraseFormula);
+  activeRange.setValues(valuesToRestoreFormula);
+}
+function onInstall(e) {
+  onOpen(e);
+}
 ```
-以下の関数をExcelのA2セルに挿入しています。
+以下の関数をExcelのH11に挿入しています。
-「範囲C1:C31のセルの色」と「A9の色」が一致した数が「A2」に表示されます。
+「範囲B1:B31のセルの色」と「G11の色」が一致した数が「H11」に表示されます。
 ```Excel
-=countCellsWithBackgroundColor(getBackgroundColor("A9"), "C1:C31")
+=countCellsWithBackgroundColor(getBackgroundColor("G11"), "B1:B31")
                                    業務一覧（昼休憩）↑　　　↑範囲（月曜日の列）
 ```
+このサンプルの画像の場合、
+「昼休憩（青のセル）」は「範囲（月曜日の列）」内で１つです。
 ### 試したこと
+・H11のセルをコピーまたは切り取りしてそのままH11にペーストしても値は更新されない
+↳コピーまたは切り取り後に一度セルの値を削除してペーストすると値は更新される。
-teratail内で[似ている質問](https://teratail.com/questions/21724)があったので
+・teratail内で[似ている質問](https://teratail.com/questions/21724)があったので
 onEditメソッド内に以下の文章を入れたところ、
 編集したセルに最終更新日時がメモとして挿入されたのを確認しているので、
 onEditのメソッドの挙動は問題ないと思います。
 ```GoogleAppScript
 function onEdit(e){
@@ -82,13 +133,10 @@
   }
 }
 ```
+・以下記事を参考に関数を実行
+セルの数式を取得→セルの数式削除→取得した数式をペースト
+[Google Sheetで選択したセルを再計算させるApps Script](https://qiita.com/katz/items/28635f226df172e2f36a)
-### 懸念点
-毎回編集する度に更新だと以下の点が気になります。
-・同時に２つのセルをコピペなどで編集した場合どうなるのか？
-・毎回更新していたら作業が進みにくそうなので、ボタンなどのトリガーで起動したほうがよい？
 ### 参考URL
 今回こちらのScriptを組む上で参考にしたURL集です。

参考リンクの修正

2020/05/13 02:33

投稿

Risney

スコア148

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -94,15 +94,16 @@
 今回こちらのScriptを組む上で参考にしたURL集です。
 ・全体の構成
-https://webapps.stackexchange.com/questions/23881/calculating-shaded-cells
+[webapps.stackexchange.com](https://webapps.stackexchange.com/questions/23881/calculating-shaded-cells)
-http://shanon-tech.blogspot.com/2015/12/2.html
+[しゃのんあどべんとかれんだー 2日目 (忘年会幹事のちょっとしたお悩みをちょっと解決してくれるかもしれない GAS)](http://shanon-tech.blogspot.com/2015/12/2.html)
 ・スプレッドシートへのアクセス方法
-https://qiita.com/negito6/items/c64a7a8589faaffcfdcf
+[【Qiita】Google Apps Script で Spreadsheet にアクセスする方法まとめ](https://qiita.com/negito6/items/c64a7a8589faaffcfdcf)
 ・トリガー
+[Google公式ドキュメント](https://developers.google.com/apps-script/guides/triggers/)
-https://webbibouroku.com/Blog/Article/gas-event-cell#outline__2
+[GAS入門 スプレッドシートのセル編集時のイベントトリガーまとめ](https://webbibouroku.com/Blog/Article/gas-event-cell#outline__2)
-http://madachugakusei.hatenablog.com/entry/2015/10/18/140439
+[Google Spreadsheetのトリガーの「編集時」と「値の変更」の違いを検証してみた](http://madachugakusei.hatenablog.com/entry/2015/10/18/140439)
 ・Tetatail内の似ている質問
-https://teratail.com/questions/21724
+[【GoogleAppsScript】OnEditを特定の範囲においてのみ動作させる方法](https://teratail.com/questions/21724)

内容の修正

2020/04/24 06:35

投稿

Risney

スコア148

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -10,17 +10,15 @@
 「onEdit」を使えば、
 ユーザーがスプレッドシートの値を変更したときに実行されるので
-「onEdit」のメソッド内に
+「onEdit」のメソッド内に処理をかけば良い。
+というところまで理解したのですが、
+**どんな処理を書くか？**が分からなくて困っています。
+なので現在、毎回セルに関数を張りなおさないと数字が更新されない状態です。
 ### 該当のソースコード
-```GoogleAppScript（JavaScript）
+```GoogleAppScript
-//参考URL　https://webapps.stackexchange.com/questions/23881/calculating-shaded-cells
-//　　　　　http://shanon-tech.blogspot.com/2015/12/2.html
-//=countCellsWithBackgroundColor(getBackgroundColor("A9"), "C1:C31")
-//                                    業務一覧（昼休憩）↑　　　↑範囲（月曜日の列）
 function getBackgroundColor(rangeSpecification) {
   var sheet = SpreadsheetApp.getActiveSheet();
   var cell = sheet.getRange(rangeSpecification);
@@ -55,30 +53,56 @@
   return x;
 }
-//アクセス方法まとめ
+// onEdit Event
-//https://qiita.com/negito6/items/c64a7a8589faaffcfdcf
+function onEdit(event) {
-//シートID　1Absxwsnc1VyxCP7CLoTkTl2twWJuQcSHLd7OHNmJusg
+  // ここに編集したシートを更新する処理をいれる？
+  }
+}
+```
-//トリガー　https://webbibouroku.com/Blog/Article/gas-event-cell#outline__2
+以下の関数をExcelのA2セルに挿入しています。
-//http://madachugakusei.hatenablog.com/entry/2015/10/18/140439
-//https://teratail.com/questions/21724
+「範囲C1:C31のセルの色」と「A9の色」が一致した数が「A2」に表示されます。
+```Excel
+=countCellsWithBackgroundColor(getBackgroundColor("A9"), "C1:C31")
+                                   業務一覧（昼休憩）↑　　　↑範囲（月曜日の列）
+```
+### 試したこと
+teratail内で[似ている質問](https://teratail.com/questions/21724)があったので
-// onEdit Event
+onEditメソッド内に以下の文章を入れたところ、
+編集したセルに最終更新日時がメモとして挿入されたのを確認しているので、
+onEditのメソッドの挙動は問題ないと思います。
+```GoogleAppScript
-function onEdit(event) {
+function onEdit(e){
-  // 編集のあったシート
-  var ss = event.source.getActiveSheet();
-  var range = ss.event.range;
+  var range = e.range;
   if(range.getColumn() == 1){
-  range.setNote('変更しました');
+    range.setNote('Last modified: ' + new Date());
   }
 }
 ```
-### 試したこと
+### 懸念点
-onEditメソッド
+毎回編集する度に更新だと以下の点が気になります。
+・同時に２つのセルをコピペなどで編集した場合どうなるのか？
+・毎回更新していたら作業が進みにくそうなので、ボタンなどのトリガーで起動したほうがよい？
-### 補足情報（FW/ツールのバージョンなど）
+### 参考URL
-ここにより詳細な情報を記載してください。
+今回こちらのScriptを組む上で参考にしたURL集です。
+・全体の構成
+https://webapps.stackexchange.com/questions/23881/calculating-shaded-cells
+http://shanon-tech.blogspot.com/2015/12/2.html
+・スプレッドシートへのアクセス方法
+https://qiita.com/negito6/items/c64a7a8589faaffcfdcf
+・トリガー
+https://webbibouroku.com/Blog/Article/gas-event-cell#outline__2
+http://madachugakusei.hatenablog.com/entry/2015/10/18/140439
+・Tetatail内の似ている質問
+https://teratail.com/questions/21724