将公式应用于列中的所有单元格，除非等于x [英] Applying a formula to all cells in a column, unless equals x

问题描述

我试图将公式应用于列，但仅当单元格不包含特定值(电子邮件已发送)时，我才具有一个脚本，当行在"AF"列中具有发送电子邮件"时发送电子邮件，并且替换为"EMAIL SENT"以避免重新发送，我需要它跳过其中包含"EMAIL SENT"的单元格.

I am trying to Apply a formula to a column but only if the cell does not contain a certain value (EMAIL SENT), I have a script that sends emails when a row has "send email" in column "AF" and replaces it with "EMAIL SENT" to avoid re-sends, I need it to skip cells with "EMAIL SENT" in them.

这是我到目前为止拥有的脚本，但是它会重写单元格并将单元格重置为发送电子邮件". 提前谢谢.
初级脚本编写者.
ps或使该公式从AF列的第一个空单元格开始.

Here is the script I have so far but it rewrites over cells and resets cells back to "send email". Thanks in advance.
A Beginner scripter.
ps or to get this formula to start at the first empty cell in column AF.

  var sss4 = SpreadsheetApp.openById('<ID>');
  var ss4 = sss4.getSheetByName('Results');
  ss4.getRange("AF2").setFormula('=IF(((AND(OR(I2="YOUR TRIP DWL",I2="MEGA PACK DWL (YT + AA + BONUS)"),M2<=0,AA2<>"",AE2<>""))), "Send Email", "Wait")');
  var lr4 = ss4. getLastRow();
  var filldownrange4 = ss4.getRange(2, 32, lr4-1);
  ss4. getRange("AF2").copyTo(filldownrange4);

推荐答案

我建议不要直接通过脚本插入公式，而建议直接将公式放入电子表格中.这将大大简化您的代码.

Rather than insert a formula by script, I suggest putting the formula directly into the spreadsheet. This would greatly simplify your code.

我接受了您的公式并添加了一些元素:

I took your formula and added some elements:

1-将列AF转换为在发送电子邮件时会更新的列.
2-添加了状态"列(也许是AG ??列).这就是公式的去处.
3-将公式转换为ARRAYFORMULA.请注意，公式中的每个范围都包括整列(例如I2:I).这还需要将AND替换为"*"，将OR替换为"+".请参阅下面的说明.
4-添加了ARRAY_CONSTRAIN，以便仅在添加新数据行时才添加公式.如果不在这里，那么该公式将显示在表格底部的每一行中-看起来不美观，并且有点令人反感.

1 - Turned Column AF in to a column that gets updated when an email is sent.
2 - Added a "Status" Column (maybe Column AG??). This is where the formula goes.
3 - Converted the formula to ARRAYFORMULA. Note that each range in the formula includes an entire column (for example I2:I). This also required replacing AND with "*" and OR with "+". Refer below for an explanation.
4 - Added ARRAY_CONSTRAIN so that the formula is added only as you add a new row of data. If this wasn't here, then the formula would display in every row of the column to the bottom of the sheet - not a great look and a bit off-putting.

=ARRAY_CONSTRAIN(ArrayFormula(if(M2:M="EMAIL SENT","EMAIL SENT",IF(((I2:I="YOUR TRIP DWL")+(I2:I="MEGA PACK DWL (YT + AA + BONUS)"))*(J2:J<=0)*(K2:K<>"")*(L2:L<>""), "Send Email", "Wait"))),COUNTA(I2:I),1)

---

这是我的布局和公式结果的屏幕截图.

---

Here's a screenshot of my layout and formula results.

逻辑原理如下:

1. 检查M列(您的AF列).评估是否已发送电子邮件.如果是，则什么也不做；如果否，则评估是否该发送电子邮件.

1. Check Column M (your Column AF). Evaluate whether an email has already been sent. If yes then do nothing; if no then evaluate whether it is time to send an email.

如果尚未发送电子邮件，请评估某些字段的状态.

