9-9 function.gs

共用函式

/* ==========================
  樣板引入函數 
  <?!= include("index-css") ?>
============================*/
function include(file){
  return HtmlService.createHtmlOutputFromFile(file).getContent();
}

class Sheet{
  //取得工作表
  static getWs(sheet){
    return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);//取得工作表
  }

  //取得標題列
  static getHead(sheet){
    let ws = this.getWs(sheet);
    let data = ws.getSheetValues(1,1,1,ws.getLastColumn())[0];
    return data;
  }

  //取得資料列
  static getData(sheet){
    let ws = this.getWs(sheet);
    let data = ws.getSheetValues(2,1,ws.getLastRow()-1 ,ws.getLastColumn());
    return data;
  }

  //設定儲存格資料
  static setCellData(sheet,rowIndex,colIndex,value,type=''){
    if(type == '文字'){
      this.getWs(sheet).getRange(rowIndex,colIndex).setNumberFormat('@').setValue(value);//寫值並設為文字類型
    }else{
      this.getWs(sheet).getRange(rowIndex,colIndex).setValue(value);//寫值
    }
  }

  //建立工作表
  static createSheet(sheet){
    let ws = this.getWs(sheet);
    if(ws === null){//條件成立
      ws = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
      ws.setName(sheet);
    }
  }

  //設定標題列
  static setHead(sheet,row){
    let i;
    for(i in row){
      this.setCellData(sheet,1,parseInt(i)+1,row[i],'文字');
    }
  }

  //渲染樣版&子樣版
  static render(file, title, argsObject){
    let tmp = HtmlService.createTemplateFromFile(file);
    if(argsObject){
      for(let i in argsObject){
        tmp[i] = argsObject[i];
      }
    }
    if(title){
      return tmp.evaluate().setTitle(title).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL).addMetaTag('viewport', 'width=device-width, initial-scale=1');
    }else{
      return tmp.evaluate().getContent();
    }
  }

}