How do I get cycle through a column and when a certain value is hit, an email is sent based on value-CodePudding

I am new to coding, specifically using google script. I have a spreadsheet with documents and document ids, and when overdue the due date changes to red and done like a traffic light system.

However what I need to set up for work is an automatic email to be sent out when a value in column L = 1 (based on the red and other values in the row being true) and if the value is 0 I want the script to pass over it and move down through the column.

I have tried reading and testing codes around using do and while and for etc, but I just can’t get it to work.

So what I have set up is inefficient and rudimentary I’m sure, but it works, just slowly - see code below.

When it does hit a 1, it then uses activate cells and offsets to get values from cells in the corresponding row and package up an email and send. The script then moves down and checks the next cell, but because I’m using activate and calling multiple functions it is taking a long time to cycle through the column.

The column is L and the range in use is L2:L60

I’d be so grateful if anyone can point me in the right direction of what the best method would be for this please.

function Findfirstcell() 
{
SpreadsheetApp.getActive().getRange('l2').activate();
GetLvalue();  
}

function GetLvalue() 
{
var st= SpreadsheetApp.getActive().getActiveSheet().getActiveCell().getValue();
if (st < 1)
Movetonext();
else
Createemail();
}

function Createemail() 
{
var ss= SpreadsheetApp.getActive()
var x = ss.getActiveSheet()
const s= x.getActiveCell();
var y= s.offset(0,-5);
var v= y.getValue();
var t= s.offset(0,-10);
var z= s.offset(0,-11);
var p= t.getValue();
var w= z.getValue();
var emailAddress = v '@gmail.com'
var message = p " is overdue - Document ID  " w
var subject = w "  is Overdue"
MailApp.sendEmail(emailAddress, subject, message);
var w= s.offset(0,-3);
w.setValue('Sent')
var end= s.offset(1,0);
end.activate();
GetLvalue();
}

function Movetonext()
{
var ss= SpreadsheetApp.getActive();
var s= ss.getActiveSheet().getActiveCell();
var end= s.offset(1,0);
end.activate();
var v= s.getValue();
if (v < 0) 
ss.getRange('a1').activate();
else
GetLvalue();
}

I have tried several other methods with no success as mentioned, one being below, where it does it for the first row (which may be because the value is 1), but it does not then continue down the column. I assumed as the condition had failed, triggering function createemail and there is nothing to loop it back

function Findfirstcell() 
{
var ss= SpreadsheetApp.getActiveSheet().getActiveCell('l2').activate;

// Or 

var ss= SpreadsheetApp.getActiveSheet().getRange('l2:l60');

for(ss<0; ss>0; ss  ){createemail();}
}

function createemail()
{
var st= SpreadsheetApp.getActive().getCurrentCell();
var y= st.offset(0,-5);
var v= y.getValue();
var t= st.offset(0,-10);
var z= st.offset(0,-11);
var p= t.getValue();
var w= z.getValue();
var emailAddress = v '@gmail.com'
var message = p " is overdue - Document ID  " w
var subject = w "  is Overdue"
MailApp.sendEmail(emailAddress, subject, message);
}

Any guidance would be greatly appreciated

CodePudding user response：

I believe your goal is as follows.

You want to check whether the value of the column "L" is 0 or 1.
When the value is 1 and the value of column "I" is not Sent, you want to send an email using the values of columns "A", "B" and "G".
When the email is sent, you want to put the value of Sent to the column "I".
You want to reduce the process cost of your script.

In this case, how about the following flow?

Retrieve values from the active sheet.
Check the value of column "I" and "L" and send the email and return the range list.
When the email is sent, the value of "Sent" is put to the column "I".

When this flow is reflected to a Google Apps Script, it becomes as follows.

Sample script:

function myFunction() {
  // 1. Retrieve values from the active sheet.
  const sheet = SpreadsheetApp.getActiveSheet();
  const [, ...values] = sheet.getDataRange().getValues();

  // 2. Check the value of column "I" and "L" and send the email and return the range list.
  const rangeList = values.reduce((ar, [a, b, , , , , g, , i, , , l], r) => {
    if (l > 0 && i != "Sent") {
      var emailAddress = g   '@gmail.com';
      var message = b   " is overdue - Document ID  "   a;
      var subject = a   "  is Overdue";
      MailApp.sendEmail(emailAddress, subject, message);
      ar.push(`i${r   2}`);
    }
    return ar;
  }, []);

  // 3. When the email is sent, the value of "Sent" is put to the column "I".
  if (rangeList.length > 0) sheet.getRangeList(rangeList).setValue("Sent");
}

Sample script:

References: