A project I have been working on involved using Google sheets as a database where an external form writes entries to a Google Sheet and on occasion reads it out again or updates parts of it. The best example I could find was this post by Martin Hawksey. It was a bit old though and some parts were outdated so I updated it and put it in a gist on Github.
I figured I’d create a small example to demonstrate how to write to a Google Sheet. We are using a form (below) to store out favourite movies to a database. We need to create a new public sheet. A page where we post from and an action script to handle the POST request. The page we post from will have three input fields that match the column names in the sheet we’ll create: title, year and rating. To get the target of the post request (the action in form), we need to create the Google sheet first.
- Create a new Google sheet
- Create the headers so they will match with your input names.
- Go to Tools > Script Editor and paste in the code from the gist below.
- In the script editor, go to File > Manage versions: add a description and save as a new version.
- Publish > Deploy as web-app > Choose latest version. Deploy as me and anyone has access even anonymous. You will get an Oauth warning that you can ignore for now.
- In this screen you see the Current web app URL. Copy this and add this to the form action.
Code for external page
<form class="form-movie" action="https://script.google.com/macros/s/AKfycbw2eiwYgfA6ZnMdo94JjkvV5DNA0G_5Z29jkDuk93nV-w8Jr2Q/exec" method="post"> <h1>Simple Movie DB</h1> <div class="form-group"> <label for="title">Movie Title</label> <input id="title" type="text" name="title" value="" placeholder="Movie title" required> </div> <div class="form-group"> <label for="year">Year of movie release</label> <input type="number" name="year" min="1900" max="2099" step="1" value="2018"> </div> <div class="form-group"> <label for="rating">Rating</label> <input id="rating" type="number" name="rating" min="1" max="10" step=".1" value="7"> </div> <div class="form-group"> <button type="submit" name="button">Submit</button> </div> </form>