DEV Community: Omer Lahav

Submit a Form to a Google Spreadsheet

Omer Lahav — Thu, 30 Jan 2020 12:23:09 +0000

Lately, I created an HTML form where the results are sent to a Google spreadsheet. I used it for analytics purposes, but it's useful even just for keeping track of the form's results. After trying all kinds of solutions and even searching beyond the 2nd page on Google, I found something that worked for me and I thought worth sharing.

Prerequisites: HTML and basic JavaScript knowledge.

The credit for this solution goes to Jamie Wilson who created and uploaded it to Github.

First, Create the Spreadsheet

Go to Google Sheets and Start a new spreadsheet with the Blank template. Disclaimer: It doesn't really matter how you name the file.
On the first row, write timestamp on the first column and email on the second column.

Create a Google Apps Script

Click on Tools > Script Editor… which should open a new tab.
Name the script Submit Form to Google Sheets or however you want for you to remember what the script does.
Delete the function myFunction() {} block withing the Code.gs tab.
Paste the following script in its place and File > Save:

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? 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()
  }
}

If you want to better understand what this script is doing, check out the form-script-commented.js file in the repo for a detailed explanation.

Run the setup function

Now, go to Run > Run Function > initialSetup to run this function.
In the Authorization Required dialog, click on Review Permissions.
Sign in or pick the Google account associated with this project.
You should see a dialog that says Hi {Your Name}, Submit Form to Google Sheets wants to...
Then click Allow

Add a new project trigger

Click on Edit > Current project’s triggers.
In the dialog click No triggers set up. Click here to add one now.
In the dropdowns select doPost
Set the events fields to From spreadsheet and On form submit
Then click Save

Publish the project as a web app

Click on Publish > Deploy as web app….
Set Project Version to New and put initial version in the input field below.
Leave Execute the app as: set to Me(your@address.com).
For Who has access to the app: select Anyone, even anonymous.
Click Deploy.
In the popup, copy the Current web app URL from the dialog.
And click OK.

IMPORTANT! If you have a custom domain with Gmail, you might need to click OK, refresh the page, and then go to Publish > Deploy as web app… again to get the proper web app URL. It should look something like https://script.google.com/a/yourdomain.com/macros/s/XXXX….

Input your web app URL

Open the file named index.html. On line 7 replace <SCRIPT URL> with your script url:

<form name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <button type="submit">Send</button>
</form>

<script>
  const scriptURL = '<SCRIPT URL>'
  const form = document.forms['submit-to-google-sheet']

  form.addEventListener('submit', e => {
    e.preventDefault()
    fetch(scriptURL, { method: 'POST', body: new FormData(form)})
      .then(response => console.log('Success!', response))
      .catch(error => console.error('Error!', error.message))
  })
</script>

As you can see, this script uses the Fetch API, a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses FormData to pass in our data as URL parameters.

Adding additional form data

To capture additional data, you'll just need to create new columns with titles matching exactly the name values from your form inputs. For example, if you want to add first and last name inputs, you'd give them name values like so:

<form name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <input name="firstName" type="text" placeholder="First Name">
  <input name="lastName" type="text" placeholder="Last Name">
  <button type="submit">Send</button>
</form>

Then, you can create new headers with the exact, case-sensitive name values. Just go to the spreadsheet and add the names to the first row, each name in a different column.

Related Polyfills

Some of this stuff is not yet fully supported by browsers or doesn't work on older ones. Here are some polyfill options to use for better support.

Since the FormData polyfill is published as a Node package and needs to be compiled for browsers to work with, a good option for including these is using Browserify's CDN called wzrd.in. This service compiles, minifies and serves the latest version of these scripts for us.

You'll want to make sure these load before the main script handling the form submission. e.g.:

<script src="https://wzrd.in/standalone/formdata-polyfill"></script>
<script src="https://wzrd.in/standalone/promise-polyfill@latest"></script>
<script src="https://wzrd.in/standalone/whatwg-fetch@latest"></script>

<script>
  const scriptURL = '<SCRIPT URL>'
  const form = document.forms['submit-to-google-sheet']
  ...
</script>

And... Yep, this is it.
After trying several different methods to do this "simple" action, that's the only one that worked for me - so I hope it helps. Feel free to share it/save it for future use and feel free to comment in case you have ideas to improve it!

5 Things I've Learned From One WordPress Calculator Plugin

Omer Lahav — Mon, 16 Dec 2019 16:56:26 +0000

