如何获取单元格值（来自 Google 表单的 Sheet1，来自公式的 Sheet2）到谷歌文档模板

Question

我有 2 张纸。Sheet1 来自 Google Form 并将它们查询到 Sheet2 然后制作公式。我想从他们那里获取单元格值到 Google Doc 模板并发送电子邮件。我可以从 Sheet1 中获取值，但不能从 Sheet2 中获取值。请帮我解决这个问题。谢谢你。 在此处输入图像描述 在此处 输入图像描述

function onFormSubmit(e){

  var docs_email = e.values[1];

  var Name = e.values[2];

  var Date1 = e.values[3];

  var Date2 = e.values[4];

  var Money1 = e.values[5];

  var Money2 = e.values[6];

  //Values from Sheet2

  var TotalDate = e.values[7];

  var Money3 = e.values[8];

  var TotalMoney = e.values[9];

  var copyId = DriveApp.getFileById('Doc ID')  //Temp document ID

  .makeCopy('Report-'+Name)

  .getId();

  var copyDoc = DocumentApp.openById(copyId);

  copyDoc.getBody()

  var copyBody = copyDoc.getActiveSection();

  copyBody.replaceText('keyName', Name);

  copyBody.replaceText('keyDate1', Date1);

  copyBody.replaceText('keyDate2', Date2);

  copyBody.replaceText('keyMoney1', Money1);

  copyBody.replaceText('keyMoney2', Money2);

  copyBody.replaceText('keyTotalDate', TotalDate);

  copyBody.replaceText('keyMoney3', Money3);

  copyBody.replaceText('keyTotalMoney', TotalMoney);

  copyDoc.saveAndClose();

  //convert to pdf

  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");

  //Send the email

  var subject = "Your Report";

  var body = "Dear " +Name+" \n\n"+"<br> This is your report";

  GmailApp.sendEmail(docs_email, subject, body,{htmlBody: body, attachments: pdf});

} 

Answer 1

这个答案怎么样？修改点：为了从“Sheet2”中的“H”到“J”列中检索值，getValues使用。从您的问题中，我可以理解“H”到“J”列具有公式。所以我认为getLastRow()可能无法直接使用。所以我认为需要在“A”列检索第一个空行。当以上几点反映到您的脚本时，它变成如下。修改后的脚本：请按如下方式修改您的脚本。从：var TotalDate = e.values[7];var Money3 = e.values[8];var TotalMoney = e.values[9];至：SpreadsheetApp.flush();&nbsp; // This might be not required.var sheet = e.source.getSheetByName("Sheet2");&nbsp; // Values are retrieved from the sheet name of "Sheet2".var row = 0;for (var i = 1; i < sheet.getLastRow(); i++) {&nbsp; if (sheet.getRange(i, 1).isBlank()) {&nbsp; &nbsp; row = i - 1;&nbsp; &nbsp; break;&nbsp; }}var [TotalDate, Money3, TotalMoney] = sheet.getRange(row, 8, 1, 3).getValues()[0];