Skip to content

Instantly share code, notes, and snippets.

@yuta-nihei

yuta-nihei/shufflesystem.js

Last active December 18, 2020 08:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save yuta-nihei/957b37ebccc7cb84184f0f0985325556 to your computer and use it in GitHub Desktop.
Save yuta-nihei/957b37ebccc7cb84184f0f0985325556 to your computer and use it in GitHub Desktop.
Download ZIP
shufflesystem
Raw
shufflesystem.js
const viewFile = 'view'
// シートオブジェクトを作成
const spreadsheet = SpreadsheetApp.openById("")
// view.html へ出力
const template = HtmlService.createTemplateFromFile('view')
const doGet = () => {
// 操作するシートのオブジェクトを取得
const s20sheet = spreadsheet.getSheetByName('s20')
const n20sheet = spreadsheet.getSheetByName('n20')
const c20sheet = spreadsheet.getSheetByName('c20')
const s20name = s20sheet.getRange(2, 1, s20sheet.getLastRow() - 1, 2).getValues()
const n20name = n20sheet.getRange(2, 1, n20sheet.getLastRow() - 1, 2).getValues()
const c20name = c20sheet.getRange(2, 1, c20sheet.getLastRow() - 1, 2).getValues()
// フォーマット
const formatedNamesS20 = shuffle(s20name)
const formatedNamesN20 = shuffle(n20name)
const formatedNamesC20 = shuffle(c20name)
// グループの作成
const groupsS20 = createGroups(formatedNamesS20)
const groupsN20 = createGroups(formatedNamesN20)
const groupsC20 = createGroups(formatedNamesC20)
console.log(groupsS20)
// HTMLデータ作成
template.group1 = (groupsS20[1][0] + ' , ' + groupsS20[1][1] + ' , ' + groupsN20[1][0] + ' , ' + groupsC20[1][0] + ' , ' + groupsC20[1][1])
template.group2 = (groupsS20[1][2] + ' , ' + groupsS20[2][0] + ' , ' + groupsN20[1][1] + ' , ' + groupsC20[1][2] + ' , ' + groupsC20[2][0])
template.group3 = (groupsS20[2][1] + ' , ' + groupsS20[2][2] + ' , ' + groupsN20[1][2] + ' , ' + groupsN20[2][0] + ' , ' + groupsC20[2][1] + ' , ' + groupsC20[2][2])
template.group4 = (groupsS20[3][0] + ' , ' + groupsS20[3][1] + ' , ' + groupsN20[2][1] + ' , ' + groupsN20[2][2] + ' , ' + groupsC20[3][0] + ' , ' + groupsC20[3][1])
template.group5 = (groupsS20[3][2] + ' , ' + groupsS20[4][0] + ' , ' + groupsN20[3][0] + ' , ' + groupsN20[3][1] + ' , ' + groupsC20[3][2] + ' , ' + groupsC20[4][0])
template.group6 = (groupsS20[4][1] + ' , ' + groupsS20[4][2] + ' , ' + groupsN20[3][2] + ' , ' + groupsN20[4][0] + ' , ' + groupsC20[4][1] + ' , ' + groupsC20[4][2])
template.group7 = (groupsS20[5][0] + ' , ' + groupsS20[5][1] + ' , ' + groupsN20[4][1] + ' , ' + groupsN20[4][2] + ' , ' + groupsC20[5][0] + ' , ' + groupsC20[5][1])
template.group8 = (groupsS20[5][2] + ' , ' + groupsS20[6][0] + ' , ' + groupsN20[5][0] + ' , ' + groupsN20[5][1] + ' , ' + groupsC20[5][2] + ' , ' + groupsC20[6][0])
template.group9 = (groupsS20[6][1] + ' , ' + groupsN20[5][2] + ' , ' + groupsN20[6][0] + ' , ' + groupsC20[6][1] + ' , ' + groupsC20[6][2])
template.group10 = (groupsS20[6][2] + ' , ' + groupsN20[6][1] + ' , ' + groupsN20[6][2] + ' , ' + groupsC20[7][0] + ' , ' + groupsC20[7][1])
template.group11 = (groupsS20[7][0] + ' , ' + groupsN20[7][0] + ' , ' + groupsN20[7][1] + ' , ' + groupsC20[7][2] + ' , ' + groupsC20[8][0])
template.group12 = (groupsS20[7][1] + ' , ' + groupsN20[7][2] + ' , ' + groupsN20[8][0] + ' , ' + groupsC20[8][1] + ' , ' + groupsC20[8][2] + ' , ' + groupsC20[9][0])
template.group13 = (groupsS20[7][2] + ' , ' + groupsN20[8][1] + ' , ' + groupsN20[8][2] + ' , ' + groupsC20[9][1] + ' , ' + groupsC20[9][2] + ' , ' + groupsC20[10][0])
template.group14 = (groupsS20[8][0] + ' , ' + groupsN20[9][0] + ' , ' + groupsN20[9][1] + ' , ' + groupsC20[10][1] + ' , ' + groupsC20[10][2] + ' , ' + groupsC20[11][0])
// シートに結果を出力
const sheetResult = spreadsheet.getSheetByName("group")
const group1 = (template.group1).toString()
const group2 = (template.group2).toString()
const group3 = (template.group3).toString()
const group4 = (template.group4).toString()
const group5 = (template.group5).toString()
const group6 = (template.group6).toString()
const group7 = (template.group7).toString()
const group8 = (template.group8).toString()
const group9 = (template.group9).toString()
const group10 = (template.group10).toString()
const group11 = (template.group11).toString()
const roup12 = (template.group12).toString()
const group13 = (template.group13).toString()
const group14 = (template.group14).toString()
console.log(group1)
sheetResult.getRange("B1").setValue(group1)
sheetResult.getRange("B2").setValue(group2)
sheetResult.getRange("B3").setValue(group3)
sheetResult.getRange("B4").setValue(group4)
sheetResult.getRange("B5").setValue(group5)
sheetResult.getRange("B6").setValue(group6)
sheetResult.getRange("B7").setValue(group7)
sheetResult.getRange("B8").setValue(group8)
sheetResult.getRange("B9").setValue(group9)
sheetResult.getRange("B10").setValue(group10)
sheetResult.getRange("B11").setValue(group11)
sheetResult.getRange("B12").setValue(group12)
sheetResult.getRange("B13").setValue(group13)
sheetResult.getRange("B14").setValue(group14)
return template.evaluate()
}
const renderTeams = () => {
const viewFile = 'view'
// シートオブジェクトを作成
// 操作するシートのオブジェクトを取得
const s20sheet = spreadsheet.getSheetByName('s20')
const n20sheet = spreadsheet.getSheetByName('n20')
const c20sheet = spreadsheet.getSheetByName('c20')
const s20name = s20sheet.getRange(2, 1, s20sheet.getLastRow() - 1, 2).getValues()
const n20name = n20sheet.getRange(2, 1, n20sheet.getLastRow() - 1, 2).getValues()
const c20name = c20sheet.getRange(2, 1, c20sheet.getLastRow() - 1, 2).getValues()
// フォーマット
const formatedNamesS20 = shuffle(s20name)
const formatedNamesN20 = shuffle(n20name)
const formatedNamesC20 = shuffle(c20name)
// グループの作成
const groupsS20 = createGroups(formatedNamesS20)
const groupsN20 = createGroups(formatedNamesN20)
const groupsC20 = createGroups(formatedNamesC20)
// HTMLデータ作成
template.group1 = (groupsS20[1][0] + ' , ' + groupsS20[1][1] + ' , ' + groupsN20[1][0] + ' , ' + groupsC20[1][0] + ' , ' + groupsC20[1][1])
template.group2 = (groupsS20[1][2] + ' , ' + groupsS20[2][0] + ' , ' + groupsN20[1][1] + ' , ' + groupsC20[1][2] + ' , ' + groupsC20[2][0])
template.group3 = (groupsS20[2][1] + ' , ' + groupsS20[2][2] + ' , ' + groupsN20[1][2] + ' , ' + groupsN20[2][0] + ' , ' + groupsC20[2][1] + ' , ' + groupsC20[2][2])
template.group4 = (groupsS20[3][0] + ' , ' + groupsS20[3][1] + ' , ' + groupsN20[2][1] + ' , ' + groupsN20[2][2] + ' , ' + groupsC20[3][0] + ' , ' + groupsC20[3][1])
template.group5 = (groupsS20[3][2] + ' , ' + groupsS20[4][0] + ' , ' + groupsN20[3][0] + ' , ' + groupsN20[3][1] + ' , ' + groupsC20[3][2] + ' , ' + groupsC20[4][0])
template.group6 = (groupsS20[4][1] + ' , ' + groupsS20[4][2] + ' , ' + groupsN20[3][2] + ' , ' + groupsN20[4][0] + ' , ' + groupsC20[4][1] + ' , ' + groupsC20[4][2])
template.group7 = (groupsS20[5][0] + ' , ' + groupsS20[5][1] + ' , ' + groupsN20[4][1] + ' , ' + groupsN20[4][2] + ' , ' + groupsC20[5][0] + ' , ' + groupsC20[5][1])
template.group8 = (groupsS20[5][2] + ' , ' + groupsS20[6][0] + ' , ' + groupsN20[5][0] + ' , ' + groupsN20[5][1] + ' , ' + groupsC20[5][2] + ' , ' + groupsC20[6][0])
template.group9 = (groupsS20[6][1] + ' , ' + groupsN20[5][2] + ' , ' + groupsN20[6][0] + ' , ' + groupsC20[6][1] + ' , ' + groupsC20[6][2])
template.group10 = (groupsS20[6][2] + ' , ' + groupsN20[6][1] + ' , ' + groupsN20[6][2] + ' , ' + groupsC20[7][0] + ' , ' + groupsC20[7][1])
template.group11 = (groupsS20[7][0] + ' , ' + groupsN20[7][0] + ' , ' + groupsN20[7][1] + ' , ' + groupsC20[7][2] + ' , ' + groupsC20[8][0])
template.group12 = (groupsS20[7][1] + ' , ' + groupsN20[7][2] + ' , ' + groupsN20[8][0] + ' , ' + groupsC20[8][1] + ' , ' + groupsC20[8][2] + ' , ' + groupsC20[9][0])
template.group13 = (groupsS20[7][2] + ' , ' + groupsN20[8][1] + ' , ' + groupsN20[8][2] + ' , ' + groupsC20[9][1] + ' , ' + groupsC20[9][2] + ' , ' + groupsC20[10][0])
template.group14 = (groupsS20[8][0] + ' , ' + groupsN20[9][0] + ' , ' + groupsN20[9][1] + ' , ' + groupsC20[10][1] + ' , ' + groupsC20[10][2] + ' , ' + groupsC20[11][0])
const test = (template.group1)
console.log(test)
// シートに結果を出力
//const sheetResult = spreadsheet.getSheetByName('group')
//const nameResult = sheetResult.getRange(2, 1).setValues()
//const getNameResult = sheetResult.getRange(2, 1, sheetResult.getLastRow() - 1).getValues()
// スプレットシートから取得したデータを整形
//const formatedNamesData = getNameResult
// シートに結果を出力
const sheetResult = spreadsheet.getSheetByName("group")
const group1 = (template.group1).toString()
const group2 = (template.group2).toString()
const group3 = (template.group3).toString()
const group4 = (template.group4).toString()
const group5 = (template.group5).toString()
const group6 = (template.group6).toString()
const group7 = (template.group7).toString()
const group8 = (template.group8).toString()
const group9 = (template.group9).toString()
const group10 = (template.group10).toString()
const group11 = (template.group11).toString()
const group12 = (template.group12).toString()
const group13 = (template.group13).toString()
const group14 = (template.group14).toString()
console.log(group1)
sheetResult.getRange("B1").setValue(group1)
sheetResult.getRange("B2").setValue(group2)
sheetResult.getRange("B3").setValue(group3)
sheetResult.getRange("B4").setValue(group4)
sheetResult.getRange("B5").setValue(group5)
sheetResult.getRange("B6").setValue(group6)
sheetResult.getRange("B7").setValue(group7)
sheetResult.getRange("B8").setValue(group8)
sheetResult.getRange("B9").setValue(group9)
sheetResult.getRange("B10").setValue(group10)
sheetResult.getRange("B11").setValue(group11)
sheetResult.getRange("B12").setValue(group12)
sheetResult.getRange("B13").setValue(group13)
sheetResult.getRange("B14").setValue(group14)
return template.evaluate().getContent()
}
// シャッフル
const shuffle = (array) => {
const result = []
for(i = array.length; i > 0; i--){
const index = Math.floor(Math.random() * i)
const val = array.splice(index, 1)[0]
result.push(val)
}
return result
}
// グループ作成
const createGroups = (array) => {
const arrayLen = array.length
// 最大グループ数
const groupNum = 14
const result = []
// メンバー人数
const memberNum = 3
// const memberNum = Math.floor(arrayLen / groupNum)
// 6人グループを1つ作成
result.push(array.slice(0, memberNum))
// 5人グループを残りのグループ数 作成
for (i = 0; i <= groupNum; i++) {
result.push(array.slice(i*memberNum, i*memberNum+memberNum))
}
return result
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment