Embedded Files
GASでデプロイしたWebアプリ内でGASのWebアプリのリンクを踏むと、Googleさんに怒られます。
「クリック」と書いてありますが、クリックせずに、別タブで開いてください。
Produce_1.mp4２分の紹介動画（声あり）
３目並べで一緒に遊んでくれるAIです。ですが本当にAIと呼んでよいのかは少し疑問が残ります。
実はこの子には名前があります。「まるばつ様１世」です。もとは「まるばつ君」でしたが、400万回にも及ぶ学習を重ねたことで即位しました。
学習方法の説明
学習方法の説明
３目並べのあらゆる盤の状態に対しても最も勝率の高い手を事前の計算で求めておきます。
具体的には
AIを同士を戦わせる。二人は同じ脳を共有してます。
空いているマスにランダムに手を打ちあう。
勝敗が決するまで続ける。
最後に勝った場合これまでに取った手に対して加点をします。負けた場合は減点。引き分けも少し加点をします。
これを24時間繰り返します。
３年の夏休みの初めから始めて受験が終わるまでなので半年弱の間学習を回してました。
なぜか学校のGoogleWorkspaceアカウントを使うと、トリガーの回数や実行時間が無制限だったので、２４時間ずっと回してました。
Spreadsheetを使って、学習データを記録しています。
A列に盤の状況を記録します。A列に示す状況のときに９マスそれぞれに手を打った場合の勝率をB～J列に記録します。
盤の左上を１番目、右下を９番目のマスとして、１～９番目のマスをそれぞれB～J列目と対応させます。
A列の 000012201 のような数字は、１を○、２を✕、０を空白として、上述したようなマスの順番通りに数字の羅列として盤の状態を記録します。
例えば 000012201 は
○✕
✕ ○
を表します。
ソースコード
ソースコード
基本の操作を記述したコード.gs
基本の操作を記述したコード.gs
swichMeAndYou
○と✕を入れ替えます。
push
指定したマスに、指定したマークを置きます。（「手」を打つ）
convertToVisibleBoard
主にデバッグのために使います。000012201のような盤の状況を示す数字の羅列を、
○✕
✕ ○
のように見やすくします。
insertOfRecordOfSituation
Spreadsheetの１行をRecordと呼んでいます。これまでに知らなかった盤の状態を発見したとき、Recordを挿入します。
chooseCellRandomly
与えられたセルの中から一つ、ランダムに選びます。
judge_withoutInteligence
与えられた状況に対して、学習データを用いずにランダムに次の一手を選びます。学習中に使用します。
judge
与えられた状況に対して、学習データを用いて最適解を返します。
learn
学習のために、１試合します。
swichMeAndYou
与えられた状況について、勝敗を確認します。
id_ss= "*********************"
const boardSize = 9
const me = 1
const you = 2
const n = 0
let muchMemory = {
"vslog":"",
"vslog_you":"",
"virtualBoard":"000000000"
}
const ss = SpreadsheetApp.openById(id_ss)
let knowledgeSheet = ss.getSheetByName("knowledge")
const properties = PropertiesService.getScriptProperties()
const key_virtualBoard = "virtualBoard"
/**
* @parm {String}
* @return {String}
*/
function swichMeAndYou(string){
console.log("swichMeAndYou(before)"+ string)
string = string.replace(/1/g,"y")
string = string.replace(/2/g,"m")
string = string.replace(/y/g, you)
string = string.replace(/m/g, me)
console.log("swichMeAndYou(after)"+ string)
return string
}
/** return value is rewrited board
* @param {Nunber}
* @parma {Number}
* @return {String}
*/
function push(judgement, who, board="n"){
let key_vslog="vslog"
if(board=="n"){
board = recognize()
}
let situation=board
if(who==you){
key_vslog="vslog_you"
situation=swichMeAndYou(situation) //
}
const responce = board.slice(0,judgement-1) + who + board.slice(judgement)
muchMemory[key_virtualBoard] = responce
console.log("push board:"+board+" judgement"+ judgement)
const vslog = muchMemory[key_vslog]
const log = vslog+ "/"+situation +","+ judgement
muchMemory[key_vslog]=log
return board
}
/**
* @return {String}
*/
function recognize(){
let board = muchMemory[key_virtualBoard]
console.log("recognize\n"+convertToVisibleBoard(board))
return board
}
/** meをOに、youをXに変換します。
* @parm {String}
* @return {String}
*/
function convertToVisibleBoard(board){
let visibleBoard = ""
visibleBoard = board.replace(/1/g,"O")
visibleBoard = visibleBoard.replace(/2/g,"X")
visibleBoard = visibleBoard.replace(/0/g," ")
const numOfLine = Math.sqrt(boardSize)
visibleBoard = visibleBoard.slice(0,numOfLine)+"\n"+visibleBoard.slice(numOfLine,numOfLine*2)+"\n"+visibleBoard.slice(numOfLine*2,numOfLine*3)
console.log("convertToVisibleBoard board:"+board+"\n"+visibleBoard)
return visibleBoard
}
/**
* @parm {String}
* @parm {Number[]}
* @return {Number[]}
*/
function getAvailableCellsInGivenCells(situation,cells){
let availableCells = []
cells.forEach(cell=>{
if(situation.slice(cell-1,cell) == String(n)){
availableCells.push(cell)
}
})
const result = availableCells
console.log("getAvailableCellsInGivenCells "+ situation+ "は、"+cells+"の中で"+availableCells + "がabaileble")
return result
}
/**return candidatePoints
* @parm {String}
* @return {[String, [Number]]}
*/
function insertRecordOfSituation(situation){
let candidatePoints = []
for(let i = 1; i<=boardSize; i++){
candidatePoints.push(0)
}
const record = [ String(situation) , ...candidatePoints]
const record_range = knowledgeSheet.getRange(knowledgeSheet.getLastRow()+1,1,1,record.length)
record_range.setValues([record])
SpreadsheetApp.flush()
console.log("insertRecordOfSituation name:" + situation + " value:"+ record)
return candidatePoints
}
/**
* @parm {String}
* @parm {Number[]} if null,cells=[1,2,...,boardSize]
* @return {Number}
*/
function chooseCellRandamly(board,cells){
let availableCells =[]
if(cells==null){
cells = []
for(let i=1;i<=boardSize;i++){cells.push(i) }
}
availableCells = getAvailableCellsInGivenCells(board,cells)
console.log("chooseCellRandamly board:" + board )
console.log("chooseCellRandamly cells(is argument):"+ cells)
cells=availableCells
console.log("chooseCellRandamly availableCells:"+ availableCells)
const index = Math.floor( cells.length * Math.random() )
console.log("chooseCellRandamly result:"+ cells[index])
return cells[index]
}
/**
* @parm {String}
* @return {Number}
*/
function judge_withoutInteligence(situation){
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,knowledgeSheet.getMaxRows(),1)
const situations = knowledgeSheet_situations_range.getValues().flat()
if(situations.indexOf(situation)==-1){
//knowledgeSheetにsituationのrecordがなかった場合
candidatePoints = insertRecordOfSituation(situation)
SpreadsheetApp.flush()
}
const judgement = chooseCellRandamly(situation)
console.log("judge_withoutInteligence judgement:" + judgement)
return judgement
}
/**
* @parm {String}
* @return {Number}
*/
function judge(situation){
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,knowledgeSheet.getMaxRows(),boardSize+1)
const dataes = knowledgeSheet_situations_range.getValues()
let situations =[]
dataes.forEach(data=>{
situations.push(data[0])
})
const index = situations.indexOf(situation)
let candidatePoints = []
if(index==-1){
//knowledgeSheetにsituationのrecordがなかった場合
if(true){
for(let i = 1; i<=boardSize; i++){
candidatePoints.push(0)
}
}else{
candidatePoints = insertRecordOfSituation(situation)
SpreadsheetApp.flush()
}
}else{
candidatePoints = dataes[index]
candidatePoints.shift()
}
let cells = []
for(let i=1;i<=boardSize;i++){cells.push(i) }
const availableCells = getAvailableCellsInGivenCells(situation,cells)
if(availableCells.length == 1){
console.log("うぇーい、お姉さんテキーラ追加～")
}
let availableCandidatePoints = []
for(let i = 0; i<=candidatePoints.length-1;i++){
if(availableCells.includes(i+1)){
availableCandidatePoints.push(candidatePoints[i])
}
}
let maxPoint = Math.max(...availableCandidatePoints)
let candidates = []
if( maxPoint == null){
candidates = availableCells
}else{
for ( let judgement = 1; judgement<=boardSize; judgement++){
if(candidatePoints[judgement-1]==maxPoint){
candidates.push(judgement)
}
}
}
console.log("judge"+"\ncandidatePoints:"+candidatePoints+"\ncandidates"+candidates)
const judgement = chooseCellRandamly(situation,candidates)
console.log("judge result:" + judgement)
return judgement
}
/**
* @parm {Number}
*/
function learn(winner,who,withOrWithouInteli="with"){
const maxRow = knowledgeSheet.getMaxRows()
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,maxRow,1)
let key_vslog ="vslog"
if(who==you){
key_vslog="vslog_you"
}
let pointAddition = 0
const vslog = muchMemory[key_vslog]
const turns = vslog.split("/")
turns.forEach(values =>{
const situationAndJudgement = values.split(",")
let situation = situationAndJudgement[0]
const judgement = Number(situationAndJudgement[1])
let situations = knowledgeSheet_situations_range.getValues().flat()
let row = situations.indexOf(situation) + 1
if(row==0 || judgement==0){
if(row==0){
insertRecordOfSituation(situation)
SpreadsheetApp.flush()
}
}else{
console.log("learn row:" + row)
const judgementPoint_sheetRange = knowledgeSheet.getRange(row,judgement+1)
let judgementPoint = judgementPoint_sheetRange.getValue()
if(winner==who){
pointAddition= 2 //勝った
}else{
if(winner==n){
pointAddition=1 //あいこ
}else{
pointAddition=-1 //負け
}
}
judgementPoint= judgementPoint + pointAddition
judgementPoint_sheetRange.setValue(judgementPoint)
console.log("learn situation:"+situation+" judgement:"+judgement +" pointAddition:"+pointAddition+ " point:"+judgementPoint)
}
})
let record= [,,,]
record[0]= Utilities.formatDate( new Date, 'Asia/Tokyo', 'yyyy/MM/dd HH:m:ss')
const lastLog = vslog.slice(-(boardSize+2))
if(pointAddition==2){ record[1]="Win" }
if(pointAddition==-1){record[1]="Lose"}
if(pointAddition==1){record[1]="draw"}
record[2]=lastLog
record[3]=vslog
let kindOfRecord=""
if(withOrWithouInteli=="without"){
kindOfRecord = "record_withoutInteli"
}else{
kindOfRecord= "record_withInteli"
}
let recordSheet = ss.getSheetByName(kindOfRecord)
if(recordSheet==null){
recordSheet = ss.insertSheet(kindOfRecord,0)
console.log("insert recordSheet")
}
const record_range = recordSheet.getRange(recordSheet.getLastRow()+1, 1,1,4)
record_range.setValues([record])
console.log("learn lastlog:"+record[1]+" "+record[2])
}
/**
* @param {String}
* @return {Number} when the game doesn't finish, return null
*/
function whoWon(board){
const numOfLine = Math.sqrt(boardSize)
let verticalLine = []
let horizontalLine = []
let upToRightLine = []
let upToLeftLine = []
const number = (r,c)=>{
return (r-1)*numOfLine + c
}
//horizontal and vertical
for(let line=1; line<=numOfLine; line++){
for(let order=1; order<=numOfLine; order++){
//horizontal
horizontalLine.push( board.slice(number(line,order)-1, number(line,order)) )
//vertical
verticalLine.push( board.slice(number(order,line)-1, number(order,line)) )
}
const lines ={
"verticalLine_meOnly" : [verticalLine.every(value=> value==me),me],
"verticalLine_youOnly" : [verticalLine.every(value=> value==you),you],
"horizontalLine_meOnly" : [horizontalLine.every(value=> value==me),me],
"horizontalLine_youOnly" : [horizontalLine.every(value=> value==you),you]
}
console.log("horizontal"+horizontalLine+"\n"+"vertical"+verticalLine)
for(let key_vslog in lines){
const isBingo = lines[key_vslog][0]
console.log("whoWon "+"line:"+line + " " +key_vslog+":"+isBingo )
if(isBingo){
return lines[key_vslog][1]
}
}
//initialize
verticalLine = []
horizontalLine = []
upToRightLine = []
upToLeftLine = []
}
//upToRight and upToLeft
for(let r=1; r<=numOfLine; r++){
for(let c=1; c<=numOfLine; c++){
//upToleft
if(r==c){
upToLeftLine.push( board.slice(number(r,c)-1, number(r,c)) )
}
//upToRight
if(r+c==numOfLine+1){
upToRightLine.push( board.slice(number(r,c)-1,number(r,c)) )
}
}
}
const lines={
"upToLeftLine_meOly" : [upToLeftLine.every(value=> value==me),me],
"upToLeftLine_youOnlY" : [upToLeftLine.every(value=> value==you),you],
"upToRightLine_meOnly" : [upToRightLine.every(value=> value==me),me],
"upToRightLine_youOnly" : [upToRightLine.every(value=> value==you),you]
}
console.log("upToLeft"+upToLeftLine+"\n"+"upToRight"+upToRightLine)
for(let key_vslog in lines){
const isBingo = lines[key_vslog][0]
console.log("whoWon " +key_vslog+ ":"+isBingo )
if(isBingo){
return lines[key_vslog][1]
}
}
//Draw
if(board.indexOf(String(n))==-1){
return n
}else{
return null
}
}
id_ss= "1LEDApYIPA_PJ2yEwwYcXEksJpf_b_bscP29eievIcuQ"
const boardSize = 9
const me = 1
const you = 2
const n = 0
let muchMemory = {
"vslog":"",
"vslog_you":"",
"virtualBoard":"000000000"
}
const ss = SpreadsheetApp.openById(id_ss)
let knowledgeSheet = ss.getSheetByName("knowledge")
const properties = PropertiesService.getScriptProperties()
const key_virtualBoard = "virtualBoard"
/**
* @parm {String}
* @return {String}
*/
function swichMeAndYou(string){
console.log("swichMeAndYou(before)"+ string)
string = string.replace(/1/g,"y")
string = string.replace(/2/g,"m")
string = string.replace(/y/g, you)
string = string.replace(/m/g, me)
console.log("swichMeAndYou(after)"+ string)
return string
}
/** return value is rewrited board
* @param {Nunber}
* @parma {Number}
* @return {String}
*/
function push(judgement, who, board="n"){
let key_vslog="vslog"
if(board=="n"){
board = recognize()
}
let situation=board
if(who==you){
key_vslog="vslog_you"
situation=swichMeAndYou(situation) //
}
const responce = board.slice(0,judgement-1) + who + board.slice(judgement)
muchMemory[key_virtualBoard] = responce
console.log("push board:"+board+" judgement"+ judgement)
const vslog = muchMemory[key_vslog]
const log = vslog+ "/"+situation +","+ judgement
muchMemory[key_vslog]=log
return board
}
/**
* @return {String}
*/
function recognize(){
let board = muchMemory[key_virtualBoard]
console.log("recognize\n"+convertToVisibleBoard(board))
return board
}
/** meをOに、youをXに変換します。
* @parm {String}
* @return {String}
*/
function convertToVisibleBoard(board){
let visibleBoard = ""
visibleBoard = board.replace(/1/g,"O")
visibleBoard = visibleBoard.replace(/2/g,"X")
visibleBoard = visibleBoard.replace(/0/g," ")
const numOfLine = Math.sqrt(boardSize)
visibleBoard = visibleBoard.slice(0,numOfLine)+"\n"+visibleBoard.slice(numOfLine,numOfLine*2)+"\n"+visibleBoard.slice(numOfLine*2,numOfLine*3)
console.log("convertToVisibleBoard board:"+board+"\n"+visibleBoard)
return visibleBoard
}
/**
* @parm {String}
* @parm {Number[]}
* @return {Number[]}
*/
function getAvailableCellsInGivenCells(situation,cells){
let availableCells = []
cells.forEach(cell=>{
if(situation.slice(cell-1,cell) == String(n)){
availableCells.push(cell)
}
})
const result = availableCells
console.log("getAvailableCellsInGivenCells "+ situation+ "は、"+cells+"の中で"+availableCells + "がabaileble")
return result
}
/**return candidatePoints
* @parm {String}
* @return {[String, [Number]]}
*/
function insertRecordOfSituation(situation){
let candidatePoints = []
for(let i = 1; i<=boardSize; i++){
candidatePoints.push(0)
}
const record = [ String(situation) , ...candidatePoints]
const record_range = knowledgeSheet.getRange(knowledgeSheet.getLastRow()+1,1,1,record.length)
record_range.setValues([record])
SpreadsheetApp.flush()
console.log("insertRecordOfSituation name:" + situation + " value:"+ record)
return candidatePoints
}
/**
* @parm {String}
* @parm {Number[]} if null,cells=[1,2,...,boardSize]
* @return {Number}
*/
function chooseCellRandamly(board,cells){
let availableCells =[]
if(cells==null){
cells = []
for(let i=1;i<=boardSize;i++){cells.push(i) }
}
availableCells = getAvailableCellsInGivenCells(board,cells)
console.log("chooseCellRandamly board:" + board )
console.log("chooseCellRandamly cells(is argument):"+ cells)
cells=availableCells
console.log("chooseCellRandamly availableCells:"+ availableCells)
const index = Math.floor( cells.length * Math.random() )
console.log("chooseCellRandamly result:"+ cells[index])
return cells[index]
}
/**
* @parm {String}
* @return {Number}
*/
function judge_withoutInteligence(situation){
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,knowledgeSheet.getMaxRows(),1)
const situations = knowledgeSheet_situations_range.getValues().flat()
if(situations.indexOf(situation)==-1){
//knowledgeSheetにsituationのrecordがなかった場合
candidatePoints = insertRecordOfSituation(situation)
SpreadsheetApp.flush()
}
const judgement = chooseCellRandamly(situation)
console.log("judge_withoutInteligence judgement:" + judgement)
return judgement
}
/**
* @parm {String}
* @return {Number}
*/
function judge(situation){
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,knowledgeSheet.getMaxRows(),boardSize+1)
const dataes = knowledgeSheet_situations_range.getValues()
let situations =[]
dataes.forEach(data=>{
situations.push(data[0])
})
const index = situations.indexOf(situation)
let candidatePoints = []
if(index==-1){
//knowledgeSheetにsituationのrecordがなかった場合
if(true){
for(let i = 1; i<=boardSize; i++){
candidatePoints.push(0)
}
}else{
candidatePoints = insertRecordOfSituation(situation)
SpreadsheetApp.flush()
}
}else{
candidatePoints = dataes[index]
candidatePoints.shift()
}
let cells = []
for(let i=1;i<=boardSize;i++){cells.push(i) }
const availableCells = getAvailableCellsInGivenCells(situation,cells)
if(availableCells.length == 1){
console.log("うぇーい、お姉さんテキーラ追加～")
}
let availableCandidatePoints = []
for(let i = 0; i<=candidatePoints.length-1;i++){
if(availableCells.includes(i+1)){
availableCandidatePoints.push(candidatePoints[i])
}
}
let maxPoint = Math.max(...availableCandidatePoints)
let candidates = []
if( maxPoint == null){
candidates = availableCells
}else{
for ( let judgement = 1; judgement<=boardSize; judgement++){
if(candidatePoints[judgement-1]==maxPoint){
candidates.push(judgement)
}
}
}
console.log("judge"+"\ncandidatePoints:"+candidatePoints+"\ncandidates"+candidates)
const judgement = chooseCellRandamly(situation,candidates)
console.log("judge result:" + judgement)
return judgement
}
/**
* @parm {Number}
*/
function learn(winner,who,withOrWithouInteli="with"){
const maxRow = knowledgeSheet.getMaxRows()
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,maxRow,1)
let key_vslog ="vslog"
if(who==you){
key_vslog="vslog_you"
}
let pointAddition = 0
const vslog = muchMemory[key_vslog]
const turns = vslog.split("/")
turns.forEach(values =>{
const situationAndJudgement = values.split(",")
let situation = situationAndJudgement[0]
const judgement = Number(situationAndJudgement[1])
let situations = knowledgeSheet_situations_range.getValues().flat()
let row = situations.indexOf(situation) + 1
if(row==0 || judgement==0){
if(row==0){
insertRecordOfSituation(situation)
SpreadsheetApp.flush()
}
}else{
console.log("learn row:" + row)
const judgementPoint_sheetRange = knowledgeSheet.getRange(row,judgement+1)
let judgementPoint = judgementPoint_sheetRange.getValue()
if(winner==who){
pointAddition= 2 //勝った
}else{
if(winner==n){
pointAddition=1 //あいこ
}else{
pointAddition=-1 //負け
}
}
judgementPoint= judgementPoint + pointAddition
judgementPoint_sheetRange.setValue(judgementPoint)
console.log("learn situation:"+situation+" judgement:"+judgement +" pointAddition:"+pointAddition+ " point:"+judgementPoint)
}
})
let record= [,,,]
record[0]= Utilities.formatDate( new Date, 'Asia/Tokyo', 'yyyy/MM/dd HH:m:ss')
const lastLog = vslog.slice(-(boardSize+2))
if(pointAddition==2){ record[1]="Win" }
if(pointAddition==-1){record[1]="Lose"}
if(pointAddition==1){record[1]="draw"}
record[2]=lastLog
record[3]=vslog
let kindOfRecord=""
if(withOrWithouInteli=="without"){
kindOfRecord = "record_withoutInteli"
}else{
kindOfRecord= "record_withInteli"
}
let recordSheet = ss.getSheetByName(kindOfRecord)
if(recordSheet==null){
recordSheet = ss.insertSheet(kindOfRecord,0)
console.log("insert recordSheet")
}
const record_range = recordSheet.getRange(recordSheet.getLastRow()+1, 1,1,4)
record_range.setValues([record])
console.log("learn lastlog:"+record[1]+" "+record[2])
}
/**
* @param {String}
* @return {Number} when the game doesn't finish, return null
*/
function whoWon(board){
const numOfLine = Math.sqrt(boardSize)
let verticalLine = []
let horizontalLine = []
let upToRightLine = []
let upToLeftLine = []
const number = (r,c)=>{
return (r-1)*numOfLine + c
}
//horizontal and vertical
for(let line=1; line<=numOfLine; line++){
for(let order=1; order<=numOfLine; order++){
//horizontal
horizontalLine.push( board.slice(number(line,order)-1, number(line,order)) )
//vertical
verticalLine.push( board.slice(number(order,line)-1, number(order,line)) )
}
const lines ={
"verticalLine_meOnly" : [verticalLine.every(value=> value==me),me],
"verticalLine_youOnly" : [verticalLine.every(value=> value==you),you],
"horizontalLine_meOnly" : [horizontalLine.every(value=> value==me),me],
"horizontalLine_youOnly" : [horizontalLine.every(value=> value==you),you]
}
console.log("horizontal"+horizontalLine+"\n"+"vertical"+verticalLine)
for(let key_vslog in lines){
const isBingo = lines[key_vslog][0]
console.log("whoWon "+"line:"+line + " " +key_vslog+":"+isBingo )
if(isBingo){
return lines[key_vslog][1]
}
}
//initialize
verticalLine = []
horizontalLine = []
upToRightLine = []
upToLeftLine = []
}
//upToRight and upToLeft
for(let r=1; r<=numOfLine; r++){
for(let c=1; c<=numOfLine; c++){
//upToleft
if(r==c){
upToLeftLine.push( board.slice(number(r,c)-1, number(r,c)) )
}
//upToRight
if(r+c==numOfLine+1){
upToRightLine.push( board.slice(number(r,c)-1,number(r,c)) )
}
}
}
const lines={
"upToLeftLine_meOly" : [upToLeftLine.every(value=> value==me),me],
"upToLeftLine_youOnlY" : [upToLeftLine.every(value=> value==you),you],
"upToRightLine_meOnly" : [upToRightLine.every(value=> value==me),me],
"upToRightLine_youOnly" : [upToRightLine.every(value=> value==you),you]
}
console.log("upToLeft"+upToLeftLine+"\n"+"upToRight"+upToRightLine)
for(let key_vslog in lines){
const isBingo = lines[key_vslog][0]
console.log("whoWon " +key_vslog+ ":"+isBingo )
if(isBingo){
return lines[key_vslog][1]
}
}
//Draw
if(board.indexOf(String(n))==-1){
return n
}else{
return null
}
}
学習を回すためのコード.gs
学習を回すためのコード.gs
function runLearning(){
const startTime = new Date()
if(knowledgeSheet == null){
knowledgeSheet= ss.insertSheet("knowledge",0)
SpreadsheetApp.flush()
knowledgeSheet.deleteColumns(boardSize+2,knowledgeSheet.getMaxColumns()-boardSize-1)
console.log("insert knowledgeSheet")
}
const knowledgeSheet_situations_range= knowledgeSheet.getRange(1,1,1,1)
const knowledgeSheet_situations_range_nuberFormat = knowledgeSheet_situations_range.getNumberFormat()
console.log("numberformat:"+knowledgeSheet_situations_range_nuberFormat)
if(knowledgeSheet_situations_range_nuberFormat=="0.###############"){
console.error('シート"knowledge"のA列の表示形式を"書式なしテキスト"にセットしてください。')
return
}
let initialBoard = ""
for(i=1;i<=boardSize;i++){
initialBoard= initialBoard+String(n)
}
while( true ){
properties.setProperty(key_virtualBoard,initialBoard)
muchMemory["vslog"] = initialBoard+","+String(n)
muchMemory["vslog_you"] = initialBoard+","+String(n)
let winner = null
for(let turn=1; winner==null; turn++){
let who = n
let judgement=0
let board = recognize()
if(turn % 2 ==0){
who = me
judgement = judge_withoutInteligence(board)
}else{
who= you
judgement = judge_withoutInteligence(board)
}
push(judgement,who)
winner = whoWon(recognize())
if(new Date()- startTime > 28*60*1000){
return
}
}
console.log("WINNER:" + winner)
learn(winner,me,"without")
learn(winner,you,"without")
}
}
Webアプリ用のコード.gs
Webアプリ用のコード.gs
let currentSituation = "000000000"
let culiculationTime = 0
const deployUrl = "https://script.google.com/macros/s/AKfycbyrtg5RrqXtnJKxMpQt64O3SnC1mJgoTXU4XDh8B1rqMiZOg5926jKBlebB6CgwX72CaA/exec"
function doGet(e) {
const startTime = new Date()
// クエリパラメータから行と列の情報を取得
const location = e.parameter.location;
currentSituation = e.parameter.situation;
console.log("situation:" + currentSituation + " location:" + location)
console.log(convertToVisibleBoard(currentSituation))
if (currentSituation == undefined) {
currentSituation = "000000000"
}
// 3×3の表の状態を表す文字列を取得
let boardSituation = ""
if (whoWon(currentSituation) == null) {
if (location != undefined) {
boardSituation = getNextBoardSituation(Number(location), "2");
} else {
boardSituation = getNextBoardSituation(0, "")
}
}
const html = createBoardHtml(boardSituation);
console.log(convertToVisibleBoard(boardSituation))
// HTMLを返す
return HtmlService.createHtmlOutput(html);
}
// 3×3の表の状態を取得する関数
function getNextBoardSituation(location, mark) {
const startTime = new Date()
let board = currentSituation
let nextBoard = "000000000"
if (location == 0) {
nextBoard = "000000000"
currentSituation = nextBoard
} else {
board = board.slice(0, location - 1) + mark + board.slice(location, boardSize)
if (whoWon(board) == null) {
const judge_locatin = judge(board)
nextBoard = board.slice(0,judge_locatin-1)+ "1" + board.slice(judge_locatin)
}else{
nextBoard = board
}
}
culiculationTime = new Date() - startTime
return nextBoard;
}
// 表を作成する関数
function createBoardHtml(boardSituation) {
const resultOfGame = whoWon(boardSituation) //Int
var table_html = "<table id='myTable'>";
for (var i = 0; i < 3; i++) {
table_html += "<tr>";
for (var j = 0; j < 3; j++) {
var index = i * 3 + j;
var cellValue = boardSituation.charAt(index);
var cellText = "";
if (cellValue === "0") {
if (resultOfGame == null) {
cellText = "<a href=" + "'" + deployUrl + "?location=" + String(index + 1) + "&situation=" + boardSituation + "'" + ">ｸﾘｯｸ</a>"
} else {
cellText = ""
}
} else if (cellValue === "1") {
cellText = "◯";
} else if (cellValue === "2") {
cellText = "✕";
}
table_html += "<td>" + cellText + "</td>";
}
table_html += "</tr>";
}
table_html += "</table>";
const html1 = '<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>全能なる まるばつ様 1世</title><style> table { border-collapse: collapse; width: 200px; height: 200px; } td { border: 1px solid black; width: 66px; height: 66px; text-align: center; font-size: 24px; cursor: pointer; }</style></head><body><p>あなたは×です</p><p>「クリック」の部分はCtrlを押しながらクリックしてください。(別タブで開く必要があります。)</p><a>判断時間(ms): ' + String(culiculationTime)
const html2 = '<p><a href=' + "'" + "https://script.google.com/macros/s/AKfycbyrtg5RrqXtnJKxMpQt64O3SnC1mJgoTXU4XDh8B1rqMiZOg5926jKBlebB6CgwX72CaA/exec?situation=000000000" + "'" + '>次のゲーム</a></p></body></html>'
let body = "<p>"
switch (resultOfGame) {
case 2: body += "残念・・・。あなたの勝ちです。"; break;
case 1: body += "全能なる『まるばつ様１世』の勝利です。 当たり前ですね❕"; break;
case 0: body += "引き分けとか草"; break;
default: body += "";
}
body += "</p>"
return html1 + body + table_html + html2;
}
データの集計に使ったプログラム
データの集計に使ったプログラム
まるばつ君を育てるために、GASのプロジェクトを複製していくつかを同時に回していました。
ですので、学習完了後、スプレッドシートがいくつもある状態でした。
それを一つのスプレッドシートにまとめるためのプログラムを作りました。
GASの機能であるプロジェクトプロパティを用いて、途中からプログラムを再開できるようにしています。
ソースコード
ソースコード
const boardSize = 9
const fileIds = [
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************",
"*************************"
]
const integratedDataSheet = SpreadsheetApp.openById("*******************************").getSheetByName("Knowledge")
const property = PropertiesService.getScriptProperties()
const lastIndex_fileIds = Number(property.getProperty("index_fileIds"))
function myFunction(){
const startTime = Date.now()
for(let index_fileIds = lastIndex_fileIds ; index_fileIds<=fileIds.length -1; index_fileIds++){
property.setProperty("index_fileIds",String(index_fileIds))
console.log("index Of fileIds(Now it's begining to process) : " + index_fileIds)
const fileId = fileIds[index_fileIds]
const sheet = SpreadsheetApp.openById(fileId).getSheetByName("Knowledge")
if(sheet.getLastColumn>boardSize+1){
sheet.deleteColumns(boardSize+2,sheet.getLastColumn()-boardSize-2)
}
const records = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues()
const lastIndex_records = Number(property.getProperty("index_records"))
for(let index_records = lastIndex_records; index_records<=records.length-1 ; index_records++){
const record = records[index_records]
const boardSituation = record[0]
const canonicalizedBoardSituation = getCanonicalizedBoardSituation(boardSituation)
record[0]= canonicalizedBoardSituation
//console.log("boardSituatin : " + boardSituation + "\ncanonicalizedBoardSituation : " + canonicalizedBoardSituation)
const boardSituations = integratedDataSheet.getRange(1,1,integratedDataSheet.getLastRow(),1).getValues().flat()
let rowNumOfBoardSituation = boardSituations.indexOf(canonicalizedBoardSituation) + 1
if(rowNumOfBoardSituation==0){
rowNumOfBoardSituation = integratedDataSheet.getLastRow()+1
}
property.setProperty("index_records", String(index_records+1))
integratedDataSheet.getRange(rowNumOfBoardSituation,1,1,record.length).setValues([record])
console.log("index_record(complete) : " + index_records)
const endTime = Date.now()
if(endTime-startTime > 25*60*1000){
return
}
}
property.setProperty("index_records","0")
}
}
function getCanonicalizedBoardSituation(boardSituation){
const lotatedBoards_num = getLotatedBoards_num(boardSituation)
let canonicalizeBoardSituation = String(Math.min(...lotatedBoards_num))
const length = canonicalizeBoardSituation.length
canonicalizeBoardSituation = "000000000".slice(0,9-length) + canonicalizeBoardSituation
return canonicalizeBoardSituation
}
function lotateBoard(boardSituation){
const loatetedBoardSituation =
boardSituation[6]+boardSituation[3]+boardSituation[0]+
boardSituation[7]+boardSituation[4]+boardSituation[1]+
boardSituation[8]+boardSituation[5]+boardSituation[2]
return loatetedBoardSituation
}
function getLotatedBoards_num(boardSituation){
let lotatedBoards_num = [Number(boardSituation)]
let currentBoard = boardSituation
for(let i =1 ; i<=3; i++){
currentBoard = lotateBoard(currentBoard)
lotatedBoards_num.push(Number(currentBoard))
}
return lotatedBoards_num
}
壊れたデータを探すプログラム
壊れたデータを探すプログラム
プログラムの実行はプロジェクトごとに管理され、実行時間が30分を超える前に殺されます。
そして、30ごとに実行が予約されているので、ほぼ24時間ずっと実行されます。
しかし、なぜか、ときどき30分経っても殺されないタスクが出てきます。
そうなると、同じプロジェクトで複数のプログラムが実行されてしまいます。
今は変更しましたが以前は実行中の試合の盤の管理にプロジェクトプロパティを使っていました。
ですので、同一プロジェクトで複数のプログラムが実行されてしまうとデータが壊れてしまうのです。
そのようなデータがいったいどれほどあるのかを調べるためにプログラムを作りました。
ソースコード
ソースコード
function myFunction2(){
fileIds.forEach(fileId => {
const spreadSheat = SpreadsheetApp.openById(fileId)
const sheet_knowledge = spreadSheat.getSheetByName("Knowledge")
const boardSituations = sheet_knowledge.getRange(1,1,sheet_knowledge.getLastRow(),1).getValues().flat()
boardSituations.forEach(boardSituation =>{
if(boardSituation.length != 9){
console.log( "なんか変！！")
}
})
})
}
function myFunction(){
let sumNumOfRecords = 0
let sumNumOfWrongRecords = 0
fileIds.forEach(fileId => {
const spreadsheet = SpreadsheetApp.openById(fileId)
const sheet_record = spreadsheet.getSheetByName("record_withoutInteli")
const sheet_knowledge = spreadsheet.getSheetByName("knowledge")
let wrongRecords = []
const result = checkRecords(sheet_record,sheet_knowledge,wrongRecords)
console.log("FileName : " + spreadsheet.getName() + "\n wrong rate : " + (result.numOfWrongRecords / result.numOfRecords) )
sumNumOfRecords = sumNumOfRecords + result.numOfRecords
sumNumOfWrongRecords = sumNumOfWrongRecords + result.numOfWrongRecords
})
console.log("entire wrong rate : " + ( sumNumOfWrongRecords / sumNumOfRecords ))
}
function pushToWrongRecords(boardSituation, locationOfAddedMark, changes,rowNomInChecking,wrongRecords){
//console.log(changes)
wrongRecords.push({
boardSituation: boardSituation,
locationOfAddedMark:locationOfAddedMark ,
rowNom:rowNomInChecking
})
return wrongRecords
}
function checkRecords(sheet_record,sheet_knowledge,wrongRecords){
//record_withoutInteliシートのD列を最終行まで取得して、一次元化。
const records = sheet_record.getRange(1,4,sheet_record.getLastRow(),1).getValues().flat()
for(let n = 0;n<=records.length-1;n++){
const record = records[n]
const rowNomInChecking = n+1
//valueには一つのセルの値が入ってるので、それを１ターンごとに配列に格納。;
const turns = record.split("/")
for(let i = 2 ; i<= turns.length-1; i++){
const turn_pre = turns[i-1].split(",")
const currentBoardSituation_pre = turn_pre[0]
const locationOfAddedOwnMark_pre = turn_pre[1]
const turn_current = turns[i].split(",")
const currentBoardSituation_current = turn_current[0]
const changes = compareStrings(currentBoardSituation_pre,currentBoardSituation_current)
if(changes.length==2){ //まず変化は2か所である必要がある;
changes.forEach(change => {
if(change.location == locationOfAddedOwnMark_pre){
//マークを追加した位置には必ず１がマークされるされている。;
if(change.to != "1"){
wrongRecords = pushToWrongRecords(currentBoardSituation_pre,locationOfAddedOwnMark_pre,changes,rowNomInChecking,wrongRecords)
}
}else{
/*
文字列を比較して変化は二か所で起こるはず。一方は自分で追加したものだが、もう一方は相手が追加したものなのでマークは２になっているはず。*/
if(change.to != "2"){
wrongRecords = pushToWrongRecords(currentBoardSituation_pre,locationOfAddedOwnMark_pre,changes,rowNomInChecking,wrongRecords)
}
}
})
}else{ //変化が２か所でないのはおかしい。
wrongRecords = pushToWrongRecords(currentBoardSituation_pre,locationOfAddedOwnMark_pre,changes,rowNomInChecking,wrongRecords)
}
}
}
return {
numOfRecords : records.length,
numOfWrongRecords : wrongRecords.length
}
}
function checkAndCorrect(sheet_record,sheet_knowledge,wrongRecords){
checkRecords(sheet_record,sheet_knowledge,wrongRecords)
correctLearnedKnowledge(sheet_record,sheet_knowledge,wrongRecords)
}
function correctLearnedKnowledge(sheet_record,sheet_knowledge,wrongRecords){
wrongRecords.forEach(wrongRecord =>{
const resultOfGame = sheet_record.getRange(wrongRecord.rowNom,2).getValue()
const boardSituations = sheet_knowledge.getRange(1,1,sheet_knowledge.getLastRow(),1).getValues().flat()
let reducingPoint = 0
const rowNom = boardSituations.indexOf(wrongRecord.boardSituation) + 1
if(rowNom==0){
console.log("knowlegeからsituationに該当する行を見つけれませんでした。")
}
if(resultOfGame=="Win"){
reducingPoint= 2 //勝った
}else{
if(resultOfGame=="draw"){
reducingPoint=1 //あいこ
}else{
reducingPoint=-1 //負け
}
}
console.log("resultOfGame,wrongRecord.boardSituation,reducingPoint,rowNom\n"+resultOfGame +","+ wrongRecord.boardSituation +","+reducingPoint +","+ rowNom)
const rangeOfWrongKnowledgeCell = sheet_knowledge.getRange(rowNom,wrongRecord.locationOfAddedMark+1)
const previousWrongKnowledgeNum = Number(rangeOfWrongKnowledgeCell.getValue())
console.log("previousWrongKnowledgeNum"+ previousWrongKnowledgeNum)
rangeOfWrongKnowledgeCell.setValue(previousWrongKnowledgeNum - reducingPoint)
})
}
function compareStrings(str1, str2) {
let changes = [];
const maxLength = Math.max(str1.length, str2.length);
for (let i = 0; i < maxLength; i++) {
if (str1[i] !== str2[i]) {
changes.push({
location: i+1,
from: str1[i] || null,
to: str2[i] || null
});
}
}
return changes;
}
Page updated
Google Sites
Report abuse