質問編集履歴

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

2020/05/13 02:33

投稿

Risney

スコア148

test CHANGED Viewed

File without changes

test CHANGED Viewed

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

参考リンクの修正

2020/05/13 02:33

投稿

Risney

スコア148

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -190,26 +190,28 @@
 ・全体の構成
-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

test CHANGED Viewed

File without changes

test CHANGED Viewed

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