学習塾を経営しております。
以下のように、指導している各生徒のテストの進捗状況をGoogle Spreadsheetで管理しております。
スプレッドシート名:生徒Aのテスト進捗
分野 | 回数 | 合否 |
---|---|---|
展開 | 第1回 | 合格 |
展開 | 第2回 | 合格 |
因数分解 | 第1回 | 合格 |
因数分解 | 第2回 | 不合格 |
ここに例えば「展開 第3回」という範囲を追加したいです。
しかし他の生徒も同様のテストを受けるので、生徒ごとにこのようなスプレッドシートを作っており、
上記の表をそれぞれコピペして管理しているためテスト範囲の更新作業がかなり面倒です。
そもそものテスト範囲を大元のスプレッドシートにまとめて記しておいて、更新した場合は
それが各生徒のスプレッドシートに自動的に反映できれば嬉しいのですがそんなことは可能でしょうか。
大元のファイルを変更するだけで以下のようにできれば幸いです。
スプレッドシート名:生徒Aのテスト進捗
分野 | 回数 | 合否 |
---|---|---|
展開 | 第1回 | 合格 |
展開 | 第2回 | 合格 |
展開 | 第3回 | |
因数分解 | 第1回 | 合格 |
因数分解 | 第2回 | 不合格 |
どうかお力添えをよろしくお願いいたします。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/08/17 01:07
回答3件
0
papinianus様の回答にもありますが諸問題を考慮しないと、
コードの実行タイミングも難しいと思います。
こういう集計をするのだとしたら
…とありましたので、なんとなくこんな感じかなと自分の勉強がてらに書いてみました。
行を追加するのではなくて、更新したいスプレッドシートの値を全部取得して、
テンプレートの値とマージするような処理です。
(罫線とか列サイズとかいろいろとあると思いますが未考慮です。。。)
GAS
1function teratail_test(){ 2 Logger.log("--開始--"); 3 4 //------テンプレートのスプレッドシートを指定 5 var template_ssid = "**********"; //テンプレート用のスプレッドシートID 6 var template_sheet = SpreadsheetApp.openById(template_ssid).getActiveSheet(); //テンプレートのスプレッドシートの現在のシートを有効(シートは1枚しかないことを想定) 7 8 var template_table = template_sheet.getRange(1, 1, template_sheet.getLastRow(), template_sheet.getLastColumn()); //シートの左上から最終行、最終列までの範囲を取得 9 var template_table_value = template_table.getValues(); //範囲の値を取得 10 //Logger.log(template_table_value); 11 //------ 12 13 //------生徒用のスプレッドシートを指定 14 var check_ssid = "**********"; //生徒用のスプレッドシートID 15 var check_sheet = SpreadsheetApp.openById(check_ssid).getActiveSheet(); //生徒用のスプレットシートの現在シートを有効(シートは1枚しかないことを想定) 16 17 var check_table = check_sheet.getRange(1, 1, check_sheet.getLastRow(), check_sheet.getLastColumn()); //シートの左上から最終行、最終列までの範囲を取得 18 var check_table_value = check_table.getValues(); //範囲の値を取得 19 //Logger.log(check_table_value); 20 //------ 21 22 23 //------生徒用のスプレッドシートのセルを上書き 24 var tocopy = []; 25 var t = 0; 26 //テンプレートシートで取得した行数分繰り返し 27 for(var i = 0; template_table_value.length > i; i++){ 28 if(t < check_table_value.length){ 29 if(template_table_value[i][0] !== check_table_value[t][0] || template_table_value[i][1] !== check_table_value[t][1]){ 30 //テンプレートと生徒の分類、回数が同じじゃない場合 31 tocopy.push(template_table_value[i]); 32 }else{ 33 //テンプレートと生徒の分類、回数が同じ場合 34 tocopy.push(check_table_value[t]); 35 t++; 36 } 37 }else{ 38 //テンプレートシートの方が行が多かった場合 39 tocopy.push(template_table_value[i]); 40 } 41 } 42 //Logger.log(tocopy); 43 var write_value = check_sheet.getRange(1, 1, tocopy.length, check_sheet.getLastColumn()).setValues(tocopy); //生徒用スプレッドシートに書き込み 44 //------ 45 46 Logger.log("--終了--"); 47}
書いてから思うのは、自分のコードが柔軟に対応できていないなぁとか思ってしまいますね。。。
テンプレートのセル始まりと生徒さんのセル始まりがずれててもうまく動かないですし。 あぁ…
修正:
・コードにコメントを追加
投稿2018/08/17 01:42
編集2018/08/17 08:16総合スコア63
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/08/17 02:53
2018/08/17 05:20
2018/08/17 08:06
0
回答として望んでないだろうことを承知で、私ならこうするだろうなというのを。
まずシートを次のような構成にします
Examシート
id | category | range |
---|---|---|
exp01 | 展開 | 第一回 |
fct01 | 因数分解 | 第一回 |
idを重複がないようにします(入力規則があれば)
細かいことですが、idの数字部分は桁を揃えるとソートがしやすいです
performシート
examid | user | judge | date |
---|---|---|---|
exp01 | A | 不合格 | 2018-04-01 |
exp01 | A | 合格 | 2018-04-11 |
examidをリストから選択できるようにします。
性質的に、いずれ合格になる、はほぼ明らか(塾なのでね)なので、合格があっても無意味だと思っています。0回以上の不合格と1回の合格があるのではないかと思いこういう構成
場合によっては、user列ではなく、シートをuser名にしてもいいのかもしれないです。
こういうデータにしておいて、一日一回くらいのトリガでreportシートに全員分をデータを集計して作るか、proceedシートでA1セルで名前(固定リスト)を選択するとリストアップされるシートを作るか、にすると思います。
ただ、gasでこの形のデータを集計するのは面倒なので、Accessを使って集計するようにすると思います(googlesheetの利点はどこからでも見れたり、web連携しやすいことですが、データの性質からいってその利点は利として働くのか疑問でもあります)
こういう集計をするのだとしたらSpreadsheetApp.getActiveSpreadsheet().getSheetByName('').getDataRange().getValues()
みたいなもので一旦データを取って、arrayをfilter,sortして、mapかreduceで集計したい形にして、setValues()
する、ということになるかと思います。
--
ただただ行を追加すると考えても、次の諸問題を解決しないと自動化することは難しいような印象です。
0. 自動処理を組んだ時点で、各生徒のシートはデータとして同じなのか
0. どういうタイミングで行追加するのか(随時?一日一回?何かの切っ掛け?)
0. 間違って追加したものを消すのはどうするのか
0. 間に入れないといけないようだが、未知の分野(の第一回)が出てきた場合、どこに挿入するのか
投稿2018/08/17 00:38
総合スコア12705
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
ベストアンサー
ぜんぜんベストプラクティスとも思っていませんが(こんだけVLOOKUPとか使ってたら重そう)
関数だけでもこんな風にできるよという一例を…。
(どう書くのが見やすいのか…。)
マスタシート
行数 | A列 | B列 | C列 |
---|---|---|---|
1 | ID | 分野 | 回数 |
2 | id001 | 展開 | 第1回 |
3 | id002 | 展開 | 第2回 |
4 | id005 | 展開 | 第3回 |
5 | id003 | 因数分解 | 第1回 |
6 | id004 | 因数分解 | 第2回 |
★順番は生徒シートに表示したい順序、IDは一意であればでOK
★途中に行の追加ガンガンしていってOK
生徒シート(入力値)
行数 | A列 | B列 | C列 | D列 |
---|---|---|---|---|
1 | =ARRAYFORMULA('マスタシート'!A:C) | 合否 | ||
2 | =VLOOKUP($A2,$F:$I,4,FALSE) | |||
3 | =VLOOKUP($A3,$F:$I,4,FALSE) | |||
4 | =VLOOKUP($A4,$F:$I,4,FALSE) | |||
5 | =VLOOKUP($A5,$F:$I,4,FALSE) | |||
6 | =VLOOKUP($A6,$F:$I,4,FALSE) |
行数 | F列 | G列 | H列 | I列 |
---|---|---|---|---|
1 | id | 分野 | 回数 | 合否 |
2 | id001 | =VLOOKUP($F2,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F2,'マスタシート'!$A:$C,3,FALSE) | 合格 |
3 | id002 | =VLOOKUP($F3,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F3,'マスタシート'!$A:$C,3,FALSE) | 合格 |
4 | id003 | =VLOOKUP($F4,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F4,'マスタシート'!$A:$C,3,FALSE) | 合格 |
5 | id004 | =VLOOKUP($F5,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F5,'マスタシート'!$A:$C,3,FALSE) | 不合格 |
6 | id005 | =VLOOKUP($F6,'マスタシート'!$A:$C,2,FALSE) | =VLOOKUP($F6,'マスタシート'!$A:$C,3,FALSE) |
★更新するときは
- 上の行のD~H行をコピペして新しい行へ
- F列のIDを新しいIDに変更(マスタシートに追加したID)
- I列に合否を記入
生徒シート(表示値)
行数 | A列 | B列 | C列 | D列 | E列 | F列 | G列 | H列 | I列 |
---|---|---|---|---|---|---|---|---|---|
1 | ID | 分野 | 回数 | 合否 | id | 分野 | 回数 | 合否 | |
2 | id001 | 展開 | 第1回 | 合格 | id001 | 展開 | 第1回 | 合格 | |
3 | id002 | 展開 | 第2回 | 合格 | id002 | 展開 | 第2回 | 合格 | |
4 | id005 | 展開 | 第3回 | id003 | 因数分解 | 第1回 | 合格 | ||
5 | id003 | 因数分解 | 第1回 | 合格 | id004 | 因数分解 | 第2回 | 不合格 | |
6 | id004 | 因数分解 | 第2回 | 不合格 | id005 | 展開 | 第3回 |
★左半分が求めてらっしゃるもので、右半分がテスト実施順の合否入力になります。
投稿2018/08/17 05:04
総合スコア3191
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。