CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Robin Silk
Robin Silk

Reputation: 1

Checking a Cell's Value, Changing Another Cell's Value accordingly, and Sending An Email Too

I am trying to write a script that will check a cell for the value "Done". If the script detects that "Done" is present in this first cell, it will write the value "X" to a corresponding cell (same row). Additionally, an email will be sent with other relevant information within that same row.

Every time the spreadsheet is opened I want the script to execute. The purpose of writing "X" in the corresponding cell is so that the script can check if that row has already been accounted for and an email has already been sent. The presence of an "X" will have the script move on to the next row without sending an email (avoiding two emails being sent about the same row).

Ultimately, I will need it to check three rows for "Done" (and three corresponding rows for "X").

I am at the point where my script can successfully check:

1) If "Done" is present 2) If an "X" is present 3) Send an email accordingly.

What I need help with is:

1) Figuring out how to write "X" to a cell! I cannot figure it out. My currently methodology might not be appropriate for what I am trying to get done.

I don't have enough "reputation" to post a screenshot of the google sheet, unfortunately.

I have tried using setValue to assign "X" to the appropriate cell but I am given the following error:

TypeError: Cannot find function setValue in object . (line 35, file "Combining Attempt")

function SendEmails() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.setActiveSheet(sheet.getSheetByName("Sheet1"));

  var DataRange = sheet.getRange("A:J");
  var data = DataRange.getValues();

  //iterating through the data
  for (i in data) {
    var rowData = data[i];

    var emailAddress = rowData[0];
    var parameter = rowData[1];
    var user = rowData[2];
    var message1 = rowData[3];
    var parameter2 = rowData[4];
    var message2 = rowData[5];
    var parameter3 = rowData[6];

    var p1Check = rowData[7];
    var p2Check = rowData[8];
    var p3Check = rowData[9];

    //checking to see if the value "Done" is present
    if (parameter == "Done") {

      //checking to see if the value "X" is present (implying a corresponding email has already been sent)
      if (p1Check !== "X") {

        var messageComplete = message1 + " This is for " + user;
        var subject = 'Client Status email update for ' + user;      

        //this line below is what I am trying to use to set the corresponding cell value to "X"; it doesn't work
        //p1Check.setValue("X");


        //Sending the email
        MailApp.sendEmail(emailAddress, subject, messageComplete); 
      }
      else {
          }

    }
    else {
    }


}
}

As mentioned above, I need the corresponding cell in column H to have "X" written to it when the condition is "Done". This is not happening and I am getting an error with my current attempted solution.

Any help would be appreciated.

Upvotes: 0

Views: 54

Answers (2)

TheMaster
TheMaster

Reputation: 50445

There's no setValue function in the object p1Check. setValue(s) is only present in range object, while p1Check is a element of a 2D array.

Easiest way is change the data array and set it back to the DataRange

for(var i in data){
  if (parameter === "Done" && p1Check !== "X" ) {
      var messageComplete = message1 + " This is for " + user;
      var subject = 'Client Status email update for ' + user;      
      rowData[7] = "X"; //set rowdata[7](p1Check) as X
  }
}
DataRange.setValues(data);//modified data is set back

Upvotes: 0

Cooper
Cooper

Reputation: 64042

Try this:

This is not tested since I have no data. But I made a few tweaks that could help.

function SendEmails() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sheet.getRange(1,1,sh.getLastRow(),sh.getLastColumn());
  var data=rg.getValues();
  for(var i=0;i<data.length;i++) {
    var rowData=data[i];
    var emailAddress=rowData[0];
    var parameter=rowData[1];
    var user=rowData[2];
    var message1=rowData[3];
    var parameter2=rowData[4];
    var message2=rowData[5];
    var parameter3=rowData[6];
    var p1Check=rowData[7];
    var p2Check=rowData[8];
    var p3Check=rowData[9];
    if(parameter=="Done" && p1Check!="X") {
      var messageComplete=message1 + " This is for " + user;
      var subject='Client Status email update for ' + user;      
      sh.getRange(i+1,8).setValue("X");
      MailApp.sendEmail(emailAddress, subject, messageComplete); 
    }
  }
}

Upvotes: 0

Related Questions