CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Erinsa
Erinsa

Reputation: 1

Automated emailing using information from a google form and different spreadsheets?

I'm new to google scripts so please bear with me. I'm working on automating communication for a 2 condition study signup using a google form and google sheets. Essentially, when a participant submits a form, I need to send a confirmation email (trigger is form submission) with the date they chose, and a new unique ID based on their condition (I have two separate lists of premade IDs I need to use). Right now, I have the main form response sheet and then I'm using = query to separate the survey responses into sheets for the two conditions based on a multiple choice question in the survey ('Morning' and 'Evening'). I have the ID lists filled into the corresponding sheets so that when the = query sorts the responses, they are automatically "assigned" an ID as the rows fill. I have the code working to send emails without the IDs, just using the name and date in the first sheet of the spreadsheet (the form response sheet), but am running into problems grabbing the ID from the other corresponding sheet to put into the email.

Here is what I have:

  function IntialEmails(e){
  var ss = SpreadsheetApp.openById('MyownSpreadsheetID'); //not including real ID for privacy reasons
  var type = e.values[4];

  if (type == 'Condition A') {
    var sheet = ss.getSheets()[1];
    ss.setActiveSheet(sheet);
  }

  if (type == 'Condition B') {
    var sheet = ss.getSheets()[2]; 
    ss.setActiveSheet(sheet);
  }


  var userName = sheet.values[0];
  var userEmail = sheet.values[2];
  var date = sheet.values[4]; //Using date from corresponding spreadsheet based on survey format
  var ID = sheet.values[5]; // Should be from corresponding sheet
  var subject = "Study Signup Confirmation and Participant ID";

  var templ = HtmlService
      .createTemplateFromFile('My html'); //not including real ID for privacy reasons

  templ.date = date;
  templ.ID = ID;
  templ.userName = userName;

  var message = templ.evaluate().getContent();

  MailApp.sendEmail({
    to: userEmail,
    subject: subject,
    htmlBody: message
  });

}

I am super stuck, and continuously getting errors. This code throws back the error 'TypeError: Cannot read property '0' of undefined' If anyone can help me out it would be much appreciated!

Upvotes: 0

Views: 65

Answers (1)

Cooper
Cooper

Reputation: 64072

If you want to get a value from a sheet you can do something like this:(Even I wouldn't normally do it this way I'm kind of following the same method that you used. I'm going to assume that you have your id's or what ever data in rows. But obviously I don't know since you didn't provide that information.

function initEmails(e) {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  var sh=(e.values[4]=="Condition A")?shts[1]:shts[2];
  var vs=sh.getRange(1,1,sh.getLastRow(),7).getValues();
  for(var i=0;i<vs.length;i++) {
    if(vs[i][7]!='USED') {
       var userName=vs[i][0];
       var userEmail=vs[i][2];
       var date=vs[i][4];
       var ID=vs[i][5];
       var subject = "Study Signup Confirmation and Participant ID";
       sh.getRange(i+1,8).setValue('USED');//this provides a way to keep track of which row was used last
    } 
    /*.............I have no idea what your trying to do beyond this point.  

  }

But I don't think that you going to be able to insert these values into your html template this way:

templ.date = date;
templ.ID = ID;
templ.userName = userName;

Normally, templates are filled with desired data during the evaluate process by using scriptlets.

Templated HTML

Upvotes: 0

Related Questions