html - Filling Google Forms with Google Sheets

Question

I have an HTML form which saves its responses to a Google Sheet. The form contains the following fields:

Name:
Email:
Subject:
Message:

Now, what I want is I want to automate sending a thank you email to a recipient as soon as he/she fills the form to the address mentioned in the "Email" field. I don't want to use Google Forms as the backend as it is hard to bypass the "Response Recorded" Confirmation page. Also, avoiding PHP would be better for me!

Is there a way to send these e-mails automatically? The format of the email is as follows:

From: <my email address>
To: <email address from the "Email" field in the spreadsheet>
Subject: Re: Submission Received

Hey <name from the "Name" field in the spreadsheet>!

Body of the mail

If there is a way to bypass the confirmation page of Google Forms, please let me know! That would also do!

question from:https://stackoverflow.com/questions/65887655/filling-google-forms-with-google-sheets

Answer 1

If you have the data in google sheets, then you can handle your automation there. I setup this sample file that you could probably base your data set on.

From there I attached the below script to the spreadsheet. You would then need to set a trigger to execute this script on a somewhat regular frequency (5 minutes? 1 minute?). I think it should accomplish what you are going for. There's a check built in to ensure that partial data is not sent.

const ss = SpreadsheetApp.getActiveSheet();
const sentMessageColumn = ss.getRange("E:E").getColumn();
function regularProcedure(){
  var aCell = ss.getRange(ss.getLastRow(),sentMessageColumn)

  while(aCell.isBlank()){
    var eRow = aCell.getRow();
    
    if(!(ss.getRange(eRow,2).isBlank() ||
      ss.getRange(eRow,3).isBlank() ||
      ss.getRange(eRow,4).isBlank()))
      {
        var newMail = GmailApp.createDraft(
        ss.getRange(eRow,2).getValue(),
        ss.getRange(eRow,3).getValue(),
        ss.getRange(eRow,4).getValue());
        newMail.send();
        aCell.setValue(true);
        }      
      aCell=aCell.offset(-1,0);
  }
}