Modify a SendGoogleForm script

Question

I am looking for a modification to a script that I found online for google forms. The scriptt fetches the responses and puts it in to an email. We use this to let supervisors send in newly hired people. I am not able to modify the script to add a new line at the end where I can use the variables from the form to type out a powershell script automatically that we then use to create accounts in the active directory etc.

I already have the powershell script but I cant seem to get it into the google forms script.

I have tried to modify this part which I assume hold what sends the message. But I cant make it include a string at the end which I can put different variables in.

    for ( var keys in columns ) {
      var key = columns[keys];
      if ( e.namedValues[key] && (e.namedValues[key] != "") ) {
        message += key + ' :: '+ e.namedValues[key] + "\n\n";

This is the google forms code

/* Send Google Form by Email v2.1 */
/* For customization, contact the developer at amit@labnol.org */
/* Tutorial: http://www.labnol.org/?p=20884 */

function Initialize() {

  var triggers = ScriptApp.getProjectTriggers();

  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  ScriptApp.newTrigger("SendGoogleForm")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();

}

function SendGoogleForm(e) 
{  
  try 
  {      
    //Här fyller du i mailadresserna för resp avdelning.
    var security = "";
    var hr = "";
    var it = "";

    //Ärende på mailet
    var subject = "Test";  

    //Slår ihop alla mailadresser till en.
    var email = hr +","+ security +","+ it;

    // You may replace this with another email address
    //var email = Session.getActiveUser().getEmail();

    var s = SpreadsheetApp.getActiveSheet();
    var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];    
    var message = "";    

    // Only include form fields that are not blank
    for ( var keys in columns ) {
      var key = columns[keys];
      if ( e.namedValues[key] && (e.namedValues[key] != "") ) {
        message += key + ' :: '+ e.namedValues[key] + "\n\n"; 
      }
      if (key == "Förnamn")
        var fornamn = e.namedValues[key];
      else if (key == "Efternamn")
        var efternamn = e.namedValues[key];

    }

    //Lägger till eventuellt namn i ämnesraden.
    subject += ", " + fornamn + " " + efternamn ;

    // This is the MailApp service of Google Apps Script that sends the email. You can also use GmailApp for HTML Mail.
    MailApp.sendEmail(email, subject, message); 

  } catch (e) {
    Logger.log(e.toString());
  }

}

Here is the powershell code which I want to get into the message sent. Between each ' ' should a variable be fetched from the google form.

New-ADUser -SamAccountName 'fornamn.efternamn' -Name 'Förnamn Efternamn' -GivenName 'Förnamn' -Surname 'Efternamn' -Description 'Läkare' -OfficePhone '' -EmailAddress 'fornamn.efternamn@yrdy.com' -Path 'OU=Users-W10,OU=VC Hornstull,OU=Neron,DC=neron,DC=internal' -Company 1 -Department 2 -Title 3

So what you want is to assign the powershell code to the message body whereby the values between ' ' should be retrieved from the variables `var fornamn` and `var efternamn`? And it is important that 'fornamn.efternamn' is lower case and 'Förnamn Efternamn' uppercase? Are the entries retrieved from Google Form uppercase or lowercase? It will be easier to implement your requests if case sensitivity is not important. — ziganotschka, Sep 20 '19 at 07:03

score 0 · Accepted Answer · answered Sep 20 '19 at 08:51

0

If case sensitivity is not important:

  //check if both efternamn and fornamn exist
  if(typeof fornamn !== 'undefined'&&typeof efternamn !== 'undefined'){
    subject += ", " + fornamn + " " + efternamn ;
    message+="New-ADUser -SamAccountName '"+fornamn+"."+efternamn+"' -Name '"+fornamn+" "+efternamn+"' -GivenName '"+fornamn+"' -Surname '"+efternamn+"' -Description 'Läkare' -OfficePhone ' ' -EmailAddress '"+fornamn+"."+efternamn+"@yrdy.com' -Path 'OU=Users-W10,OU=VC Hornstull,OU=Neron,DC=neron,DC=internal' -Company 1 -Department 2 -Title 3";
    MailApp.sendEmail(email, subject, message);     
  }

The combination of single quotes and double quotes allows you to incorporate variables into a string.

answered Sep 20 '19 at 08:51

ziganotschka

25,866
2
16
33

Thanks for the respons! Does case sensitive mean the "åäö"-characters? If so, then yes I would appreciate it due to the form being swedish and the keys will probably be swedish(?). – Johan Hård Sep 20 '19 at 13:22
Case sensitive means uppercase and lowercase because in your request `fornamn.efternamn` is lowercase but `'Förnamn Efternamn' start with uppercase letters. If you need to change from lowercase to uppercase or vice versa - this would make your code more complicated. If this is not necessary - the code I provided above should work correctly for you. – ziganotschka Sep 20 '19 at 13:38
Hi again ziganotschka! I have now tested your code a bunch and I have gotten it work as expected except that when it send the email with the code it will break the line where it has to and that adds about 5 spaces between each line. So a copy and paste of the script is then no longer possible. I thought maybe there is a way to create a file and send somehow. Any ideas? – Johan Hård Sep 27 '19 at 11:35
@JohanHård: I am glad my answer was helpful for you. If it works for you, please consider accepting it. If you have a new question, it would be good I suggest you to formulate it more in detail in a separate post, so it would receive more visibility. – ziganotschka Oct 01 '19 at 15:04

Modify a SendGoogleForm script

1 Answers1

The combination of single quotes and double quotes allows you to incorporate variables into a string.