If an email hasn't been sent yet, then evaluate the status of certain fields.

• 第I栏(您的第I栏)-它是否包含您的旅行DWL"或大型包装DWL(YT + AA +奖金)".如果是，则继续，否则等待".

• Column I (your column I) - does it contain either 'YOUR TRIP DWL' or 'MEGA PACK DWL (YT + AA + BONUS)'. If yes, then continue, otherwise 'Wait'.

检查以下各列的值为true.

Check that each of the following columns evaluate as true.

• J列(您的M列)< = 0(小于或等于零)
• 第K列(您的AA列)<>"(不为空白)
• L列(您的AE列)<>"(不为空白)
• 如果所有这些都评估为真"，则发送电子邮件"，否则为等待".

显示公式结果的行数由ARRAY_CONSTRAIN和参数COUNTA(I2:I)控制.这将对第I列(您的I列)中包含内容的行数进行计数.选择此列的前提是，列L中始终会有一个值.如果不是这样，则选择另一个始终具有内容的单元格/列.如果选择带有数字值的单元格/列，则可能必须将COUNTA更改为COUNT.

The number of rows in which the formula results are displayed is controlled by ARRAY_CONSTRAIN and the parameter COUNTA(I2:I). This counts the number of rows with content in Column I (your Column I). Choosing this column was on the assumption that there would always be a value in Column L. If that's not the case, then choose another cell/column that will always have content. You may have to alter COUNTA to COUNT if you choose a cell/column with numeric values.

