2025年10月11日 星期六

透過Excel讀寫Google試算表,解決中文亂碼與日期格式問題

 您可以透過Excel,直接將資料寫入或讀取Google試算表,但在過程中可能會遇到中文變亂碼,以及回傳時間為ISO 8601日期格式的問題。本文將引導您如何解決這些問題,並順利完成資料串接。

事前準備

要將Excel與Google試算表連結,您需要先在Google Apps Script中部署一個網路應用程式,並取得其URL。您可以參考相關教學文章,了解如何部署Google網路應用程式。[1]

解決中文亂碼

在Excel中透過VBA將中文資料傳送到Google試算表時,若未經處理,很容易會變成亂碼。

  • 問題原因: 這是因為在資料傳輸過程中,中文字元的編碼未被正確處理。

  • 解決方法: 使用UrlEncode函式將中文字串進行編碼轉換,即可解決亂碼問題。這個函式可以將中文轉換為URL安全的格式,確保資料在傳輸過程中不會出錯。

處理ISO 8601日期格式

當您從Google試算表讀取資料時,可能會發現日期時間的格式是ISO 8601標準格式,這是一種國際通用的日期和時間表示方法,但可能與您習慣的格式不同。[2][3]

  • 問題原因: Google試算表預設會使用ISO 8601格式來處理日期與時間。[4]

  • 解決方法:

    1. 在Google Apps Script中處理: 您可以在Google Apps Script中撰寫程式碼,在回傳資料前,就先將日期時間轉換成您需要的格式。

    2. 在Excel中處理: 也可以在Excel VBA中,加入處理ISO 8601格式的函式,將讀取到的日期時間字串轉換為Excel可辨識的日期格式。

GET與POST方法的差異

在與Google Apps Script溝通時,您可以使用GET或POST方法來傳遞資料。

  • GET:

    • 會將資料附加在URL後面,格式為?key1=value1&key2=value2

    • 由於URL長度限制,不適合傳遞大量資料。

    • 在VBA中,使用GET方法時,需將要傳遞的資料字串串接在URL後面。

  • POST:

    • 將資料放在請求的主體中傳遞,較為安全,且沒有資料長度限制。

    • 在VBA中,使用POST方法時,需將資料放在send方法的參數中。

在處理中文資料時,建議使用POST方法,並搭配UrlEncode函式,能更穩定地避免亂碼問題。

讀取Google試算表資料

您可以透過Google Apps Script的doGet函式來讀取試算表中的資料。[5]

  1. doGet函式中,撰寫程式碼讀取您需要的儲存格範圍。

  2. 將讀取到的資料轉換為JSON格式。

  3. 透過ContentService將JSON資料回傳。

  4. 在Excel VBA中,使用GET方法呼叫此URL,即可取得JSON格式的資料。

  5. 再透過字串處理的方式,將JSON資料解析並填入Excel儲存格中。

透過上述步驟,您就能夠順利在Excel與Google試算表之間進行資料的讀寫,並有效解決中文亂碼和日期格式的問題。



透過網頁將資料寫入 Google 試算表,打造簡易留言板!

想讓使用者在你的網站上留言,並自動將留言存到 Google 試算表嗎?透過 Google Apps Script,你可以輕鬆實現這個功能!本文將引導你一步步建立一個簡易的留言板,讓網頁上的資料能夠即時寫入 Google 試算表。

核心概念:Google Apps Script 網路應用程式

Google 提供了將程式發佈成網路應用程式的功能,讓你可以透過一般的 POST 或 GET 請求來傳送及讀取資料。[1][2] 這表示,我們可以撰寫一支 Google Apps Script,並將它部署成一個網路應用程式,這個應用程式就能接收來自我們網頁的資料,再將資料寫入指定的 Google 試算表。[1]

步驟一:建立 Google 試算表

  1. 登入你的 Google 帳戶,並前往 Google 雲端硬碟。

  2. 點擊左上角的「+ 新增」,選擇「Google 試算表」,建立一個新的試算表。

  3. 為你的試算表命名,例如「留言板」。

步驟二:撰寫 Google Apps Script

  1. 在你的「留言板」試算表中,點擊上方選單的「擴充功能」>「Apps Script」。

  2. 這會開啟一個新的指令碼編輯器分頁。將預設的 myFunction 程式碼刪除,並貼上以下的程式碼:

JavaScript
function doGet(e) {
  // 宣告一個變數 ws,代表目前活躍的工作表
  var ws = SpreadsheetApp.getActiveSheet();
  // 取得網址中的參數
  var parameter = e.parameter;
  var username = parameter.username;
  var message = parameter.message;
  
  // 將使用者名稱、訊息和目前時間寫入新的一列
  ws.appendRow([username, message, new Date()]);
  
  // 建立一個變數 output,並設定其值為 "success write data"
  var output = "success write data";
  
  // 回傳一個包含 output 內容的文字輸出
  return ContentService.createTextOutput(output);
}

