【GAS入門】spreadsheetの情報をもとにgoogleSlideを自動で編集してみた！

# 今回やること
今回やることは僕が所属する団体のイベントで使用する、投票を集計し、スライドに反映するシステムをgasで書きたいと思います！
#### 今回やる流れ

[1. googleformで投票フォームを作成する](#-1-googleformで投票フォームを作成する)
[2. googlespreadsheetで投票結果を集計する](#-2-googlespreadsheetで投票結果を集計する)
[3. 集計結果をgoogleslideに反映させる](#-3-集計結果をgoogleslideに反映させる)

こんな感じでやっていきたいと思います！
spreadsheetやgasをある程度書けるよ！っていう人は3番目のgoogleslideに反映させるとこだけ見てくださ〜〜〜い

<details><summary>今回使ったスプシとかフォームとかコードとかもろもろ</summary>

[投票フォーム](https://forms.gle/ZbZMPho8bhd3TUBG8)
[spreadsheet](https://docs.google.com/spreadsheets/d/107NbFeayvBbz01qCYLOD9YGAui_95qxcfXd-3Sf4qNQ/edit?usp=sharing)
[GoogleSlide](https://docs.google.com/presentation/d/1hvp14BT1WiPTrgNcf87ydGbegzDbvvX6F-j3OU4zuYE/edit?usp=sharing)
```
var sheet = SpreadsheetApp.openById("107NbFeayvBbz01qCYLOD9YGAui_95qxcfXd-3Sf4qNQ").getSheetByName("フォームの回答 1")


function voteJudge() {

  for (let i = 4; i <= sheet.getLastRow(); i++) {
    if (sheet.getRange(i, 9).getValue() > 5) {
      sheet.deleteRow(i--)
    }
  }
}



function updateslide() {
  const data = sheet.getRange(1, 3, 3, 5).getValues() //情報取得
  let point = data[1].filter(function (ele, i, list) {
    return list.indexOf(ele) === i
  }) //重複削除
  point.sort((a, b) => { return b - a }) //data[1]を大きい順に並べ替え
  let f_list = [] //1位の人の名前を格納するためのリスト
  let s_list = [] //2位の人の名前を格納するためのリスト
  let t_list = [] //3位の人の名前を格納するためのリスト
  for (let i = 0; i < data[1].length; i++) {
    switch (data[1][i]) { //点数に応じてランキング分けてる
      case point[0]:
        f_list.push(data[0][i])
        break
      case point[1]:
        s_list.push(data[0][i])
        break
      case point[2]:
        t_list.push(data[0][i])
        break
    }
  }
  console.log(point[0], point[1], point[2])
  console.log(f_list, s_list, t_list)


  const slide = SlidesApp.openById("1hvp14BT1WiPTrgNcf87ydGbegzDbvvX6F-j3OU4zuYE").getSlideById("g13ffc53b676_0_0")
  // for (let shape of slide.getShapes()) {
  //   console.log(shape.getObjectId(), shape.getText().asString())
  // }
  slide.getPageElementById("g13ffc53b676_0_154").asShape().getText().setText(f_list.join(", ")) //1位の人の名前
  slide.getPageElementById("g13ffc53b676_0_156").asShape().getText().setText(s_list.join(", ")) //2位の人の名前
  slide.getPageElementById("g13ffc53b676_0_158").asShape().getText().setText(t_list.join(", ")) //3位の人の名前
  slide.getPageElementById("g13ffc53b676_0_155").asShape().getText().setText(point[0] + "点") //1位の人の得点
  slide.getPageElementById("g13ffc53b676_0_157").asShape().getText().setText(point[1] + "点") //2位の人の得点
  slide.getPageElementById("g13ffc53b676_0_159").asShape().getText().setText(point[2] + "点") //3位の人の得点
}
```
</details>

# 1. googleformで投票フォームを作成する

>[完成版](https://forms.gle/ZbZMPho8bhd3TUBG8)はこんな感じです！

#### 投票のルールについて

投票者は**持ち点が５点**あり、**1人にmax３点まで**投票できる
1人にmax３点までのところはformを作る際に工夫できましたが、5点以上投票した場合がformでは判定できませんでした。
なので、spreadsheetで集計する際に工夫していこうと思います！

#### Point
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/e812f0b4-d4a3-2ca0-0c3b-82a708365b06.png)
GoogleChromeで`form.new`でformを作りましょう！[完成版](https://forms.gle/ZbZMPho8bhd3TUBG8)を参照ください！
複数回投票されるのを防ぐため以下の写真のようにしときましょー！
![スクリーンショット 2022-07-30 21.59.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/83eedab3-31c9-50d0-f712-213c2d983a19.png)

# 2. googlespreadsheetで投票結果を集計する

![スクリーンショット 2022-07-30 22.17.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/a49f7893-e5aa-2bca-6ea0-166022fb0c91.png)

上の画像のようにスプシを開いたら、2行目と3行目を列の挿入で追加し、関数を追加していきます！
sumとrankで得票数とランキングを表示します！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/a15e1b54-2635-54d5-abf4-d1d1dcd0c76f.png)
>[参考](https://docs.google.com/spreadsheets/d/107NbFeayvBbz01qCYLOD9YGAui_95qxcfXd-3Sf4qNQ/edit?usp=sharing)

これで集計が終わったので、スライドに反映させるだけですが、その前に５点以上投票した人を判定していきましょう！！

### ５点以上投票した人を判定する
上で作ったspreadsheetの`I4`に **`=ArrayFormula(SUMIF(IF(COLUMN(C4:G4),ROW(C4:C)),ROW(G4:G),C4:G))`** と書きましょう！
これで合計値を表示できます！（やってることは普通のsumと同じです！全部の列でできるようにしてるだけです！）

この関数の意味は全部の行で`=sum(~~~)`と書くのがめんどくさいので1つのセルでまとめちゃおう〜〜！って感じです
なので理解できなかったらsumsumsumsumsumで全部の行で合計値出せばOKです！
```
=ArrayFormula(SUMIF(IF(COLUMN(1番上の行),ROW(1番左の列)),ROW(1番左の列),全範囲))
```
arrayformulaとsumは両方範囲を指定し処理を行うという点から相性が悪く、sumifを使い合計値を出している:scream:
別の書き方で行くと
```
=ArrayFormula(C4:C+D4:D+E4:E+F4:F+G4:G)
```
このようにも書けます！とにかくarrayformulaとsumを併用しようとしたのが間違いだったのか。。。

ここからgasを書いていきます！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/a4e8b3ba-ef91-4d5e-61f7-446a14f0d9a9.png)

「拡張機能」>「Apps Script」からGASを書いていきましょー！！

```
function voteJudge() {
  const sheet = SpreadsheetApp.openById("上で作ったspreadsheetのID").getSheetByName("解答が送られてくるsheetの名前")
  // sheetを指定

  for (let i = 4; i <= sheet.getLastRow(); i++) {
    if (sheet.getRange(i, 9).getValue() > 5) { // getRange(i, 9)は合計を計算してる列です！
      // ５点超えてたら列ごと消しちゃう
      sheet.deleteRow(i--) // i--は列が消えてしまうので数合わせです
    }
  }
}
```
spreadsheetのIDは↓↓こんな感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/1057ac11-17d6-a597-758f-7582ca05b2ba.png)

これで1度実行してみましょー！(ctrl + sで保存しときましょーって書くの忘れてた)
![スクリーンショット 2022-07-31 1.26.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/ad462b9d-e1d6-c34f-76c0-b9bef0a63402.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/5dcb0af6-91cb-5b3b-3204-4574beb28520.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/fdbc53d1-d9a3-45da-36f6-5ecd561a2ab2.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/b52ac09f-3761-5ace-c748-b28f6d7d9d9e.png)

**Before**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/35fa5429-95bc-cb06-19db-02a0573fcbee.png)
**After**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/c26a2346-09fe-a767-90e6-679e2a8b66ef.png)

こんな感じで9票投票していた人の票を削除できました！
+αでメール送ったり、webhookURLを使ってslackで通知したりなどして、消したことを通知すると良きだと思います！
あとはトリガーを設定して、投票されたら判定していくだけです！
まずgasの左の時計マークみたいなやつを押しましょー！
![スクリーンショット 2022-07-31 3.09.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/a0e7994e-0058-d014-f798-4bfeb85ab119.png)
左下の「トリガーを追加」から追加していきます！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/9afabd0b-7205-ad66-4944-69fa157be33a.png)
こんな感じに書き換えて保存しましょう！！
集計は以上です！これで得票数とランキングの表示に加え、ルール違反した票を無効にできました！！
長かったー
だるうううう
しんどかったらここでちょっと休憩してください
ラストはもうちょいめんどいので

# 3. 集計結果をgoogleslideに反映させる

[書き換えるスライド](https://docs.google.com/presentation/d/1hvp14BT1WiPTrgNcf87ydGbegzDbvvX6F-j3OU4zuYE/edit?usp=sharing)

## まずスライドの開き方！2パターンあります！
### 1つ目
```
const slide = SlidesApp.openById("スライドのID").getSlideById("スライド1枚1枚固有のID")
```

![スクリーンショット 2022-07-31 4.00.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/cfe1f8fb-7fa8-b290-0345-e70c1f9e7480.png)
**`https://docs.google.com/presentation/d/スライドのID/edit#slide=id.スライド1枚1枚固有のID`**
こんな感じになってます！

### 2つ目
```
const slide = SlidesApp.openById("スライドのID").getSlides()[何枚目のスライドか]
```
ちょっと注意！
普段からプログラミングをしているみなさんならお気づきかもしれませんが、
例えば10枚目のスライドを取得しようとすれば
```
const slide = SlidesApp.openById("スライドのID").getSlides()[9]
```
このようになります！つまり数え方が0から始まるのでスライド番号-1にして指定しましょー！

## 次に取得したスライド内の要素を取得します！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/8745d3e0-61cf-7ee1-e0e6-3365a75f5d62.png)
こんな感じのクソスライドを作りました！スライド内の1つ1つの要素はshapeと言います！
(画像とかはshapeじゃないみたい、、、文字とか図形とかがshape!)
すべてのshapeをgasで表示してみましょう！！
```
function updateslide() {
  const slide = SlidesApp.openById("スライドのID").getSlideById("スライド1枚1枚固有のID")
  for (let shape of slide.getShapes()) { //すべてのshapeを取得して展開しよう！って感じ
    console.log(shape.getText().asString())
  }
}
```
ここで僕はなんでやねんと思ったのですが、4行目の`console.log(shape.getText().asString())`色々書きすぎやん
`console.log(shape.getText())`でいいやんと思ったのですが、ここにはもちろんshape内の文字列も入っているのですが、それ以外に、文字の大きさや色といった色々変な設定が含まれます！なので表示するときは文字列のみを取り出す必要があります！

実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/4b17f9f5-988d-9f27-1a1e-af175a62b9a7.png)

ちゃんと取得できていますね！！

### スライドの編集
次は表示した文字を編集していきたいと思います！
spreadsheetを編集する場合はgetRangeで場所を指定し、書き換えをしていくと思いますが、slideではオブジェクトIDを取得し、要素の場所を指定して書き換えをします！

オブジェクトIDを取得するのがちょっと面倒なのですが、一緒にやっていきましょう！！
1個前にやったコードに少し付け加えるだけです！
```
function updateslide() {
  const slide = SlidesApp.openById("スライドのID").getSlideById("スライド1枚1枚固有のID")
  for (let shape of slide.getShapes()) {
    console.log(shape.getObjectId(), shape.getText().asString()) // 変更箇所
  }
}
```
4行目を変更しました！
これでどの文字がどんなオブジェクトIDかを表示させることができます！

実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/f09205b0-ff25-0e32-6f02-9c2ea9e3e862.png)
ここまできたらあとはどうとでもできます！
```
slide.getPageElementById("オブジェクトID").asShape().getText().setText("書き換えたい文字")
```
```
function updateslide() {
  const slide = SlidesApp.openById("スライドのID").getSlideById("スライド1枚1枚固有のID")
  // for (let shape of slide.getShapes()) {
  //   console.log(shape.getObjectId(), shape.getText().asString())
  // }
  slide.getPageElementById("g13ffc53b676_0_154").asShape().getText().setText("GASで編集したったぜえええ") //追記
}
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/1fcb38aa-6b23-de9c-3a0d-26cf14f40d08.png)

やったー！これで書き換え成功です！

## ついにラストです！！spreadsheetから情報を取得してslideを変更しましょう！！

ここからは[spreadsheet(C2:G3)](https://docs.google.com/spreadsheets/d/107NbFeayvBbz01qCYLOD9YGAui_95qxcfXd-3Sf4qNQ/edit?usp=sharing)を参考にスライドを書き換えていきます！！

ここでちょっと気になるポイント！
spreadsheet内の関数rankで1、2、3位を記録しているのですが、例えば1位が2人いた場合2位がいなくなります:scream:
できるだけいっぱい表彰してあげたいので複数人いても絶対0人表彰がないようにアルゴリズムを考えていきたいと思います！！
(spreadsheetで書いたrank関数いらんかった、、、)

### 流れ
1. 得票数を取得し、多い順に並べ替える
    1. スプシの2行目
1. 得票数が同じ人がいる場合、重複を無くす
1. 得票数の上位3つを取得する
    1. これで1、2、3位の点数が確定する
1. 1、2、3位の点数をもとに合致する人の名前を取得
1. スライドに反映させる

### コード
```
function updateslide() {
  const sheet = SpreadsheetApp.openById("107NbFeayvBbz01qCYLOD9YGAui_95qxcfXd-3Sf4qNQ").getSheetByName("フォームの回答 1")
  const data = sheet.getRange(1, 3, 3, 5).getValues() //情報取得
  let point = data[1].filter(function (ele, i, list) {
    return list.indexOf(ele) === i
  }) //重複削除
  point.sort((a, b) => { return b - a }) //data[1]を大きい順に並べ替え
  let f_list = [] //1位の人の名前を格納するためのリスト
  let s_list = [] //2位の人の名前を格納するためのリスト
  let t_list = [] //3位の人の名前を格納するためのリスト
  for (let i = 0; i < data[1].length; i++) {
    switch (data[1][i]) { //点数に応じてランキング分けてる
      case point[0]:
        f_list.push(data[0][i])
        break
      case point[1]:
        s_list.push(data[0][i])
        break
      case point[2]:
        t_list.push(data[0][i])
        break
    }
  }
  console.log(point[0], point[1], point[2])
  console.log(f_list, s_list, t_list)


  const slide = SlidesApp.openById("1hvp14BT1WiPTrgNcf87ydGbegzDbvvX6F-j3OU4zuYE").getSlideById("g13ffc53b676_0_0")
  // for (let shape of slide.getShapes()) {
  //   console.log(shape.getObjectId(), shape.getText().asString())
  // }
  slide.getPageElementById("g13ffc53b676_0_154").asShape().getText().setText(f_list.join(", ")) //1位の人の名前
  slide.getPageElementById("g13ffc53b676_0_156").asShape().getText().setText(s_list.join(", ")) //2位の人の名前
  slide.getPageElementById("g13ffc53b676_0_158").asShape().getText().setText(t_list.join(", ")) //3位の人の名前
  slide.getPageElementById("g13ffc53b676_0_155").asShape().getText().setText(point[0] + "点") //1位の人の得点
  slide.getPageElementById("g13ffc53b676_0_157").asShape().getText().setText(point[1] + "点") //2位の人の得点
  slide.getPageElementById("g13ffc53b676_0_159").asShape().getText().setText(point[2] + "点") //3位の人の得点
}
```

実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/00f1b118-5c92-4a19-4b1b-4a893517a0ce.png)

うまく反映されてますね！！
仕上げにgasで実行しなくてもスライドに反映できるようにspreadsheet上に図形を書いていきましょう！
![スクリーンショット 2022-07-31 6.39.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/b0b51e26-8ffd-1918-339a-6f6f943ed73c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/bdf01706-e1ef-d4a3-fbb7-f00788aa1d4c.png)
![スクリーンショット 2022-07-31 6.42.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/1dfd5703-6caa-043d-98ea-8b52620b0a7a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2586059/2621bf84-f208-f096-f71a-540a3d71d4d6.png)

終わったああああ！！

# 最後に
ここまで読んでいただきありがとうございます！
わかりづらいところやよりスマートに書く方法とかあったらコメントにお願いします！

それじゃああばいばいき〜〜ん