如何在Google Script发送Sheet为PDF时从指定单元格获取邮箱地址

我当前使用的以下脚本可正常运行，但需要从指定单元格中获取邮箱地址。请问如何将脚本中的"example@mail.com"替换为来自特定Sheet单元格（例如"Invoice!A2"）的邮箱地址？

原脚本代码：

function emailPDF() {
    let token = ScriptApp.getOAuthToken();
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    spreadsheet.getSheetByName("Invoice");
    let spreadsheetId = spreadsheet.getId();
    let sheetId = spreadsheet.getSheetByName("Invoice").getSheetId();
    let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId + "&portrait=true" + "&exportFormat=pdf";
    let request = UrlFetchApp.fetch(sheetUrl, {
        headers: {
            'Authorization': 'Bearer ' + token
        }
    });
    let content = request.getContent();
    let message = {
        to: "example@mail.com",
        subject: "Invoice",
        body: "Hello.",
        attachments: [{
            fileName: "Invoice.pdf",
            content: content,
            mimeType: "application/pdf"
        }]
    }
    MailApp.sendEmail(message);
    spreadsheet.getSheetByName("Invoice").activate();
}

修改方法 #

只需两步就能完成需求：

1. 在脚本中获取Invoice工作表里A2单元格的内容
2. 将该内容替换原脚本里的固定邮箱地址

修改后的完整脚本：

function emailPDF() {
    let token = ScriptApp.getOAuthToken();
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    // 获取Invoice工作表并存储为变量，避免重复调用提升效率
    let invoiceSheet = spreadsheet.getSheetByName("Invoice");
    let spreadsheetId = spreadsheet.getId();
    let sheetId = invoiceSheet.getSheetId();
    // 读取A2单元格的邮箱地址
    let recipientEmail = invoiceSheet.getRange("A2").getValue();
    
    let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId + "&portrait=true" + "&exportFormat=pdf";
    let request = UrlFetchApp.fetch(sheetUrl, {
        headers: {
            'Authorization': 'Bearer ' + token
        }
    });
    let content = request.getContent();
    let message = {
        to: recipientEmail, // 替换为单元格获取的邮箱地址
        subject: "Invoice",
        body: "Hello.",
        attachments: [{
            fileName: "Invoice.pdf",
            content: content,
            mimeType: "application/pdf"
        }]
    }
    MailApp.sendEmail(message);
    invoiceSheet.activate();
}

关键说明 #

• 新增invoiceSheet变量存储工作表对象，避免重复调用getSheetByName方法，优化脚本运行效率
• 通过getRange("A2").getValue()直接读取指定单元格的文本内容，也就是目标邮箱地址
• 将message对象中to字段的固定值替换为读取到的邮箱变量，即可实现从单元格获取收件人邮箱的需求

内容的提问来源于stack exchange，提问作者igor.ognjanovic