程式碼解說:

  • doGet(e):這是 Google Apps Script 中一個特殊的函式,當你的網路應用程式收到 GET 請求時,就會觸發這個函式。[3][4][5]

  • SpreadsheetApp.getActiveSheet():取得目前正在使用的工作表。

  • e.parametere 是一個事件物件,包含了請求的相關資訊,e.parameter 則可以取得網址中的參數。[6]

  • ws.appendRow([...]):在工作表的最後一列新增一筆資料,[] 中依序放入要寫入的資料。

  • ContentService.createTextOutput(...):建立一個純文字的回應。[3]

步驟三:部署為網路應用程式

  1. 在指令碼編輯器中,點擊右上角的「部署」>「新增部署作業」。

  2. 在「選取類型」中,點擊齒輪圖示並選擇「網頁應用程式」。

  3. 在「說明」欄位中,可以為這次的部署加上描述(可選填)。

  4. 在「網頁應用程式」的「執行身分」選擇「我」。

  5. 在「誰可以存取」的下拉選單中,選擇「任何人」。

  6. 點擊「部署」。

  7. 接著會跳出授權請求的視窗,點擊「授權存取」,並選擇你的 Google 帳戶。

  8. 可能會出現「Google 尚未驗證這個應用程式」的警告,點擊「進階」,然後選擇「前往『(你的專案名稱)』(不安全)」。

  9. 最後點擊「允許」,完成授權。

  10. 部署完成後,你會得到一個「網頁應用程式」的網址,請將它複製下來。

步驟四:測試你的網路應用程式

  1. 開啟一個新的瀏覽器分頁,將你剛剛複製的網址貼上。

  2. 在網址的最後面加上 ?username=你的名字&message=你的留言,例如:
    https://script.google.com/macros/s/AKfycby.../exec?username=小明&message=你好

  3. 按下 Enter 送出後,如果頁面顯示 "success write data",就表示資料已經成功寫入了!

  4. 回到你的「留言板」試算表,你就會看到剛剛輸入的資料已經被記錄下來了。

應用與延伸

透過這個簡單的範例,你已經學會了如何透過網頁將資料寫入 Google 試算表。你可以將這個技術應用在各種情境,例如:

  • 簡易留言板或聯絡表單: 讓訪客可以直接在你的網站上留下訊息。

  • 資料收集: 透過網頁表單收集各種資料,並自動整理到試算表中。[7]

  • 簡易資料庫: 將 Google 試算表當作一個簡單的資料庫來使用,儲存和讀取資料。[8][9]

你可以進一步學習如何使用 doPost 來處理 POST 請求,或是如何讀取試算表中的資料並顯示在網頁上,打造功能更豐富的網路應用程式。

 



2025年10月8日 星期三

【Excel VBA小工具】快速合併相同資料並加總,報表整理好幫手

 您是否在整理 Excel 報表時,經常需要將多筆屬於同一個人的資料彙整成一筆,同時將文字內容串接、數值加總?這個過程如果手動操作,不僅耗時,還容易出錯。

今天,我們要介紹一個由「彰化一整天的 Blog」所提供的免費 Excel VBA 小工具,它可以幫您一鍵完成這個重複性的任務,快速將相同資料合併或計算加總,大幅提升您的工作效率! #### 工具特色與使用說明 這個工具非常直觀,透過簡單的參數設定,就能處理大部分的資料合併需求。在使用前,請先了解以下幾個重點: 1. **必須有標題列**:您的原始資料第一列必須是欄位標題(例如:姓名、月份、金額),否則工具會將第一筆資料誤判為標題而無法處理。 2. **自動排序功能**:如果您的資料尚未排序,工具可以自動依據您指定的「比較欄位」進行排序,確保相同項目能被正確群組。若資料本身已排序,可選擇關閉此功能以保留原始順序。 3. **自訂合併符號**:合併文字欄位時,您可以自訂分隔符號,例如使用逗號 `,`、分號 `;`,甚至可以使用 `Alt`+`Enter` 實現儲存格內換行,讓報表更清晰。 4. **重複資料合併**:當合併的文字內容有重複時,可選擇只顯示一次(不重複的值),讓結果更簡潔。 #### 操作範例:員工三個月獎金彙總 我們以下方這份員工獎金表為例,目標是將每位員工三個月的獎金資料合併成一筆,並計算出總獎金。 **原始資料(處理前):** **Step 1. 下載並開啟工具** 首先,請至下方連結下載工具檔案 `mergetool.xls`。 * **檔案下載頁面**: https://drive.google.com/file/d/1i6usq7PpdYKvCUwKyS-SdTTtbHgt3son/view **Step 2. 準備與設定參數** 打開檔案後,切換到「合併參數」工作表,根據我們的需求進行設定: * **比較欄位 (1)**:我們要依據「人員」來合併資料,而「人員」在 A 欄,所以填 `1`。 * **合併欄位 (2)**:我們要將「月份」的內容串接起來,「月份」在 B 欄,所以填 `2`。 * **加總欄位(1) (3)**:我們要將「獎金金額」加總,「獎金金額」在 C 欄,所以填 `3`。 * **合併符號 (,)**:我們希望月份之間用逗號分隔,維持預設即可。若想換行,可在這個儲存格內按 `Alt`+`Enter`。 * **是否自動排序 (N)**:範例中的原始資料已經按「人員」排好序,所以填 `N`,避免影響原始順序。如果您的資料是散亂的,請填 `Y`。 **Step 3. 執行合併與檢視結果** 設定完成後,點擊 **[合併相同欄位]** 按鈕。 程式執行完畢後,會自動產生一個名為「處理過」的新工作表,裡面就是我們想要的彙總結果! **處理結果:** 從結果可以看到,「呂宗鴻」的三筆資料已經被合併成一筆,「月份」欄位被串接起來,而「獎金金額」也已自動計算出總和並放在新的 D 欄中,非常方便! 這個實用的小工具推薦給所有需要頻繁整理 Excel 報表的朋友,快下載來試試看吧!




