0

I have a Google Form to collect information from my workers working in remote locations

Emp No *
Punch *
Customer details / mode or travel

The data goes into a Google spreadsheet with the below structure

Timestamp   Emp No  Punch   Remark  Name    GeoCode GeoAddress  Email

I am able to capture the GPS co-ordinates of the user by the below script. I made a web app (anyone even anonymous can run) and asked the user to click the link.

What I am not able to do :

I want to save the email ID (or emp no) of the user filling the form. But the email ID is not getting captured into the form. If I fill the form, the email ID is captured. For other users it is not captured. I don't want all the users to authenticate the script (to run the script as the logged in user). It must be captured by some other way. Is it possible?

If the GPS is not captured (it is empty), I want to display a different message in the HTML page. How to do it?

Code.gs

function doGet() {
    return HtmlService.createHtmlOutputFromFile("Index");
}
//
function getLoc(value) {
  var destId = FormApp.getActiveForm().getDestinationId() ;
  var ss = SpreadsheetApp.openById(destId) ;
  var respSheet = ss.getSheetByName("Location");
  var numResponses = respSheet.getLastRow();
  var currentemail = Session.getActiveUser().getEmail();
  var c=value[0]; var d=value[1];
  var e=c + "," + d ;
  //respSheet.getRange(numResponses,6).setValue(e);
  //respSheet.getRange(numResponses,8).setValue(currentemail);
  var response = Maps.newGeocoder().reverseGeocode(value[0], value[1]);
  var f= response.results[0].formatted_address;
  //respSheet.getRange(numResponses,7).setValue(f);
  respSheet.getRange(numResponses,6,1,3 ).setValues([[ e, f, currentemail ]]);
}
//

index.html

<!DOCTYPE html>
<html>
<script>
(function getLocation() {
    if (navigator.geolocation) {
      navigator.geolocation.getCurrentPosition(showPosition);
      }
})()      
function showPosition(position){
 var a= position.coords.latitude;
 var b= position.coords.longitude;
 var c=[a,b]
 getPos(c)
 function getPos(value){
 google.script.run.getLoc(value);
 }
}

</script>
<body>
<p>Please ensure your GPS is on to record your location. You can generate the report from website to check. Pl. close this window (version 3)</p>
</body>
</html>
Rubén
  • 34,714
  • 9
  • 70
  • 166
arul selvan
  • 616
  • 4
  • 17
  • 1
    Ask them to fill it – TheMaster Sep 07 '20 at 12:11
  • They can fill a wrong employee number. I want to avoid it. Further, I want to record who filled it without their doing anything (authentication). Pl. help me with the second question, which may not be very hard – arul selvan Sep 07 '20 at 12:28
  • Is this gsuite? How do you expect the script to know something out of thin air? If they don't authenticate, how will the script know anything about the user executing the script? About the second question, as said in the answer, use a `if...else` client side or server side withSuccessHandler. – TheMaster Sep 07 '20 at 12:54

2 Answers2

0

From the question

I want to save the email ID (or emp no) of the user filling the form. But the email ID is not getting captured into the form. If I fill the form, the email ID is captured. For other users it is not captured. I don't want all the users to authenticate the script (to run the script as the logged in user). It must be captured by some other way. Is it possible?

On a web application created using Google Apps Script to automatically get the user email ID you could set your web application to be executed as the user running the application instead being executed as you but if don't want to use this feature then you have to set your own authentication process.

From the question

If the GPS is not captured (it is empty), I want to display a different message in the HTML page. How to do it?

Use a JavaScript conditional expression

function getLocation() {
    if (navigator.geolocation) {
      navigator.geolocation.getCurrentPosition(showPosition);
    } else {
      alert('Can\'t get the position');
    }
})() 

function showPosition(position){
 var a= position.coords.latitude;
 var b= position.coords.longitude;
 var c=[a,b];
 getPos(c);
 function getPos(value){
 google.script.run.getLoc(value);
 }
}

The above code uses alert but you could use the DOM.

