ペイントロジックのテンプレートを現在のスプレッドシートに作ってくれる、Google Apps Script

InitPaintLogic.gs

/** 色の指定 */
function CellColor()
{
  this.initial = "#ffeecc" ;
  this.painted = "brown" ;
  this.cantPaint = "gray" ;
}

/** 定数 */
function Constance()
{
  this.squareSize    = 25 ; // セル１つの幅＆高さ
  this.settingLength =  2 ; // 塗りつぶすマス以外の部分の行数・列数
  this.regionLength  =  5 ; // 区切りの線を引く間隔
  this.sheet = SpreadsheetApp.getActiveSheet() ;
}

/** 解答を記入するマスの Range を取得 */
function AnswerZone()
{
  var constance = new Constance() ;
  var sheet = constance.sheet ;
  
  var answerZoneRange = sheet.getRange( (constance.settingLength + 1), (constance.settingLength + 1), (sheet.getLastRow() - constance.settingLength), (sheet.getLastColumn() - constance.settingLength) ) ;
  
  this.range = answerZoneRange ;
  this.horizontalLength = answerZoneRange.getNumColumns() ;
  this.verticalLength  = answerZoneRange.getNumRows() ;
}


/** 回答部分の数値と色を初期化 */
function answerClear()
{
  var zone  = new AnswerZone() ;
  var color = new CellColor() ;
  
  zone.range.setBackground( color.initial ) ;
  zone.range.setValue( "0" ) ;
}

/** main : セルのフォーマットを作成 */
function writeCellFormat()
{
  var constance = new Constance() ;
  var currentSheet = constance.sheet ;
  
  var cellLength = inputCellLength() ; // 一列のセルの個数を入力してもらう
  
  if(cellLength > 0){
      currentSheet.clearFormats() ;
  }else{
    return ;
  }

  // デフォルトの状態では 26 列 1000 行なので、必要に応じて足さなければいけない
  var cellEndLine = Math.ceil(cellLength / constance.regionLength) * constance.regionLength + constance.settingLength ;
  settingEndOnSheet( cellEndLine, cellEndLine ) ;

  settingHeightAndWidth( Math.ceil(cellLength / constance.regionLength) * constance.regionLength ) ;
  
  writeLineNumber(cellLength) ;
  
  writeRegionBorder(cellLength) ;
  
  // 問題となる数字を書くところの書式設定
  settingStringFormat(cellLength) ;
}

/** 不要な行と列を裁ち落とす */
function settingEndOnSheet(rows, columns)
{
  var currentSheet = SpreadsheetApp.getActiveSheet() ;
  
  if( rows === currentSheet.getMaxRows() ){
  }else if( rows > currentSheet.getMaxRows() ){
    currentSheet.insertRows( currentSheet.getMaxRows(), rows - currentSheet.getMaxRows() ) ;
  }else{
    currentSheet.deleteRows( 1, currentSheet.getMaxRows() - rows ) ;
  }
  
  if( columns === currentSheet.getMaxColumns() ){
  }else if( columns > currentSheet.getMaxColumns() ){
    currentSheet.insertColumns( currentSheet.getMaxColumns(), columns - currentSheet.getMaxColumns() ) ;
  }else{
    currentSheet.deleteColumns( 1, currentSheet.getMaxColumns() - columns ) ;
  }  
}

/** 各セルの高さと幅を調整 */
function settingHeightAndWidth(cellLength)
{
  var constance = new Constance() ;
  var currentSheet = constance.sheet ;
    
  currentSheet.setColumnWidth(1, constance.squareSize) ;
  currentSheet.setRowHeight(  1, constance.squareSize) ;
  currentSheet.setColumnWidth(2, constance.squareSize * 5) ;
  currentSheet.setRowHeight(  2, constance.squareSize * 5) ;
  
  for(var i = 1; i <= cellLength; ++i)
  {
    currentSheet.setColumnWidth(i + constance.settingLength, constance.squareSize) ;
    currentSheet.setRowHeight(  i + constance.settingLength, constance.squareSize) ;
  }
}

/** 行番号、列番号を表記する */
function writeLineNumber(cellLength)
{
  var constance = new Constance() ;
  var currentSheet = constance.sheet ;
    
  // 列番号を書く
  var integers = new Array() ;
  for(var i = 1; i <= cellLength; ++i)
  {
        integers.push( i ) ;
  }
  Logger.log( integers ) ;
  
  var cells = currentSheet.getRange(1, 1 + constance.settingLength, 1, cellLength ) ;
  cells.setBackground("#781313") ;
  cells.setFontColor("white") ;
  cells.setHorizontalAlignment("center") ;
  cells.setVerticalAlignment("center") ;
  cells.setValues( [ integers ] ) ;
  Logger.log( currentSheet.getName() ) ;

  // 行番号を書く
  integers = new Array() ;
  for(var i = 1; i <= cellLength; ++i)
  {
        integers.push( [ i ] ) ;
  }
  Logger.log( integers ) ;
  
  var cells = currentSheet.getRange(1 + constance.settingLength, 1, cellLength, 1 ) ;
  cells.setBackground("#781313") ;
  cells.setFontColor("white") ;
  cells.setHorizontalAlignment("center") ;
  cells.setValues( integers ) ;
  Logger.log( currentSheet.getName() ) ;
}

