Node.js自动化清洗Google Sheets数据：服务账号与API实战-平芜编程栈

1. 项目概述：当老旧数据遇上现代自动化

接手一个社区食品储藏室的老旧数据库，面对成百上千条过时、混乱的访客记录，手动逐条清理不仅耗时费力，还容易出错。这正是我最近遇到的一个真实场景。对于许多非营利组织或小型社区服务团体来说，技术资源有限，但数据维护的需求却非常迫切。Google Sheets 因其易用性和协作性，常常成为这类组织的首选数据存储工具，但随之而来的数据清洗、格式统一和定期更新就成了老大难问题。

这个项目的核心，就是利用 Node.js 和 Google Sheets API，构建一个轻量级但功能强大的自动化脚本。它能够模拟一个“虚拟员工”，自动登录表格、读取数据、根据预设规则（比如日期有效性、字段完整性）判断记录是否有效，然后执行清理或标记操作。整个过程无需人工干预，可以设置为定时任务，比如每月自动运行一次，确保数据库始终反映当前情况。

对于开发者而言，这不仅仅是一个关于特定 API 的教程，更是一次典型的“外部服务集成+数据处理”实战。你将掌握如何让 Node.js 应用安全地与云端服务（Google Sheets）对话，如何设计稳健的数据处理逻辑，以及如何将一次性的脚本转化为可重复使用的自动化工具。无论你是想为自己团队优化报表流程，还是为客户解决类似的数据痛点，这里面的思路和代码都有很高的参考价值。

2. 核心思路与技术选型解析

2.1 为什么选择 Node.js + Google Sheets API 这个组合？

面对数据清洗任务，可选方案很多，比如 Python 的 pandas、专门的 ETL 工具，甚至直接用 Excel 宏。选择 Node.js 搭配 Google Sheets API，是基于以下几个关键考量：

首先，生态与效率。Node.js 的异步非阻塞 I/O 模型，非常适合处理像网络请求（访问 API）这类 I/O 密集型的操作。Google Sheets API v4 是基于 HTTP 的 RESTful API，Node.js 的googleapis库对其有良好的封装，能让我们以更少的代码处理认证、请求和错误重试。对于需要处理数百行、可能涉及多次 API 调用的场景，这种效率优势很明显。

其次，无缝集成现代 Web 开发栈。如果这个自动化脚本未来需要扩展成一个带有简单 Web 界面（供管理员触发任务或查看报告）的小型服务，Node.js 的全栈能力（如 Express.js）能让扩展变得非常平滑。JavaScript 一门语言贯穿前后端，降低了技术栈的复杂度。

再者，服务账号认证的天然优势。与需要用户交互的 OAuth 2.0 授权相比，服务账号（Service Account）认证允许服务器端应用在无人值守的情况下访问 Google 服务。这正是自动化任务（如定时脚本）所需要的。我们创建一个代表“机器人”的服务账号，将其邮箱地址添加到目标 Google Sheets 的共享编辑者中，脚本就能以这个“机器人”的身份安全地操作表格，无需存储或处理任何真实用户的登录凭证。

2.2 整体架构与数据流设计

整个自动化处理流程可以抽象为一个清晰的数据管道：

