在先前的文章裡，我們成功建立了自動記錄打卡時間到 Google Sheets 的基礎流程。然而，目前的版本有個小缺點：每次觸發都會新增一筆紀錄，這在需要短暫外出又返回公司的情境下（像是買午餐），會造成不必要的重複紀錄
這篇文章將改善這個流程，讓我們的自動化系統更加好用：
上班打卡 (Check-In)：只允許每天記錄一次，避免重複。
下班打卡 (Check-Out)：自動更新當天的記錄，而非新增一筆。
為了實作更複雜的判斷，我們需要一個統一的入口來處理所有打卡請求
把最後一個節點改為「Code」，命名為「Complete Attendance Handler」之後會用到
// 完整的打卡處理邏輯，統一先讀取資料再決定動作
const basicData = $("Edit Fields").item.json;
const todayDate = basicData.todayDate;
const direction = basicData.direction;
const currentTime = basicData.currentTime;
const spreadsheetId = basicData.spreadsheetId;
// 無論是 Check-In 還是 Check-Out，都需要先讀取資料來檢查今天是否已有記錄
return [
{
json: {
action: "read_first",
todayDate: todayDate,
currentTime: currentTime,
direction: direction,
spreadsheetId: spreadsheetId,
message: `Processing ${direction} for ${todayDate}`,
},
},
];
接下來，我們要根據 read_first 指令來實際讀取試算表
再來接「If」
填入
{
{
$json.action;
}
}
read_first;
接著在「true」的路徑上，選擇「HTTP Request」
「URL」填寫如下
https://sheets.googleapis.com/v4/spreadsheets/{{ $json.spreadsheetId }}/values/Worklog
驗證方式選「Predefined Credential Type」
「Credential Type」選擇之前有設定過的「Google Sheets OAuth2 API」
再下個欄位選「Google Sheets account」
讀取到資料後，就來到最關鍵的一步：判斷該執行什麼動作
下個節點再選擇「Code」，命名為「Process Attendance」
// 處理所有打卡邏輯（Check-In 和 Check-Out）
const basicData = $("Complete Attendance Handler").item.json;
const sheetData = $input.item.json;
const todayDate = basicData.todayDate;
const currentTime = basicData.currentTime;
const direction = basicData.direction;
const spreadsheetId = basicData.spreadsheetId;
const values = sheetData.values || [];
let foundRowIndex = -1;
let foundRow = null;
// 尋找今天的記錄（從第二行開始，跳過標題）
for (let i = 1; i < values.length; i++) {
const row = values[i];
if (row[0] === todayDate) {
// Date 欄位
foundRowIndex = i + 1; // Google Sheets 行號從 1 開始
foundRow = row;
break;
}
}
if (direction === "Check-In") {
if (foundRowIndex > 0) {
// 今天已經有 Check-In 記錄，不執行任何動作
return [
{
json: {
action: "skip",
message: `Check-In already exists for ${todayDate}. Skipping.`,
spreadsheetId: spreadsheetId,
},
},
];
} else {
// 今天沒有記錄，新增 Check-In 記錄
return [
{
json: {
action: "append",
data: {
values: [[todayDate, currentTime, ""]],
},
spreadsheetId: spreadsheetId,
message: `Creating new Check-In record for ${todayDate}`,
},
},
];
}
} else if (direction === "Check-Out") {
if (foundRowIndex > 0) {
// 找到今天的記錄，直接更新 Check-Out 欄位 (無論有無舊資料都會覆蓋)
return [
{
json: {
action: "update",
rowIndex: foundRowIndex,
range: `Worklog!C${foundRowIndex}`, // C 欄是 Check-Out
values: [[currentTime]],
spreadsheetId: spreadsheetId,
message: `Updating/Overwriting Check-Out for ${todayDate} at row ${foundRowIndex}`,
},
},
];
} else {
// 沒找到今天的記錄，新增一筆只有 Check-Out 的記錄
return [
{
json: {
action: "append",
data: {
values: [[todayDate, "", currentTime]],
},
spreadsheetId: spreadsheetId,
message: `Creating new Check-Out record for ${todayDate}`,
},
},
];
}
} else {
return [
{
json: {
action: "error",
message: "Invalid direction. Use Check-In or Check-Out.",
spreadsheetId: spreadsheetId,
},
},
];
}
現在，我們有了 append 和 update 兩種指令，需要讓流程根據指令執行對應的操作
下個節點再兵分兩路，都選「If」
第一條命名為「Determine Final Action」
填入
{
{
$json.action;
}
}
append;
第二條「If」路線命名為「Should Update?」，填入
{
{
$json.action;
}
}
update;
現在的畫布會長這樣
接著在「Determine Final Action」的下個節點選擇「HTTP Request」
設定為「POST」請求
「URL」
https://sheets.googleapis.com/v4/spreadsheets/{{ $json.spreadsheetId }}/values/Worklog:append?valueInputOption=USER_ENTERED
憑證的選擇跟剛才一樣
接著把「Send Body」開啟，內容填寫
values;
{
{
$json.data.values;
}
}
接著到剛剛的「Should Update?」設定下個節點，同樣是「HTTP Request」
節點可以命名為「Update Check-Out via API」
請求設定為「PUT」
「URL」
https://sheets.googleapis.com/v4/spreadsheets/{{ $json.spreadsheetId }}/values/{{ $json.range }}?valueInputOption=USER_ENTERED
憑證選擇相同的設定
「Send Body」同樣開啟，內容填寫
values;
{
{
$json.values;
}
}
記得到右上角的設定裡面，把時區改為台灣時間
最後讓初始節點留下「Webhook」，就可以拿手機起來做測試惹
看到試算表都有寫入時間，代表成功囉
底下附上這次流程的 JSON 檔案
{
"name": "自動記錄打卡時間",
"nodes": [
{
"parameters": {
"jsCode": "return [\n {\n json: {\n empty: items.length == 1 && Object.keys(items[0].json).length == 0,\n },\n },\n];\n"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [448, -144],
"id": "217d2210-7364-4873-b1e0-08247718b9fb",
"name": "Return if Null"
},
{
"parameters": {
"resource": "spreadsheet",
"title": "WorkTimeTracking",
"sheetsUi": {
"sheetValues": [
{
"title": "Worklog"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.6,
"position": [880, -272],
"id": "08e69bb8-71c4-410f-9ef5-459e08533322",
"name": "Create spreadsheet",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "YOUR_GOOGLE_SHEETS_CREDENTIAL_ID",
"name": "Your Google Sheets Account"
}
}
},
{
"parameters": {
"jsCode": "return [{\n json: {\n Date: \"\",\n \"Check-In\": \"\",\n \"Check-Out\": \"\"\n }\n}];"
},
"id": "generate-header-data-fixed",
"name": "Generate Header Data",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [1088, -272]
},
{
"parameters": {
"operation": "append",
"documentId": {
"__rl": true,
"value": "={{ $('Create spreadsheet').item.json.spreadsheetId }}",
"mode": "id"
},
"sheetName": {
"__rl": true,
"value": "Worklog",
"mode": "name"
},
"columns": {
"mappingMode": "autoMapInputData",
"value": {},
"matchingColumns": [],
"schema": [
{
"id": "Date",
"displayName": "Date",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Check-In",
"displayName": "Check-In",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Check-Out",
"displayName": "Check-Out",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
}
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {
"cellFormat": "USER_ENTERED"
}
},
"id": "add-headers-simple",
"name": "Add Headers",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.6,
"position": [1312, -272],
"credentials": {
"googleSheetsOAuth2Api": {
"id": "YOUR_GOOGLE_SHEETS_CREDENTIAL_ID",
"name": "Your Google Sheets Account"
}
}
},
{
"parameters": {
"path": "your-webhook-path",
"options": {}
},
"type": "n8n-nodes-base.webhook",
"typeVersion": 2.1,
"position": [0, -64],
"id": "9d0c71fa-9cc9-4335-897d-8e6e9b82731d",
"name": "Webhook",
"webhookId": "YOUR_WEBHOOK_ID"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "5860c006-af45-4884-af63-c7f1afa2447b",
"leftValue": "={{$json[\"empty\"]}}",
"rightValue": "",
"operator": {
"type": "boolean",
"operation": "true",
"singleValue": true
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [672, -144],
"id": "8ff4da35-0f2b-4d6b-ac7f-9af62a7e4b3f",
"name": "Doesn't exist?"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "ac6f1aa1-7a34-4a33-a7ca-22c2c7ba6366",
"leftValue": "={{$json.action}}",
"rightValue": "read_first",
"operator": {
"type": "string",
"operation": "equals",
"name": "filter.operator.equals"
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [432, 192],
"id": "6989cc48-2514-452f-9c05-7f76e1aa8240",
"name": "Should Read Data?"
},
{
"parameters": {
"url": "=https://sheets.googleapis.com/v4/spreadsheets/{{ $json.spreadsheetId }}/values/Worklog",
"authentication": "predefinedCredentialType",
"nodeCredentialType": "googleSheetsOAuth2Api",
"options": {}
},
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.2,
"position": [656, 160],
"id": "a5923022-eb71-43c9-9eb7-679849c2ca0b",
"name": "Read Sheet Data",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "YOUR_GOOGLE_SHEETS_CREDENTIAL_ID",
"name": "Your Google Sheets Account"
}
}
},
{
"parameters": {
"jsCode": "// 完整的打卡處理邏輯，統一先讀取資料再決定動作\nconst basicData = $('Set Basic Data').item.json;\nconst todayDate = basicData.todayDate;\nconst direction = basicData.direction;\nconst currentTime = basicData.currentTime;\nconst spreadsheetId = basicData.spreadsheetId;\n\n// 無論是 Check-In 還是 Check-Out，都需要先讀取資料來檢查今天是否已有記錄\nreturn [{\n json: {\n action: 'read_first',\n todayDate: todayDate,\n currentTime: currentTime,\n direction: direction,\n spreadsheetId: spreadsheetId,\n message: `Processing ${direction} for ${todayDate}`\n }\n}];"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [224, 192],
"id": "236340ba-f466-424b-a0ef-be7e45a52fb6",
"name": "Complete Attendance Handler"
},
{
"parameters": {
"jsCode": "// 處理所有打卡邏輯（Check-In 和 Check-Out）\nconst basicData = $('Complete Attendance Handler').item.json;\nconst sheetData = $input.item.json;\nconst todayDate = basicData.todayDate;\nconst currentTime = basicData.currentTime;\nconst direction = basicData.direction;\nconst spreadsheetId = basicData.spreadsheetId;\n\nconst values = sheetData.values || [];\nlet foundRowIndex = -1;\nlet foundRow = null;\n\n// 尋找今天的記錄（從第二行開始，跳過標題）\nfor (let i = 1; i < values.length; i++) {\n const row = values[i];\n if (row[0] === todayDate) { // Date 欄位\n foundRowIndex = i + 1; // Google Sheets 行號從 1 開始\n foundRow = row;\n break;\n }\n}\n\nif (direction === 'Check-In') {\n if (foundRowIndex > 0) {\n // 今天已經有 Check-In 記錄，不執行任何動作\n return [{\n json: {\n action: 'skip',\n message: `Check-In already exists for ${todayDate}. Skipping.`,\n spreadsheetId: spreadsheetId\n }\n }];\n } else {\n // 今天沒有記錄，新增 Check-In 記錄\n return [{\n json: {\n action: 'append',\n data: {\n values: [[todayDate, currentTime, '']]\n },\n spreadsheetId: spreadsheetId,\n message: `Creating new Check-In record for ${todayDate}`\n }\n }];\n }\n} else if (direction === 'Check-Out') {\n if (foundRowIndex > 0) {\n // 找到今天的記錄，直接更新 Check-Out 欄位 (無論有無舊資料都會覆蓋)\n return [{\n json: {\n action: 'update',\n rowIndex: foundRowIndex,\n range: `Worklog!C${foundRowIndex}`, // C 欄是 Check-Out\n values: [[currentTime]],\n spreadsheetId: spreadsheetId,\n message: `Updating/Overwriting Check-Out for ${todayDate} at row ${foundRowIndex}`\n }\n }];\n } else {\n // 沒找到今天的記錄，新增一筆只有 Check-Out 的記錄\n return [{\n json: {\n action: 'append',\n data: {\n values: [[todayDate, '', currentTime]]\n },\n spreadsheetId: spreadsheetId,\n message: `Creating new Check-Out record for ${todayDate}`\n }\n }];\n }\n} else {\n return [{\n json: {\n action: 'error',\n message: 'Invalid direction. Use Check-In or Check-Out.',\n spreadsheetId: spreadsheetId\n }\n }];\n}"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [864, 160],
"id": "250d32a9-07c9-4cb9-bd36-b51ff4983a4f",
"name": "Process Attendance"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "6784a186-e2b8-4d8b-9c03-45b4223bf1f2",
"leftValue": "={{$json.action}}",
"rightValue": "append",
"operator": {
"type": "string",
"operation": "equals",
"name": "filter.operator.equals"
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [1072, 144],
"id": "d6158010-19e8-421c-9386-a1ed7bd32b63",
"name": "Determine Final Action"
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "e281ee76-ad12-4602-9dc9-8e12afc21b3c",
"leftValue": "={{$json.action}}",
"rightValue": "update",
"operator": {
"type": "string",
"operation": "equals",
"name": "filter.operator.equals"
}
}
],
"combinator": "and"
},
"options": {}
},
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [1072, 304],
"id": "721fb5fe-5297-4c1f-8351-ffc93b2c166a",
"name": "Should Update?"
},
{
"parameters": {
"method": "POST",
"url": "=https://sheets.googleapis.com/v4/spreadsheets/{{ $json.spreadsheetId }}/values/Worklog:append?valueInputOption=USER_ENTERED",
"authentication": "predefinedCredentialType",
"nodeCredentialType": "googleSheetsOAuth2Api",
"sendBody": true,
"bodyParameters": {
"parameters": [
{
"name": "values",
"value": "={{ $json.data.values }}"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.2,
"position": [1296, 64],
"id": "90c769c0-61f3-4254-8993-685dafaae302",
"name": "Append Record via API",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "YOUR_GOOGLE_SHEETS_CREDENTIAL_ID",
"name": "Your Google Sheets Account"
}
}
},
{
"parameters": {
"method": "PUT",
"url": "=https://sheets.googleapis.com/v4/spreadsheets/{{ $json.spreadsheetId }}/values/{{ $json.range }}?valueInputOption=USER_ENTERED",
"authentication": "predefinedCredentialType",
"nodeCredentialType": "googleSheetsOAuth2Api",
"sendBody": true,
"bodyParameters": {
"parameters": [
{
"name": "values",
"value": "={{ $json.values }}"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 4.2,
"position": [1296, 224],
"id": "c32e3058-7852-4828-9f16-1edad3ab6199",
"name": "Update Check-Out via API",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "YOUR_GOOGLE_SHEETS_CREDENTIAL_ID",
"name": "Your Google Sheets Account"
}
}
},
{
"parameters": {
"mode": "raw",
"jsonOutput": "={\n \"todayDate\": \"{{ $now.format('yyyy-MM-dd') }}\",\n \"currentTime\": \"{{ $now.format('HH:mm:ss') }}\",\n \"direction\": \"{{ $('Webhook').item.json.headers.direction ? $('Webhook').item.json.headers.direction : 'Check-In'}}\",\n \"spreadsheetId\": \"{{ $('Search files and folders').item.json.id }}\"\n}\n",
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [1088, -64],
"id": "5b2a7c91-0734-4239-9eda-42f8bcfc9444",
"name": "Set Basic Data"
},
{
"parameters": {
"resource": "fileFolder",
"queryString": "WorkTimeTracking",
"returnAll": true,
"filter": {},
"options": {}
},
"type": "n8n-nodes-base.googleDrive",
"typeVersion": 3,
"position": [224, -144],
"id": "874f71f2-35ab-466b-adc4-c5ce69471ebe",
"name": "Search files and folders",
"alwaysOutputData": true,
"credentials": {
"googleDriveOAuth2Api": {
"id": "YOUR_GOOGLE_DRIVE_CREDENTIAL_ID",
"name": "Your Google Drive Account"
}
}
}
],
"pinData": {},
"connections": {
"Return if Null": {
"main": [
[
{
"node": "Doesn't exist?",
"type": "main",
"index": 0
}
]
]
},
"Create spreadsheet": {
"main": [
[
{
"node": "Generate Header Data",
"type": "main",
"index": 0
}
]
]
},
"Generate Header Data": {
"main": [
[
{
"node": "Add Headers",
"type": "main",
"index": 0
}
]
]
},
"Webhook": {
"main": [
[
{
"node": "Search files and folders",
"type": "main",
"index": 0
}
]
]
},
"Add Headers": {
"main": [
[
{
"node": "Set Basic Data",
"type": "main",
"index": 0
}
]
]
},
"Doesn't exist?": {
"main": [
[
{
"node": "Create spreadsheet",
"type": "main",
"index": 0
}
],
[
{
"node": "Set Basic Data",
"type": "main",
"index": 0
}
]
]
},
"Should Read Data?": {
"main": [
[
{
"node": "Read Sheet Data",
"type": "main",
"index": 0
}
]
]
},
"Read Sheet Data": {
"main": [
[
{
"node": "Process Attendance",
"type": "main",
"index": 0
}
]
]
},
"Complete Attendance Handler": {
"main": [
[
{
"node": "Should Read Data?",
"type": "main",
"index": 0
}
]
]
},
"Process Attendance": {
"main": [
[
{
"node": "Determine Final Action",
"type": "main",
"index": 0
},
{
"node": "Should Update?",
"type": "main",
"index": 0
}
]
]
},
"Determine Final Action": {
"main": [
[
{
"node": "Append Record via API",
"type": "main",
"index": 0
}
]
]
},
"Should Update?": {
"main": [
[
{
"node": "Update Check-Out via API",
"type": "main",
"index": 0
}
]
]
},
"Set Basic Data": {
"main": [
[
{
"node": "Complete Attendance Handler",
"type": "main",
"index": 0
}
]
]
},
"Search files and folders": {
"main": [
[
{
"node": "Return if Null",
"type": "main",
"index": 0
}
]
]
}
},
"active": true,
"settings": {
"executionOrder": "v1",
"callerPolicy": "workflowsFromSameOwner"
},
"versionId": "YOUR_WORKFLOW_VERSION_ID",
"meta": {
"templateCredsSetupCompleted": true,
"instanceId": "YOUR_INSTANCE_ID"
},
"id": "YOUR_WORKFLOW_ID",
"tags": []
}
iThome鐵人賽
看影片追技術