Spreadsheets的Google脚本（如果语句） [英] Google scripts for Spreadsheets (If statements)

问题描述

我希望有人能帮助我解决这个问题。我是编码新手。我有一个带有脚本的Google电子表格，可以发送电子邮件。我试图让脚本忽略脚本已经发出邮件的行。
帮助请！
提前致谢！

function onOpen（）{var ui = SpreadsheetApp.getUi（）; //或DocumentApp或FormApp。 ui.createMenu（'Email Menu'）.addItem（'Send Email'，'myFunction'）.addSeparator（）.addToUi（）;}; function myFunction（）{var msg ='Email sent！'; var ss = SpreadsheetApp.getActiveSpreadsheet（）var sheet = ss.getActiveSheet（）; var range = sheet.getDataRange（）; var values = range.getValues（）; var EMAIL_SENT =EMAIL_SENT; var startRow = 2; for（i = 1; i< values.length; i ++）{// sent = values [i] [4]; //如果X在已发送列中，则添加此项以尝试跳过一行// if（sent ==，）//如果X在发送列中，则添加此项以尝试跳过一行var email = values [i] [0]; var name = values [i] [i]; var subject = values [i] [2]; var body = values [i] [3]; var sent = values [i] [5]; if（sent！= EMAIL_SENT）{MailApp.sendEmail（email，subject，body）; sheet.getRange（startRow + i，5）.setValue（EMAIL_SENT）; 。用SpreadsheetApp.getUi（）警报（MSG）; }}}

Google电子表格 解决方案小修正。替换

  sheet.getRange（startRow + i，5）.setValue（EMAIL_SENT）; 
  

至

  sheet.getRange（startRow + i  -  1，6）.setValue（EMAIL_SENT）; 
  

根据脚本的其余部分，这应该是最简单的修复方法。这假设已发送列为 F （第6列）而不是 E （第5栏）。包含更新脚本的示例表格此处。

和！您应该将 var name = values [i] [i]; 更改为 var name = values [i] [1];


I am hoping someone can help me with this issue. I am new to coding. I have a google spreadsheet with a script that pushes out an email. I am attempting to have the script ignore the rows where the script has already sent out an email on. Help Please! Thanks in advance!

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Email Menu')
    .addItem('Send Email', 'myFunction')
    .addSeparator()
    .addToUi();
};

function myFunction() {
  var msg = 'Email sent!';
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  var EMAIL_SENT = "EMAIL_SENT";
  var startRow = 2;
  
  for (i=1; i<values.length;i++) {
   
    //sent = values[i][4]; //added this in to try and skip a row if "X" was in Sent Column
    
    //if (sent ==" ", ) //added this in to try and skip a row if "X" was in Send Column
    
    var email = values[i][0];
    var name = values[i][i];
    var subject = values[i][2];
    var body = values[i][3];
    var sent = values[i][5];
    
    if (sent != EMAIL_SENT){
      MailApp.sendEmail(email, subject, body);
      sheet.getRange(startRow + i,5).setValue(EMAIL_SENT);
      SpreadsheetApp.getUi().alert(msg);
    }
 }}
  

Google spreadsheet

解决方案

Small fix. Replace

sheet.getRange(startRow + i,5).setValue(EMAIL_SENT); 

to

sheet.getRange(startRow + i - 1, 6).setValue(EMAIL_SENT); 

According to the rest of the script, this should be the most simple fix. This assumes sent column is F (column 6) not E (column 5). Sample sheet including updated script is here.

And! You should change var name = values[i][i]; to var name = values[i][1]; as @jrook pointed. I missed it...

这篇关于Spreadsheets的Google脚本（如果语句）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！