所有程式碼整合檔案
本檔案整合了「零基礎快速入門：利用 AI 輕鬆打造自動化程式」書中的所有程式碼範例，按照學習順序排列。
第一部分：基礎概念
理解 AI 提供的程式碼
AI 程式碼示範 – Python 讀取 Excel
import openpyxl
def print_column_data(filename, sheet_name, column_letter):
wb = openpyxl.load_workbook(filename)
sheet = wb[sheet_name]
for cell in sheet[column_letter]:
print(cell.value)
wb.close()
print_column_data("sales.xlsx", "Sheet1", "A")
加上註解的版本
import openpyxl
def print_column_data(filename, sheet_name, column_letter):
# 使用 openpyxl 讀取指定檔案
wb = openpyxl.load_workbook(filename)
# 取得特定工作表
sheet = wb[sheet_name]
# 逐列讀取指定欄位的儲存格資料
for cell in sheet[column_letter]:
print(cell.value)
# 關閉工作簿
wb.close()
# 呼叫函式以印出 sales.xlsx 的 Sheet1 工作表 A 欄資料
print_column_data("sales.xlsx", "Sheet1", "A")
第二部分：各語言基本語法
Google Apps Script 基本語法
function：每一段程式的開始
function myFunction() {
// 這裡面就是你要執行的自動化步驟
}
Logger.log()：讓你看到程式到底做了什麼
Logger.log("Hello World");
var / let / const：儲存資訊的關鍵字
let name = "小明";
const PI = 3.14;
var age = 18;
if 條件判斷
if (score >= 60) {
Logger.log("及格！");
} else {
Logger.log("不及格！");
}
for 迴圈：重複做某件事
for (let i = 0; i < 3; i++) {
Logger.log("第 " + i + " 次");
}
操作試算表的基本語法
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("表單1");
let data = sheet.getRange("A1:B10").getValues();
寫註解：讓自己（或別人）看得懂
// 這是用來寄信的程式
Python 基本語法
def：定義一段動作（函式）
def greet():
print("Hello")
print()：把東西顯示出來
print("Hello, world")
變數（Variable）：幫資料取個名字
name = "小明"
age = 18
if 條件判斷
if age >= 18:
print("成年了")
else:
print("還沒成年")
for 迴圈：重複做某件事
for i in range(3):
print("第", i, "次")
串列（List）：放很多資料的容器
fruits = ["蘋果", "香蕉", "芒果"]
for fruit in fruits:
print(fruit)
寫註解
# 這是用來印出名字的程式
VBA 基本語法
Sub：一段 VBA 程式的開始
Sub SayHello()
MsgBox "Hello"
End Sub
MsgBox：跳出提示訊息
MsgBox "請記得存檔喔！"
變數宣告：Dim
Dim name As String
Dim score As Integer
If 判斷式
If score >= 60 Then
MsgBox "及格"
Else
MsgBox "不及格"
End If
For 迴圈：重複做一件事
Dim i As Integer
For i = 1 To 3
MsgBox "第" & i & "次"
Next i
操作 Excel 表格
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("工作表1")
ws.Range("A1").Value = "Hello"
註解的寫法
' 這是一段提示使用者的訊息
第三部分：實際應用案例
自動寄送客製化信件或廣告
Google Apps Script 範例（GAS）
function sendCustomEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("名單");
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var name = data[i][0];
var email = data[i][1];
var dept = data[i][2];
var sales = data[i][3];
var subject = name + "，本月成績很棒！";
var body = "親愛的 " + name + "，您在" + dept + "的表現非常優秀，本月業績達到 " + sales + "，請繼續保持！";
MailApp.sendEmail(email, subject, body);
}
}
VBA 範例（Excel + Outlook）
Sub SendEmails()
Dim OutlookApp As Object
Dim Mail As Object
Dim ws As Worksheet
Dim i As Long
Set OutlookApp = CreateObject("Outlook.Application")
Set ws = ThisWorkbook.Sheets("名單")
i = 2
Do While ws.Cells(i, 1).Value <> ""
Dim name As String, email As String, dept As String, sales As String
name = ws.Cells(i, 1).Value
email = ws.Cells(i, 2).Value
dept = ws.Cells(i, 3).Value
sales = ws.Cells(i, 4).Value
Set Mail = OutlookApp.CreateItem(0)
With Mail
.To = email
.Subject = name & "，本月成績很棒！"
.Body = "親愛的 " & name & "，您在" & dept & "的表現非常優秀，本月業績達到 " & sales & "，請繼續保持！"
.Send
End With
i = i + 1
Loop
End Sub
Python 範例（Excel + SMTP）
import pandas as pd
import smtplib
from email.mime.text import MIMEText
# 讀取 Excel 名單
df = pd.read_excel("名單.xlsx")
# SMTP 設定
smtp_server = "smtp.gmail.com"
smtp_port = 587
sender_email = "[email protected]"
sender_password = "your_password" # 可搭配 App 密碼設定更安全
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(sender_email, sender_password)
for index, row in df.iterrows():
name = row["姓名"]
email = row["Email"]
dept = row["部門"]
sales = row["業績"]
subject = f"{name}，本月成績很棒！"
body = f"親愛的 {name}，您在{dept}的表現非常優秀，本月業績達到 {sales}，請繼續保持！"
msg = MIMEText(body)
msg["Subject"] = subject
msg["From"] = sender_email
msg["To"] = email
server.sendmail(sender_email, email, msg.as_string())
server.quit()
一鍵拆分表格：讓資料自動分類到不同工作表
Google Apps Script 範例
function splitSheetByRegion() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("母表");
const data = sheet.getDataRange().getValues();
const header = data[0];
const regionIndex = header.indexOf("地區"); // 如果要依其他欄拆分，請修改這裡
const regionMap = {};
// 分類資料
for (let i = 1; i < data.length; i++) {
const row = data[i];
const region = row[regionIndex];
if (!regionMap[region]) {
regionMap[region] = [];
}
regionMap[region].push(row);
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
// 建立子表
for (let region in regionMap) {
let sheet = ss.getSheetByName(region);
if (!sheet) {
sheet = ss.insertSheet(region);
} else {
sheet.clear();
}
sheet.appendRow(header);
regionMap[region].forEach(row => {
sheet.appendRow(row);
});
}
}
Python（使用 pandas）範例
import pandas as pd
# 讀取母表
df = pd.read_excel("母表.xlsx") # 請將檔案名稱改成自己的
# 拆分依據欄位
group_column = "地區" # 如果要依其他欄位拆，請修改這裡
# 依地區分組後輸出成多個檔案
for region, group_df in df.groupby(group_column):
output_file = f"{region}.xlsx"
group_df.to_excel(output_file, index=False)
print(f"{region} 檔案已建立")
Excel VBA 範例
Sub SplitByRegion()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("母表") ' 原始資料表
Dim lastRow As Long, lastCol As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Dim header As Range
Set header = ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol))
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 2 To lastRow
Dim region As String
region = ws.Cells(i, 2).Value ' 第2欄是地區，如有變動可調整
If Not dict.exists(region) Then
dict.Add region, Nothing
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count))
newSheet.Name = region
header.Copy Destination:=newSheet.Range("A1")
End If
ws.Rows(i).Copy Destination:=Worksheets(region).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub
自動整理 Google Drive 裡的檔案
程式範例（Google Apps Script）
function autoOrganizeFiles() {
var folderName = "收件夾"; // 來源資料夾名稱
var folder = DriveApp.getFoldersByName(folderName).next();
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
var fileName = file.getName();
// 檢查關鍵字並搬移
if (fileName.includes("發票")) {
moveToFolder(file, "發票");
} else if (fileName.includes("會議記錄")) {
moveToFolder(file, "會議記錄");
} else if (fileName.includes("報名表")) {
moveToFolder(file, "報名表");
}
}
}
function moveToFolder(file, targetFolderName) {
var targetFolder = DriveApp.getFoldersByName(targetFolderName);
if (targetFolder.hasNext()) {
var folder = targetFolder.next();
folder.addFile(file); // 加進新資料夾
var parentFolders = file.getParents();
while (parentFolders.hasNext()) {
var oldFolder = parentFolders.next();
oldFolder.removeFile(file); // 從舊資料夾移除
}
} else {
Logger.log("找不到資料夾：" + targetFolderName);
}
}
串接開放資料 API：即時 YouBike 資訊大公開！
Google Apps Script 版本（存進 Google Sheet）
function fetchYouBikeData() {
var apiUrl = "https://tcgbusfs.blob.core.windows.net/dotapp/youbike/v2/youbike_immediate.json";
try {
var response = UrlFetchApp.fetch(apiUrl);
var jsonData = JSON.parse(response.getContentText());
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
sheet.clear();
var header = [
"站點編號", "站點名稱", "區域", "地址", "可借車數量",
"可還車位數量", "總停車位", "更新時間", "經度", "緯度"
];
sheet.appendRow(header);
jsonData.forEach(function(station) {
var rowData = [
station.sno,
station.sna,
station.sarea,
station.ar,
station.available_rent_bikes,
station.available_return_bikes,
station.total,
station.mday,
station.longitude,
station.latitude
];
sheet.appendRow(rowData);
});
Logger.log("YouBike 資料已成功寫入試算表！");
} catch (error) {
Logger.log("錯誤：" + error.message);
}
}
Python 版本（存成 Excel）
import requests
import openpyxl
# 設定 API URL
api_url = "https://tcgbusfs.blob.core.windows.net/dotapp/youbike/v2/youbike_immediate.json"
# 發送 GET 請求
response = requests.get(api_url)
data = response.json()
# 建立 Excel 檔案與工作表
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "YouBike 即時資料"
# 標題列
header = [
"站點編號", "站點名稱", "區域", "地址", "可借車數量",
"可還車位數量", "總停車位", "更新時間", "經度", "緯度"
]
ws.append(header)
# 寫入資料
for station in data:
row = [
station["sno"],
station["sna"],
station["sarea"],
station["ar"],
station["available_rent_bikes"],
station["available_return_bikes"],
station["total"],
station["mday"],
station["longitude"],
station["latitude"]
]
ws.append(row)
# 儲存檔案
wb.save("youbike_data.xlsx")
print("已成功寫入 Excel！")
VBA 版本（適合直接整合 Excel）
Sub FetchYouBikeData()
Dim http As Object
Dim json As Object
Dim item As Object
Dim i As Long
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://tcgbusfs.blob.core.windows.net/dotapp/youbike/v2/youbike_immediate.json", False
http.Send
If http.Status = 200 Then
Set json = JsonConverter.ParseJson(http.ResponseText)
' 清空工作表
With Sheet1
.Cells.Clear
.Range("A1:J1").Value = Array("站點編號", "站點名稱", "區域", "地址", "可借車數量", _
"可還車位數量", "總停車位", "更新時間", "經度", "緯度")
i = 2
For Each item In json
.Cells(i, 1).Value = item("sno")
.Cells(i, 2).Value = item("sna")
.Cells(i, 3).Value = item("sarea")
.Cells(i, 4).Value = item("ar")
.Cells(i, 5).Value = item("available_rent_bikes")
.Cells(i, 6).Value = item("available_return_bikes")
.Cells(i, 7).Value = item("total")
.Cells(i, 8).Value = item("mday")
.Cells(i, 9).Value = item("longitude")
.Cells(i, 10).Value = item("latitude")
i = i + 1
Next
End With
Else
MsgBox "抓取資料失敗：" & http.Status
End If
End Sub
VBA 使用 JSON 的範例程式碼
Dim json As Object
Set json = JsonConverter.ParseJson(jsonText)
早報自動送到手：新聞標題爬蟲教學
Google Apps Script 版本（存進 Google Sheet）
function fetchYahooNews() {
const url = 'https://tw.news.yahoo.com/';
const html = UrlFetchApp.fetch(url).getContentText();
const pattern = /<h3[^>]*>\s*<a href="(.*?)"[^>]*>(.*?)<\/a>/g;
const matches = [...html.matchAll(pattern)];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("新聞");
if (!sheet) return;
sheet.clear();
sheet.appendRow(["標題", "連結"]);
matches.forEach(match => {
const link = "https://tw.news.yahoo.com" + match[1];
const title = match[2].replace(/<[^>]*>/g, '').trim(); // 去除 html tag
sheet.appendRow([title, link]);
});
}
Python 版本（存成文字檔或 CSV）
import requests
from bs4 import BeautifulSoup
url = "https://tw.news.yahoo.com/"
res = requests.get(url, headers={"User-Agent": "Mozilla/5.0"})
soup = BeautifulSoup(res.text, "html.parser")
news_items = soup.find_all("h3")
with open("news.csv", "w", encoding="utf-8") as f:
f.write("標題,連結\n")
for h in news_items:
a = h.find("a")
if a and a.text and a["href"]:
title = a.text.strip()
href = "https://tw.news.yahoo.com" + a["href"]
f.write(f'"{title}","{href}"\n')
VBA 版本（直接寫進 Excel）
Sub GetYahooNews()
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
Dim html As String, url As String
url = "https://tw.news.yahoo.com/"
http.Open "GET", url, False
http.setRequestHeader "User-Agent", "Mozilla/5.0"
http.Send
html = http.ResponseText
Dim rowIndex As Long: rowIndex = 1
Dim startPos As Long: startPos = 1
Dim link As String, title As String
Sheet1.Cells.Clear
Sheet1.Cells(1, 1) = "標題"
Sheet1.Cells(1, 2) = "連結"
rowIndex = 2
Do
startPos = InStr(startPos, html, "<h3")
If startPos = 0 Then Exit Do
Dim linkStart As Long
linkStart = InStr(startPos, html, "href=""") + 6
Dim linkEnd As Long
linkEnd = InStr(linkStart, html, """")
link = Mid(html, linkStart, linkEnd - linkStart)
Dim textStart As Long
textStart = InStr(linkEnd, html, ">") + 1
Dim textEnd As Long
textEnd = InStr(textStart, html, "</a>")
title = Mid(html, textStart, textEnd - textStart)
title = Replace(title, "<u class=""StretchedBox""></u>", "")
title = Replace(title, "<[^>]*>", "") ' 簡易去 tag
If title <> "" Then
Sheet1.Cells(rowIndex, 1).Value = Trim(title)
Sheet1.Cells(rowIndex, 2).Value = "https://tw.news.yahoo.com" & link
rowIndex = rowIndex + 1
End If
startPos = textEnd
Loop
End Sub
總結
本檔案共提取了 51 個程式碼區塊，涵蓋了：
- Google Apps Script：11 個程式碼範例
- Python：10 個程式碼範例
- VBA：10 個程式碼範例
- 實際應用案例：17 個綜合範例
這些程式碼按照學習順序排列，從基礎語法到實際應用，可以幫助讀者循序漸進地掌握程式自動化的技能。