认证与连接：脚本使用服务账号的密钥文件，向 Google 认证服务器证明自己的身份，获取一个短期有效的访问令牌（Access Token）。
数据提取：携带令牌，向 Google Sheets API 发起请求，读取指定电子表格和单元格范围内的所有数据。API 会以 JSON 数组的形式返回行和列的值。
核心处理（清洗逻辑）：这是脚本的大脑。我们会在内存中遍历上一步得到的数据数组。针对每一行（代表一条记录），应用我们定义的业务规则。例如：
- 过期检查：解析“最后认证日期”列，与当前日期比较，标记超过一定期限（如两年）的记录为“过期”。
- 完整性校验：检查关键字段（如姓名、联系方式）是否为空或格式明显错误。
- 重复项检测：基于某些字段（如身份证号、邮箱）进行比对，标记或合并疑似重复的记录。
数据回写与报告：根据清洗逻辑的判断结果，脚本可以执行多种操作：
- 直接清理：将过期或无效的记录从原始数据范围中删除（使用 API 的clear或batchUpdate请求）。
- 标记而非删除：更安全的做法是在表格中新增一列（如“状态”），将记录标记为“有效”、“待核实”、“已过期”等。这保留了原始数据，便于审计。
- 生成日志：脚本在控制台或本地文件输出本次运行的摘要，例如“共处理 350 条记录，其中标记过期 45 条，修复格式错误 12 条”。
调度与执行：清洗后的脚本文件（index.js）可以通过系统的定时任务工具来定期执行。在 Linux/macOS 上可以用cron，在 Windows 上可以用“任务计划程序”。只需配置一条命令，如cd /path/to/your/project && node index.js，即可实现全自动运行。

注意：在设计和执行删除操作前，务必进行备份或先在测试表格上验证逻辑。直接操作生产数据存在风险。建议前期采用“标记”策略，稳定运行一段时间后再考虑物理删除。

3. 环境准备与服务账号配置详解

3.1 本地开发环境搭建

工欲善其事，必先利其器。首先确保你的电脑上已经安装了 Node.js。打开终端（Windows 的 PowerShell 或 CMD，macOS/Linux 的 Terminal），输入node --version和npm --version。如果能看到版本号（建议 Node.js 版本在 16 以上），说明环境已就绪。如果没有，请前往 Node.js 官网下载 LTS（长期支持）版本进行安装。

接下来为项目创建一个独立的工作空间，这能避免依赖包污染全局环境，也便于管理。在终端中执行以下命令：

# 创建一个新的项目目录，名字可以自定，比如 `sheets-cleaner` mkdir sheets-cleaner # 进入该目录 cd sheets-cleaner # 初始化一个新的 Node.js 项目，这会生成 package.json 文件 npm init -y

现在，安装本项目最核心的依赖库——Google APIs Node.js 客户端库。这个库封装了访问包括 Sheets、Drive 在内的所有 Google 服务的接口。

npm install googleapis

这里没有指定@39这样的版本，因为我们会安装当前稳定版。安装完成后，你的package.json文件的dependencies部分应该包含了googleapis。

3.2 创建 Google Cloud 项目与服务账号

这是整个流程中最关键也最容易出错的一步，我们一步步来。

访问 Google Cloud Console：打开浏览器，访问 Google Cloud Console 。使用你的 Google 账号登录。
创建新项目：在页面顶部的项目选择器旁边（通常显示当前项目名称），点击下拉菜单，然后点击“新建项目”。给你的项目起一个易于识别的名字，例如 “Sheets Automation Script”。点击“创建”。创建完成后，确保在控制台左上角已选中这个新项目。
启用所需 API：我们需要启用两个 API：Google Sheets API 和 Google Drive API（因为访问 Sheets 文件本质上是通过 Drive 的权限）。在控制台顶部的搜索栏中，搜索“Google Sheets API”，进入该 API 页面，点击蓝色的“启用”按钮。同样地，搜索并启用“Google Drive API”。
创建服务账号：
- 在左侧导航栏，依次点击“IAM 和管理” -> “服务账号”。
- 点击页面顶部的“+ 创建服务账号”。
- 步骤 1：输入服务账号名称，例如sheets-automation-bot。系统会自动生成一个服务账号 ID（一个邮箱地址）。描述可以选填，如“用于自动化处理 Google Sheets 数据”。
- 步骤 2（授予权限）：点击“角色”下拉框，选择“基本角色” -> “编辑者”。这个角色允许服务账号在项目内以及它被共享的资源（如我们的表格）上进行读写操作。点击“继续”。
- 步骤 3（可选）：这里可以授予用户访问此服务账号的权限，对于我们这个私有脚本项目，无需填写，直接点击“完成”。
生成并下载密钥文件：
- 在服务账号列表中，找到刚刚创建的账号，点击其邮箱地址进入详情页。
- 切换到“密钥”标签页。
- 点击“添加密钥” -> “创建新密钥”。
- 密钥类型选择“JSON”，然后点击“创建”。浏览器会自动下载一个 JSON 格式的密钥文件（如sheets-automation-bot-abcdef123456.json）。请务必妥善保管此文件，它相当于这个服务账号的“密码”，一旦泄露他人可凭此文件以该账号身份操作资源。

