-1

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.

6
  • 1
    Welcome to Web Applications. Please show the best of the script variations that you have tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Nov 8 '21 at 3:54
  • Hi and welcome. I've tried about six different script variations but they all fail what you have tried and why they fail is valuable information. Would you please edit your question to include these details.
    – Tedinoz
    Nov 8 '21 at 21:20
  • _ I want the email to send automatically at 9am everyday except Sunday._ You'll need a time-driven trigger; use the ClockTriggerBuilder to create the trigger. Google script trigger weekdays only is a good example and all you need to do is add "Saturday" to the list and change the time from 11am to 9am and... hey presto
    – Tedinoz
    Nov 9 '21 at 0:20
  • Suggestion: create sheet/tab for the purpose of containing a QUERY that lists form responses for today. How to make a query with Google Spreadsheet, that checks for a date on webapps might help with the syntax for a query that targets a particular date. Unfortunately it doesn't cover comparison of a date with a timestamp BUT Query "Today" on google forms timestamp column? from Stackoverflow provides the exact syntax for a query to select only form responses received today.
    – Tedinoz
    Nov 10 '21 at 1:13
  • Suggestion: try this code from StackOverflow: Send one Email to Multiple Addresses in Google Spreadsheet for creating an email address list
    – Tedinoz
    Nov 10 '21 at 2:38
0

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

screenshot

Query sheet

screenshot

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 = [ "abc@email.com", "def@email.com" ]
  // Join the emails into a single string separated by commas
  var combinedEmails = emails.join() // "john.doe@email.com,jane.doe@email.com"

  // 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.