Title1

Title2

Title3

8-6 JS class 靜態方法 - 設定「儲存格資料」

 


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);//寫值
    }
  }

}

//程式碼.gs

  let ws = Sheet.getWs('工作表1');
  rowIndex = ws.getLastRow() + 1;
  for(i in data){ 
    for(j in data[i]){
      colIndex = parseInt(j)+1;
        
      if(colIndex === 2){
        type = '文字';
      }else{
        type = '';
      }
      value = data[i][j];
      Sheet.setCellData(sheet,rowIndex,colIndex,value,type);
    } 
    // rowIndex = rowIndex +1;
    rowIndex++;
    
  }