I work for the largest online tech magazine in Israel as a full-stack developer. The fun part is that we always try to go the extra mile as part of our agenda. So when one of our partners reached out and said they'd like to implement a salary calculator in our WordPress based website, we said "sure!". They get exposure, our readers get value - everyone's happy. The really fun part? They got the data, and they wanted me to build it.

The thing is... I've started playing with software development ever since middle school. Yet, I started doing it professionally only a year and a half ago, when I graduated college - so I'm dealing with a lot of new problems. Therefore, I decided to write this article to help beginners like me with how they approach problems.

1. Plan ahead. It will save you more time than you think

The data I got from our clients was given to me in an Excel spreadsheet. Different categories, different titles, different conditions for display - All in one spreadsheet of one file. Dealing with Excel files isn't my first priority, so I've started thinking about how should I manage the data in order to easily use it in the plugin. In one of my last projects, I imported data from an external JSON file so I decided to play a bit more with this familiar format.

The problem was the need to rearrange everything in a proper way in order to differentiate the different categories and conditions for a certain salary. So I asked my developers friends on Twitter and from seriously thinking about creating a 3-dimensional array in JSON, I figured an easier more logical way to arrange all the data. I even wrote part of the JSON file I plan to have in a notebook I have, just to be sure - and writing it manually actually made me find a mistake in the excel file.

2. The internet is full of shortcuts. Use them!

It will probably be the most obvious part of this article, but nowadays - we have online tools for everything. You don't necessarily have to install an add-on in your local software, nor install something completely new - there's probably something online that does exactly what you want. After I looked for a proper tool on the first two pages of Google, I found this open-source XLSX-TO-JSON converter that's hosted on Github. It doesn't look anything fancy or requires any installment in your computer - but it definitely gets the job done.

3. Divide and conquer your plugin directory

I've seen two main work methods when it comes to software development. Some developers try to gather all of their code into the least amount of files and folders as possible.

I belong to the group that does the opposite.

I built this calculator as a Wordpress plugin that creates a Wordpress' shortcode of the calculator that I can place wherever I want. The layout I used was:

Assets folder - A folder that contains all CSS, JavaScript and the JSON files (including any related images) of the project.
config.php - A file that contains constant variables relevant to the plugin.
scriptsenqueue.php - A file that enqueues all the CSS & JavaScript files that are relevant to this plugin.
main-plugin-file.php - The main file of the plugins, that contains the relevant comments for a Wordpress plugin, and import all the other files in the folder. In this case, I put the shortcode content in this file, even though I could split it to another file as well.

Why such division is necessary? Even with such a small feature, there might be problems in different places like the shortcode's content, the CSS settings, the JavaScript's logic... No matter what's the problem, you'll know exactly where to find it.

4. Accept any small feature as a challenge

In the beginning, the initial request was: "Make us a salary calculator". They haven't told us anything besides they have the data for us, but they want us to develop it for them since it's our website and we're familiar with its infrastructure. I sent them the first draft of how the calculator would be - and I got gazillion other requests in return. From "make it look more like this other calculator", to "add an icon to make it pretty and make it spin!" and even "add a statistics feature to it!" after it was already uploaded to production (I'm not kidding).

Nonetheless, because of this one plugin - I've dealt with CSS' animations, integration to a Google spreadsheet, Wordpress shortcode & plugin creation, and JSON files related functions in jQuery. No matter which assignment you're asked to do - take it, because you might learn from it more than you think.

5. Dealing with clients is sometimes just part of the job

Depends on your workplace, you won't always have to deal with clients. Sometimes you will get requirements from your project/product manager, and sometimes you'll have to talk to the clients directly. Talking to someone who's not necessarily technical might be a bit challenging, but that's a skill you should have sooner or later.

Whether you'll begin to work freelance, become a product/project manager or even just a senior developer - having the skill of talking to clients can help you deal better with people, and knowing how such people think can help you define your problems and needs better, and because of that - create better products.

Take as many opportunities

Like plenty of other junior developers, I'm familiar with the thirst for developing gigantic stuff. My humble recommendation for any other junior developer - enjoy those small projects. They most definitely will help you in the long term.

Credit: And as a living proof for lesson #2 of this article, how do you think I made this article's banner? A small hint:

Introducing a banner generator for your articles on DEV

Christopher Kade ・ May 31 '19

#webdev #meta

What do you guys think? Would you recommend anything differently, or do you have anything else to add for beginners?