function myFunction() {
let ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('day2');
let range = ws.getRange(1, 1);
range.setValue('流水號');
range = ws.getRange(1, 2);
range.setValue('客戶名稱');
range = ws.getRange(1, 3);
range.setValue('客戶電話');
range = ws.getRange(1, 4);
range.setValue('客戶地址');
range = ws.getRange(1, 5);
range.setValue('備註');
}
/*========================================
設定客戶標題列
=========================================*/
function set_head_custom() {
setCellData('day2', 1, 1, '流水號');
setCellData('day2', 1, 2, '客戶名稱');
setCellData('day2', 1, 3, '客戶電話');
setCellData('day2', 1, 4, '客戶地址');
setCellData('day2', 1, 5, '備註');
}
/*========================================
資料寫入單一儲存格
=========================================*/
function setCellData(sheet, rowIndex, colIndex, value) {
let ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);
let range = ws.getRange(rowIndex, colIndex);
range.setValue(value);
}
/*========================================
設定標題列
=========================================*/
function set_head_custom() {
// setCellData('day2', 1, 1, '流水號');
// setCellData('day2', 1, 2, '客戶名稱');
// setCellData('day2', 1, 3, '客戶電話');
// setCellData('day2', 1, 4, '客戶地址');
// setCellData('day2', 1, 5, '備註');
let headData = ['流水號', '客戶名稱', '客戶電話', '客戶地址', '備註']; //headData[0] => '流水號' 、headData[1] => '客戶名稱' .....
let sheet = 'day2';
let rowIndex = 1;
for (let i in headData) {
let colIndex = Number(i) + 1;//i 型態為字串,須轉為數字做計算
setCellData(sheet, rowIndex, colIndex, headData[i]);
}
}
此時 set_head_custom() 其實就是 寫入一列(一筆記錄)的函式
9. 設定客戶資料
/*========================================
設定客戶資料
=========================================*/
function set_data_custom() {
let sheet = 'day2';
let rowIndex = 2;
let customData = [1, '育將電腦', '0123456789', '台南市永康區大灣路158號', '備註1'];
for (let i in customData) {
let colIndex = Number(i) + 1;//i 型態為字串,須轉為數字做計算
setCellData(sheet, rowIndex, colIndex, customData[i]);
}
rowIndex = 3;
customData = [2, 'Google', '1234567890', '美國', '備註2'];
for (let i in customData) {
let colIndex = Number(i) + 1;//i 型態為字串,須轉為數字做計算
setCellData(sheet, rowIndex, colIndex, customData[i]);
}
}
客戶電話:第1個'0'被吃掉了,因為儲存格的格式自動變成「數字」格式,如果資料是字串,則在寫入時需要特別處理,
setNumberFormat('@') :將數字或日期格式,設為「字串」。
https://developers.google.com/apps-script/reference/spreadsheet/range?hl=zh-tw#setnumberformatnumberformat
新增資料,希望可以自動偵測寫入最後資料那列的下一列
getLastRow():取得工作表的最後列指標
getLastColumn():取得工作表的最後欄指標