Is there a Sheets or Excel file you and your team have been using that you find is not very user friendly and is very tedious to maintain? If so, learn how you can start managing the same data in App Maker by building your first simple app!
The Getting Started with Google App Maker series is targeted help newcomers of Apps Script / App Maker and software development to start writing their first web app with App Maker
This is Part 3 of the series; view Part 2 here.
Converting an existing Sheets/Excel spreadsheet that is heavily used in a business process to an App Maker app is probably one of the most common use case of App Maker that I have encountered. If you find you and your team are spending too much time simply managing a spreadsheet (and not doing the actual work) then a custom app can be a way for you to optimize your process.
Take the simple spreadsheet above as an example; a company’s social club may be using something like this to track the events they organize for the company. While the example spreadsheet is small, imagine if the club is quite active and runs hundreds of events through the year – this spreadsheet can get quite long and tedious to maintain.
So how do you start? In Part 2, I covered the 3 building blocks of App Maker: Data, Pages and Scripts. In this simple app example, we only need 2: Data and Pages.
Full walkthrough video can be viewed here; I’ve embedded specific segments of the video below so you can follow through with the content here.
Start a New Project
You access App Maker through the URL https://appmaker.google.com – this is the “editor” interface to App Maker where you will be creating your application. Go to “New” and select Blank Application to start your project.
Since we are starting with an existing spreadsheet, we can use it to build our Data Model for the application. The Import Spreadsheet function makes this a snap. Note: This only imports your column headers as fields; it does not import the data in your spreadsheet
The steps demonstrated in the video segment below are as follows:
- Click [+] in the Data section
- Select “Google Cloud SQL” as the model type and hit Next
- Note – If this option is not available, it means your G-Suite Admin has not set up a default SQL instance for App Maker. Contact your G-Suite Admin for more details.
- Click “Import Fields” and select the spreadsheet you want to use. Double-check the field names and types that was read from the Sheet before you proceed in creating the model.
- Once created, App Maker will show you the model created.
- Go to the Security tab in the model. This is where you can define who can actually use the data in your data model. By default, only users who has the role of Admins for the application can view and manage the data in your model. In this beginner tutorial, we won’t go too much into setting up roles and permissions, hence, we will just change the permission to give access to “Everyone”.
For this application, I will show you how to create 3 pages for your users:
- A “New Event” page that allows user to create a new event
- A “View Events” page that allows user to view all the events currently created in the application
- An “Edit Event” page that allow user to edit an existing event
I generally would draw a quick diagram of all the pages I want to build for my users, and how they would get to each page:
You build pages by dragging Widgets to a page. In the following video segment, you’ll see how to create the 3 pages using two kinds of widgets: the Form widget, which is used when you need user to enter something to your data model, and the Table widget which is used to display data in your data model in a table format.
As you can see, the Form and Table widgets relies on the existence of a Data Model, designed to allow users to interact with records in the Data Model.
The Form widget comes in two types: “Insert”, for user to add a new record to the Data Model you’ve selected, and “Edit”, for user to edit an existing record in the Data Model. You generally would need an “Insert” form to let user put data into your Data model. The “Edit” form works with existing records; it comes with buttons for user to scroll through existing records and create new ones if they wish.
The Table widget comes with sorting, pagination and delete controls. You can also make the table column editable so that users can edit directly on the table, instead of sending them to an edit screen. However, when dealing with a large number of records, fields and/or text entry, it is generally more user friendly to build an edit form for a user so they don’t have to scroll too much to work with the data.
Another thing your users would need is a way to navigate across the three pages. To do that, we will be using onClick events – this is our method of telling a widget to do something when a user clicks on it. App Maker has a nice set of preset actions to help you define what that “something” is.
For navigation, the present action “Navigate to Page…” is extremely useful for you to link your pages together.
See in the demo below to learn:
- How to add a Button to the View Events page to let user go to the New Event page
- How to make the table row clickable so the user can click on a row to go to the Edit Event page to edit that row
- How to add a Button to the New Event and Edit Event page to let user go to the View Events page
Let’s Test through Preview
At any point in time, you can click on the Preview Button to see a quick view of your end product. You’ll be using this Preview mode regularly to test and check whether your application looks and behave as you expected.
Note: The Preview mode of the application should not be shared to others! It is for your own use only. Your app will not function properly for others if you were to send them the link to your Preview app.
All Done! Time to Publish to Share Your App to Your Users
Once you’re happy with how your app works in Preview, it is time to Publish!
Before you first publish, it is good to check what your app’s Home Page is set to – this is the page your user will see first when they go to your app. For our Event Management app, I would like the user to see View Events first, so I can check under App Settings (The gear icon on the right corner) and ensure the correct Home Page is selected.
Once I set my Home Page, I can hit Publish to Create a New Deployment to Publish my application.
One thing you’ll notice is that the deployed application does not have the data from Preview mode. This is because every deployed ‘instance’ by default would have its own database, i.e. its own set of data. This is very useful when it comes to application development:
- You can run multiple deployments of the same application for different teams, if they need to work with their own data set.
- You can create a “Production” instance for your users to use, and deploy a “Test” instance for yourself for testing purposes – that way, you can play with the features of your application and try out new things, without affecting the ‘live’ data your users are using in the Production instance.
You will also notice when you created your Deployment, there are various settings you can change – I have left it in the default settings in the demo, but this is where you can tailor your security settings, such as restricting who has access to your deployed app, and who has the “Admin” role to your application (a point I touched on earlier in the Data Model section).
And that’s it! If you have followed through, you have now created your very first App Maker application! You can essentially copy the same steps with any simple spreadsheet of data that you want to manage in an app instead.
In the next article, I’ll be demonstrating how to improve the user experience of this application, such as adding a search function to the View Events page, and adding a couple lines of script to auto-complete some information
Follow me on Twitter to get latest news and updates on the site!