質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Google

Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

Q&A

解決済

3回答

1384閲覧

テストのタスク表を一元管理したい

rhyme1992

総合スコア8

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Google

Googleは、アメリカ合衆国に位置する、インターネット関連のサービスや製品を提供している企業です。検索エンジンからアプリケーションの提供まで、多岐にわたるサービスを提供しています。

0グッド

0クリップ

投稿2018/08/16 17:58

学習塾を経営しております。
以下のように、指導している各生徒のテストの進捗状況をGoogle Spreadsheetで管理しております。

スプレッドシート名:生徒Aのテスト進捗

分野回数合否
展開第1回合格
展開第2回合格
因数分解第1回合格
因数分解第2回不合格

ここに例えば「展開 第3回」という範囲を追加したいです。

しかし他の生徒も同様のテストを受けるので、生徒ごとにこのようなスプレッドシートを作っており、
上記の表をそれぞれコピペして管理しているためテスト範囲の更新作業がかなり面倒です。

そもそものテスト範囲を大元のスプレッドシートにまとめて記しておいて、更新した場合は
それが各生徒のスプレッドシートに自動的に反映できれば嬉しいのですがそんなことは可能でしょうか。

大元のファイルを変更するだけで以下のようにできれば幸いです。

スプレッドシート名:生徒Aのテスト進捗

分野回数合否
展開第1回合格
展開第2回合格
展開第3回
因数分解第1回合格
因数分解第2回不合格

どうかお力添えをよろしくお願いいたします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

macaron_xxx

2018/08/17 00:25

可能です。方法は色々あります。管理方法にもよりますが、idふってARRAYFORMULAとVLOOKUPぐらいを組み合わせるだけでもできそうですね。
rhyme1992

2018/08/17 01:07

macaron_xxx様 ご回答ありがとうございます。よろしければその方法を御指南頂けないでしょうか。よろしくお願いします。
guest

回答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
hot-lemoned

総合スコア63

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

papinianus

2018/08/17 02:53

惑わせましたが「こういう集計をするのだとしたら」は私が書いた方法で報告または集計をするとしたらです。 質問者さんがやりたい方法の実装として回答を拝見しましたが、分野と回数の出現順が揃っていることなど提示された情報が最大限遵守されると仮定すれば、適切に組まれていると思います(私が質問者だったらBAだと思います)。
rhyme1992

2018/08/17 05:20

hot-lemoned様、papinianus様 ご回答ありがとうございます。 GASの解読に時間がかかるので、取り急ぎお礼のご連絡です。 ありがとうございました。
hot-lemoned

2018/08/17 08:06

papnianus様 すいません、私が誤読しておりました… ご指摘ありがとうございます。 またありがたきお言葉をいただけて光栄です。 rhyme1992様 コードに少しコメントを入れますのでお手すきの際にご確認ください。 このコードだと単体スプレッドシートにしか有効されないので、 ファイル命名で取得したり、フォルダに含まれているなどの条件でお試しください。
guest

0

回答として望んでないだろうことを承知で、私ならこうするだろうなというのを。

まずシートを次のような構成にします

Examシート

idcategoryrange
exp01展開第一回
fct01因数分解第一回

idを重複がないようにします(入力規則があれば)
細かいことですが、idの数字部分は桁を揃えるとソートがしやすいです

performシート

examiduserjudgedate
exp01A不合格2018-04-01
exp01A合格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

papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ベストアンサー

ぜんぜんベストプラクティスとも思っていませんが(こんだけVLOOKUPとか使ってたら重そう)
関数だけでもこんな風にできるよという一例を…。

(どう書くのが見やすいのか…。)

マスタシート

行数A列B列C列
1ID分野回数
2id001展開第1回
3id002展開第2回
4id005展開第3回
5id003因数分解第1回
6id004因数分解第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列
1id分野回数合否
2id001=VLOOKUP($F2,'マスタシート'!$A:$C,2,FALSE)=VLOOKUP($F2,'マスタシート'!$A:$C,3,FALSE)合格
3id002=VLOOKUP($F3,'マスタシート'!$A:$C,2,FALSE)=VLOOKUP($F3,'マスタシート'!$A:$C,3,FALSE)合格
4id003=VLOOKUP($F4,'マスタシート'!$A:$C,2,FALSE)=VLOOKUP($F4,'マスタシート'!$A:$C,3,FALSE)合格
5id004=VLOOKUP($F5,'マスタシート'!$A:$C,2,FALSE)=VLOOKUP($F5,'マスタシート'!$A:$C,3,FALSE)不合格
6id005=VLOOKUP($F6,'マスタシート'!$A:$C,2,FALSE)=VLOOKUP($F6,'マスタシート'!$A:$C,3,FALSE)

★更新するときは

  1. 上の行のD~H行をコピペして新しい行へ
  2. F列のIDを新しいIDに変更(マスタシートに追加したID)
  3. I列に合否を記入

生徒シート(表示値)

行数A列B列C列D列E列F列G列H列I列
1ID分野回数合否id分野回数合否
2id001展開第1回合格id001展開第1回合格
3id002展開第2回合格id002展開第2回合格
4id005展開第3回id003因数分解第1回合格
5id003因数分解第1回合格id004因数分解第2回不合格
6id004因数分解第2回不合格id005展開第3回

★左半分が求めてらっしゃるもので、右半分がテスト実施順の合否入力になります。

投稿2018/08/17 05:04

macaron_xxx

総合スコア3191

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

rhyme1992

2018/08/17 07:14 編集

marcaron_xxx様 ご回答ありがとうございます。 idを付与するというのはこういうことなんですね、勉強になりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問