用 Gemini 清理 Google 試算表資料｜AI 講義

Overview這篇教你做什麼

手上的 Google 試算表常常一團亂：日期格式東一種西一種、公司名稱有全形有半形、產品分類全靠人工判斷。這篇要帶你用 Google Apps Script 串接 Gemini，讓 AI 幫你把整份資料清乾淨。

Key Takeaways

用 SpreadsheetApp 讀取整份試算表的資料
把每一列交給 Gemini，請它統一格式、修正錯字
讓 Gemini 依內容自動判斷分類並填回欄位
把整理後的結果寫回試算表新的欄位
金鑰用 PropertiesService 保管，不寫在程式裡
全程在瀏覽器完成，不需要安裝任何軟體

整個流程的核心是一個小小的 askGemini 函式，負責把文字送給 Gemini 並拿回答案；主流程則負責讀取儲存格、組出提示、寫回結果。學會這個模式之後，你可以把它套用到任何需要 AI 判斷的表格工作。

Why為什麼要這樣做

人工清理資料最花時間，也最容易出錯。一份幾百列的名單，光是把「台北市」「臺北市」「北市」統一成同一種寫法，就得逐格檢查。交給 Gemini，它能理解語意，知道這三種寫法指的是同一個地方，並依照你的規則統一輸出。

更重要的是，這套做法可以重複使用。今天清完這份，下週再來一份新的，只要按一下選單就跑完。你把判斷邏輯寫成提示，AI 每次都照著同一套標準做，比不同同事各自手動處理更一致、更可靠。

Prepare開始前要準備什麼

Checklist

一個可以使用 Google 試算表的 Google 帳號
一把 Gemini API 金鑰，到 ai.google.dev 免費申請
一份要整理的 Google 試算表，例如客戶名單或訂單資料

申請金鑰時，登入 ai.google.dev 後點選建立 API 金鑰，複製那串英數字保存好。這把金鑰等一下會存進 Apps Script 的屬性服務，不會直接寫在程式碼裡，安全性比較高。

Steps一步一步實作

1打開試算表的指令碼編輯器

動作在 Google 試算表上方選單點「擴充功能」→「Apps Script」，會開啟一個新分頁，這就是寫程式的地方。

常見地雷如果選單裡找不到 Apps Script，先確認你是用電腦版瀏覽器，手機版不支援。

2把金鑰存進屬性服務

動作在編輯器左側點「專案設定」（齒輪圖示），往下找到「指令碼屬性」，新增一列，屬性名稱填 GEMINI_API_KEY，值貼上你的金鑰，按儲存。

常見地雷屬性名稱要一字不差，多一個空格或大小寫不同都會讓程式抓不到金鑰。

3貼上 askGemini 函式

動作回到程式碼區，先貼上下方第一段的 askGemini 函式，它負責把文字送給 Gemini 並回傳答案。

常見地雷網址裡的模型名稱 gemini-2.5-flash 不要改錯，打錯字會得到 404 錯誤。

4貼上主流程 cleanSheet 函式

動作接著貼上第二段主流程，它會讀取工作表資料、逐列請 Gemini 整理，再把結果寫回。

常見地雷第一次執行會跳出授權視窗，要按「允許」讓程式能存取試算表與外部網路。

5確認資料範圍與欄位

動作看一下你的試算表，把程式裡的欄位對應改成你自己的：例如原始資料在第 1 到第 3 欄，整理結果寫到第 4 欄。

常見地雷試算表第一列如果是標題列，記得從第 2 列開始處理，否則標題也會被送去清理。

6執行並觀察結果

動作在編輯器上方選擇 cleanSheet 函式，按「執行」，等幾秒後回到試算表看結果欄位是否填入乾淨的資料。

常見地雷資料很多時一次全跑可能會超過執行時間上限，先拿前 10 列測試沒問題再放大。

7加上選單方便日後使用

動作把 onOpen 函式一起貼上並存檔，之後每次打開試算表，上方會多一個自訂選單，點一下就能執行清理。

常見地雷新增 onOpen 後要重新整理試算表頁面，自訂選單才會出現。

下面兩段程式碼直接照抄即可。第一段是共用的 askGemini，之後每一篇教學都會用到同一段；第二段是這篇專屬的主流程，負責讀取與寫回試算表。

function askGemini(prompt) {
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=' + key;
  const payload = { contents: [{ parts: [{ text: prompt }] }] };
  const res = UrlFetchApp.fetch(url, { method:'post', contentType:'application/json', payload: JSON.stringify(payload), muteHttpExceptions:true });
  return JSON.parse(res.getContentText()).candidates[0].content.parts[0].text.trim();
}

function cleanSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  // 假設 A 欄公司名、B 欄地區、C 欄描述，整理結果寫到 D 與 E 欄
  for (let row = 2; row <= lastRow; row++) {
    const name = sheet.getRange(row, 1).getValue();
    const area = sheet.getRange(row, 2).getValue();
    const desc = sheet.getRange(row, 3).getValue();
    if (!name && !area && !desc) continue;
    const prompt = '你是資料整理助理。請把以下資料統一格式：公司名稱去除多餘空白並統一全形括號、地區統一成「台北市」這類標準寫法、修正明顯錯字。' +
      '接著依描述判斷所屬分類（只能是：業務、技術、行政、其他其中之一）。' +
      '只輸出兩行，第一行是整理後的完整資料（公司｜地區），第二行是分類，不要多加說明。\n' +
      '公司：' + name + '\n地區：' + area + '\n描述：' + desc;
    const answer = askGemini(prompt);
    const lines = answer.split('\n').filter(function(l){ return l.trim() !== ''; });
    sheet.getRange(row, 4).setValue(lines[0] || '');
    sheet.getRange(row, 5).setValue(lines[1] || '');
    Utilities.sleep(500); // 稍微間隔，避免短時間送出太多請求
  }
  SpreadsheetApp.getActiveSpreadsheet().toast('清理完成');
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI 工具')
    .addItem('清理資料', 'cleanSheet')
    .addToMenu();
}

