使用 Google 表格根據單元格值發送電子郵件

從 Google 表格發送電子郵件需要使用 Google Apps 腳本。但不用擔心，如果您以前從未創建過單個 Google Apps 腳本，那麼發送電子郵件非常簡單。

在以下教程中，您將學習如何在Google 表格中檢查單元格的值。如果該值超過某個閾值限制，您可以自動向您喜歡的任何電子郵件地址發送警報電子郵件。

這個腳本有很多用途。如果您的銷售報告中的每日收入低於某個水平，您可能會收到警報。或者，如果您的員工在您的項目跟踪電子表格中報告他們向客戶收費的時間過多，您可能會收到一封電子郵件。

無論是什麼應用程序，這個腳本都是引人注目的。它還可以節省您手動監控電子表格更新的時間。

第 1 步：使用 Google 表格發送電子郵件

在創建 Google Apps 腳本以從 Google 表格發送電子郵件之前，您還需要一個 Gmail 電子郵件地址，Google Apps 腳本將訪問該地址以發送您的提醒電子郵件。

您還需要創建一個包含電子郵件地址的新電子表格。

只需添加一個姓名列和一個電子郵件列，然後填寫您希望接收警報電子郵件的人。

使用 Google 表格根據單元格值發送電子郵件

現在您有了一個可以發送警報電子郵件的電子郵件地址，是時候創建您的腳本了。

要進入腳本編輯器，請單擊工具，然後單擊腳本編輯器。

您將看到一個帶有名為 myFunction()的默認函數的腳本窗口。將其重命名為SendEmail()。

接下來，將以下代碼粘貼到 SendEmail() 函數中：

// Fetch the email address var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2"); var emailAddress = emailRange.getValues(); // Send Alert Email. var message = 'This is your Alert email!'; // Second column var subject = 'Your Google Spreadsheet Alert'; MailApp.sendEmail(emailAddress, subject, message);

以下是這段代碼的工作原理：

getRange和getValues從 getRange 方法中指定的單元格中提取值。
var message和var subject定義將構建警報電子郵件的文本。
MailApp.sendEmail函數最終使用您連接的Google 帳戶執行 Google Scripts 發送電子郵件功能。

單擊磁盤圖標保存腳本，然後單擊運行圖標（右箭頭）運行它。

請記住，Google Script 需要獲得訪問您的 Gmail 帳戶的權限才能發送電子郵件。因此，第一次運行腳本時，您可能會看到如下所示的警報。

使用 Google 表格根據單元格值發送電子郵件

單擊Review Permissions，您將看到另一個需要繞過的警報屏幕。

此警報屏幕是因為您正在編寫一個未註冊為官方腳本的自定義 Google 腳本。

使用 Google 表格根據單元格值發送電子郵件

只需單擊Advanced，然後單擊Go to SendEmail (unsafe)鏈接。

您只需執行一次。您的腳本將運行，您在電子表格中指定的電子郵件地址將收到一封如下所示的電子郵件。

使用 Google 表格根據單元格值發送電子郵件

第 2 步：從 Google 表格中的單元格讀取值

現在您已經成功編寫了一個可以發送提醒電子郵件的 Google Apps 腳本，是時候讓提醒電子郵件更實用了。

您將學習的下一步是如何從 Google 電子表格中讀取數據值，檢查該值，並在該值高於或低於上限時發出彈出消息。

在執行此操作之前，您需要在您正在使用的 Google 電子表格中創建另一個工作表。將此新工作表稱為“MyReport”。

使用 Google 表格根據單元格值發送電子郵件

請記住，單元格 D2 是您要檢查和比較的單元格。想像一下，您每個月都想知道您的總銷售額是否已降至 16,000 美元以下。

讓我們創建執行此操作的 Google Apps 腳本。

通過單擊Tools和Script Editor返回您的 Script Editor 窗口。

如果您使用的是同一個電子表格，您仍然可以在其中使用 SendEmail()函數。剪切該代碼並將其粘貼到記事本中。你稍後會需要它。

將以下函數粘貼到代碼窗口中。

