線上書籍

 

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++; }