2025年10月7日 星期二

【Excel 教學】快速合併相同資料、自動加總與串接,自訂多欄位合併神器!

 在處理 Excel 資料時,您是否經常遇到需要將多筆重複的資料(例如同一位客戶的多筆訂單)合併成一筆,並將相關資訊彙整起來的困擾?手動複製貼上不僅耗時,更容易出錯。

今天,【彰化一整天】的站長要分享一個功能強大的 Excel 工具,它可以快速合併相同資料的其它欄位,無論是文字串接還是數值加總,都能輕鬆搞定。這個工具經過改良,增加了更多自訂選項,適用於電商訂單整理、公司帳務核對等多種情境,絕對是提升您工作效率的好幫手!

工具下載與準備

  1. 下載檔案:請點擊下方連結下載本次教學使用的 Excel 活頁簿。

  2. 啟用內容:下載後開啟檔案,請務必點選上方的「啟用編輯」與「啟用內容」,這樣內含的 VBA 巨集才能正常運作。

功能核心:

這個工具的核心操作都在 設定 這張工作表中完成。您只需要根據需求填寫對應的欄位名稱,就能客製化合併的規則。

以下是各個設定項目的詳細說明:

  1. 比較欄位 (Key Columns)

    • 用途:用來判斷哪些資料是「相同」的依據。您可以填入一個或多個欄位名稱(例如:English NameChinese Name)。程式會根據這些欄位的組合來判斷資料是否為同一筆。

    • ⚠️ 最重要提醒:在執行合併之前,您必須先到 處理前 的工作表,依照您設定的「比較欄位」進行排序,這樣程式才能正確地連續處理相同的資料。

  2. 合併欄位 (Columns to Concatenate)

    • 用途:當找到相同資料時,您希望將哪些欄位的文字內容「串接」在一起。將這些欄位的名稱填入此處即可。

    • 合併符號:您可以自訂用來分隔串接文字的符號,例如:

      • 半形逗號 ,

      • 頓號 

      • 換行符號 (在儲存格中按 Alt + Enter 輸入)

  3. 加總欄位 (Columns to Sum)

    • 用途:針對數值欄位進行「加總」。例如,您可以將每筆訂單的金額欄位填入此處,合併後會自動計算總金額。

    • 實用技巧:如果您想計算某筆資料被合併了幾次,可以在 處理前 的資料旁新增一個欄位(例如 count),並將所有值都填上 1。然後將 count 填入加總欄位,合併後的數字就是該筆資料的出現次數。

  4. 進階選項 (新功能!)

    • 空白資料是否合併

      • :如果合併的欄位中有空白儲存格,也會保留其位置,可能產生連續的分隔符號(例如 資料A,,資料C)。

      • :忽略空白儲存格,只串接有內容的資料,結果會更乾淨(例如 資料A,資料C)。

    • 大小寫是否相同

      • :在比較時,會忽略英文字母的大小寫差異(例如 Mark 和 mark 會被視為相同資料)。

      • :嚴格區分大小寫,Mark 和 mark 會被視為兩筆不同的資料。

操作步驟示範

  1. 準備資料:將您要處理的原始資料複製並貼到 處理前 這張工作表中。

  2. 排序資料:根據您即將在「比較欄位」中設定的欄位,對 處理前 的資料進行排序。

  3. 完成設定:切換到 設定 工作表,依照您的需求填寫上述所有設定項目。

  4. 執行合併:點擊「合併相同欄位」按鈕。

  5. 檢視結果:程式執行完畢後,會跳出完成訊息。您可以切換到 處理後 工作表,查看已經整理好的乾淨資料!

結語

這個自訂多欄位合併工具,將繁瑣的資料整理工作變成了一鍵完成的簡單任務。透過靈活的設定,無論您的需求是合併文字、加總數字,還是計算次數,它都能完美勝任。趕快下載試用,體驗自動化處理帶來的便利吧!



透過Excel讀寫Google試算表,解決中文亂碼與日期格式問題

  您可以透過Excel,直接將資料寫入或讀取Google試算表,但在過程中可能會遇到中文變亂碼,以及回傳時間為ISO 8601日期格式的問題。本文將引導您如何解決這些問題,並順利完成資料串接。 事前準備 要將Excel與Google試算表連結,您需要先在Google Apps ...