问题：基于电子邮件的Google表单回复

问题描述：

我找到了我正在寻找的答案的一部分。以下是我更新的问题：在表单提交时，根据表单中提供的答案生成定制电子邮件。我遇到的问题是当前的代码没有提供最后一行中的数据。我收到的电子邮件的数据来自右侧的表格，右侧的一列，但是错误的一行。我想要与最后一行相关的数据（最后一个表单提交）。我该怎么做呢？问题：基于电子邮件的Google表单回复

function sendEmail(range) { 
Logger.log("[METHOD] sendEmail"); 


var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('THIS SHEET HAS THE DATA I WANT'); 
var values = sheet.getDataRange().getValues() 
var row = values[0]; 

var Name = row[1]; 
var teacherEmail = row[2]; 
var principalEmail = row[3]; 
var riskType = row[13]; 
var riskTodo = row[14]; 

var emailRecipients = "[email protected]"; 
var emailSubject = "Trip plan submitted by "+teacherEmail+""; 
var emailBody = "This is static text that gets pre-filled, the text below is taken directly from the google sheet (and based on what the teacher clicks)<p>\ 
<h3 style='line-height:90%'> These are the hazards: "+riskType+"<br /> <br>(These are the defenses "+riskTodo+")"; 

// SEND EMAIL // 
MailApp.sendEmail({ 
to: emailRecipients, 
subject: emailSubject, 
htmlBody: emailBody 
}); 
}

这是链接到窗体：https://docs.google.com/spreadsheets/d/1EUPTkTtJkPA9hEdrb7TM-VOzy6_-ib0Qg9-KZezAgPQ/edit?usp=sharing

你能指定什么不工作？问题是在发送电子邮件后持续存在数据，还是首先生成电子邮件？ –

什么是错误？ –

“不起作用”不会为我们提供有用的信息。请详细说明*如何*它不起作用。你有错误吗？预期的行为是什么？它实际上在做什么？ – Amy

答

我设法找到一个答案，我的问题（一个程序员或两个的帮助）。我在这里发布答案以防别人帮助别人。我有3个需求。

第一：我需要从特定工作表中获取电子邮件数据（表单答案需要在单独的工作表上完成一些计算）。

其次，我需要找到该工作表的最后一行。第三：因为我在第二张纸上的计算在所有单元格中都重复了（为了在新表单被回答时自动执行处理过程），我需要将最后一行标识为字符串（单元格中的可见文本），而不是值在单元格内（大部分单元格中的背景公式被视为具有值且不为空的单元格）。

下面是代码：

function sendEmail(range) { 

    //Get values in last row of sheet (sheet has a repeating formula in background. 
    // This requires more code to identify last row with 
    // written content as opposed to last row with formula in it) 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetname'); //this goes 
              //to the specific sheet I want the data from 
var values = sheet.getDataRange().getValues(); 

var column_2_Data; 
    column_2_Data = sheet.getRange(1,2,sheet.getLastRow(),1).getValues(); 
    column_2_Data = column_2_Data.toString();//this converts column 2 to string 
    column_2_Data = column_2_Data.split(",");//this converts to a 1D array 
     //I chose column 2 because this column did not have a formula in it. 
     //It ended at the same row as the last form submission. 

var lastRowOfData = column_2_Data.indexOf("");//this gets the index of the empty element 
               //(the position of the empty row) 
var lastRow = lastRowOfData - 1; //data is zero indexed - subtract 1 

     //code to link customized portion of the email with specific columns in sheet 
var row = values[lastRow]; 
var name = row[1]; 
var personXEmail = row[2]; 
var bossEmail = row[3]; 
var riskType = row[13]; 
var riskTodo = row[14]; 

     //email content 
var emailRecipients = ""+bossEmail+""; 
var emailSubject = "Trip plan submitted by "+personXEmail+""; 
var emailBody = "This is static text that gets pre-filled, the text below is taken directly from the google sheet (and based on what the person clicks)<p>\ 
        <h3 style='line-height:90%'> These are the hazards: "+riskType+"<br /> <br>(These are the defenses "+riskTodo+")"; 

    // SEND EMAIL 
MailApp.sendEmail({ 
to: emailRecipients, 
subject: emailSubject, 
htmlBody: emailBody 
}); 
}

如果你只需要从没有重复的公式是片的最后一行获取数据，你可以使用这个简单的代码。

function sendEmail(range) { 
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetname'); 
var values = sheet.getDataRange().getValues(); 
var lastRow; 
    lastRow = values.length - 1;//data is zero indexed - subtract 1 
var row = values[lastRow]; 

    //the rest would be 
var name = row[1]; 
var personXEmail = row[2]; 
    //etc......

我希望这会有所帮助。

问题：基于电子邮件的Google表单回复

相关推荐