Flow整個流程長這樣

從雜亂資料到乾淨表格

1打開試算表並存好 Gemini 金鑰

2主流程逐列讀取原始欄位

3組出提示送給 Gemini 整理與分類

4取回答案並拆成整理結果與分類

5把結果寫回新欄位

6全部跑完跳出完成提示

這張流程圖等價於前面程式碼的運作方式：讀取、送出、取回、寫回，一列接一列。理解這個循環之後，你就能把任何欄位換成自己的需求，例如清電話號碼格式或統一產品代號。

Tips幾個實用小技巧

先用前 10 列測試提示效果，確認輸出格式穩定再處理整份資料
在提示裡明確規定「只輸出幾行、不要說明」，可避免 AI 多話難以解析
分類選項用固定清單限制，例如「只能是業務、技術、行政、其他」，結果更整齊
加上 Utilities.sleep 稍微間隔請求，避免短時間送太多而被限流
把原始資料保留在原欄位，整理結果寫到新欄位，方便隨時比對與還原

FAQ常見問題

常見問題 FAQ

Gemini API 是免費的嗎？

Gemini API 提供免費額度，個人學習與小量使用通常不會產生費用。免費方案有每分鐘與每天的請求次數上限，處理大量資料時可能會碰到限制。如果需要更高的用量，可以在 Google AI Studio 開通付費方案。建議先用免費額度測試流程，確認做法可行後再評估是否升級。

為什麼程式抓不到我的金鑰？

最常見的原因是指令碼屬性的名稱打錯。程式裡讀取的名稱是 GEMINI_API_KEY，屬性設定裡也要一字不差地填成 GEMINI_API_KEY，大小寫、底線、有沒有多餘空格都要完全一致。設定完記得按儲存，然後重新執行一次程式。

執行時跳出授權要求正常嗎？

完全正常。因為這支程式要存取你的試算表，還要連到外部的 Gemini 網路服務，Google 會請你確認授權。按照畫面指示點選你的帳號，看到警告時選進階再允許即可。授權只需做一次，之後執行就不會再問。

資料很多跑到一半停掉怎麼辦？

Apps Script 單次執行有時間上限，資料太多可能會中斷。解法是分批處理，例如每次只跑一定列數，或用觸發器分次執行。也可以在提示裡精簡文字、減少每列送出的資料量，降低每次呼叫的時間，讓整體跑得更快更穩。

Gemini 回傳的格式跟我要的不一樣？

這通常是提示不夠明確造成的。在提示裡清楚規定輸出幾行、每行放什麼、不要加任何說明文字，AI 就會更聽話。也可以在提示裡直接給一個範例輸出，讓 Gemini 照著格式回答，解析起來就穩定多了。

會不會把我原本的資料改壞？

只要把整理結果寫到新的欄位，原始資料就完全不受影響。程式裡的寫回位置是第 4、5 欄，原始資料在第 1 到 3 欄，兩者分開。萬一結果不理想，直接清掉新欄位重跑就好，原本的資料一直都在。

可以一次處理好幾個工作表嗎？

可以。程式目前用 getActiveSheet 取得目前這個工作表，你可以改成用 getSheetByName 指定名稱，或用迴圈跑過所有工作表。不過工作表越多、資料越大，總執行時間越長，建議分批或分次執行，避免超過單次時間上限。

提示要用中文還是英文寫？

中文英文都可以，Gemini 兩種語言都理解得很好。如果你的資料是中文，用中文寫提示通常更自然，判斷分類也更準確。重點不在語言，而在把規則講清楚：要統一成什麼格式、分類有哪些選項、輸出要長什麼樣子。

每列都呼叫一次 API 會不會太慢？

逐列呼叫在資料量小時很方便，但列數多就會慢。如果追求速度，可以改成一次把多列打包進同一個提示，請 Gemini 一次回傳多列結果，減少呼叫次數。不過打包太多列容易讓輸出變亂，實務上建議一次處理十到二十列就好。

這套做法只能用在試算表嗎？

不是。askGemini 這個函式是通用的，任何 Apps Script 專案都能用。你可以把它接到 Google 表單、Gmail、文件或行事曆，凡是需要 AI 判斷或生成文字的地方都適用。這篇是用試算表當範例，換個服務、換個提示，就能解決別的工作。

CTA想更進一步嗎

把重複的資料整理交給 AI 之後，你會發現省下的時間可以拿去做更有價值的判斷。如果你的團隊有一整套重複的表格工作想自動化，讓專業講師帶你們一次上手會更快。

想讓團隊用 AI＋自動化把重複工作交給流程?賴家榮提供 Workspace／GAS／Gemini 企業內訓與大學課程。

預約授課・免費諮詢

延伸閱讀

本文為賴家榮 AI 教學中心原創教學內容；想讓團隊學會用 AI，歡迎看AI 課程或預約授課 →

代表圖片來源：圖片來源：Pexels

用 Gemini 清理 Google 試算表資料