GAS超入門③ - 計算結果を別のシートに書き出してみよう

はじめに

これまで、2回に渡ってGASを使ってスプレッドシートを扱う方法を学んできました。
今回は、GASを使って特定のシートの値を計算して計算結果を別のシートに書き出す方法を学んで行きます。
これができるようになると「学校で、クラスごとの平均点数を出してランキング出したり」「会社で担当部署ごとの営業成績を一括で計算して、目標が未達の部署のみアラートを出したり」などが簡単にできるようになります。

スプレッドシートを用意する

まずはスプレッドシートを用意しましょう。今回はこのように各生徒の科目ごとの点数の一覧が書かれたシートを用意しました。
このシートの名前は「点数一覧」としてます。

スプレッドシートから値を取得してみる

さて、まずはこのシートから値を取得してみましょう。計算するための元データの取得って感じですね。

スプレッドシートからスクリプトエディタを開く方法は第一回を参照して下さい。

function myFunction() {
 // spreadSheetオブジェクトの取得
 let spreadSheetByActive = SpreadsheetApp.getActive()
 
 // sheetオブジェクトの取得
 let sheetByActive = spreadSheetByActive.getSheetByName("点数一覧")
 
 // rangeオブジェクトの取得  
 // 引数の意味
 // 【第1,2引数】2,1 = A2から、
 // 【第3引数】sheetByActive.getLastRow() - 1 = 縦の合計行数から1引いた分(2行目からスタートしてるので)
 // 【第4引数】3 = 3列分
 let range = sheetByActive.getRange(2,1,sheetByActive.getLastRow() -1, 3)

 // 生徒の科目ごとの点数
 let values = range.getValues()

 // 一旦ログに吐き出してみる
 Logger.log(values)
}

シートから値を取得する方法は以下の通りでしたね。

1. spreadSheetオブジェクトを取得
2. sheetオブジェクトを取得
3. sheetオブジェクトのgetRangeメソッドでrange(範囲)オブジェクトを取得
4. rangeオブジェクトから値を取得

↓ 結果はこのような2次元配列で出力されます。

生徒ごとの合計点数を計算する

では、この結果を用いて、まずは生徒ごとの合計点数を計算してみましょう。

先程のコードに続きを書いていきます。

  /**
  * 計算結果を入れるオブジェクトを定義
  */
 let result = {
   "西田" : {"合計": 0, "平均": 0},
   "寺西" : {"合計": 0, "平均": 0},
 }

 /**
  * 合計点数を求める
  */
 for (let i in values) {
   let name  = values[i][0]
   let point = values[i][2]
   result[name]["合計"] += point
 }

 Logger.log(result)

resultに、西田と寺西の合計点数が入りました。

生徒ごとの平均点数を計算する

次に合計から平均を計算していきます。

 /**
  * 平均点を求める
  */
 Object.keys(result).forEach((name) => {
   result[name]["平均"] = result[name]["合計"] / 2
 })


 Logger.log(result)

こんな感じで、すでに「result[name]の"合計"プロパティ」には合計点数が入ってるので、それを2で割って平均を算出してます。

OK、これで2人の合計点数と平均点が計算できました。

オブジェクトを配列に変換する

さて、このまま、setValuesして別シートに書き出していきたいのですが、setValuesは値であるか、2次元配列である必要があります。

今回は計算結果がオブジェクトのままなので、これを配列に変換していきます。
変換の仕方は以前Twitterでも紹介したこの方法を使います。

Object.keys(obj)を使ってループを回して、その結果(key,value)を配列にpushして追加していってます。

 // 結果を挿入するための配列
 let resultArray = []

 /**
  * オブジェクト => 配列に変換する
  */
 Object.keys(result).forEach((name) => {
   resultArray.push([name, result[name]["合計"], result[name]["平均"]])
 })
 
Logger.log(resultArray)

今回だとこんな感じです、「[名前, 合計点数, 平均点数]」という配列をresultArrayにpushして2次元の配列を作ります。

計算結果を別のシートに書き出してみる

さて、最後に結果を別シートに吐き出します。まずは「生徒別合計と平均」という空のシートを作っちゃいましょう。

そして、このシートのrangeオブジェクトを取得していきます。

 let targetSheet = spreadSheetByActive.getSheetByName("生徒別合計と平均")
 let targetRange = targetSheet.getRange(1,1,3,3)

すでにspreadSheetオブジェクトはコードの冒頭で取得してるのでそれを使いつつ、getSheetByNameで「生徒別合計と平均」のsheetオブジェクトを取得します。
今回は、このシートのA1:C3に対して値を入れていきたいので、このようにrangeオブジェクトを取得します。

最後に、これに先程変換した2次元配列をsetValuesしてみます。

  targetRange.setValues(resultArray)

さて、これを実行して、「生徒別合計と平均」のシートを見てみましょう。

おお、見事に生徒別の合計と平均点数を別のシートに書き出すことができました。

理解度チェックテスト① - クラスごとの平均点数を出してみよう

以下のようなシートを用意して、クラスごとの平均点数を計算した別シートを作ってください。

回答はいつもどおりTwitterで#スキプラチャレンジのハッシュタグを付けてアウトプットしてみてください！

クラス	名前	科目	点数
A組	西田	数学	20
A組	西田	国語	98
A組	西田	英語	30
A組	村山	数学	30
A組	村山	国語	90
A組	村山	英語	80
A組	岸田	数学	34
A組	岸田	国語	43
A組	岸田	英語	23
B組	西田	数学	90
B組	西田	国語	80
B組	西田	英語	34
B組	村山	数学	99
B組	村山	国語	12
B組	村山	英語	100
B組	岸田	数学	90
B組	岸田	国語	80
B組	岸田	英語	88
C組	相田	数学	70
C組	相田	国語	90
C組	相田	英語	93
C組	村田	数学	34
C組	村田	国語	25
C組	村田	英語	40
C組	桜田	数学	30
C組	桜田	国語	50
C組	桜田	英語	66

理解度チェックテスト② - 赤点の生徒の情報だけ出力してみよう

さっきの理解度チェックテスト①と同じシートを使います。
この中から赤点(30点以下)の生徒の情報[クラス名、名前、科目、点数]だけを抽出して別シートを作ってください。

さいごにお願い

このnoteの「スキ」ボタンを押してください🙇‍♂️
(嬉しいメッセージが出てくるかも？笑)

シェアしてください🙇‍♂️
(モチベーション上がって有益情報を引き続き発信しまくります！