Applying a formula to all cells in a column, unles

2019-09-04 18:04发布

站内文章 / 后端开发
29 0 0

问题:

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.

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);

回答1:

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 - 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.

The logic works like this:

  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.

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

    • 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'.

    • Check that each of the following columns evaluate as true.

      • Column J (your Column M) <=0 (less than or equal to zero)
      • Column K (your Column AA) <>"" (isn't blank)
      • Column L (your Column AE) <>"" (isn't blank)
      • If these all evaluate as true, then 'Send an email', otherwise 'Wait'.

  3. 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 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.

What happened to AND and OR in the IF statement?

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.

UPDATE: Code changes

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 - 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;
}

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

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

Code Only Solution

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

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

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

        }
    }
}


标签： google-apps-script google-sheets
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~