I work as a data analyst in my day job, and as with most companies, we use Office 365 for our productivity suite tools. While I’ve gotten accustomed to using Excel for pretty much everything from exploratory data analysis to creating dashboards, I’ve really started to prefer Google Sheets for its ability to create bespoke data entry forms. So in this blog, I’m going to show you how to create a basic App Script to enter data into a Google Sheet and deploy it as a web app!
Before we start, I’d like to mention that App Scripts use JavaScript and HTML to function. This blog (mostly) doesn’t explain how any of the code or markdown works but it doesn’t take much time to get up to speed with either of these topics – especially with the help of AI tools nowadays!
What are Google App Scripts?
Google App Scripts are ways of programmatically automating or completing tasks in Google’s productivity tools such as Docs, Gmail, and Sheets. If you’ve ever used Office Scripts for Excel Online, these are pretty much Googles version of the same thing. If you have a Google account, you can start to create and run very your own App Scripts.
Creating your first App Script
It’s incredibly straightforward to create your first App Script. Navigate to your Google Drive, click ‘New’, hover over the ‘More’ button and find the ‘App Script’ button. Click it. A new App Script editor window will be launched with your project already open.
You’ll notice on the side that the file we’re editing is called ‘Code.gs’. This is our script file that acts as an interface between other services that we want to use, such as Google Sheets. This is analogous to having a back-server for a website.
We can create other files by clicking the plus button. These can be script files or HTML files. It can be useful to create multiple script or HTML files in order to keep our App Script modular, which means that the elements of our code are structured and divided in a way such that it’s easy to see, find, and understand each component. But before we get to deep into all of that stuff, let’s create an HTML file. It should look like this:
We’re going to deploy our App Script as a web app, and this HTML file will form the front end. In the body of this HTML script, I’m just going to create a header element that says ‘Hello World’. The next thing we need to do is to return to our script file and add the function that then renders our HTML upon load. This requires us to go back to our Code.gs file and create a doGet function.
Deploying the App Script as a web app
In order to deploy our script as a web app, we’re going to need to create a doGet function. This function will run whenever someone sends our app a HTML GET request. When they send us this request, we want to load the contents of our app using our Site.html file.
This function has a parameter ‘e’ (or whatever you want to call it, but conventionally this parameter is named ‘e’) which refers to the information or metadata about the GET request that was made by yourself or someone else to your web app. As I mentioned before, this function along with a return statement for an HtmlOutput or TextOutput object is mandatory in order for you to deploy your App Script as a web app.
We now have everything in place to deploy our App Script. The next steps are quite simple. We navigate to the deploy button in the top-right, click new deployment, click on the cog and select ‘Web app’, then fill out the information and metadata about the deployment, before finally clicking ‘Deploy’ in the bottom-left corner.
You can then click on the provided link and see your web app:
That’s all! Now you can start customising your web app to interact with Google Sheets. This can be done by having some inputs and a button in your HTML, then bounding that button to a function in your script file that can pass in this data as arguments to a function in your Code.gs file.
Example of a simple data entry form
basicWebApp.gs
basicWebApp.html
basicWebAppCSS.html
basicWebAppScript.html
This example (which is how I started off with App Scripts) is from Learn Google Sheets & Excel Spreadsheets on YouTube. Despite being over 5 years old now, they are still mostly relevant and very helpful when it comes to App Scripts.
Final tips
- Create multiple HTML files – one HTML file for your site, one for your CSS, and one for your scripts! Then you can use inline JavaScript in your site to run a function from your .gs file that generates the content of your CSS and your script file. This is demoed in the simple data entry form example.
- Add a ‘defer=true’ attribute to your script tag / file so that you can ensure all of your HTML elements have been generated before any other code that interacts with them is run. This is quite important if, for example, you want to have a drag-and-drop list where the list items are populated from an array in your Google Sheet.
That’s it!
Hopefully now you’re able to create some basic data entry forms. If you’re interested in learning more, I highly recommend watching some of the videos on App Scripts by Learn Google Sheets & Excel Spreadsheets on YouTube.
Thanks for reading!