Resources

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Sorry @Ruben, the user is not getting the alert if the GPS is off – arul selvan Sep 08 '20 at 09:42
  • I incorporated the edit. Now the alert is not happening (if GPS is off) and the location is not getting recorded (even if the GPS is on). – arul selvan Sep 09 '20 at 05:20
  • @arulselvan See the example I just posted in [Getting my current position from my phone with script in Google spreadsheet](https://stackoverflow.com/q/59808246/1595451) – Rubén Sep 09 '20 at 20:00
  • pl. refer to my answer dated 16 Sep above - a few users are reporting that their punches are not getting recorded. I tested and I could see that the punches are not getting recorded. I want to display the "last record" from the sheet after the punch is made. This will serve as a positive confirmation for the user that the punch has been recorded in the sheet. I am not able to do it. @Rubén Can you help me. – arul selvan Oct 03 '20 at 04:05
  • The HTML form is working alright for most of the users. But, it is not working for some users. I asked the users to clear history and logout and then try. Still, some users are not able to record the location. The script is not creating a record (by clicking the webapp) in other browsers also (like UCm opera, fire fox etc). – arul selvan Oct 17 '20 at 11:58
  • @arulselvan Followup questions should be posted as new questions. – Rubén Oct 17 '20 at 22:25
0

I was able to make a complete solution without any google form (just HTML) and managed to display an alert message also. The "Login" is still not possible.

Code.gs

It runs the form and saves the answers in the required columns into google sheet. It runs faster than google form and "Submit" has to be clicked only once. As the saving happens by "append row", the jumbling of data (between rows) which was happening in my earlier method is avoided.


/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
  .getContent();
}

/* @Process Form */
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/...../edit#gid=52499297";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Location");
  var response = Maps.newGeocoder().reverseGeocode(formObject.lat, formObject.long);
  var address= response.results[0].formatted_address;
  ws.appendRow(
    [
      new Date(),
      formObject.empno,
      formObject.punch,
      formObject.rem,
      "",
      formObject.lat+","+formObject.long,
      address
    ]
  );
}

Index.html

This has the questions.

<!DOCTYPE html>
<html>

<head>
    <base target="_top">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <?!= include('JavaScript'); ?>
</head>

<body>
    <div class="container">
        <div class="row">
            <div class="col-6">
                <form id="myForm" onsubmit="handleFormSubmit(this);">
                    <p class="h4 mb-4 text-left">Record Attendance and Location</p>

                    <div class="form-group">
                        <label for="empno">Emp No - <a href="https://docs.google.com/spreadsheets/d/1yhcUpfhvyPcWyDkwglJRJwMn7VnBYEHaAjz959JC0wk/edit#gid=0">Click to see list</a></label>
                        <input type="number" class="form-control" id="empno" name="empno"  min="1" max="9999999"  required>
                    </div>

                    <div class="form-group">
                        <label for="punch">Punch (Select one)</label>
                        <select class="form-control" id="punch" name="punch" required>
                            <option selected disabled hidden style='display: none' value=''></option>
                            <option value="In">In</option>
                            <option value="Out">Out</option>
                            <option value="Started">Started</option>
                            <option value="Reached">Reached</option>
                        </select>
                    </div>

                    <div class="form-group">
                        <label for="rem">Remark</label>
                        <input type="text" class="form-control" id="rem" name="rem">
                    </div>


                    <div class="form-group">
                        <input type="hidden" class="form-control" id="lat" name="lat">
                        <input type="hidden" class="form-control" id="long" name="long">
                    </div>

                    <button type="submit" class="btn btn-primary btn-block">Submit</button>
                </form>

                <div id="output"></div>
            </div>
        </div>
    </div>
</body>

</html>

JavaScript.html

This processes the answers

<script>
    function showPosition() {
        navigator.geolocation.getCurrentPosition(showMap);
    }

    function showMap(position) {
        // Get location data
        var lat = position.coords.latitude;
        var geo1 = document.getElementById("lat");
        geo1.value = lat;
        var long = position.coords.longitude;
        var geo2 = document.getElementById("long");
        geo2.value = long;
    }

    // Prevent forms from submitting.
    function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
            forms[i].addEventListener('submit', function(event) {
                event.preventDefault();
            });
        }
    }
    window.addEventListener('load', preventFormSubmit);
    window.addEventListener('load', showPosition);

    function handleFormSubmit(formObject) {
        google.script.run.processForm(formObject);
        document.getElementById("myForm").reset();
        alert('Data saved successfully');
        
    }
</script>
arul selvan
  • 616
  • 4
  • 17