Google Spreadsheet自動產生摩台結算日

前一篇 Excel VBA自動產生摩台結算日，現在筆者將同樣的觀念在Google Spreadsheet上使用Google Apps Script、JavaScript寫一個同樣功能程式，以方便套用在 Google Spredsheet 抓 SGX 摩台未平倉量與摩台次月契約開倉成本 文章上。
同樣的規則再提一次，摩台結算日為每個月倒數第2個交易日，有以下4種規則：

1. 每個月倒數第2個交易日，落在週一 ~ 週四，當天為結算日。
2. 每個月倒數第2個交易日，落在週五，則前1天為結算日。
3. 每個月倒數第2個交易日，落在週六 ~ 週日，則前2天為結算日。
4. 年節封關提早、農曆年節提早、7月~8月颱風延後，需手動調整。

接著就是透過Google Apps Script、JavaScript將上述內容轉成程式碼。

function CreateSGXSettlementdate()
{
  var nGap = 15;
  var datelist = new Array(2* nGap +1);
  
  var dPastdate, dBasedate, dCurrentdate ;
  var nCount = 0;
  
  /* 指定起始日期 */
  dPastdate = DateAdd("m", (-1)*nGap, new Date());   
  for(var i = 0 ; i <= 2 * nGap + 1 ; i++)
  {
    /* 取出該日期所屬月份的第一天 */
    dBasedate = DateAdd("m", i, new Date(dPastdate.getYear(), dPastdate.getMonth(), 1));
    
    /* 確認該日期是否為摩台結算日 */
    dCurrentdate  = checkSGXSettlementdate(dBasedate);
    
    /* 調整結算日格式 */
    datelist[nCount] = dCurrentdate.getFullYear() + "/" + padLeft(parseInt(dCurrentdate.getMonth()+1), 2) + "/" + padLeft(dCurrentdate.getDate(), 2);
    nCount++; 
  }
  
  /* 清除"結算日"工作表內容 */
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("摩台結算日");  
  var Row = getLastRowOfColumn("摩台結算日", "A:A");
  if (Row == 1) Row++;
  ss.getSheetByName("摩台結算日").getRange("A2:A"+Row).clear();
  
  /* 整合特定結算日期 */
  Row = getLastRowOfColumn("摩台結算日", "B:B"); 
  for(var i = 0 ; i < nCount ; i++)
  {  
    for(var j = 2 ; j < Row ; j++)
    {
      /* 比對產生的結算日與特定結算日 */
      if(datelist[i].substring(0, 8) == sheet.getRange(j, 2).getValue().substring(0, 8))
      {
        datelist[i] = sheet.getRange(j, 2).getValue();        
      }
    }
    sheet.getRange(i+2, 1).setValue("'" +datelist[i]);
  }
}

/* 確認摩台結算日 */
/* 方法一 */
function checkSGXSettlementdate(date)
{
  var dt, Dayofweek;
  var i = 0;
  i--;
  dt = new Date(date.getYear(), date.getMonth() + 1, -1);
  
  /* 出取日期的星期 */
  Dayofweek = dt.getDay();
  switch (Dayofweek)
  {
    case 5: i--; break;      
    case 6: case 0: i = i-2; break;      
    default:
  }
  return new Date(date.getYear(), date.getMonth() + 1, i);
}

/* 方法二 */
function checkSGXSettlementdate2(date)
{
  var dt, Dayofweek;
  dt = new Date(date.getYear(), date.getMonth(), -1);
  Dayofweek = dt.getDay() == 0 ? 6 : dt.getDay(); 
  return new Date(date.getYear(), date.getMonth(), -1 - Dayofweek % 4);
}

/* 取得資料欄的最後一個位置 */
function getLastRowOfColumn(sheetName, sheetRange)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Rowdata = ss.getSheetByName(sheetName).getRange(sheetRange).getValues();
  var row = 0;
  while ( Rowdata[row].join("") )  row++;  
  //while ( Rowdata[row][0] != "" )  row++;
  return row;
}

//補0
function padLeft(str, len)
{
    str = '' + str;
    if (str.length >= len) {
        return str;
    } else {
        return padLeft("0" + str, len);
    }
}

/* 取得日期資訊 */
function DatePart(interval, date)
{
  switch (interval.toLowerCase()) {
      case "q":
        dayIndex  = Math.floor((date.getMonth() + 1)/3) + ((date.getMonth() + 1) % 3 ? 1 : 0);
        break;
      case "wm":
        dayIndex  = date.getWeekOfMonth();
        break;      
      case "w":
        dayIndex  = date.getDay();
        break;
      case "h":
        dayIndex  = date.getHours();
        break;
      case "m":
        dayIndex  = date.getMinutes();
        break;
      case "s":
        dayIndex  = date.getSeconds();
        break;
    }
   return dayIndex 
}

/* 日期時間加減 */
function DateAdd(interval, number, date)
{
  switch (interval.toLowerCase()) {
    case "y": return new Date(date.setFullYear(date.getFullYear() + number));  
    case "m": return new Date(date.setMonth(date.getMonth() + number));  
    case "d": return new Date(date.setDate(date.getDate() + number));  
    case "w": return new Date(date.setDate(date.getDate() + 7*number));  
    case "h": return new Date(date.setHours(date.getHours() + number));  
    case "n": return new Date(date.setMinutes(date.getMinutes() + number));  
    case "s": return new Date(date.setSeconds(date.getSeconds() + number));  
    case "l": return new Date(date.setMilliseconds(date.getMilliseconds() + number)); 
  }
}

// 計算當前日期在本月份的周數
Date.prototype.getWeekOfMonth = function(weekStart)
{
 weekStart = (weekStart || 0) - 0;
 if(isNaN(weekStart) || weekStart > 6)
  weekStart = 0;

 var dayOfWeek = this.getDay();
 var day = this.getDate();
 return Math.ceil((day - dayOfWeek - 1) / 7) + ((dayOfWeek >= weekStart) ? 1 : 0);
}

執行結果：

參考資料：

• Excel VBA自動產生摩台結算日
• Google Spredsheet 抓 SGX 摩台未平倉量與摩台次月契約開倉成本