How to Build HTML Form with Ability to Extract Geolocation Data From the Browser/Mobile Devices and Populate the Form Data in Google Sheets

Just as the post title suggest, this post is about how to do it. Here are the steps. 

1. Create a New Google Sheets Spreadsheet. Give a meaningful and consistent header such as the image below: 

2. Share it as public (anyone with the link) and set the anyone as Editor. 

3. From the GSheets Menu, go to Extension > App Scripts. 

4. Delete the initial function, and copy/paste code snippets below. 

// Original code from https://github.com/jamiewilson/form-to-google-sheets

// Updated for 2021 and ES6 standards

const sheetName = 'Geolocation'

const scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {

  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()

  scriptProp.setProperty('key', activeSpreadsheet.getId())

}

function doPost (e) {

  const lock = LockService.getScriptLock()

  lock.tryLock(10000)

  try {

    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))

    const sheet = doc.getSheetByName(sheetName)

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]

    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function(header) {

      return header === 'Date' ? new Date() : e.parameter[header]

    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService

      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))

      .setMimeType(ContentService.MimeType.JSON)

  }

  catch (e) {

    return ContentService

      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))

      .setMimeType(ContentService.MimeType.JSON)

  }

  finally {

    lock.releaseLock()

  }

}

Pay attention to SheetName constructor, the SheetName should be exactly matched with the Google Spreadsheet you are working on. 

5. Run the Initial Setup Function. Image description as follow. 

you will see a Prompt asking for permission, because the Script has not been reviewed by Google, it will generate a warning before you can continue. You must click the "show advanced" text followed by click the "<Script Name>" for the script to have the correct permissions to update your form. Sorry I don't have the image description, just message me if you are confused about this part. Basically, this is just similar permission prompt when you add new plugins or modules in your usual google workspace stuffs. If you are done on this step, continue with the step 6. 

6. Add trigger from the Script. 

Select the project "Triggers" from the sidebar and then click the Add Trigger button.

In the show up window, select the following options:

• Choose which function to run: doPost
• Choose which deployment should run: Head
• Select event source: From spreadsheet
• Select event type: On form submit

then, Click SAVE. 

7. Publish The Project 

Now your project is ready to publish. Hit the Deploy button and New Deployment from the drop-down.

Click the "Select type" icon and select Web app.

In the form that appears, select the following options:

• Description: Geolocation (The description text is free to modify as you want)
• Web app → Execute As: Me
• Web app → Who has access: Anyone

Then click Deploy.

Save the Web App URL before you are moving to the next step. 

8. Create The HTML Form. 

build your own HTML form as you wish, make it stylish with cool CSS if you want, give a fancy background and transparent effect as needed. However, the most important part is on the Input Field, make sure the Input Name Tags is matched with the GSheets Headers. 

second, Add the Geolocation Request Function inside the <script> tags, and point the captured data to the HTML input fields, 

Third, place the Web App URL in Step 7 to the Form Action tag. 

Here is a simple HTML code example (deploy it in webserver such as XAMPP, WAMP or host it in public devs portal such as Github or any other service) to see how it works. Remember to put your Web App URL in the Form Action. And Pay Attention to the Input Name in the Form, make sure they are exactly matched with the GSheets Headers Name. 

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>Google Sheet Form</title>
    <style>
      form {
        display: flex;
        flex-direction: column;
        align-items: center;
        gap: 1rem;
      }

      label {
        font-weight: bold;
      }

      input[type="text"],
      input[type="email"] {
        padding: 0.5rem;
        border: 2px solid gray;
        border-radius: 0.5rem;
        font-size: 1rem;
        width: 100%;
      }

      button[type="submit"] {
        padding: 0.5rem;
        border: none;
        border-radius: 0.5rem;
        font-size: 1rem;
        background-color: blue;
        color: white;
        cursor: pointer;
      }

      button[type="submit"]:hover {
        background-color: darkblue;
      }
    </style>
    <script>
      function getLocation() {
        // Check if the browser supports geolocation
        if (navigator.geolocation) {
          // Get the current position of the user
          navigator.geolocation.getCurrentPosition(showPosition);
        } else {
          alert("Geolocation is not supported by this browser.");
        }
      }

      function showPosition(position) {
        // Get the latitude and longitude values from the geolocation data
        var latitude = position.coords.latitude;
        var longitude = position.coords.longitude;
        // Populate the input field with the latitude and longitude values
        document.getElementById("Latitude").value = latitude;
        document.getElementById("Longitude").value = longitude;
      }
    </script>
  </head>
  <body>
    <form action="YOUR WEBAPP URL" method="post">
      <input name="Email" type="email" placeholder="Email" required>
      <input name="Name" type="text" placeholder="Name" required>
      <input name="Description" type="text" placeholder="Description" required>
      <input type="text" id="Latitude" name="Latitude" placeholder="Latitude">
      <input type="text" id="Longitude" name="Longitude" placeholder="Longitude">
      <button type="button" onclick="getLocation()">Get Location</button>
      <button type="submit">Send</button>
    </form>
  </body>
</html>

The form will look like this below.

When you hit the Get Location Button, browser or device will ask about your device location, if you approve it, latitude and longitude values will automatically be populated in the respective fields, all other fields can be typed manually, when you are done, hit the Send button, the data will be send to GSheets. 

Here is the example of inputted data in GSheets

by these methods, you can design the forms in a very flexible way, and you can make it beauty using CSS while not losing the important functions. 

Cheers. 

If you prefer a video tutorial about this workflow, here is below