/** 見やすいように、５個区切りで線を引く */
function writeRegionBorder(cellLength)
{
  var constance = new Constance() ;
  var currentSheet = constance.sheet ;
    
  var regionsInOneLine = Math.ceil( cellLength / constance.regionLength ) ;
  
  for(var i = 0; i < regionsInOneLine; ++i)
  {
    for(var j = 0; j < regionsInOneLine; ++j)
    {
      var regionCells = currentSheet.getRange( constance.settingLength + constance.regionLength * i + 1, 
                                              constance.settingLength + constance.regionLength * j + 1, 
                                              constance.regionLength, constance.regionLength ) ;
      regionCells.setBorder( true, true, true, true, false, false ) ;
    }
  }
  
  // ついでに、外側の線についても同様に５個区切りで線を引く
  writeOutsideBorder(regionsInOneLine) ;
}

/** 外側の問題を入力する箇所にも、５個区切りで線を引く */
function writeOutsideBorder(regionsInOneLine)
{
  var constance = new Constance() ;
  var currentSheet = constance.sheet ;
    
  // ５行ごと
  for(var i = 0; i < regionsInOneLine; ++i)
  {
    var cells = currentSheet.getRange( constance.settingLength + constance.regionLength * i + 1, 1, 
                                      constance.regionLength, constance.settingLength ) ;
    cells.setBorder( true, true, true, true, true, false ) ;
  }
  // ５列ごと
  for(var i = 0; i < regionsInOneLine; ++i)
  {
    var cells = currentSheet.getRange( 1, constance.settingLength + constance.regionLength * i + 1, 
                                      constance.settingLength, constance.regionLength ) ;
    cells.setBorder( true, true, true, true, false, true ) ;
  }
}

/** 問題の数字たちを入力するところの書式設定 */
function settingStringFormat(cellLength)
{
  var constance = new Constance() ;
  var currentSheet = constance.sheet ;
    
  var verticalCells = currentSheet.getRange(constance.settingLength + 1, 2, cellLength, 1) ;
  verticalCells.setWrap( false ) ;
  verticalCells.setVerticalAlignment("center") ;
  verticalCells.setHorizontalAlignment("right") ;
  verticalCells.setNumberFormat("@") ; // 書式なし数値表記

  var horizontalCells = currentSheet.getRange(2, constance.settingLength + 1, 1, cellLength) ;
  horizontalCells.setWrap( true ) ;
  horizontalCells.setVerticalAlignment("bottom") ;
  horizontalCells.setHorizontalAlignment("center") ;
  horizontalCells.setNumberFormat("@") ;
}

/** ユーザーに数値を入力してもらう */
function inputCellLength()
{
  var ui = SpreadsheetApp.getUi(); // Same variations.
  
  var result = ui.prompt(
    "入力マスの整形" ,
    "マスの一列あたりの個数を、半角数字で入力してください" ,
    ui.ButtonSet.OK) ;
  
  // Process the user's response.
  var inputString = result.getResponseText() ;

  inputString = convertToHalfChar(inputString) ;
  var inputNumber = parseInt(inputString) ;

  if(inputNumber === NaN){ inputNumber = 0 ; }

  Logger.log("入力された値 : " + inputNumber) ;

  return inputNumber ;
}

/* 全角数字は半角に直す */
function convertToHalfChar(_string)
{
  var string = _string.concat() ;

  string = string.replace(/[０-９]/g, function(s)
                          {
                            return String.fromCharCode(s.charCodeAt(0) - 0xFEE0) ;
                          }
                         ) ;
  // Logger.log(string) ;
  return string ;
}

/** 書き込む前の確認 */
function writeAlert(targetSheet)
{
  var ui = SpreadsheetApp.getUi() ;
  var pushed = ui.alert("大事な確認", "現在のシート \" " + targetSheet.getName() + " \" に書き込みます。\nよろしいですか？\n\
（※表に書き込みがある場合は、先に全て削除されることをおすすめします）", ui.ButtonSet.YES_NO) ;
  if( pushed === ui.Button.YES )
  {
    return true ;
  }
  else
  {
    return false ;
  }
}

/** 問題部分の数字たちをきれいに見やすくする */
function rewriteQuestionNumbers()
{
  var constance = new Constance() ;
  var sheet = constance.sheet ;
  
  var question = new QuestionNumbersArray() ;

  // 水平部分について  
  var horizontalStringArray = new Array() ;
  for(var i = 0; i < question.horizontal.length; ++i)
  {
    horizontalStringArray.push( question.horizontal[i].join( String.fromCharCode(10) ) ) ;
    // String.fromCharCode(10) : グーグルスプレッドシートでの改行
  }
  
  question.horizontalRange.setValues( [ horizontalStringArray ] ) ;
  
  // タテについて
  var verticalStringArray = new Array() ;
  for(var i = 0; i < question.vertical.length; ++i)
  {
    verticalStringArray.push( question.vertical[i].join( " " ) ) ;
  }
  
  var writeVerticalStringArray = [ verticalStringArray ] ;
  Logger.log( writeVerticalStringArray[0].length ) ;

  var newDoubleArray = [] ;
  for(var i = 0; i < writeVerticalStringArray[0].length; ++i)
  {
    newDoubleArray.push( [] ) ;
    for(var j = 0; j < writeVerticalStringArray.length; ++j)
    {
      newDoubleArray[i].push( writeVerticalStringArray[j][i] ) ;
    }
  }

  // writeVerticalStringArray = replaceRowAndColumn( writeVerticalStringArray ) ;
  question.verticalRange.setValues( newDoubleArray ) ;  
}

/* スプレッドシート起動時 */
function onOpen(event)
{
  var menuitems = [
    {name:"入力マスを作る", functionName:"writeCellFormat"}, 
    {name:"問題部分を見やすくする", functionName:"rewriteQuestionNumbers"},
    {name:"解答を削除", functionName:"answerClear"},
  ] ;
  var sheet = SpreadsheetApp.getActiveSpreadsheet() ;
  sheet.addMenu("★", menuitems) ;
}