3.3 配置表格权限与定位资源

拿到密钥文件后，我们需要授权这个“机器人”访问我们的目标表格。

找到服务账号邮箱：用文本编辑器打开下载的 JSON 密钥文件。找到client_email字段，其值类似于sheets-automation-bot@your-project-id.iam.gserviceaccount.com。复制这个邮箱地址。
分享 Google Sheets：打开你需要自动化的那个 Google Sheets 文件。点击右上角的“共享”按钮。在分享对话框中，粘贴上一步复制的服务账号邮箱。将权限设置为“编辑者”（因为我们可能需要写入数据），然后点击“发送”或“共享”。
获取表格 ID：表格的 ID 隐藏在它的 URL 中。打开你的表格，看浏览器地址栏，URL 格式通常为https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=SHEET_ID。其中SPREADSHEET_ID就是我们需要的那一串长字符，它位于/d/之后和/edit之前。复制这个 ID。

现在，将下载的密钥 JSON 文件移动到你的项目目录sheets-cleaner中。为了在代码中引用方便，可以将其重命名为一个简单的名字，比如credentials.json。

4. 核心代码实现与数据清洗逻辑

4.1 初始化认证与 API 客户端

在项目根目录下，创建主脚本文件index.js。我们首先引入依赖并建立与 Google 服务的连接。

const { google } = require('googleapis'); const path = require('path'); // 1. 定义认证范围（我们需要读写 Sheets 的权限） const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; // 2. 加载服务账号密钥文件 // 假设你的 credentials.json 文件放在项目根目录 const KEYFILEPATH = path.join(__dirname, 'credentials.json'); // 3. 创建 JWT（JSON Web Token）认证客户端 const auth = new google.auth.GoogleAuth({ keyFile: KEYFILEPATH, scopes: SCOPES, }); // 4. 创建 Google Sheets API 的客户端实例 const sheets = google.sheets({ version: 'v4', auth }); // 5. 配置你的表格信息 const SPREADSHEET_ID = '你的_表格_ID_粘贴在这里'; // 替换为你的实际 ID const TARGET_SHEET_NAME = 'Sheet1'; // 你要操作的工作表名称，默认为第一个工作表，可按需修改 const DATA_RANGE = 'A:Z'; // 要读取的数据范围，A:Z 表示读取所有列。可根据实际数据量调整，如'A1:F1000' async function main() { try { // 测试连接：获取表格基本信息 const spreadsheetInfo = await sheets.spreadsheets.get({ spreadsheetId: SPREADSHEET_ID, }); console.log(`成功连接到表格: ${spreadsheetInfo.data.properties.title}`); // 接下来，我们将在这里添加数据读取和清洗逻辑 await readAndProcessData(); } catch (error) { console.error('执行过程中发生错误:', error.message); // 更详细的错误信息有助于调试 if (error.response) { console.error('API 错误详情:', error.response.data); } } } // 启动脚本 main();

运行node index.js，如果控制台成功打印出你的表格名称，恭喜你，最难的认证和连接部分已经打通了。

4.2 实现数据读取与遍历清洗

现在，我们在main函数中调用并实现readAndProcessData函数。这个函数负责读取数据、应用业务逻辑，并决定如何更新表格。

