# GAS主題課程 : Google App Script與Google Sheet互動
## 前言
我們會開始讓Google App Script去與Google Workspace 的各種工具進行互動，所以你可以根據自己所好去看需要的工具
而今天，我們先從最常使用，也最需要自動化的Google Sheet開始。
>[!Note] 基本目標 : 資深員工資格
>我們現在Google Sheet中，"工作表1"有員工資訊、員工所在部門、員工入職年份
>而我們現在2025年，為犒賞工作滿4年(含)以上的資深員工，我們將根據"入職年份"來決定員工是否符合資格
## 完整程式碼
[基本目標程式碼](https://github.com/bsbacon0966/Google-App-Script-30-days-example/blob/main/day3/basic.js)
[進階目標程式碼](https://github.com/bsbacon0966/Google-App-Script-30-days-example/blob/main/day3/advance.js)
## 試算表與Sheet
請先到Google Sheet(Google試算表)中，並點擊+號建立一個新試算表
<div style="text-align: center;">
<img src="https://hackmd.io/_uploads/SJ5wnGQFyl.png" style="width: 50%; border: 2px solid black; padding: 5px;" />
</div>
而我有事先輸入一些資訊，之後說明將會以下圖的樣貌進行說明。
> 下圖結構 : 此張試算表中，有三個工作表，分別叫做工作表1、2、3，工作表1有輸入一些簡單的資訊。
<div style="text-align: center;">
<img src="https://hackmd.io/_uploads/B1Y6FrXKyl.png" style="width: 80%; border: 2px solid black; padding: 5px;" />
</div>
- **試算表（Spreadsheet）** : 指的是一整個檔案，並且此檔案會有 ==**獨一無二的ID**==
- **工作表（Worksheet/Sheet）** : 工作表1 / 工作表2 / 工作表3 皆為各自獨立的Sheet
- **工作表中的坐標系 Sheet(row,column)**
- 工作表的行列索引**是從 (1,1) 開始的**，而不像大多數程式語言（如 JavaScript、Python）中的陣列索引是從 (0,0) 開始的。
- 則以上圖而言，(1,1)對應標題"名稱"，(1,2)對應"小祥"，(2,1)對應標題"職位"，(2,2)對應"資訊部門"
>[!Note]ID 與 url
>如果你抬頭看一下網址，你可以觀察一下網址，
> <div style="text-align: center;">
<img src="https://hackmd.io/_uploads/Skng5BXt1l.png" style="border: 2px solid black; padding: 5px;" />
</div>
> 1cWVnqRwyh-NxbSrbPK2igSnPdG_CkOklvLnYD7vulJY = 試算表的"ID"
> https://docs.google.com/spreadsheets/d/1cWVnqRwyh-NxbSrbPK2igSnPdG_CkOklvLnYD7vulJY/edit?gid=0#gid=0 = 試算表的"url"
## 看懂SpreadsheetApp API與實際操作
建立一個新的Google Apps Script檔案
<div style="text-align: center;">
<img src="https://hackmd.io/_uploads/Hy-6gQmK1g.png" style="width: 80%; border: 2px solid black; padding: 5px;" />
</div>
而Google App Script腳本檔案**並不會通靈知道你的程式碼要操作哪一個試算表**，所以你必須使用 SpreadsheetApp API 明確指定要操作的試算表。
### 1.抓取試算表
- `SpreadsheetApp.getActiveSpreadsheet()` : 取得目前關聯的試算表（通常用於直接在試算表附加的 Apps Script）。
- `SpreadsheetApp.openById(id)` : 透過**試算表的 ID** 來開啟試算表。
- `SpreadsheetApp.openByUrl(url)` : 透過**試算表的 URL** 來開啟試算表。
### 2.抓取Sheet
- `spreadsheet.getSheetByName(name)` : 透過Sheet名稱取得特定Sheet。
- `spreadsheet.getSheets()`: 取得所有Sheet的陣列。
所以這時候我們就可以寫一個簡單的程式去確認我們是否有抓到我們要操作的Sheet，也可以利用if判斷+`Logger.log()`輸出訊息以確認是否抓取成功
```javascript
const Day3_Spreadsheet = SpreadsheetApp.openById("1ndr6fIsKUXcqwLb5bf5VvK0SYSyGhxDpIwPc4DAqRto"); //貼上你的Google Sheet ID
if (Day3_Spreadsheet) { //確認是否抓到指定的試算表(Spreadsheet)
Logger.log(`此試算表存在`);
}
const Day3_sheet1 = Day3_Spreadsheet.getSheetByName('工作表1'); //從剛剛抓取的試算表(Spreadsheet)找指定的工作表(sheet)
if (Day3_sheet1) { //確認是否抓到指定的工作表(sheet)
Logger.log(`工作表1存在`);
}
```
<div style="text-align: center;">
<img src="https://hackmd.io/_uploads/H1QKNm7tke.png" style="width: 1000%; border: 2px solid black; padding: 5px;" />
</div>
如果執行即果訊息如上方，代表抓到了指定的試算表與工作表。
### 3.操作Sheet
根據上方程式，我們已經成功抓取了工作表1的資料，接下來我們將根據今年的年份與員工資訊來完成任務。
#### a. **取得當前年份**：使用 `Date().getFullYear()` 來獲得今年的年份。
```javascript
// 取得當前年份（例如2025）
const currentYear = new Date().getFullYear();
```
#### b. **抓取員工資料範圍**：使用 `getRange(row, column, numRows, numColumns)` 來抓取工作表中的員工資料。
```javascript
// 取得員工資料範圍（從第二行開始）
const employee_data = Day3_sheet1.getRange(2, 1, Day3_sheet1.getLastRow() - 1, Day3_sheet1.getLastColumn()).getValues();
Logger.log(employee_data);
```
這裡的.getRange(row, column, numRows, numColumns):
- row = 從第幾行開始抓
- column = 從第幾列開始抓
- numRows = 共抓幾行(此處用`.getLastRow()-1`)
- numColumns = 共抓幾列(此處用`.getLastColumn()`)
並且後方的`.getValues()` 是用來將抓取到的範圍轉換為數值，方便後續操作
<div style="text-align: center;"><img src="https://hackmd.io/_uploads/HyeLGPQtyl.jpg" style="width: 80%; border: 2px solid black; padding: 5px;" /></div>
<div style="text-align: center;"><img src="https://hackmd.io/_uploads/HycPqBQFkl.png" style="width: 1000%; border: 2px solid black; padding: 5px;" /></div>
#### c. employee_data二維陣列
當我們已經取得了員工資訊的二維陣列後，透過 for 迴圈來遍歷每位員工的資料，一一完成判斷。
而這個二維陣列，employee_data[rowIndex][columnIndex]，其中：
- rowIndex 代表第幾位員工（從 0 開始）。
- columnIndex 代表該員工資料的第幾個欄位（如姓名、部門、入職年份等）。
| 欄位名稱 | columnIndex(陣列位置)|
| -------- | -------- |
| 員工姓名 | 0 |
| 部門 | 1 |
| 入職年份 | 2 |
| 是否為資深員工 | 3 |
所以我們可以先宣告變數:
```javascript
const nameIndex = 0; // 員工姓名（第一列）
const departmentIndex = 1; // 部門（第二列）
const joinYearIndex = 2; // 入職年份（第三列）
const isSeniorIndex = 3; // 是否為資深員工（第四列）
```
則此時
| 欄位名稱 | columnIndex(陣列位置)|
| -------- | -------- |
| 員工姓名 | 0 = nameIndex |
| 部門 | 1 = departmentIndex |
| 入職年份 | 2 = joinYeraIndex |
| 是否為資深員工 | 3 = isSeniorIndex |
#### d. 使用 `getRange(row, column).setValue()` 來將指定格子填寫"是否符合資深員工身分"結果
```javascript
// 遍歷每一位員工的資料
for (let i = 0; i < employee_data.length; i++) {
const joinYear = employee_data[i][joinYearIndex];// 年資
// 計算年資(當前年分 - 開始工作年份)
const yearsWorked = currentYear - joinYear;
// 若年資大於4年，則是資深員工
if (yearsWorked >= 4) {
Day3_sheet1.getRange(i + 2, isSeniorIndex + 1).setValue("是");
}
else{
Day3_sheet1.getRange(i + 2, isSeniorIndex + 1).setValue("否");
}
}
```
這裡的`.getRange(row, column)` = 抓取Sheet座標(Row，Column)的格子
後方的`.setValue()` 是用來對抓取到的格子填寫所需資料
>[!Warning] 坐標系問題
><div style="text-align: center;"><img src="https://hackmd.io/_uploads/H1dP9XNtke.jpg" style="width: 1000%; border: 2px solid black; padding: 5px;" /></div>
我們的程式要判斷是否為資深員工，只需要判斷"年資"而已 = 僅需要判斷employee_data[i][joinYearIndex]。
如果`當年年份 - employee_data[i][joinYearIndex] >= 4`，我們即要將這位員工的"是否符合資深員工身分"格子填上是，不然即填否。
<div style="text-align: center;">
<img src="https://hackmd.io/_uploads/HJ1gpI7Y1g.png" style="width: 80%; border: 2px solid black; padding: 5px;" />
</div>
最終跑完程式碼後，就可以看到你的表格有變化了!即可簡單判斷程式是否正確:
小祥因為開始工作年份為2022，2025-2022=3，不符合資深員工
其他人開始工作年份為2020與2021，2025-2021=4，2025-2020=5，符合資深員工
程式正確!
## 進階挑戰
>[!Warning] 進階目標 : 資深員工資格與獎金數
>假設除了資格外，需要額外考慮工作時長、所在部門將影響員工可以拿到的獎金數，並且將個個員工該拿到的獎金數填到表格當中
>>獎金計算邏輯：
技術部門：年資每滿一年，獎金增加 4000 元。
財政部門：年資每滿一年，獎金增加 3500 元。
其他部門：年資每滿一年，獎金增加 3000 元。
>> <div style="text-align: center;">
<img src="https://hackmd.io/_uploads/SkxWGkLYJg.png" style="width: 80%; border: 2px solid black; padding: 5px;" />
</div>
根據要求我們知道
1. 獎金在第五列，則當我們抓取成二維data時，獎金會在`index = 4`的位置，則先設定變數
```javascript
const bonusIndex = 4;
```
2.在for環圈判斷時，我們要根據**年資(即上述變數yearsWorked)**與**部門**來決定其獎金數，則if/else判斷
- 如果是"資訊部門"，則其獎金數等同`4000*年資`
- 否則，如果是"財政部門"，則其獎金數等同`3500*年資`
- 否則，其獎金數等同`3000*年資`
```javascript
const employee_department = employee_data[i][bonusIndex]; //員工部門
if(employee_department=="資訊部門") bonus = 4000 * yearsWorked;
else if(employee_department=="財政部門") bonus = 3500 * yearsWorked;
else bonus = 3000 * yearsWorked;
Day3_sheet1.getRange(i + 2, bonusIndex + 1).setValue(bonus);
```
將其程式貼到for迴圈判斷中，即可完成今天的進階目標
<div style="text-align: center;">
<img src="https://hackmd.io/_uploads/S1mJH1Itkl.png" style="width: 80%; border: 2px solid black; padding: 5px;" />
</div>
Sign in with Wallet
Connect another wallet