[初投稿]Google Apps Script(GAS)を使って、部活の備品管理システムを作る！無料で

0,初めに

初投稿です。よろしくお願いします。機械・パソコンが大好きなので、浅く広く色々なことに興味を持ってやっています。

今の部活では備品管理にスプレットシートを利用しています。しかし、借りたい人がスプレットシートに借りるものと名前を書いているだけで、ぐちゃぐちゃだったので、GASを使ってみようと思いつきました。
プログラミングを学びはじめて間もない頃のコードなので拙いところも多いですが、誰かの役に立てたら嬉しいです。

1,成果物

貸出、返却はgoogleフォームを使って行います。貸出、返却を行うたびに在庫リストが更新されて、今現在借りることができるものだけが、プルダウンに残ります。返却も同様に、今現在借りられているものだけが表示されています。 貸出、返却状況はスプレットシートに記録されています。

GASをチョイスした理由

元々はpythonとdjangoを使って作る予定でしたが、僕が部活に在籍できるのが長くても一年なので、(高3はほとんど行けない)保守がしやすいようベースをスプレットシートにし、googleフォームを使うことで、入力を簡単にしました。本当は貸し出しページをhtmlなどで作りたかったのですが、google workspaceアカウントだとなぜかdopostがうまくいかず、送信ができないという不具合があり、泣く泣く諦めました。

2,仕様

2.1,大まかな動き

• 貸し出しをする時
  1.googleフォームを開く
  2.現在貸し出しができる在庫の中から借りたいものを選択する
  3.返却期日を設定する
  4.チェック(確認用)を入れる
  5.送信
  6.この時にスクリプトを動かす。

• 返却をする時
  1.googleフォームを開く
  2.現在貸し出し中の備品の中から、返却したいものを選ぶ
  3.送信
  4.この時にもスクリプトを動かす

2.2,スプレットシートとの連携とか

ファイル構成

フォルダ
├─ googleフォーム1
|
├─ googleフォーム2
│
└─ スプレットシート
　　└─ フォーム1の結果
　　└─フォーム2の結果
　　└─メンバーリスト(学年、学籍番号、名前)
　　└─備品リスト(備品名、在庫数)
　　└─ログ(名前、備品名、貸出日、返却予定日、返却日)

googleフォーム1が貸出、2が返却です。

大まかに考えていたこと

貸出が行われたら、在庫リストから在庫を1→0に書き換えて、フォーム1の結果に名前、メアド、備品名、貸出予定日を記述。その後、貸出、返却フォームのプルダウンを書き換える。
返却が行われたら、在庫リストから在庫を0→1に書き換えて、フォーム2の結果に名前、メアド、備品名を記述。貸出、返却フォームのプルダウンを書き換える。

問題点

もっとシンプルにかきたい。

3.1第一回目のコード

main.js

//googleフォームリストの更新を行う
function rewrite_GoogleFormsListItem() {
  const form = FormApp.openById('FORM1ID')
  const items = form.getItems()
  const section = rentchoicevalueslist()
  const nosection = returnnochoicevalueslists()
  console.log(section)
  //デバック用Google Formsの質問名と、IDを取得
  for (let i = 0; i < items.length; i++) {

    const item = items[i]
    const itemName = item.getTitle()
    const itemId = item.getId()

    console.log(`質問名 ${itemName}, \n質問のID ${itemId}`)

    //スプレッドシートのシェアハウスDBから内容を読み取って、Formの項目を更新する。
    if (itemName == '借りるものを選んでください') {
      items[i].asListItem().setChoiceValues(section)
      nosection.join
      items[i].setHelpText("現在貸出中のものは　"+nosection+",　です")
    }
  
  }
}


//[xxx,在庫ありor在庫なし]
function rentchoiceValues() {
  const spreadsheet =  SpreadsheetApp.openById('SPREADSHEET-ID');
  const sheet = spreadsheet.getSheetByName('goods_list');
  const lastRow = sheet.getLastRow()
  const valueslist = sheet.getRange(1, 2, lastRow, 2).getValues()
  valueslist.shift()
  console.log(valueslist)
  return valueslist
}

//プルダウンデータ[a,b,c,d,e,f,...]在庫あり名前だけ
function rentchoicevalueslist(){
 const valuelist =rentchoiceValues()
 var result = valuelist.filter( function( value){
   return value[1] == "在庫あり"
   })
 const result1 = result.map(elm=>elm[0])
 console.log(result1)
 return result1
}

