I am a first-time user of Javascript as well as App script and I appreciate your help. In the following query which I manage to write, I want to get a list of 'Activity' numbers on the email if the check values are equal to 1. The missing part at the moment is adding 'Activity' numbers to the email. I will attach a screenshot of my sheet as well. Thank you all in advance.

``
function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Comparison'); 
  const data = sh.getRange('I2:I').getValues();
  const data2 = sh.getRange('J2:J').getValues();
  //const activity = sh.getRange('C2:C').getValues();
  var send= false;
  var send2= false;

  data.forEach(r=>{
    //Logger.log(r)
     let check = r[0];  
     if (check === 1){
          send= true

     }
  });  

  data2.forEach(r=>{
    //Logger.log(r)
     let check2 = r[0];  
     if (check2 === 1){
          send2= true

     }
  }); 

  
  if (send=== true || send2 === true){       
        let subject = 'Some dates have changed'
        let message = "Please look at the dashboard below to find the changes:\n  " ;
        //Logger.log(message)
        MailApp.sendEmail('[email protected]', subject,message); 
         }
         

}
``

I have tried the above code and just need a bit help for one more addition to that code.

CodePudding user response：

I didn't test this because I don't want to send mayself a bunch of emails but I'm pretty sure it will work.

First, rather than get each column seperately just get all the data at once using .getDataRange().getValues().

Next remove the headers data.shift().

Now using Array.some() the first occurance of 1 in column I or J triggers the send flag and the loop exits.

So send the email notice.

function sendEmail() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh = ss.getSheetByName('Comparison');
  let data = sh.getDataRange().getValues();
  data.shift(); // remove headers
  let send= false;

  data.some( row => {
      if( ( row[8] === 1 ) || ( row[9] === 1 ) ) {
        send = true;
        return true;
      }
      return false;
    }
  );

  if ( send ) {       
        let subject = 'Some dates have changed'
        let message = "Please look at the dashboard below to find the changes:\n  " ;
        //Logger.log(message)
        MailApp.sendEmail('[email protected]', subject,message); 
  }       

}

Reference

• Sheet.getDataRange()
• Array.shift()
• Array.some()
• Best Practices

CodePudding user response：

This should work.

You don't have to getValues column by column with script, since it takes quite some time to call getValues(), the more times you do it, the slower you script will run. (unless your data size is too big),

Normally, it is better to get the whole table, and process it with javascript.

In this script, all values in the range A2:J are loaded in one go.

.some() method will return a boolean value if the iterated array matchs any of the set condtion, in which our case is row[6] or row[7] is not 0 or ''.

Since JavaScript array runs in 0 index base, the index 6 and 7 of the array 'row' is equal to column I and J if you loaded data starts from column A.

In JavaScript, 0, undefined, false, are all falsy values, with double '!' before a value will return false if the value is falsy, otherwise return true.

When condition is matched, (in our case, variable 'check' is true), get the list of changes by filtering the data array with only rows where Column I or J is not a falsy value, than map the array row to only returns index 0 of the row, which is column A, aka 'ACTIVITY'.

Here the list is you need is formed, but in array format, but to print the array into an email, you need to change the array data into string, .join() method get this done, joined every value of the array with '\n'.

At last, placed the list into you email message.

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Comparison'); 
  const data = sh.getRange('A2:J').getValues();
  const send = data.some(row => (!!row[6] || !!row[7]));
  
  if (send) {
    const listOfChanges = data
      .filter(row => (!!row[6] || !!row[7]))
      .map(row => row[0])
      .join('\n');
    const subject = 'Some dates have changed';
    const message = `Please look at the dashboard below to find the changes:\n${listOfChanges}`;
    MailApp.sendEmail('[email protected]', subject,message);
  }
}

!! Be careful, if the values you put into Column I and J is not a number 0, but a string "0" instead, this may break the falsy checking.

If this is your case, consider to change the checking from !!row[6] || !!row[7] to row[6] == 0 || row[7] == 0