ARRAYFORMULA该公式仅输入到一个单元格中(相当于状态"列中的单元格AG2). ARRAYFORMULA逐行评估公式，并在列的每一行中自动显示结果. ARRAY_CONSTRAIN约束结果；在我们的例子中，它仅限于具有值的行.

ARRAYFORMULA The formula is entered into one cell only (the equivalent of Cell AG2 in the "Status" Column). ARRAYFORMULA evaluates the formula on a row-by-row and automatically displays the results in every row of the Column. ARRAY_CONSTRAIN constrains the results; in our case, it is limited to just those rows that have a value.

请注意，IF语句实际上不包含AND和OR函数.显然，它们对ARRAYFORMULA具有无效作用.但要记入 Max Makhrov -sheets-arrayformula-with-nested-if-statements>他建议的带有嵌套if语句的Google表格ARRAYFORMULA ".

Note that the IF statement doesn't literally include the AND and OR functions. Apparently, they have a nullifying effect on ARRAYFORMULA. BUT credit to Max Makhrov in "Google Sheets ARRAYFORMULA with nested if statements" where he advised.

Replace AND with * and OR with +

您的代码的含义

您的代码应遍历并评估状态"列.
如果值为发送电子邮件"，则发送电子邮件并将状态"列更新为发送电子邮件".否则，什么都不做.

Implications for your code

Your code should loop through and evaluate the "Status" Column.
If the value is "Send Email", then send an email and update the "Status" column to "EMAIL SENT". Otherwise, do nothing.

这是与提议的公式一起使用的代码.该代码是自记录的，并且相当容易遵循.大致流程如下.

This is the Code that would go with the formula proposed. The code is self-documented and reasonably easy to follow. The broad flow is processing is as follows.

1-允许用户变量
2-远程打开电子表格
3-计算数据的行数
4-按行循环遍历数据
5-如果单元格值=发送电子邮件"，则发送电子邮件，并使用"EMAIL SENT"更新相邻列的值

1 - allow for user variables
2 - remotely open the spreadsheet
3 - calculate the number of rows of data
4 - loop through the data by row
5 - if cell value = "Send Email", then send email, and update value of adjacent column with "EMAIL SENT"

function so_52680239() {

  // Declare user variables
  // SpreadsheetID
  var ResultsSheetID = "1Iz-qmOnzZp4EAmGzWJORNpJGkueYzGFUTkpUZ9g3-as";
  // SheetName
  var SheetName = "Results";
  // Column letter for the Status Column
  var StatusColumnLetter = "N";
  // Start row for the Status Column
  var StatusHeaderRow = 1;


  // open the Results Spreadsheet
  // Supply the ID
  var ResultsSheet = SpreadsheetApp.openById(ResultsSheetID);
  var Results_ss = ResultsSheet.getSheetByName(SheetName);

  // Convert the column letter for Status Column to a number
  var StatusColumnNumber = letterToColumn(StatusColumnLetter);
  //Logger.log("StatusColumnNumber = "+StatusColumnNumber);

  // Create a string variable range for the Status Column
  var StatusStartRange = StatusColumnLetter+StatusHeaderRow+":"+StatusColumnLetter;
  //Logger.log("StatusStartRange = "+StatusStartRange);

  // Get the last row with content in the StatusColumn
  var LastRowvals = Results_ss.getRange(StatusStartRange).getValues();
  var LastDataRow = LastRowvals.filter(String).length;
  //Logger.log('Last row = '+LastDataRow);

  // declare the search range
  var searchRange = Results_ss.getRange(StatusHeaderRow+1,StatusColumnNumber, LastDataRow-1);
  //var searchRangeA1 = searchRange.getA1Notation();
  //Logger.log('searchRangeA1 = '+searchRangeA1);

  // Get array of values in the search Range
  var rangeValues = searchRange.getValues();


  // Loop through array and if condition met, send email
  // Note, only one column of data so no looping by column
    for ( j = 0 ; j < LastDataRow - 1; j++){
      if(rangeValues[j][0] === 'Send Email'){
        // do stuff
        // insert code to send email
        //Logger.log("j = "+j+", value = "+rangeValues[j][0]+", so Send Email");
        // Update your Column AF (one column to the left of the formula results column) with "EMAIL SENT"
        Results_ss.getRange(StatusHeaderRow+1+j,StatusColumnNumber).offset(0, -1).setValue('EMAIL SENT');
      }else {
        // do Nothing
        //Logger.log("j = "+j+", value = "+rangeValues[j][0]+", so do nothing");
      }; 
    };
}

//Utility to convert a column letter to a column number
function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

---

屏幕截图-运行代码后.注释记录2已更新.

---

Screenshot - AFTER running the code. Note record#2 is updated.

信用: yagisanatode.com，以便快速/高效地更新数据:

Credit: yagisanatode.com for fast/efficient way to update data: Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way

如果无法为公式手动更新电子表格，则脚本可以执行相同的任务.

In the event that the spreadsheet can't be manually updated for the formula, a script can do the same task.

假设:第I列始终包含数据；用于计算要处理的数据行数.

Assumption: Column I always contains data; this is used to calculate how many rows of data to be processed.

条件:如果在运行脚本时AF2中的相关单元格为空，则公式将被插入该单元格中.然后，该公式将评估数据，结果可能是它显示发送邮件"值.但是，没有规定在相同的更新例程期间发送邮件.

Provisio: If, at the time of running the script, the relevant cell in AF2 is empty, then the formula will be inserted in that cell. The formula will then evaluate the data and the result may be that it displays the "Send Mail" value. However, there is no provision to then send the mail during the same update routine.

关于他们如何选择处理此事，我将留给发问者.

I'll leave it to the Questioner as to how they choose to deal with that matter.

function so_52680239_02() {

    // Declare user variables
    // SpreadsheetID
    var ResultsSheetID = "1ILmQ5cAwRD0Va0lPKcAhr7OTvwLaX_UtFCJ8EEL4TwM";
    // SheetName
    var SheetName = "Results01";
    // Column letter for the Status Column
    var StatusColumnLetter = "M";
    // Start row for the Status Column
    var StatusHeaderRow = 1;
    // Column letter for the TripType (assume that there is always data in this cell)
    var TripTypeColumnLetter = "I";


    // open the Results Spreadsheet
    // Supply the ID
    var ResultsSheet = SpreadsheetApp.openById(ResultsSheetID);
    var Results_ss = ResultsSheet.getSheetByName(SheetName);

    // Convert the column letter for Status Column to a number
    var StatusColumnNumber = letterToColumn(StatusColumnLetter);
    // Logger.log("StatusColumnNumber = "+StatusColumnNumber); //DEBUG

    // Convert the column letter for Trip Type Column to a number
    var TripTypeColumnNumber = letterToColumn(TripTypeColumnLetter);
    // Logger.log("TripTypeColumnNumber = "+TripTypeColumnNumber);// DEBUG

    // Create a string variable range for the Status Column
    var StatusStartRange = StatusColumnLetter + StatusHeaderRow + ":" + StatusColumnLetter;
    //  Logger.log("StatusStartRange = "+StatusStartRange); // DEBUG

    // Create a string variable range for the Trip Type Column
    var TripTypeRange = TripTypeColumnLetter + StatusHeaderRow + ":" + TripTypeColumnLetter;
    //  Logger.log("TripTypeRange = "+TripTypeRange); // DEBUG

    // Get the last row with content in the Trip Type Column
    var TripLastRowvals = Results_ss.getRange(TripTypeRange).getValues();
    var TripLastDataRow = TripLastRowvals.filter(String).length;
    //  Logger.log('Last trip row = '+TripLastDataRow);// DEBUG

    // Define the formula
    var myformula = '=IF(((AND(OR(I' + row + '="YOUR TRIP DWL",I' + row + '="MEGA PACK DWL (YT + AA + BONUS)"),M' + row + '<=0,AA' + row + '<>"",AE' + row + '<>""))), "Send Email", "Wait")';

    // declare the search range in Column AF
    var searchRange = Results_ss.getRange(StatusHeaderRow + 1, StatusColumnNumber, TripLastDataRow - 1);
    // Logger.log('searchRange = '+searchRange.searchRangeA1);  //DEBUG

    //Get array of values in the search Range
    var rangeValues = searchRange.getValues();
    //Logger.log("rangevalues = "+rangeValues)

    // establish some variablkes for use in the loop
    var emailformula = "";
    var emailformularange = "";

    // loop for email formula
    for (var row = 0; row < TripLastDataRow - 1; row++) {

        if (rangeValues[row][0] == "") {
            // cell is empty, insert formula

            emailformula = '=IF(((I' + (row + 2) + '="YOUR TRIP DWL")+(I' + (row + 2) + '="MEGA PACK DWL (YT + AA + BONUS)"))*(J' + (row + 2) + '<=0)*(K' + (row + 2) + '<>"")*(L' + (row + 2) + '<>""), "Send Email", "Wait")';
            emailformularange = '' + StatusColumnLetter + (row + 2) + ''
            var rangeList = Results_ss.getRangeList([emailformularange]);
            rangeList.setFormula(emailformula);
            //Logger.log("row = "+row+", value = "+rangeValues[row][0]+", Action: Cell is empty, so insert formula in "+emailformularange);//DEBUG

        } else if (rangeValues[row][0] == "EMAIL SENT") {
            //do nothing

            //Logger.log("row = "+row+", value = "+rangeValues[row][0]+", Action: Email already sent so do nothing"); // DEBUG

        } else if (rangeValues[row][0] == "Wait") {

            //do nothing

            //Logger.log("row = "+row+", value = "+rangeValues[row][0]+", Action: Wait, so do nothing"); //DEBUG

        } else if (rangeValues[row][0] == "Send Email") {

            // Send mail

            emailformularange = '' + StatusColumnLetter + (row + 2) + ''
            Results_ss.getRange(emailformularange).setValue('EMAIL SENT');
            // Logger.log("row = "+row+", value = "+rangeValues[row][0]+", Action: Send Mail and update cell "+emailformularange+" to 'EMAIL SENT'");  //DEBUG

        }
    }
}

这篇关于将公式应用于列中的所有单元格，除非等于x的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！