Google form and Sign in with linkedin

== Google form ==

Now working with some friends on a registration function.

I just learned Google forms is a convenient tool to create simple registration forms. You can do it from either Google Forms or Google Drive. This form will be linked to a spreadsheet online, such that all records are stored there.

The submission of a form is much better if it 1) sends a confirmation email, and also 2) include a link to edit the submission.  It's also good to 3) have a dashboard that displays submitted information, which you often want an interface independent from the spreadsheet.  These can all be done with Google Forms API.

1) and 2) need writing a javascript function.

See Email confirmations from Google Forms for how to set up a script triggered by form submission action.

This is my code to include edit link:

function myFunction(e) {
  if (typeof e == 'undefined') {
    Logger.log("e is undefined");
    return;
  }

  //var userName = e.values[1];
  //var userEmail = e.values[2];
  var userName = e.namedValues["Name"][0]; // From a field whose name is "Name".
  var userEmail = e.namedValues["Email"][0]; // From a field whose name is "Email".
  if (userEmail == '') return;
 
  var subject = "Form Submitted";
 
  var form = FormApp.openById('[form id]');
  var formResponses = form.getResponses(); // All responses/rows in spreadsheet.
  var formResponse = formResponses[formResponses.length-1]; // Get the just submitted item - last row.
  //Logger.log("formResponses.length = " + formResponses.length);
 
  var message = "Thank you, " + userName + " for finishing the survey.\n\n";
  message += "You can see the current list at [dash board page link]\n\n";
  message += "You can edit your information at: " + formResponse.getEditResponseUrl() + "\n\n";
  message += "Have a good day.";
  MailApp.sendEmail (userEmail, subject, message);
}

Note in the code above, the "form id" must be the id of the form, and not the spreadsheet. The code itself it a code of the spreadsheet.

Here is another piece of code that works equally well, but should be embedded in the form, and not the spreadsheet. This code is better in that it does not need to specify any form id. I prefer this one.

function onFormSubmit(e) {
  if (typeof e == 'undefined') {
    Logger.log("e is undefined");
    return;
  }
 
  var itemResponses = e.response.getItemResponses();
 
  /*
   for (var i = 0; i < itemResponses.length; i++) {
     var itemResponse = itemResponses[i];
     Logger.log('Response #%s to the question "%s" was "%s"',
         (i + 1).toString(),
         itemResponse.getItem().getTitle(),
         itemResponse.getResponse());
   }
  */
 
  var subject = "Form Submitted";
  var userName = itemResponses[0].getResponse();
  var userEmail = itemResponses[4].getResponse();
  var message = "Thank you, " + userName + " for finishing the survey.\n\n";
  message += "You can edit your information at: " + e.response.getEditResponseUrl() + "\n\n";
  message += "Have a good day.";
 
  MailApp.sendEmail (userEmail, subject, message);
}

3) Displays submitted information not using the spreadsheet.

See Query a Google Spreadsheet like a Database with Google Visualization API Query Language. This shows how to display a table containing selected spreadsheet columns. The link will be below (replace group id and group id with your values):

https://docs.google.com/spreadsheets/d/[form id]/gviz/tq?tqx=out:html&tq&gid=[group id]

If you want to display only selected columns, e.g., columns A and B, you can specify this with the tq parameter: tq=SELECT+A,B, so the links becomes:

https://docs.google.com/spreadsheets/d/[form id]/gviz/tq?tqx=out:html&tq=SELECT+A,B&gid=[group id]

One concern of giving people this link is security: they can modify the value of tq to see all fields. To overcome this is easy: set up a php page that read in the contents and display, this way the url is hidden. It is also really easy to set up, just 1 line of php code is needed:

<?php
echo file_get_contents("https://docs.google.com/spreadsheets/d/[form id]/gviz/tq?tqx=out:html&tq=SELECT+A,B&gid=[group id]");
?>

== Reliability issue ==

Well, it seems the script in google forms are not always reliably triggered. The above code stops to function without any reason. Searched on line for "google form script trigger not reliable", it seems many other people had similar experience. Free lunch is not always tasty.

 
== Sign in with Linkedin ==

Say you want people to fill the above form, but not everyone, only those who registered with linked in. So what you do is to set up a page that requires linkedin authentication, then forward people to the above link. For details, see [1][2][3][4].

Following example code in [4]. The code to set up such a page is in appendix. 

Note that the Google form itself is not protected by session. So if anyone knows the url of the form, he will be able to register the form. I have not studied about ways to do this. There may not be a way of doing it, since it's not a full-fledged website anyway. One can change the setting of make a Google form private/public or accessible to only some people, that's what you can do if you don't want it public.


