Webforms on a static, serverless site can be difficult to implement on your own. For ease of use you could opt for one of the paid website form services, or spend a few minutes connecting it with a google sheet (or forms).
Integrating forms could take a bit of effort if you're deploying using a serverless service. You can use one of the paid tools that can expose an endpoint, use a server route (or cloud functions) to put data into a database, or, as this guide will demonstrate, connect a Google Sheet to your form.
Serverless Forms with Google Sheets
Google offers scripts for it's applications. A spreadsheet is the most popular (not the best always, but well, it's simpler) way of storing data, and Google automatically links it's forms to a sheet, but those are not very customizable. This method allows you to link any form to a google sheet.
Start by creating a spreadsheet and setup your column names on a frozen row. These will serve as keys to your form values. We'll name the form fields to these.
Next, go to the script editor in tools.
Google automatically sets up a
.gs code file. This is where we'll put in the code for the script.
Remove the existing code and setup two variables for the sheet name (Default is 'Sheet1') and script properties.
Next, we'll add a setup function and set the ID of the active spreadsheet in the global
scriptProp. The IDs change whenever google wants them to, so this is a precautionary measure to ensure we modify the right sheet.
Google sheets deployed as a web application require a
doGet() function returning a HTML or Text output. Let's setup a post function for the row inserter.
Let's add the code for the post endpoint to insert rows into the sheet.
Firstly we need to ensure only one execution of the script takes place. Google provides a lock service for the same.
Google scripts throw errors when something is not right, so for the sake of this example we'll wrap the code into a try-catch, returning a errored response if an error occurs.
Finally we also need to release the
lock so other waiting processes can get hold of the sheet.
Inside the try-catch, we'll first need to get the spreadsheet, and then the specified sheet within the spreadsheet.
We can also use
SpreadsheetApp.getActiveSheet() to get an active sheet, but this could return whichever sheet is active in the spreadsheet. If you've multiple forms you want to link to a spreadsheet, you can pass the sheet name in the request, which will be available in the event object
e. For example:
Now let's get our named headers (or keys) from the first row.
Get the index of the new row we're about to insert (at the end). To insert at the top, you'll have to move rows 2 to last, one row down.
Now create a new row value array based on the headers. Also set the current time in case of timestamp (first column). You can also calculate derived properties (split name into first and last, etc.)
newRow into the sheet
And finally return a successful response
The script is ready to be deployed now. Firstly save the script and run the
setup function. This only has to happen once.
Now select deploy to deploy the script as a web app.
Web app as the deployment type, execute as yourself (will not require logging in via google), and accessible to anyone.
Once deployed, you'll get a web app link you can use as an form action as such.
Now you can simply make a POST request from a random form using fetch (or any library). Sheets use the encoded form data object, JSON request body will not work. But you can attach it to the HTML
<form> action if needed.
On submission of the form,
You should see the sheet updated with the details.
There you have it, a free and simple way of linking sheets to web forms.