function CheckSales() { // Fetch the monthly sales var monthSalesRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MyReport").getRange("D2"); var monthSales = monthSalesRange.getValue(); var ui = SpreadsheetApp.getUi(); // Check totals sales if (monthSales < 16000){="" ui.alert('sales="" too="" low!');="" }="">

此代碼如何工作：

將單元格D2中的值加載到monthSales變量中。
IF 語句將單元格 D2 中的月銷售額與 $16,000 進行比較
如果值超過 16,000，代碼將觸髮帶有警報的瀏覽器消息框。

保存此代碼並運行它。如果它正常工作，您應該會在瀏覽器中看到以下警報消息。

使用 Google 表格根據單元格值發送電子郵件

現在您已經有了一個可以發送電子郵件警報的 Google Apps 腳本和另一個可以比較電子表格中的值的腳本，您可以將兩者結合起來發送警報，而不是觸發警報消息。

第 3 步：將它們放在一起

現在是時候將您創建的兩個腳本組合成一個腳本了。

此時，您應該有一個電子表格，其中包含一個名為 Sheet1 的選項卡，其中包含警報電子郵件收件人。另一個名為 MyReport 的選項卡包含您的所有銷售信息。

回到腳本編輯器，是時候將您迄今為止所學的一切付諸實踐了。

用您的兩個函數替換腳本編輯器中的所有代碼，如下所示進行編輯。

function CheckSales() { // Fetch the monthly sales var monthSalesRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MyReport").getRange("D2"); var monthSales = monthSalesRange.getValue(); // Check totals sales if (monthSales < 16000){="" fetch="" the="" email="" address="" var="" emailrange="SpreadsheetApp.getActiveSpreadsheet().getSheetByName(" sheet1").getrange("b2");"="" var="" emailaddress="emailRange.getValues();" send="" alert="" email.="" var="" message='This month your sales were ' +="" monthsales;="" second="" column="" var="" subject='Low Sales Alert' ;="" mailapp.sendemail(emailaddress,="" subject,="" message);="" }="">

注意這裡的編輯。

在 IF 語句中，將SendEmail腳本粘貼到 if 語句括號內的CheckSales()函數中。

其次，使用+字符將monthSales變量連接到電子郵件消息的末尾。

唯一剩下要做的就是每個月觸發 CheckSales() 函數。

為此，在腳本編輯器中：

單擊Edit菜單項，然後單擊Current project's triggers。
在屏幕底部，單擊創建新觸發器。
選擇要運行的CheckSales函數。
將Select 事件源更改為time-driven。
將 Select type of time based trigger更改為Month timer。

單擊保存以完成觸發器。

使用 Google 表格根據單元格值發送電子郵件

每個月，您的新腳本都會運行並將單元格 D2 中的每月總銷售額與 16,000 美元進行比較。

如果它更少，它會發送一封警報電子郵件，通知您每月銷售額較低。

使用 Google 表格根據單元格值發送電子郵件

如您所見，Google Apps 腳本在一個小包中包含了許多功能。只需幾行簡單的代碼，您就可以做一些非常了不起的事情。

如果您想進行更多試驗，請嘗試將 16,000 美元的比較限制添加到電子表格的另一個單元格中，然後在進行比較之前將其讀入您的腳本。這樣，您只需更改工作表中的值即可更改限制。

通過調整代碼並添加新的代碼塊，您可以在您所學的這些簡單事物的基礎上構建一些令人驚嘆的 Google 腳本。

流程自動化

到目前為止一切順利，但如果我們不設定任何觸發器，上面的腳本只會手動運行。幸運的是，Google使觸發的自動化配置變得簡單。以下是步驟。

在Apps 腳本編輯器中，點擊左側邊欄上的 觸發器 圖示（它看起來像一個小時鐘）。< /span>

點選 建立新觸發器r 連結或 新增觸發器 觸發器頁面右下角的按鈕。

在 選擇要執行的函數 下拉式選單中，選擇 傳送電子郵件 < i=4> 函數。

在 選擇事件來源 下拉式選單中，選擇 時間驅動.

在 選擇基於時間的觸發類型 下拉選單中，選擇您想要的頻率（例如， 日計時器 用於每日電子郵件， 週計時器 用於每週電子郵件等。

如果適用，請選擇所需的時間範圍或星期幾。

點選儲存建立觸發器。

儲存後，點選右上角的部署按鈕，然後依照指示標記並定義部署。您需要授予自動化運行權限，腳本本身也是如此。

至於給定的觸發器，由於我們的範例是關於發送發票提醒，因此我們假設這些提醒是每月的，並且每月 1 日下午 1 點到 2 點之間發送它們是合適的。當然，您可以根據需要更改頻率和時間。

如何發送帶有附件的電子郵件

為了向您展示如何發送帶有附件的電子郵件，我們將重複使用帶有發票提醒的範例工作表，並重構程式碼片段以包含附件。現在，我們從函數開始，電子郵件範本位於程式碼的後面。發送郵件提醒

我們應該強調，下面的程式碼片段從 Google 雲端硬碟資料夾中提取電子郵件附件，其中包含資料夾 ID 並遵循給定的命名約定至關重要。 讓我們分解程式碼部分可詳細了解相關內容。

讓我們分解一下程式碼

1. 定義函數：sendEmailReminders

函數 sendEmailReminders() {

該行宣告了一個名為的函數，其中包含發送帶有附件的電子郵件提醒的程式碼。sendEmailReminders

2. 取得活動工作表：

var Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

該行從目前開啟的 Google Sheets 文件中檢索活動工作表。

3.取得資料範圍（不包括標題行）：

var dataRange =sheet.getRange(2, 1,sheet.getLastRow() - 1,sheet.getLastColumn());

此行取得工作表中的資料範圍，不包括標題行。它從第二行（第 2 行）和第一列（第 1 列）開始，延伸到工作表的最後一行和最後一列。

附註： 建立自訂工作表時，您需要修改以符合您的工作表。數據範圍

4. 取得資料範圍內的值：

var data = dataRange.getValues();

此行從上一個步驟中定義的單元格範圍中檢索值（內容）。

5. 循環資料行：

for (var i = 0; i < data.length; i++) { var row = data[i];

此循環迭代數組中的每一行。此變數表示每次迭代中的目前資料行。fordatarow

6. 取得每列的值：

var 業務名稱 = 行[0]; var 電子郵件 = 行[1]; var 發票號碼 = 行[2]; var 發票金額 = 行[3]; var dueDate = 行[4]; var 主題 = 行[5];

上面的行從目前資料行的每一列中提取值。每個值稍後都會分配給一個變量，以便於在腳本中引用。給定的值是根據我們的範例表自訂的。

7. 建立電子郵件正文：

var emailBody = “親愛的” + 公司名稱 + ",\n\n" +“這是發票#的提醒” + 發票號碼 + "對於$” + 發票金額 + "到期日為“” + 截止日期 + “.\n” +“請尋找隨附的發票。\n\n” +「感謝您對此事的及時關注。」;

該程式碼使用從工作表中提取的值來建立電子郵件正文。電子郵件正文是一個字串，其中包括公司名稱、發票編號、發票金額和到期日。

8.取得對應的發票文件：

var InvoiceFolderId = “您的資料夾 ID_此處”; var InvoiceFolder = DriveApp.getFolderById(invoiceFolderId); var InvoiceFiles = InvoiceFolder.getFilesByName(InvoiceNumber + '.pdf'); var 發票文件； if (invoiceFiles.hasNext()) { 發票文件 = 發票文件.next(); } else { console.error("找不到發票號碼的發票檔案：" +發票號碼);繼續; }

這些行檢索與目前行關聯的發票文件。

首先，使用發票資料夾 ID 存取發票資料夾。請務必替換為實際的資料夾 ID。 YOUR_FOLDER_ID_HERE

請注意，我們範例中的命名約定是，實際發票名為「123456.pdf」。如果您的附件檔案的名稱不同，您需要更新格式。（發票編號 + “.pdf”）

接下來，腳本搜尋與發票編號同名的文件（假設該文件為 PDF 格式）。

如果找到該文件，則將其指派給該變數；否則，腳本會記錄一條錯誤訊息並移至下一行。invoiceFile

9. 發送帶有附件的電子郵件：

MailApp.sendEmail({ to: email, subject: subject, body: emailBody,attachments: [invoiceFile] });

此行發送正文和電子郵件附件，以下是您應該得到的結果。此外，所有自動化步驟均如前所述應用，並且保留每月觸發是有意義的，因為這是關於發送發票提醒。

使用 GSheets 傳送 HTML 電子郵件

要傳送 HTML 電子郵件，您可以對前面描述的腳本進行一些非常簡單的修改。這裡是：

在此版本中，純文字被替換為包含 HTML 格式內容的變數。 emailBodyemailBodyHtml

呼叫函數時，body 屬性也會替換為屬性，這會將電子郵件作為 HTML email.MailApp.sendEmail()htmlBody 發送

附註：

為了方便起見，我們在腳本中使用而不是。任何一個選項都可以工作，但毫無疑問，更具可讀性。不管怎樣，請隨意重新格式化腳本，並在字串插值時將串聯運算子替換為佔位符。 varconstconst${表達式}

此外，所有腳本都不包含函數，因為這些函數對於我們的範例來說不是必需的。當然，也可以使用這些類型的腳本進行新增甚至自動化。密件副本

最後，您可以透過新增自訂 CSS 樣式來增添趣味，請參閱下面的更新功能。 var emailBodyHtml =

// 有 CSS 樣式的 HTML 電子郵件正文 var emailBodyHtml = "<html><head><style>"; +“內文{字型系列：Arial，無襯線}” + “p {字體大小：14px;}” +“.invoice-info {字體粗細：粗體；顏色：#4a4a4a;}” +“</style></head><body>” +“<p>親愛的<span class='發票資訊'>” + 公司名稱 + “,

專業提示：

請放心，某些郵箱提供者可能不支援和正確呈現高度風格化的電子郵件，或者根本不支援和呈現。 <風格>
使用 Mailtrap 電子郵件測試來查看有多少郵箱提供者支援您的 HTML 電子郵件。
再次強調， 如果您需要大量發送，最好使用適當的 MTA 而不是函數。發送電子郵件
請注意您的 Gmail 帳戶限制。對於 Google Workspace 和/或您的私人帳號，這些內容有所不同。

如何從 Google 試算表發送觸發電子郵件？

當單元格達到一定值時

我們將向您展示如何根據儲存格值發送自動電子郵件。在我們的範例中，當發票金額儲存格達到「0」（表示客戶流失）時，將發送發票。

給定的儲存格值會觸發自訂流失電子郵件，以立即聯絡客戶。同樣，自動化步驟保持不變，您仍然可以對這些電子郵件使用每月觸發器。

請記住，該腳本與我們用於發送 HTML 電子郵件和附件的腳本相同。但它還有另一層功能來引入所描述的條件邏輯。這是代碼。

直到程式碼中斷

請注意，我們只關注函數中的內容，以避免重複已經描述的內容。電子郵件正文HTML

代碼的更新部分根據發票金額決定電子郵件正文的內容。如果發票金額為，則假定為流失客戶，並使用不同的電子郵件範本。這是程式碼的細分：“0”

var emailBodyHtml;

它聲明一個名為並儲存電子郵件正文內容的變數。它最初是未定義的。emailBodyHtml

if (發票金額 == 0) { ... }

此語句檢查發票金額是否為 0。如果是，則會執行大括號內的代碼。此區塊包含流失客戶電子郵件範本。if(`{}`)

subject =「流失客戶通知」；

該行設定流失客戶的電子郵件主題。“流失客戶通知”

emailBodyHtml = “

該行將變數的值設定為流失客戶電子郵件範本。該模板是一個 HTML 字串，其 CSS 樣式在部分中定義。電子郵件正文包含公司名稱的佔位符和給客戶的訊息。emailBodyHtml

} 別的 { ... }

當發票金額不為 0 時，將執行該區塊。它將變數設定為常規發票電子郵件範本。電子郵件模板是一個 HTML 字串，其 CSS 樣式在部分中定義。電子郵件正文包含公司名稱、發票編號、發票金額和到期日期的佔位符。elseemailBodyHtml

透過使用此條件邏輯，腳本會根據發票金額是（流失客戶）還是非零（常規發票）發送不同的電子郵件正文。 0

當單元格值改變時

現在，我們將採用上面使用的相同腳本並將其擴展以涵蓋另一個用例 - 客戶端升級，並且客戶經理需要發送一封帶有發票的「謝謝」電子郵件。

解釋一下，這個想法是，客戶經理每個月都會有一張包含客戶憑證的新工作表（在我們的範例中為工作表 2）。

代碼現在將比較兩張表，並向升級的客戶（他們的 發票金額）發送一封自動的「謝謝」電子郵件，而不是常規的發票電子郵件 a> 比前一個更大）。

此外，無需觸及流失電子郵件，因為有人可能決定停止使用我們想像的服務。這是腳本。

function sendEmailReminders() { varsheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”); varsheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet2”); var dataRange1 =sheet1.getRange(2, 1,sheet1.getLastRow() - 1,sheet1.getLastColumn()); var dataRange2 =sheet2.getRange(2, 1,sheet2.getLastRow() - 1,sheet2.getLastColumn()); var data1 = dataRange1.getValues(); var data2 = dataRange2.getValues(); for (var i = 0; i < data1.length; i++) { var row1 = data1[i]; var row2 = data2[i]; var 業務名稱 = row1[0]; var 電子郵件 = row1[1]; var 發票號碼 = row1[2]; var InvoiceAmount1 = parseFloat(row1[3].toString().replace('$', '').replace(',', 39;.')); var InvoiceAmount2 = parseFloat(row2[3].toString().replace('$', '').replace(',', 39;.')); if (isNaN(invoiceAmount1) || isNaN(invoiceAmount2)) { console.error("無效的業務發票金額：" + 業務名稱+ "。發票金額1: " + 發票金額1 + ", 發票金額2: " +發票金額2 ）；繼續; } var dueDate = row1[4];變數主題； var emailBodyHtml; if (invoiceAmount1

直到程式碼中斷

1. 腳本首先取得 Sheet1 和 Sheet2 的引用。

varsheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”); varsheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet2”);

2. 然後，它從兩個工作表中檢索數據，從第二行開始排除標題。

var dataRange1 =sheet1.getRange(2, 1,sheet1.getLastRow() - 1,sheet1.getLastColumn()); var dataRange2 =sheet2.getRange(2, 1,sheet2.getLastRow() - 1,sheet2.getLastColumn()); var data1 = dataRange1.getValues(); var data2 = dataRange2.getValues();

3. 腳本迭代兩個工作表中的行（假設兩個工作表中的行數相同）。它比較 Sheet1 和 Sheet2 中每個對應行的發票金額。

for (var i = 0; i < data1.length; i++) { var row1 = data1[i]; var row2 = data2[i]; // 從兩張表中取得發票金額 var InvoiceAmount1 = parseFloat(row1[3].toString().replace('$', '').replace(' ,','.')); var InvoiceAmount2 = parseFloat(row2[3].toString().replace('$', '').replace(',', 39;.'));

複製

4. 然後，腳本根據 Sheet1 和 Sheet2 中的發票金額比較來檢查發送不同類型電子郵件的條件。

if (invoiceAmount1

複製

關鍵的比較是，檢查 Sheet1 中的發票金額是否小於 Sheet2 中同一行（即同一客戶）的發票金額。如果為 true，則腳本假定客戶已升級並傳送電子郵件。否則，它會檢查其他情況（流失或定期發票提醒）並發送適當的電子郵件。if (invoiceAmount1

當 Google 試算表檔案更新時

現在，討論一些不同的用例是值得的，在該用例中，除客戶經理之外的其他人都可以存取電子表格。

假設我們的客戶經理有一天會專門會見客戶並回答他們的問題。客戶可以存取電子表格，在其中可以簡要描述問題並預訂時段。

好吧，有更好的方法來處理客戶預訂和查詢。但為了教程的目的，我們將堅持這種方法。

步驟1

首先，我們需要建立一個表。如下所示，我們的非常簡單，僅包含三行，即 Manager、Time slot 和 Query。

第2步

接下來，您需要 Google 腳本在有人用查詢更新工作表時觸發電子郵件，從而預訂空位。這是一個例子。

//@OnlyCurrentDoc function processEdit(e) { MailApp.sendEmail({ to: "[email protected]" ；，主題：“新預訂－客戶時段”，正文：“客戶有一個問題要問您。” }); }

複製

為了幫助您理解正在發生的事情，討論主要功能是值得的。

//@OnlyCurrentDoc – 此註解表示您希望腳本僅在指定的 Google 試算表中執行。刪除註解會在其他文件中啟用該腳本。
function processEdit () – 觸發器（我們將在下一個步驟中設定）使用此函數執行腳本。簡而言之，函數設定了每次有人更新工作表時獲取電子郵件的過程。
(e) – 此註解表示包含有關編輯的資料的物件。它有一個範圍屬性來表示某個範圍或單元格已被編輯。
MailApp – 此函數用來轉送電子郵件的物件。

在繼續之前，您可以測試工作表和程式碼。如果您使用範例工作表，只需填入「查詢」列下的儲存格之一，然後執行腳本。電子郵件應該立即到達您的收件匣，但請注意，該腳本尚未自動化。

步驟3

是時候設定並授權觸發器自動發送電子郵件了。在 Apps 腳本中，點擊側邊選單中的鬧鐘圖示。然後，點選「…建立新觸發器」。