Script to Automatically Email Every Morning

Question

I have a Google Form which links to a Google Sheet "Form responses 3". Crew have to check in to our job sites each day and I want to write a script to automatically email our supervisors with the list of who has checked in each day.

Who to send email to = a set list of email addresses which will not change

Column A = Timestamp - I only want it to send todays row data Column B = First Name Column C = Last Name Column G = Site Address

I want the email subject to be "Todays Site Check-Ins" I want the email body to say "Hi Supervisors, Please refer below list of subbies who have checked in to site today. Please follow up with your teams to make sure that everyone has checked in." and then I want to show the table of names and which site they checked in at. I want the email to send automatically at 9am everyday except Sunday. But if this is too hard then every day is fine. I've tried about six different script variations but they all fail so I need some help from you superstars please.

Answer 1

This is a compilation of various suggestions included in the Comments. It doesn't include the explanation of how to create the time-driven trigger but Google script trigger weekdays only (referenced in Comments) is very detailed.

Create a data sheet containing QUERY

• Create a new sheet/tab. Name it what ever you wish. The sample is called "Daily_email_data"
• Insert this formula in cell A1
  =query('Form Responses 1'!A1:G,"select G,C,B where A is not null and A contains date '"&text(TODAY(), "yyyy-mm-dd")&"' order by G asc, C asc")

---

Sample data

Query sheet

SCRIPT

I adapted used the script from StackOverflow Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message

function sendMail(){
  // specify the sheet with the query
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily_email_data")

  // get the data
  var data = sh.getDataRange().getValues();

  // start the message
  var htmltable = '<table>'

  // loop through the data rows
  for (row = 0; row<data.length; row++){

    htmltable += '<tr>';

    // loop through the data columns
    for (col = 0 ;col<data[row].length; col++){
      if (data[row][col] === "" || 0) {
        htmltable += '<td>' + 'None' + '</td>';
      } 
      else if (row === 0)  {
      htmltable += '<th>' + data[row][col] + '</th>';
      }

      else {
        htmltable += '<td>' + data[row][col] + '</td>';
      }
    }

    htmltable += '</tr>';
  }

  // close the table
  htmltable += '</table>';
  
  // build the email distribution list
  var emails = [ "[email protected]", "[email protected]" ]
  // Join the emails into a single string separated by commas
  var combinedEmails = emails.join() // "[email protected],[email protected]"

  // build the subject
  var subject = "Todays Site Check-Ins"

  // build body
  var body = "Hi Supervisors, Please refer below list of subbies who have checked in to site today. Please follow up with your teams to make sure that everyone has checked in."
  htmltable = body +"<br>"+ htmltable
   
  // send the email
  MailApp.sendEmail(combinedEmails, subject,'',{htmlBody: htmltable})
}

---

Comments

• there is no error-checking (such as for "no data") but this can be added by the OP
• for (row = 0; row<data.length; row++){ - the advantage of this nested loop is that it is dynamic; it will manage as many rows and columns as exist in the data range. Add more rows, or add extra columns, and the loop will automatically include them in the outout.

Changes

• getSheetByName("Daily_email_data") vs getActiveSheet(); - eliminates risk of sending wrong data
• getDataRange(); vs getRange("A2:O38"); - since all the data on this sheet is being sent, there is nothing to be gained by using a getRange method (to say nothing of the calculations required to establish the range parameters).
• var htmltable = '<table>' vs var htmltable = '<table ' + TABLEFORMAT +' ">' - this creates a very simple table in the email. The syntax of TABLEFORMAT could be edited but that isn't in the scope of this question.
• htmltable = body +"<br>"+ htmltable - add your introductory paragraph to the html content.