テーブル定義からDDL作成

DDL作成スクリプト作成

/* create文作成 */
function execute() {
  var result = "";
  result = GetSql();
  ShowResult(result);
}

function ShowResult(result) {
  var showResult = Browser.msgBox(result, 
                        Browser.Buttons.OK);
}

function GetDbName(sheet)
{
  var result = GetCellValue(sheet, "1", "1");
  return result;
}

function GetTableName(sheet)
{
  var result = GetCellValue(sheet, "1", "2");
  return result;
}


function GetRowCount(sheet)
{
  var result = GetCellValue(sheet, "1", "3");
  return result;
}


function GetSql()
{
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  var headerCreateTable = "create table "+ GetTableName(sheet)  + "( ";
  var footerCreateTable = " );"

  var rowCount = GetRowCount(sheet);
  var columnName = '';
  var columnType = '';
  var columnSize = '';

  var defineCols = '';
  for(i=0;i <rowCount; i++)
  {
    columnName = GetCellValue(sheet, (i+2), 2);
    columnType = GetCellValue(sheet, (i+2), 3);    
    if(columnType == "varchar") {
      columnSize =  '(' + GetCellValue(sheet, (i+2), 4) + ')';
    }
    defineCols = defineCols + columnName + " " + columnType + columnSize + ' ';
    columnSize = '';
    
    if(i != (rowCount-1))
    {
      defineCols = defineCols + ", ";
    }
  }
  
  return headerCreateTable +  defineCols + footerCreateTable;
}


function GetCellValue(sheet, row, column) {
  var range = sheet.getRange(row, column)  
  return range.getValue();
}