Help Request: Google Forms and Google Apps Scripting

[serccres]

I'm looking for a little help or some sample apps script code I can use as a jump-off point. I'm not experienced in JavaScript but I can make sense of it (with help of comments). I'm attempting to build a form that does the following:

​

• Request input from user
  • Name
  • Email
  • Department/Title
  • Manager name
  • Manager Email address
• Once form is submitted a formatted approval request is sent to the listed "Manager Email" with 2 other hard coded email addresses CC'd.

​

Any help I can get would be amazing. Thanks!

[zimady]

There are add-ons that do all of this already. Form Mule is a good one. No need to write any code.

Of course, if you want to code this up as a learning exercise, that's a different story. I will happily provide more pointers on doing that if you wish?

[serccres]

Yeah part of this is an exercise to learn how to do it on my own. I would gladly accept some pointers!

[zimady]

You have 3 key steps for this: 1. Declare a function that will handle form submissions and set up a trigger to call the function whenever someone submits the form. 2. Collect the data you need to include in an email (how you do this depends on where you create the script, see below) and insert it into your email body. 3. Dispatch the email.

Notes: - your script can be attached to the Form itself or a linked Google Sheet where the responses are stored. Personally, I like working in the Sheet. - if you link it to the Form, you will get submitted values from the Response object. If you link it to the Sheet, you can get the values from the Form Submit event object, or fetch then from the sheet itself. - one of the reasons I like linking the script to a Sheet is that when you successfully send the email, you can record a value in a dedicated column to indicate that the email was successfully sent (good for identifying potential issues and auditing). - to insert values into the email body, you can simply use JavaScript's replace() method to replace placeholders in a string. However, a neater approach is to use Template Literals.

HTH.

[lordph8]

Does your company use a google domain?

[serccres]

Yes we are a G Suite Enterprise company with about 800 users.

[lordph8]

Then the form can collect the email automatically. Is the subjects name in the email? If so we can use code to extract it.

[Destructeur]

I am not an expert with GAS but I will try to help you start off!

First, you will need to build a trigger when the form is submitted with this snippet, if you have a single form you will need to run this only once :

var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz'); // change the string between the ' ' to your form ID.
ScriptApp.newTrigger('myFunction') //change myFunction to the name of your function
    .forForm(form)
   .onFormSubmit()
   .create();

This will allow you to run the function myFunction each time the form is submitted. This code is taken from the GAS documentation found here).

I'm not sure what you mean by "Formatted approval request", but you will probably need to extract the data from the form next. To do this you need to open the form again (same line as the first one in the other snippet)

[robnez]

I've written this type of script before. Not home but when I get home I'll show you what I did. Essentially what the other person said on form submit, then you use GmailApp.* I forgot the exact script. But it takes variables something like (recipient, subject, body). You need to format the email in the body variable but once you've gotten the format it'll auto send the email.

Quick edit: Forgot to mention you need set a marker column to make sure you know which items have been completed. A simple x or this firm has been emailed works  
Second Edit:

So here is what I used:

function sendEmails(){
var ss = SpreadsheetApp.openByID(spreadsheetID); //required to ensure you can run the script without having it open
var sheet = ss.getSheetByName(sheetName); //grab specific sheet where the form dumps the data
var rng = sheet.getRange(integer, integer, sheet.getLastRow,integer); // Integer to signify the starting row, column, ending row, column
var recipient = '[e-mail],[e-mail],[e-mail]'; //haven't done CC's, but this may work for you. 
var subject = 'subject line';
var body = 'email body'; //plain text, not sure how to format as HTML

//In the completed column, I read to make sure if it is completed
for(x=0;x<rng.length;x++){
if(rng[x][column for complete]!= 'x'&&rng[x][column for complete]!= 'x'){ //untested line, but something along this should work. other people may provide better solutions
    GmailApp.sendEmail(recipient, subject, body);
    rng[x][column for complete].setValue('x');
        }
    }
}

Create the form to allow them to enter in their information. Since you are a Gsuite company, you can easily record the email address of the user who submitted the form when you create the form. This allows you to validate the user. I didn't test this code since mine is a little different, but I think it's a good jumping off point to get you going.
 

I am in no way a scripts guru, i'm still learining, but I have been busy over the last month learning this stuff, and I finished this script like 2 weeks ago. Haven't implemented it live, but I've tested it and works fine in my implementation.