This is a quick tutorial to explain how to use Google Sheet as database to serve site content, connect HTML forms. To do this, here I will tell you about a very nice online open source tool called, Stein. This tool basically takes a Google Sheet URL from you and then converts it into a no coding database, an API. You can then use that API at various places. You can use the API to use Google Sheet as backend database, you can serve site content from Google Sheets and even connect HTML forms to capture form data directly into Google Sheets.
To use this simple tool, you need to have some HTML knowledge. All you have to do is load an external script on the web page and use a specific syntax to load data from Google Sheet and send data to the sheet. In the free plan of the service, you can use unlimited Google Sheets not having more than 200 rows. In addition to that, you only get 5000 requests per month in the free tier. There is a detailed documentation about the usage of this tool and instructions to host it on your own server.
How to Use Google Sheet as Database to Serve Site Content, Connect HTML Forms?
For getting started with this, tool, you will have to sign in using your Google account on its homepage. After that, all you have to do is just give it a Google Sheet URL and then it will process that. It will give you an API URL in return that you will have to use in the HTML code. You can add unlimited Google Sheets here and it will keep adding them in the dashboard that it creates for you.
You can go through the documentation page to see how to implement this. There are various applications for this tool but the two main uses that I see are for serving website content and connecting HTML forms. You can serve data for website content, you will have to use mail merge like fields in the HTML code. For example, you can see the following code and its output. The “data-stein-limit=2” parameter is responsible for fetching the specified number of records from the Google Sheet. In this case, it is 2 but you can change it according to your requirements.
<!--- Replace the data-stein-url value with your API URL --->
<div
data-stein-url="https://api.steinhq.com/v1/storages/5cc15dcb40/Sheet1"
data-stein-limit="2"
>
<div>
<h1>{{Caption}}</h1>
<h6>By {{ID}}</h6>
<p>
{{Time}}
</p>
<p>Read on <a href="{{URL}}">Medium</a></p>
</div>
</div>
Similarly, you can use it to connect HTML forms. If you want then you can use this API to get data from HTML forms and save that in a Google Sheet. The code to create simple form is given below. Do note that, the name of the input fields should be same as the columns name in the sheet in which you want to send the data.
<!--- Add post to Google Sheet --->
<form
data-stein-url="https://api.steinhq.com/v1/storages/5cc158079ec99a2f484dcb40/Sheet1"
>
<!--- Notice that the name attribute is set to the column name -->
<input placeholder="Title" name="title" />
<input placeholder="Author Name" name="author" />
<input type="url" placeholder="Link to Post" name="link" />
<textarea placeholder="Summary" name="content" rows="5"></textarea>
<button type="submit">Submit</button>
</form>
In the screenshots above, you can see how this works in serving website content and in HTML forms. You just have to write a simple HTML code to make it work. Also, apart from just the code, you have to add an extra line as well just before the closing of the “</body>” tag. You can see that below.
<script src="https://unpkg.com/[email protected]/dist/index.js"></script>
In this way, you can use this simple and powerful tool to use Google Sheets as a database for your site. Also, its ability of connecting HTML forms is what makes it awesome. And you don’t have to make your sheet public in order to use this API. As it has access to your Google account, so you can use private sheets with it.
Final thoughts
Once, I covered a tool to convert a Google Sheet to API but Stein is much more powerful than that. You just have to create a free account in order to use all of its power. Add unlimited sheets to read data or feed data in to the sheets from any web app or service. If you want more API calls and want to use long sheets then you can even upgrade to one of its premium plan as well.
