如何获得电子邮件地址从单元格在谷歌工作表/谷歌脚本，同时发送工作表为PDF

Question

这个脚本适用于我，但我需要得到电子邮件从值在单元格。我的问题是如何取代电子邮件从这个脚本"[e-mail]"来自特定工作表单元格的电子邮件，例如"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: "[e-mail]",
subject: "Invoice",
body: "Hello.",
attachments: [{
fileName: "Invoice.pdf",
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
spreadsheet.getSheetByName("Invoice").activate();
}

Answer 1

我可以通过下面的脚本完成:

请在这里找到进一步澄清的参考

function onOpen() 
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('GetValues')
.addItem('get current', 'getCurrentCellValue')
.addItem('get by row col', 'getByRowAndColumn')
.addItem('get by address a1', 'getByCellAddressA1Notation')
.addToUi();
}

function getCurrentCellValue()
{
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var a1 = cell.getA1Notation();
var val = cell.getValue();

SpreadsheetApp.getUi().alert("The active cell "+a1+" value is "+val);
}

function getByRowAndColumn()
{
var ui = SpreadsheetApp.getUi();

var response = ui.prompt(
'Cell address',
'Please enter the cell address in this format: row,col for example, 5,2 means row 5 col 2',
ui.ButtonSet.OK);

var resp_text = response.getResponseText();

var match = resp_text.match(/^(d+),(d+)/);


var row = parseInt(match[1]);
var col = parseInt(match[2]);
var value = SpreadsheetApp.getActiveSheet().getRange(row,col).getValue();


SpreadsheetApp.getUi().alert("The value is "+value);
}

function getByCellAddressA1Notation()
{
var ui = SpreadsheetApp.getUi();

var response = ui.prompt(
'Cell address',
'Please enter the cell address (A1 notation)',
ui.ButtonSet.OK);

var a1 = response.getResponseText();

var value = SpreadsheetApp.getActiveSheet().getRange(a1).getValue();

ui.alert("cell value "+value);
}