Search code examples
google-apps-scriptgoogle-workspace

Two App scripts running on Forms and Sheets, need to connect them both

I have an onboarding form that puts all the responses in a google sheet which has an app script running to add user to google admin and different groups by taking in values from the last row of the sheet. That works fine, it's just that I have to run the script every time the form is filled so I want to create a form trigger.

It made sense to create a form submit trigger on the app script attached to the google form and I added the library and script id of the other appscipt and pulled in a method from there like such


// Create a form submit installable trigger
// using Apps Script.
function createFormSubmitTrigger() {

  // Get the form object.
  var form = FormApp.getActiveForm();

  // Since we know this project should only have a single trigger
  // we'll simply check if there are more than 0 triggers. If yes,
  // we'll assume this function was already run so we won't create
  // a trigger.
  var currentTriggers = ScriptApp.getProjectTriggers();
  if(currentTriggers.length > 0)
    return;
  
  // Create a trigger that will run the onFormSubmit function
  // whenever the form is submitted.
  ScriptApp.newTrigger("onFormSubmit").forForm(form).onFormSubmit().create();
}

function wait(ms){
   var start = new Date().getTime();
   var end = start;
   while(end < start + ms) {
     end = new Date().getTime();
  }
}

function onFormSubmit() {
  wait(7000);
  AddingUserAutomation.createUserFromSheets()
}

The trouble is I get the error

TypeError: Cannot read property 'getLastRow' of null
    at createUserFromSheets(Code:43:19)

My createUserFromSheets function is taking the active sheet

function createUserFromSheets(){
  let data = SpreadsheetApp.getActiveSheet();
  let row  = data.getLastRow();

  let firstname = data.getRange(row,2).getValue();
  let lastname = data.getRange(row,3).getValue();
... etc etc 
}

I think it is unable to pull the getActiveSheet part that is why I had added the wait() function on formSubmit() but it still would not work.

Is there a way to solve this or a better way to do it?

function createWorkspaceUser(recentResponse) {
  console.log("Creating account for:\n"+recentResponse[1]);
  debugger;
  var user = {"primaryEmail": recentResponse[0] + '.' + recentResponse[1] + '@' + recentResponse[3],
              "name": {
                "givenName": recentResponse[0],
                "familyName": recentResponse[1]
              },
              "password": newPassword(),
              };
  try {
    user = AdminDirectory.Users.insert(user);
    console.log('User %s created with ID %s.', user.primaryEmail, user.id);
  }catch(err) {
    console.log('Failed with error %s', err.message);
  }
}

I am doing it this way but it's running an error on primaryemail

enter image description here

enter image description here

Solution

  • Suggestion [NEW UPDATE]

    As mentioned by RemcoE33

    To have a more simplified setup, perhaps skip the library part and do all the scripting (bound script) in your Google Form itself.

    Since we don't have the complete overview of your actual Google Form. See this sample below as a reference:

    Google Form Script

    function onFormSubmit() {
  var form = FormApp.getActiveForm();
  var count = 0;
  var recentResponse = [];
  var formResponses = form.getResponses();

  for (var i in formResponses) {
    count += 1;
    var formResponse = formResponses[i];
    var itemResponses = formResponse.getItemResponses();
    for (var j = 0; j < itemResponses.length; j++) {
      if(formResponses.length === count){ //Process only the recently submitted response
        var itemResponse = itemResponses[j];
        recentResponse.push(itemResponse.getResponse())
      }
    }
  }
  createWorkspaceUser(recentResponse);
}

function createWorkspaceUser(recentResponse){
  var user = {"primaryEmail": recentResponse[0].replace(/\s/g, '') + '.' + recentResponse[1].replace(/\s/g, '') + '@' +recentResponse[3],
              "name": {
              "givenName": recentResponse[0],
              "familyName": recentResponse[1]
              },
              "password":newPassword(),
             };

  try{
    user = AdminDirectory.Users.insert(user);
    Logger.log('User %s created with ID %s.', user.primaryEmail, user.id);
  }catch (err) {
    Logger.log('Failed with error %s', err.message);
  }

  console.log(user);
}

    NOTE: You no longer need to build an on form submit trigger since the onFormSubmit() function will automatically run right after hitting the submit button.

    Demonstration

    1. Submit user data from sample form:

    enter image description here

    2. Test user account will be created on Workspace Admin Console Users:

    enter image description here

    Reference