How to add cc in Google Apps Script and onFormSubmit trigger

user_16240325 0
Viewed 53

I am not sure how can I add a line for cc. Below is the script i have. Grateful if anyone can help. Thanks!

Meanwhile, I know there is a trigger called onFormSubmit trigger. May I know how can I send an email according to the specific answer coming in from a form?

For instance, in the Google form. I have question like Which colour do you like? Red or Yellow. If someone picked yellow, an email would be sent. However, I am not sure how to do that.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Send Email",
    functionName : "sendEmails"
  }
                ];
  sheet.addMenu("Let's do it", entries);
};

function hindex(header_row, value){
  for (var j = 0; j < header_row.length; j++) {
    if(value == header_row[j]) return j;
  }
  return 0;
}
  

function sendEmails() {
  var htmlcode = String(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HTML").getRange(1, 1).getValue());
  var replyTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sender").getRange(2, 2).getValue();
  var name_shown = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sender").getRange(1, 2).getValue();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Receiver List");
  var htmlsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Testhtml");
  var startRow = 2;  // First row of data to process
  var LastRow = sheet.getLastRow();   // Number of rows to process
  
  var dataRange = sheet.getRange(startRow, 1, LastRow-1, 50);
  var data = dataRange.getValues();
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    
    var name = row[0];
    var emailAddress = row[1];
    var subject = row[2];
    var confirm = row[3];
    var EmailSent = row[4];
    var strings = data[0];
    var obj = {};
    var message = htmlcode;
    
    for (var k = 0; k < strings.length; k++){
      if(strings[k].length == 0)continue;
      obj[strings[k]] = row[hindex(data[0],strings[k])];
      var reg = new RegExp("\{\{"+strings[k]+"\}\}", "g");
      //htmlsheet.getRange(k+1, 3).setValue(reg);
      message = message.replace(reg,obj[strings[k]])
    }
/*    
    var r1 = row[hindex(data[0],"r1")];
    var r2 = row[hindex(data[0],"r2")];
    var r3 = row[hindex(data[0],"r3")];
    var r4 = row[hindex(data[0],"r4")];
    var r5 = row[hindex(data[0],"r5")];
    var r6 = row[hindex(data[0],"r6")];
    var r7 = row[hindex(data[0],"r7")];
    var r8 = row[hindex(data[0],"r8")];
    var r9 = row[hindex(data[0],"r9")];
    var r10 = row[hindex(data[0],"r10")];
    var r11 = row[hindex(data[0],"r11")];
    var r12 = row[hindex(data[0],"r12")];
    var r13 = row[hindex(data[0],"r13")];
    var q1 = row[hindex(data[0],"q1")];
    var r14 = row[hindex(data[0],"r14")];
    var q2 = row[hindex(data[0],"q2")];
    var q3 = row[hindex(data[0],"q3")];
    var q4 = row[hindex(data[0],"q4")];
    
    var q5 = row[hindex(data[0],"q5")];
    var r15 = row[hindex(data[0],"r15")];
    var q6 = row[hindex(data[0],"q6")];
    var r16 = row[hindex(data[0],"r16")];
    var q7 = row[hindex(data[0],"q7")];

 
    message = htmlcode.replace(/\{\{r1\}\}/,r1)
                      .replace(/\{\{r2\}\}/,r2)
                      .replace(/\{\{r3\}\}/,r3)
                      .replace(/\{\{r4\}\}/,r4)
                      .replace(/\{\{r5\}\}/,r5)
                      .replace(/\{\{r6\}\}/,r6)
                      .replace(/\{\{r7\}\}/,r7)
                      .replace(/\{\{r8\}\}/,r8)
                      .replace(/\{\{r9\}\}/,r9)
                      .replace(/\{\{r10\}\}/,r10)
                      .replace(/\{\{r11\}\}/,r11)
                      .replace(/\{\{r12\}\}/,r12)
                      .replace(/\{\{r13\}\}/,r13)
                      .replace(/\{\{r14\}\}/,r14)
                      .replace(/\{\{r15\}\}/,r15)
                      .replace(/\{\{r16\}\}/,r16)
                      .replace(/\{\{q1\}\}/,q1)
                      .replace(/\{\{q2\}\}/,q2)
                      .replace(/\{\{q3\}\}/,q3)
                      .replace(/\{\{q4\}\}/,q4)
                      .replace(/\{\{q5\}\}/,q5)
                      .replace(/\{\{q6\}\}/,q6)
                      .replace(/\{\{q7\}\}/,q7)
                      .replace(/\{\{name\}\}/,name);    
 */
    
    htmlsheet.getRange(1, 1).setValue(message);
    htmlsheet.getRange(1, 2).setValue(JSON.stringify(obj));
    if (confirm == "Yes" && EmailSent != "Email Sent") { 
      MailApp.sendEmail(emailAddress,subject, "", {
        htmlBody: message,
        replyTo: replyTo,
        name: name_shown
      });
      sheet.getRange(startRow + i, 5).setValue("Email Sent");
    }
  }
}
google-apps-script
1 Answers

Add cc:

The cc is an advanced parameter at sendEmail(recipient, subject, body, options), like htmlBody or replyTo. Therefore, when using sendEmail, you should add it this way:

MailApp.sendEmail(emailAddress,subject, "", {
  htmlBody: message,
  replyTo: replyTo,
  name: name_shown,
  cc: "YOUR_DESIRED_CC_EMAIL"
});

Use onFormSubmit:

There are two types of onFormSubmit triggers, one for the spreadsheet linked to the Form and the other for the Form itself. Depending on which one you install (see Installable Triggers for more information on how to install a trigger), you'd retrieve the value of a response in different ways, using the corresponding event object.

It's probably easier to get this information via the Sheets event object, so I'd suggest you to use the Sheets trigger. In this case, the event object has a field namedValues, which refers to an object containing the question names and values from the form submission. In the function triggered by the form submission, just find the key that corresponds to the item you want to fetch the response from, and retrieve its value. For example:

function triggeredByFormSubmit(e) {
  const submittedColor = e.namedValues["Which colour do you like?"];
  // ...
}

If you installed a Form's onFormSubmit instead, you should find the item response via the event property response, which corresponds to the FormResponse.

Note: Please note that this is supposed to run automatically when a user submits a form. If you try to execute it directly from the editor, or from the custom menu, it will fail because the event object (in the sample above, e) won't be defined.

user_106120110
Related