References:

[1] Sign In With LinkedIn
[2] Linkedin authentication documentation - Important. [3] below is linked from here.
[3] Linkedin developer network - Register here to get a linkedin application account. Important.
[4] Linkedin authentication code sample in PHP - Useful


Appendix. Authentication with Linkedin.

<?php
// Change these 5 fields.
define('API_KEY',      '...');
define('API_SECRET',   '...');
define('REDIRECT_URI', 'http://...');
define('SCOPE',        ''); //r_fullprofile r_emailaddress rw_nus');
$reg_url = "https://docs.google.com/forms/d/[form id]/viewform?c=0&w=1&usp=mail_form_link";

// You'll probably use a database
session_name('linkedin');
session_start();

$user = fetch('GET', '/v1/people/~:(firstName,lastName)');

// OAuth 2 Control Flow
if (isset($_GET['error'])) {
    // LinkedIn returned an error
    //print $_GET['error'] . ': ' . $_GET['error_description'];
    //exit;
} elseif (isset($_GET['code'])) {
    // User authorized your application
    if ($_SESSION['state'] == $_GET['state']) {
        // Get token so you can make API calls
        getAccessToken();
    } else {
        // CSRF attack? Or did you mix up your states?
        exit;
    }
} elseif (isset($_GET['logout'])) {
    $_SESSION = array();
} elseif (isset($_GET['login'])) {
    if ((empty($_SESSION['expires_at'])) || (time() > $_SESSION['expires_at'])) {
        // Token has expired, clear the state
        $_SESSION = array();
    }
    if (empty($_SESSION['access_token'])) {
        // Start authorization process
        getAuthorizationCode();
    }
    else {
        print "?";
    }
}

$user = fetch('GET', '/v1/people/~:(firstName,lastName)');
if ($user->firstName == '' && $user->lastName == '') {
    print "Please <a href='" . $_SERVER['PHP_SELF'] . "?login=1'>log into linkedin</a> before registration.<br/>";
    exit;
} else {
    header("Location: $reg_url");
    //echo file_get_contents($reg_url);
    //exit;
    //print "Hello $user->firstName $user->lastName. Click here to go to <a href='$reg_url'>registration form</a>.";
    //print "<br/><a href='" . $_SERVER['PHP_SELF'] . "?logout=1'>logout</a>";
    exit;
}

function getAuthorizationCode() {
    $_SESSION['state'] = uniqid('', true); // unique long string.
    $params = array('response_type' => 'code',
                    'client_id' => API_KEY,
                    'scope' => SCOPE,
                    'state' => $_SESSION['state'],
                    'redirect_uri' => REDIRECT_URI,
              );

    // Authentication request
    $url = 'https://www.linkedin.com/uas/oauth2/authorization?' . http_build_query($params);
    
    // Needed to identify request when it returns to us
    $_SESSION['state'] = $params['state'];

    // Redirect user to authenticate
    header("Location: $url");
    exit;
}
    
function getAccessToken() {
    $params = array('grant_type' => 'authorization_code',
                    'client_id' => API_KEY,
                    'client_secret' => API_SECRET,
                    'code' => $_GET['code'],
                    'redirect_uri' => REDIRECT_URI,
              );
    
    // Access Token request
    $url = 'https://www.linkedin.com/uas/oauth2/accessToken?' . http_build_query($params);
    
    // Tell streams to make a POST request
    $context = stream_context_create(
                    array('http' =>
                        array('method' => 'POST',
                        )
                    )
                );

    // Retrieve access token information
    $response = file_get_contents($url, false, $context);

    // Native PHP object, please
    $token = json_decode($response);

    // Store access token and expiration time
    $_SESSION['access_token'] = $token->access_token; // guard this!
    $_SESSION['expires_in']   = $token->expires_in; // relative time (in seconds)
    $_SESSION['expires_at']   = time() + $_SESSION['expires_in']; // absolute time
    
    return true;
}

function fetch($method, $resource, $body = '') {
    $params = array('oauth2_access_token' => $_SESSION['access_token'],
                    'format' => 'json',
              );
    
    // Need to use HTTPS
    $url = 'https://api.linkedin.com' . $resource . '?' . http_build_query($params);
    // Tell streams to make a (GET, POST, PUT, or DELETE) request
    $context = stream_context_create(
                    array('http' =>
                        array('method' => $method,
                        )
                    )
                );

    // Hocus Pocus
    $response = file_get_contents($url, false, $context);

    // Native PHP object, please
    return json_decode($response);
}

?>