//day,name,y/n,item,address,number
function returnchoiceValues() {
  const spreadsheet =  SpreadsheetApp.openById('SPREADSHEET-ID');
  const sheet = spreadsheet.getSheetByName('リクエスト');
  const lastRow = sheet.getLastRow()
  const valueslist = sheet.getRange(1, 1, lastRow, 7).getValues()
  valueslist.shift()
   const filterlist =valueslist.filter( function( value){
   return value[6] != 0
    })
 console.log(valueslist)
 console.log(filterlist)
  return filterlist
}

function returnnochoicevalueslists(){
  const novaluelist =returnchoiceValues()
  var result = novaluelist.filter( function( value){
   return value[2] == "n"
   })
 const result1 = result.map(elm　=> [elm[3]])
 console.log(result1)
 return result1
}

function returnAddGoogleFormsListItem() {
  const form = FormApp.openById('FORM2-ID')
  const items = form.getItems()
  let section = returnnochoicevalueslists()
  const long = section.length
  const empty =["空です(これを送信しないでください)"]
  if (long == 0){
    section = empty
  }
  //デバック用Google Formsの質問名と、IDを取得
  for (let i = 0; i < items.length; i++) {

    const item = items[i]
    const itemName = item.getTitle()
    const itemId = item.getId()
    console.log(`質問名 ${itemName}, \n質問のID ${itemId}`)

    //スプレッドシートのシェアハウスDBから内容を読み取って、Formの項目を更新する。
    if (itemName == '返すものを選んでください' ) {
     items[i].asListItem().setChoiceValues(section)
      }
    //if
  
   }//for
}//end
//リクエストページの書き換えn=>yへ
function delate (){
  const spreadsheet =  SpreadsheetApp.openById('SPREADSHEET-ID')
  const sheet = spreadsheet.getSheetByName('返却リクエスト')
  const sheetre = spreadsheet.getSheetByName("リクエスト")
  const lastRow = sheet.getLastRow()
  const need = sheet.getRange(lastRow,2).getValue()
  console.log(need)
  const sheet2 = returnchoiceValues()
  console.log(sheet2)
  const sheet2flat = sheet2.map(elm => elm[3])
  console.log(sheet2flat)
  const neetretu = sheet2flat.lastIndexOf(need)
  console.log(neetretu)
 sheetre.getRange(neetretu+2,3).setValue("y")
}

長々と書いてありますね。整理すると

• rewrite_googleformslistitem

  • rentchoicevalues
  • rentchoicevalueslist

• returnaddgoogleformslistitem

  • returnchoicevalues
  • returnnochoicevalueslists

• delete
  という処理に分けることができます。1つ1つ説明していきます。

貸出編

まずrentchoicevaluesで備品リストの中からアイテム名、現在の状況(在庫あり/なし)を最終まで取り出した後、一番上の行を消去します。(一番上の行は見出しだから)
次にrentchoicevalueslistを使って"在庫あり"だけを取り出します。その後result.mapで配列を備品名だけの一次配列にします。
それをrewrite_googleformslistitemでプルダウンに代入します。
現在貸し出し中のものは後ほど説明します。

貸出処理の時に在庫有無書き換えないの？と思った方はいい勘をしていますね。在庫あり、なしの書き換えについてはスプレットシートを利用しています。GASが動くよりもスプレットシートの関数を使った方が早いので、フォーム1の結果からまだ返してないものを抽出し、vlookupでそこにある(今現在借りられている)なら在庫なし、なかったら在庫ありというふうにしています。
実はスプレットシートの式にも工夫があって、arreyformula関数を使い、シートの行を直前まで消すことで、無駄な空白の行を減らし処理の高速化を図っています。

返却編

returnchoicevaluesでフォーム1の結果を読み込み、見出しを消去し、returnnochoicevalueslistsで今現在借りているものだけをフィルターし、1次配列にします。この配列はrewrite_googleformslistitemでも利用しています。この後は同様にプルダウンに代入します。

delete編

返却処理が行われると、フォーム2の結果に書き込みがされます。フォーム2の結果の中から一番下の行の備品名を取得し、returnchoicevaluesの4行目だけを取り出し、下から探します。見つかったらn→yに書き込みます。

3.2反省

思いつきで書いていたので無駄な処理が多過ぎる、あと名前の付け方がごちゃごちゃ過ぎてバグる。うまく動かないことがある。これは、トリガーと言って〇〇が実行された際に/何時頃に、処理を行うと言った機能を使って実行しているのですが、例えば返却フォームが送られた時、deleteの処理が先行してしまって、書き込みよりも前に実行されてしまい、返却がうまくできないなどの問題が発生しました。

まとめ

自分の言葉で自分のプログラムを説明することは難しいですね。コードを整形したバージョンも投稿するのでぜひ見てください。