async function readAndProcessData() { // 1. 读取原始数据 const response = await sheets.spreadsheets.values.get({ spreadsheetId: SPREADSHEET_ID, range: `${TARGET_SHEET_NAME}!${DATA_RANGE}`, // 例如 'Sheet1!A:Z' }); const rows = response.data.values; if (!rows || rows.length === 0) { console.log('未在指定范围找到数据。'); return; } console.log(`共读取到 ${rows.length} 行数据。`); // 假设第一行是表头 const headers = rows[0]; console.log('表头:', headers); // 2. 定义清洗逻辑和准备更新数据 const updates = []; // 用于存储需要更新的单元格请求 const rowsToClear = []; // 用于存储需要整行清除的行号（谨慎使用） let expiredCount = 0; let invalidCount = 0; // 确定关键列的索引。更健壮的做法是根据表头名查找。 // 假设表头为: ['姓名', '电话', '最后到访日期', '状态', ...] const nameIndex = headers.indexOf('姓名'); const dateIndex = headers.indexOf('最后到访日期'); const statusIndex = headers.indexOf('状态') !== -1 ? headers.indexOf('状态') : headers.length; // 如果没状态列，我们加到末尾 // 是否需要新增“状态”列？ const needsStatusColumn = headers.indexOf('状态') === -1; if (needsStatusColumn) { console.log('检测到无“状态”列，将在处理完成后添加。'); } // 从第二行开始遍历数据（跳过表头） for (let i = 1; i < rows.length; i++) { const row = rows[i]; const rowNumber = i + 1; // Google Sheets 行号从1开始，且包含表头行 // 初始化一个与表头等长的数组，用于构建更新行，先填充空字符串 let updatedRow = new Array(headers.length).fill(''); // 规则1: 检查关键信息缺失 if (!row[nameIndex] || row[nameIndex].trim() === '') { // 姓名为空，标记为无效 console.log(`第 ${rowNumber} 行: 姓名为空，标记为“信息无效”。`); if (statusIndex < row.length) { row[statusIndex] = '信息无效'; } else { // 如果原行没有状态列，则在 updatedRow 对应位置标记 updatedRow[statusIndex] = '信息无效'; } invalidCount++; // 将原行其他数据复制到 updatedRow 对应位置，避免覆盖 for (let j = 0; j < row.length; j++) { updatedRow[j] = row[j]; } updates.push({ range: `${TARGET_SHEET_NAME}!A${rowNumber}:${String.fromCharCode(65 + headers.length - 1)}${rowNumber}`, values: [updatedRow], }); continue; // 跳过后续日期检查 } // 规则2: 检查最后到访日期是否过期（假设超过730天即2年为过期） if (row[dateIndex]) { const lastVisitStr = row[dateIndex].trim(); let lastVisitDate; // 尝试解析日期，这里假设格式为 'YYYY-MM-DD' 或 'MM/DD/YYYY' if (lastVisitStr.match(/^\d{4}-\d{2}-\d{2}$/)) { lastVisitDate = new Date(lastVisitStr); } else if (lastVisitStr.match(/^\d{1,2}\/\d{1,2}\/\d{4}$/)) { lastVisitDate = new Date(lastVisitStr); } else { // 无法识别的日期格式，标记为需核实 console.log(`第 ${rowNumber} 行: 日期格式无法识别 "${lastVisitStr}"，标记为“需核实”。`); updatedRow[statusIndex] = '需核实'; for (let j = 0; j < row.length; j++) updatedRow[j] = row[j]; updates.push({ range: `${TARGET_SHEET_NAME}!A${rowNumber}:${String.fromCharCode(65 + headers.length - 1)}${rowNumber}`, values: [updatedRow] }); continue; } const today = new Date(); const daysDiff = Math.floor((today - lastVisitDate) / (1000 * 60 * 60 * 24)); if (daysDiff > 730) { console.log(`第 ${rowNumber} 行: 最后到访于 ${lastVisitStr}，已超过730天，标记为“已过期”。`); // 标记为过期，而非直接删除 if (statusIndex < row.length) { row[statusIndex] = '已过期'; } else { updatedRow[statusIndex] = '已过期'; for (let j = 0; j < row.length; j++) updatedRow[j] = row[j]; } expiredCount++; // 构建更新请求 const targetRange = `${TARGET_SHEET_NAME}!A${rowNumber}:${String.fromCharCode(65 + headers.length - 1)}${rowNumber}`; const valuesToUpdate = statusIndex < row.length ? [row] : [updatedRow]; updates.push({ range: targetRange, values: valuesToUpdate }); } else { // 日期有效，可以标记为“有效”或不做处理 // 这里选择标记有效，便于区分 if (statusIndex >= row.length || row[statusIndex] !== '有效') { console.log(`第 ${rowNumber} 行: 记录有效。`); if (statusIndex < row.length) { row[statusIndex] = '有效'; } else { updatedRow[statusIndex] = '有效'; for (let j = 0; j < row.length; j++) updatedRow[j] = row[j]; } updates.push({ range: `${TARGET_SHEET_NAME}!A${rowNumber}:${String.fromCharCode(65 + headers.length - 1)}${rowNumber}`, values: statusIndex < row.length ? [row] : [updatedRow] }); } } } else { // 日期为空 console.log(`第 ${rowNumber} 行: 最后到访日期为空，标记为“需核实”。`); updatedRow[statusIndex] = '需核实'; for (let j = 0; j < row.length; j++) updatedRow[j] = row[j]; updates.push({ range: `${TARGET_SHEET_NAME}!A${rowNumber}:${String.fromCharCode(65 + headers.length - 1)}${rowNumber}`, values: [updatedRow] }); } } // 3. 执行批量更新（如果有需要更新的内容） if (updates.length > 0) { console.log(`准备批量更新 ${updates.length} 处数据...`); // 如果之前没有状态列，需要先在表头添加 if (needsStatusColumn) { const headerUpdate = { range: `${TARGET_SHEET_NAME}!${String.fromCharCode(65 + headers.length)}1`, values: [['状态']], }; // 注意：插入新列可能会改变后续更新的列索引，这里为简化，我们在所有更新后添加表头。 // 更严谨的做法是先添加表头，然后重新计算所有更新的范围。 // 此处采用一个变通：先更新数据（状态值会写在最后一列），再更新表头。 // 但更推荐的做法是使用 `sheets.spreadsheets.batchUpdate` 的 `appendDimension` 请求来插入列。 // 为了逻辑清晰，本例假设原表有“状态”列。若无，建议先手动添加或使用更复杂的列插入API。 console.log('提示：检测到需要添加“状态”列，建议先在表格中手动添加该列，或使用 sheets.spreadsheets.batchUpdate 的 insertDimension 请求。'); // 本例中，我们假设状态列已存在或已手动添加，继续执行数据更新。 } const updateRequests = updates.map(update => ({ updateCells: { range: { sheetId: await getSheetId(TARGET_SHEET_NAME), // 需要先获取工作表ID的函数 startRowIndex: update.range.match(/!A(\d+)/)[1] - 1, endRowIndex: update.range.match(/!A(\d+)/)[1], startColumnIndex: columnLetterToIndex(update.range.split('!')[1].split(':')[0].replace(/\d/g, '')), endColumnIndex: columnLetterToIndex(update.range.split('!')[1].split(':')[1].replace(/\d/g, '')) + 1, }, rows: [{ values: update.values[0].map(cell => ({ userEnteredValue: { stringValue: cell } })) }], fields: 'userEnteredValue', }, })); // 使用 batchUpdate 进行更精细的单元格更新（可选，比 values.update 更复杂但功能更强） // 对于初学者，使用 values.batchUpdate 更简单： const batchUpdateRequest = { spreadsheetId: SPREADSHEET_ID, resource: { valueInputOption: 'USER_ENTERED', // 或 'RAW' data: updates, // updates数组本身符合 values.batchUpdate 的 data 格式 }, }; try { const updateResponse = await sheets.spreadsheets.values.batchUpdate(batchUpdateRequest); console.log(`成功更新了 ${updateResponse.data.totalUpdatedCells} 个单元格。`); } catch (updateError) { console.error('批量更新失败:', updateError.message); } } else { console.log('没有需要更新的数据。'); } console.log(`处理完成。统计：标记过期 ${expiredCount} 条，标记信息无效 ${invalidCount} 条。`); } // 辅助函数：根据工作表名获取其ID（用于batchUpdate中的range） async function getSheetId(sheetName) { const spreadsheet = await sheets.spreadsheets.get({ spreadsheetId: SPREADSHEET_ID }); const sheet = spreadsheet.data.sheets.find(s => s.properties.title === sheetName); return sheet ? sheet.properties.sheetId : null; } // 辅助函数：将列字母（如'A', 'AB'）转换为索引（0-based） function columnLetterToIndex(letter) { let index = 0; for (let i = 0; i < letter.length; i++) { index = index * 26 + (letter.charCodeAt(i) - 65 + 1); } return index - 1; }

这段代码实现了一个相对完整的清洗流程：读取数据、逐行应用业务规则（检查姓名和日期）、并根据结果在“状态”列打上标记。它采用了标记而非直接删除的安全策略，并通过批量更新 API 来减少网络请求次数。

4.3 扩展：实现定时自动执行

脚本写好了，我们当然不希望每次都手动运行。在 Linux 或 macOS 系统上，可以使用cron定时任务。首先，确保你的脚本在命令行中能独立运行成功：cd /完整/路径/到/sheets-cleaner && node index.js > /tmp/sheets-cleaner.log 2>&1。

然后，编辑当前用户的 cron 任务表：crontab -e。在文件末尾添加一行，例如，设定每天凌晨 2 点执行：

0 2 * * * cd /完整/路径/到/sheets-cleaner && /usr/local/bin/node index.js >> /完整/路径/到/sheets-cleaner/automation.log 2>&1

在 Windows 系统上，可以使用“任务计划程序”。创建一个基本任务，设置触发器（如每日），操作是“启动程序”，程序或脚本填写node.exe的完整路径（如C:\Program Files\nodejs\node.exe），参数填写你的脚本完整路径（如D:\projects\sheets-cleaner\index.js），起始于填写你的项目目录。

重要心得：在将脚本投入定时任务前，务必进行充分的测试。建议创建一个测试表格，复制一部分生产数据过去，用脚本跑几遍。观察日志输出和表格变化是否符合预期。特别是日期解析逻辑，不同地区、不同来源的数据格式可能千奇百怪，需要增加更多的格式判断和容错处理，或者在清洗前先对数据格式进行标准化。

5. 常见问题排查与性能优化技巧

5.1 认证与权限类问题

错误：Invalid grant: account not found
- 原因：最常见的原因是服务账号的邮箱没有被正确添加到 Google Sheets 的共享成员中。或者，你复制的client_email有误。
- 排查：1. 双重检查 JSON 密钥文件中的client_email。2. 打开 Google Sheets，点击“共享”，确认该邮箱地址在成员列表中，且权限至少是“查看者”（对于读操作）或“编辑者”（对于写操作）。3. 有时共享后需要几分钟才会生效。
错误：The caller does not have permission或API not enabled
- 原因：Google Cloud 项目中对应的 API（Sheets API 或 Drive API）没有启用，或者服务账号所在的项目没有启用结算功能（某些高级 API 需要）。
- 排查：1. 回到 Google Cloud Console，在“API 和服务”->“仪表板”中，确认“Google Sheets API”和“Google Drive API”显示为“已启用”。2. 对于简单的读写操作，通常不需要启用结算，但如果你在免费配额用尽后收到此错误，可能需要关联结算账户。
错误：Quota exceeded for quota metric
- 原因：Google Sheets API 有每日请求次数配额（免费 tier 通常足够个人使用）。如果你的脚本设计不当，在循环中为每个单元格都发起一个 API 请求，很容易触发限制。
- 解决：务必使用批量操作。如上文代码所示，使用spreadsheets.values.batchUpdate一次性提交所有更新，而不是在循环内调用spreadsheets.values.update。将读取操作也尽量合并，一次读取所需范围的所有数据。

5.2 数据处理与脚本逻辑问题

日期解析错误或结果不符合预期
- 原因：JavaScript 的Date对象对输入字符串的解析因浏览器/环境而异，不可靠。
- 解决：永远不要直接使用new Date(string)来解析非 ISO 8601 格式的字符串。推荐使用专门的库，如moment.js（已进入维护模式）或其现代替代品day.js或date-fns。例如，安装dayjs(npm install dayjs) 和自定义解析插件，可以稳定地处理MM/DD/YYYY、DD-MM-YYYY等多种格式。
```
const dayjs = require('dayjs'); const customParseFormat = require('dayjs/plugin/customParseFormat'); dayjs.extend(customParseFormat); const dateStr = '04/15/2023'; const parsedDate = dayjs(dateStr, 'MM/DD/YYYY'); if (parsedDate.isValid()) { // 进行日期比较 const isExpired = dayjs().diff(parsedDate, 'day') > 730; }
```
脚本处理大量数据时速度慢或内存占用高
- 原因：一次性读取整个超大范围（如A:Z）的数据到内存中，如果表格有数万行，会导致性能问题。
- 优化：
  1. 分页读取：利用range参数分批读取数据，例如每次读取 1000 行。这需要更复杂的循环逻辑。
  2. 精确限定范围：如果数据量确实大，尽量不要用A:Z，而是根据实际数据量估算一个结束行号，如A1:Z5000。
  3. 流式处理思维：对于超大数据集，可以考虑结合 Google Sheets API 的“导出为 CSV”功能（通过 Drive API），下载到本地后用 Node.js 的流（Stream）来处理，但这会失去部分 API 的便利性。
更新操作覆盖了原有格式或公式
- 原因：使用values.update或values.batchUpdate并设置valueInputOption: 'RAW'会直接覆盖单元格的值和格式。
- 解决：如果只想更新值而保留格式，可以使用valueInputOption: 'USER_ENTERED'。如果需要进行更复杂的操作（如只更新部分单元格、保留公式、调整格式），则需要使用功能更强大的spreadsheets.batchUpdate方法，并精心构造UpdateCellsRequest，设置fields参数为'userEnteredValue'来指明只更新值。

5.3 部署与运行环境问题

在服务器上运行失败，本地却成功
- 原因：服务器环境可能缺少 Node.js，或者项目依赖没有安装，或者密钥文件路径不对。
- 排查：
  1. 在服务器上执行node --version和npm --version。
  2. 进入项目目录，运行npm install安装所有依赖。
  3. 确认credentials.json文件已上传至服务器，并且脚本中KEYFILEPATH的路径指向正确的位置。绝对不要将密钥文件提交到 Git 等版本控制系统，务必将其添加到.gitignore文件中。
  4. 检查服务器是否有网络出口，能否访问https://www.googleapis.com。
如何查看详细的运行日志？
- 技巧：在脚本中关键步骤添加console.log。对于定时任务，务必将标准输出和错误输出重定向到日志文件，如上文 crontab 示例中的>> automation.log 2>&1。这样，任何运行时错误或打印信息都会保存在automation.log文件中，便于事后排查。

通过以上步骤，你不仅构建了一个能解决实际问题的自动化脚本，更掌握了一套处理外部 API 集成、数据清洗和任务自动化的完整方法论。这套方法可以轻松迁移到处理 Airtable、Notion API 或其他任何提供 REST API 的数据源上。记住，安全第一、测试充分、日志清晰，是让自动化脚本长期稳定运行的不二法门。