Google Apps Script - Search row for string and return row 1 of matching column

Question

I am new to Google App Scripts and have been searching for help with my problem with no luck. I have a google form populating a spreadsheet. Row 1 has the questions and each subsequent row the answers from the form. If a question is answered "Yes" I want to be able to return the question ie. row 1 of that column. I have some code which will send an email if Column 1 (name) is not blank and column 12 (email already sent) is blank. I want to be able to put in the body of the email 'name has answered Yes to the following questions; Q1, Q2 etc.(The headers of each column that have a yes in the searched row) I don't know how to search each row, find the yes answers and return all the column headers.

Here is what I have so far.

// automatically send emails to respondents with their information
function sendEmailv_2() {

  // get the spreadsheet information
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const TodayFail = ss.getSheetByName('TodayFail');
  const data = TodayFail.getDataRange().getValues();
 

  //remove the header
  data.shift();
 
  // loop over the rows
  data.forEach((row, i) => {

    // identify ones not replied to
    if (row[12] ==="" && row[1] !=="") {

      // get the email address
      const name = row[1];
      const email = '[email protected]';
     
      
      // write the email
      const subject = name + ' has answered Yes';
      const body = `Hi  <br><br>
      ` + name + ` has answered yes to the following questions<br><br>
      `;


      // send the email 
      GmailApp.sendEmail(email, subject, '', { htmlBody: body });

      // Mark as sent
      const d = new Date();
      TodayFail.getRange(i + 2, 13).setValue(d);

    }
  });
}```

It would be helpful if you could share the sheet with some examples to provide you with support. You can share in viewer mode. — fullfine
– fullfine, Commented Apr 28, 2021 at 8:38

doubleunary · Accepted Answer · 2021-04-28 09:03:58Z

1

To search each row, find the "Yes" answers, and put the associated column headers in the email body, try this untested code:

function batchSendEmail() {
  const emailAddress = '[email protected]';
  const sheet = SpreadsheetApp.getActive().getSheetByName('TodayFail');
  const [questions, ...data] = sheet.getDataRange().getValues();
  let numRowsToSend = 0;
  let body = 'Greetings.<br /><br />';
  data.forEach((row, rowIndex) => {
    if (!row[1] || row[12]) {
      return;
    }
    const yesQuestions = [];
    row.forEach((answer, columnIndex) => {
      if (String(answer).trim().match(/^(Yes)$/i)) {
        yesQuestions.push(questions[columnIndex]);
      }
    });
    if (yesQuestions.length) {
      body += row[1] + ' has answered "Yes" to the following questions:<br />';
      body += '<ul><li>' + yesQuestions.join('<li>') + '</ul>';
    }
    sheet.getRange(rowIndex + 2, 13).setValue(new Date());
    numRowsToSend += 1;
  });
  if (numRowsToSend) {
    const subject = numRowsToSend + ' new Yes answers';
    GmailApp.sendEmail(emailAddress, subject, '', { htmlBody: body });
  }
}

answered Apr 28, 2021 at 9:03

doubleunary

21.9k13 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tudor68 Over a year ago

Thank you so much. This works. I don't think I understand it fully but it absolutely does the job. Thank you again

Collectives™ on Stack Overflow

Google Apps Script - Search row for string and return row 1 of matching column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related