0

I'm trying to create a script which will do the following:

  • Check if the number in col. B for each row is > 4
  • If it is: send an email & mark the associated cell in col. C as "Yes"

e.g.:

enter image description here

--> Here, the email should be sent for FL, and mark "Yes" in col. C

Here's my code so far, the problem seems to come from the HCrange.getRange at the very end, but not sure how to fix it


function checkHC() {
 // Load the sheet that contains the State & EEs.
 var sheet = SpreadsheetApp.getActive().getSheetByName("Intrinsic State Location");
 var HCrange = sheet.getRange(1,1,sheet.getLastRow(),3).getValues();

 // Use a for loop to process each row of data
 for(var index in HCrange) {
 
   // For each row, get the state's name and number of EEs
   var column = HCrange[index];
   var state = column[0];
   var NumberOfEmployees = column[1];
   var RegistrationCommunicated = column[2];
 
   // Check if there are more than 4 EEs
    if((NumberOfEmployees > 4) && (RegistrationCommunicated ==! "Yes")) {
     //If yes, send an email reminder
     emailReminder(state,NumberOfEmployees,HCrange);
   }
 }
 }
 
// Function to send the email reminder
function emailReminder(state,NumberOfEmployees,HCrange) {
 var emailRange = SpreadsheetApp.getActive().getSheetByName("Intrinsic State Location").getRange("F2");
 var subjectRange = SpreadsheetApp.getActive().getSheetByName("Intrinsic State Location").getRange("G2");
  var emailAdress = emailRange.getValue();
  var subject = subjectRange.getValue() + state;
 var message = "Please register " + state + " with payroll, as it now has " + NumberOfEmployees + " employees";
 MailApp.sendEmail(emailAdress, subject, message);

for(var i=0;i<100;i++){
 var sheet = SpreadsheetApp.getActive().getSheetByName("Intrinsic State Location");
 var HCrange = sheet.getRange(1,1,sheet.getLastRow(),3).getValue();
 HCrange.getRange(i+1,1).setValue("Yes");
 }
}
1
  • Welcome to Web Applications What is the problem? If you got an error message, add it textually.
    – Rubén
    Jun 29 at 8:30

1 Answer 1

0

Please consider these changes


function checkHC() {
  // Load the sheet that contains the State & EEs.
  var sheet = SpreadsheetApp.getActive().getSheetByName("Intrinsic State Location")
  var HCrange = sheet.getRange(1,1,sheet.getLastRow(),3).getValues();

  // Use a for loop to process each row of data
  for(var index in HCrange) {  
   
    // For each row, get the state's name and number of EEs
    var state = HCrange[index][0]
    var NumberOfEmployees = HCrange[index][1]
    var RegistrationCommunicated = HCrange[index][2]

    // Check if there are more than 4 EEs
    if((NumberOfEmployees > 4) && (RegistrationCommunicated ==! "Yes")) {
      //If yes, send an email reminder
      //emailReminder(state,NumberOfEmployees,index);
      Logger.log("send email reminder for index:"+index+", state:"+state)
    }
 }
}

// Function to send the email reminder
function emailReminder(state,NumberOfEmployees,index) {
 
  var sheet = SpreadsheetApp.getActive().getSheetByName("Intrinsic State Location");
  var emailAdress = sheet.getRange("F2").getValue();
  var subject = sheet.getRange("G2").getValue() + state;
  var message = "Please register " + state + " with payroll, as it now has " + NumberOfEmployees + " employees";
  //MailApp.sendEmail(emailAdress, subject, message);

  var commRange = sheet.getRange(+index+1,3)
  commRange.setValue("Yes")
  Logger.log("Communication value updated")
}

Function checkHC

  • var column = HCrange[index]; This does NOT work the way you think it does. Plus it is inefficient. I deleted this and declared the variables in the next three rows.
  • emailReminder does not need HCrange as an argument. However it does need the index value.

Function emailReminder

  • it is more efficient to declare the sheet just once.
  • there is no benefit to declaring the email and subject ranges as well as the values. I've combined them.
  • the 100-event loop serves no purpose.
    • the range for the Communication value can be established using the index
    • I've split the range and the value so that you can focus on how the range is determined. In practice a single line of code would be sufficient.
    • Such as: sheet.getRange(+index+1,3).setValue("Yes")

FWIW #1: HCRange is actually the values, not the range. For clarity I would suggest do a search and replace on "HCRange" => "hcValues"

FWIW #2: You'll notice that HCRange, NumberOfEmployees and RegistrationCommunicated appear in red in the editor rather than black. That's because they are supposed to be written in "lowerCamelCase". With lowerCamelCase, a name is created by joining one or more words to look like a single word and the first letter of each word is capitalised except for the first word.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.