Free Tutorial – EZ Hardware app

Hi there!

Let’s get you building your first app right away. While developing the outline and script for the full StepSheet Course, I realised that I was getting so detailed that I could turn this first tutorial into an online version – so here it is, for free :).

To find out when the full course launches, sign up to the StepSheet newsletter.

When I developed this app – it took me less than an hour (because I was already quite familiar with AppSheet). It will probably take you longer, maybe a few hours, but even so this is SUPER FAST in app development terms.

I hope you enjoy this tutorial and the feeling of empowerment that building your own apps will give you :).

Overview

Ok, let’s get started. Meet Jenna. She’s the CEO of fictional hardware chain based in California called EZ Hardware.

P1S1_1

She sells all the tools and materials that DIY enthusiasts and contractors could want, but there’s a lot of competition in the market.  She identified the need for a new way to communicate with contractors in the area.

Attracting contractors is  good for business. Contractors buy a lot of stuff, and they buy often. Jenna knows that having a way of connecting with contractors will be valuable to her and them. They will be able to stay on top of trends, gain new skills and sell more products. In turn, her hardware chain will sell more as well, and become a mainstay of the communities her stores are located in.

Jenna knows that contractors are mobile and don’t have time to sit around checking their emails. She’s realised that an app is the best way to connect her business with theirs.

Time to shine. Jenna has asked you to build an app for her company which will allow her to market her stores to contractors and will leverage some of the other marketing tools they have.

 

What you'll build - App Preview

This is a preview of the final app you’ll be developing. P1S1_2

P1S1_3

The app has a bunch of cool features:

  • Contractors can see upcoming events that will be held at the stores.
  • They can also view news articles that EZ Hardware feels would be interesting to them.
  • They can see featured product reviews.
  • In the menu, theres more options
  • Easy access to stores listings
  • A map showing all the EZ hardware stores – as well as a link to get directions to any of the stores
  • Links to useful “How to” Videos
  • And a link to EZ Hardware’s Digital accounts such as twitter, facebook and the company website.

And what’s amazing is that all this info comes from a Google Sheet!

 

Google Sheet preview

For Jenna, the ease of having this app built in AppSheet is that she can update all the information from a Google Sheet!

Here you can see the store listings…

P1S1_4

 

The upcoming training events…

P1S1_5

 

Links to the How To videos..

P1S1_6

 

The news articles…

P1S1_7

 

The Featured products…

P1S1_8

 

and the Digital Accounts….

P1S1_9

 

Whenever the spreadsheets get updated, the data in the app updates with it. AppSheet is democratising app development!

 

What you'll learn

This is a great introduction to AppSheet. You’ll learn the basics of how to make an app that displays information from a Spreadsheet.

You’ll learn:

  • How to create an an app from a spreadsheet
  • How to display data in a table
  • How to display location data on a map
  • List items chronologically ie. display news from oldest to newest
  • How to display data in reverse chronological order – for example, show events from newest to oldest
  • How to create links to YouTube videos
  • How to display images and link to web pages
  • How to link to social media accounts

Ready? Great! Let’s go.

 

Getting set up

To start, you’ll need an AppSheet account and a Google account, both of which you can create for free.

Sign up to AppSheet by clicking here and logging in with your Google account.

You’ll be building up each View in the app, one at a time. Let’s start with a Store Listing View.
Launching an app is quite simple, and we’ll get to that in a moment. First you’ll create the first bit of data that your app will need.

Select or create a Folder in Google Drive where you want to keep your app’s data.

Go ahead and create a folder called “Stepsheet Course” (where you can keep all your apps) and within that create another folder called “Tutorial 1 – EZ Hardware”.

P1S2_2

P1S2_3
Now, create a spreadsheet to store the app’s data. Call it whatever you want. I Suggest “EZ Hardware” or similar.
Here you can see the spreadsheet for the app I’ve already completed.

P1S2_4

Great. Now you next step is to install the AppSheet Add-on in Google Sheets. You will only need to install the add-on once.

Inside the Google Sheet, click on Add-ons and the Get Add-ons. 
P1S2_5

 

Then search for “AppSheet” and clock on “Add to Chrome”. Mine says “Manage” because it’s already installed.

P1S2_6

You’ll only need to install the AppSheet Add-on once.

Ok you’re all set! Let’s get to the next section.

 

Create your first View

Start off by renaming this first sheet “Store_Listing”. You’ll see that I’ve placed an underscore in between the two words rather than a space in the name. AppSheet will work fine with a space, but I recommend using an underscore.

Later, when you build more complex apps that reference tables and sheets, you’ll see that you will find it easier for you to read and follow what you are doing when referring to formulas, which can sometimes get complicated.
When spaces are present in the names, it becomes less clear whether the table name is “Store Listing” or just “Listing”. Like I said, this will become clearer when building other apps. For now, just take my advice :).

Now complete the header row for this set of data. You will always need to use the first row in the spreadsheet as your header row and then input your data into the columns. AppSheet won’t work if you list the headers in the column and then fill out data in the rows.
All stores have the same information. There’s the store name, the address, the operating hours and the store’s phone number. These will be the names of the columns for the header row.

P1S3_1

Go ahead and fill in data for the six fictional stores. You can use the same data as I’ve got here or input whatever you want.

P1S3_2
When adding stores addresses, choose some for 2 or three different cities so that a few cities have multiple stores in them- you’ll add some nice sorting options to the app using these groupings.

 

Launch the app

Now for the fun part. You’ll need to do this next step for each App you create – that is launching the AppSheet AddOn which will create the app. You will need to do this for each individual app because AppSheet only links to specific spreadsheets that you specify – it doesn’t have access to any other documents on your Google Drive account.

Go to Add-Ons in the menu. Click on AppSheet, then Launch. A sidebar will appear. Click on “GO!”.

P1S3_3

This will launch AppShet and will create the app.

Ta Da – there you go – your first  App!

P1S3_4

 

We’ll still tidy this up and add more views. But how cool is that? You typed some data in a spreadsheet and with the click of a button, created an app!

You can click on a Store Listing to view more info about the store.

P1S3_5

You can click on the Map and see where all the stores are listed as well .

P1S3_6

You can also call or text the store number, or launch the device’s Map app.

Pretty cool right?

Before we start building more of the app, let’s take a moment to tour the Editor.

 

Tour of the Editor

You’ll see there’s quite a lot going on here. You’ll be spending a lot of time in this environment and you’ll get to know it well.
AppSheet is constantly updating and improving the Editor, so don’t be surprised if it looks a bit different from what you see here, but I’ll do my best to keep this as up-to-date as possible.

  • On the left you’ve got the Menu.
  • The central portion has Menu Tabs which change as you cycle through the Menu.
  • And on the right is a handy preview pane which shows a preview of your app.
  • Along the top you have your account-level options.

P1S4_1

 

You’ll be mostly working with two parts of the Editor:

  1. The Data menu – which is where you manage the links between the app and the spreadsheet data, the formulas and operations on the data and so on.
  2. The UX menu (short for User eXperience) is where you will deal with the Views of the app that the User sees, linking Views to data, customising branding options and some other formatting options.

P1S4_2

 

With that short tour out of the way, let’s carry on working with the Store Listing View.

 

Clean up the Store Listing View's data settings

Now that you have your first view, let’s make sure all the data is being handled correctly by AppSheet and then we can look at the view.

Firstly, here’s a problem, see this big plus sign?

P1S5_1

 

Let’s click it as a User would be able to, and let’s see what happens.

P1S5_2

Woops – looks like a user can add their own store listing – that’s not right!

 

The Plus sign means Users can add data to the underlying spreadsheet, which for this app you don’t want. Let’s change a setting to prevent that from happening.  

Hit the “Cancel” button on the preview window to go back to the Store Listing View.

Now, because we are dealing with Data and how the Data is being handled, we’ll need to find the right setting in the Data menu. 

AppSheet works with this concept of Tables.

Each table links to a spreadsheet. Here you can see the first table that was created when we launched the app is call EZ Hardware Demo. We’ll add new tables when we start adding more data, for now we’ll just work with the current table.

P1S5_3

 

Here you can see the Table Name, the source path (which is the name of the Google Sheet) and finally, the worksheet within that Google Sheet workbook where the data is stored.

In this case, its the Store_Listing sheet.

Here the setting we want to change. See where is says : Are updates allowed? The default setting is to allow for Adds and Updates, which means users can Add to the Google sheet and Update the google sheet that this table links to.

 

Clicking on the drop-down menu, you can you can see all the different options available. You can control what users are able to do with each sheet.

P1S5_4

In other apps we will want users to manipulate data, but not in this one. We’re going to change it to “Read only” to make sure that Users can only view the data, not change the data, or add any data to the underlying spreadsheet.
Now you’ll see the plus sign option has disappeared. Some changes in AppSheet take immediate effect. Others require you to hit this “Save Changes” prompt that pops up. For good measure, you should always hit “Save Changes” to make sure everything works as expected.

P1S5_5

 

Ok great, now let’s look at the next Tab in the Data menu – Column Structure. This is where all the magic happens!

At the moment there is only one table, but this will start getting populated with more as you develop the app.

Clicking on Show All Properties, you can see the list of all the columns that you have in your spreadsheet.

P1S5_6

AppSheet does its best to identify what type of data is in each column – it does this by looking at the name and if present, the data inside each column.

 

This is important to get right so that the app behaves in the right manner. If for example this phone column was identified as text, then the app will not be able to launch the device’s phone when the number is clicked, because it would think it was just text. If the Address column is not identified as an address, then the app wouldn’t able to link to the Maps app on the device.

You’ll see there are quite a lot of options available!
Take a moment to verify that your app matches each of these column types.

P1S5_6_1

 

You can collapse and expand the Preview pane by clicking on this little arrow. Collapse it now to get a better view of the rest of the screen.

P1S5_7
Now you’ll notice some other settings here – Type Qualifiers, App Formulas and so on. These are outside the scope of this tutorial, but this is where you control some visibility options, as well as how columns treated in your app.

P1S5_8
OK you can expand the Preview panel again.

P1S5_9

 

Next, let’s head on over to the next Menu item – the UX menu!

 

Clean up the Store listing View's UX settings

Now you’ll notice that you actually already have two views.P1S6_1

 

You can click on the Map to see a map with all the stores identified.  Now, AppSheet saw that you have Map data in your table and so assumes that you want a map as well and it created a Map view. In this case, AppSheet is right! So leave the View in place, but this is a good chance to show you the View options.

Here you can see the two views listed. There’s the List View and the Map. AppSheet auto-generated this view when we launched the app, but we’ll be adding additional views manually. Click on the List View and you’ll see these options pop up. There’s a tonne of setting available and you’ll become familiar with them as you build more apps and experiment.

 

For starters, let’s change the name of the View. I think “Stores” is more descriptive than List.

P1S6_2

There, you can see the name of the button changed in the Preview window.  

You can also see that the data for this view comes from the “EZ Hardware” table.

There are a bunch of different View Types available. The Table view works well for this one.

You can also Change the position of the button on the screen. Leave it where it is for now – we can tidy up further one we have all our views in place.

 

Let’s change the icon to something a bit better.

Enter  “list” in the icon search bar and you’ll be able to select from a bunch of icons that look like lists. Select the bold-list icon.P1S6_3

There are some sorting options for how the data in this view should be displayed. For now, set it to Store Name, Ascending. You will see it’s sorted by the Store Name, in alphabetical order.

Click on “Save Changes” to make sure everything saves.

 

You can also look at the map view for good measure. All the settings look pretty good and the map looks good, so we don’t need to  change anything.P1S6_4

You’ll see the data source for this View is also the “EZ Hardware” table. You can have multiple Views link to one table. Generally however, you will have a single view for each table you create.

 

One last thing before you move onto building your next view.

Head back to the Data menu item.

 

As you can see, you currently only have a single data table, which AppSheet has defaulted to naming whatever the Google Sheet name is – in this example, it’s “EZ Hardware demo S3”. As you can see in the worksheet name, its the Store_Listing sheet that we started with.P1S6_5

 

For good measure, let’s go ahead and rename this Table to “Store_Listing” so that it matches the others that we’ll creating for consistency. Then you won’t need to try and remember two sets of names for where the data comes from.

P1S6_6
You’ll only need to do this renaming for the first table. The rest that you create will take on the name of the spreadsheet it’s based on, automatically. For some reason, AppSheet doesn’t name the first one automatically when launching an app from a sheet, but in other cases it does.

 

Ok, hit save changes. Now here’s another example of the beauty of AppSheet – anything that depended on this table that you’ve renamed has been automatically changed to reflect this name change. As you can see, the Store View has had its data source changed from the old name to the new Table name, and the same goes for the Map View as well!

 

P1S6_7
Little touches like this really help speed up the app development process!

That took a little while because I was explaining everything, but you’ll see the beauty of AppSheet once you get familiar with it – it’s amazingly fast to add data, views and quickly experiment with different settings.
Ok, let’s get cracking with some more views!

 

Add the Events View

Jenna’s team organises a bunch of different events at the stores.

Usually these are demos from manufacturers and other contractors which cover subjects like new products and installation techniques.

She wants to include information about these events in the app, as it will greatly improve the attendance and will attract more exhibitors.

Create a new sheet

The Events data will be quite different from the Store Listing Data, so you’ll need to create a new sheet within the workbook.

Go ahead and create a new sheet and call it Events.

P1S7_1

Now for the Header row. Remember that the Headers must always be on the first row of the spreadsheet.

Users of the app will need to know which store the event is at, so add a Store Name.

Next, add a short title for the Event.

Then, add the event date and the time it starts.

It worth also adding a longer description of the event so that Jenna or her team can add more information about any discounts or giveaways.

Now, complete the information for a couple of events. Make up whatever you want, or just copy what I have here.
Just be sure that the store names you add are also in the Store_Listings sheet. You’ll see towards the end of this tutorial why this is important when we start linking Views in the app together.

Next, you’ll add this new data to your app!

Create a new Table

Open up the Editor. Because you are dealing with the Data of the app, you’ll find what you need in the Data menu.

You previously launched AppSheet from the Google sheet to create the app, and at the same time, the first table was created – which you’ve just renamed to “Store_Listing” earlier in this turorial. The First two views were created – the list of stores and a map. The map view was automatically created because AppSheet identified that you have a list of addresses in your data.

 

Now that the app has been created, you will add additional tables and views from within the Editor.

Ok, now from the Data menu and in the Tables tab, click the Add New Table button.

P1S7_2

This next workflow order changes occasionally – sometimes you may need to first select the source for the table, but it should automatically pick up the same Google sheet that the app is based on..

If you need to select the source: Here you’ll need to connect a source – in other words, you’ll need to tell AppSheet where to get the data from. The options you first see may vary quite a lot depending on how AppSheet has updated, what data sources you’re using and so on. For now, find the Google option and select it. Next, search through the folders in your google Drive to find the workbook where the data is stored.

Now you will need to specify which sheet you want to connect. Select the Events sheet.P1S7_3

Now click on the Next button.

 

Set the permissions

Now you are asked to confirm which permissions you want enable for the Users of this app. You can always change this later if you need to.P1S7_4Because this is an app where contractors can only View information, we’ll need to uncheck all of these options.
If you select Allow Updates, then users of the app will be able to update the rows in the Google Sheet.

If you select Add, then users will be able to Add new data.

Similarly, if you select Allow Deletes, then Users will be able to Delete data from the spreadsheet.

You can allow different permissions for different sheets, but in this case Jenna does not want Users users to add, delete or edit any data, they must only view data, which makes sense.
In other tutorials we’ll get into the different permissions for when you do want users to manipulate the data but for this tutorial, we’ll always uncheck all of these options.

Once you’ve done that – go ahead and click Create Table.

 

Check the Data settings

After a moment, you’ll see in the Data Tab that you have a new Table called Events – the name taken directly from the name of the spreadsheet that the data is based on. You can see now why it was good practice to change the first table’s name to match the name of the spreadsheet i.e. Store_Listing. In that way, your naming convention remains consistent.P1S7_5
The Events table permissions are set to Read Only under the heading “Are Updates Allowed?”.

Before you look at the new Events View, take a moment to check that AppSheet has correctly identified the type of data you have in each column.

In the Data menu, click on Column Structure. Then click on the “Show All Properties” button next to the Events table name listed there.P1S7_6

Have a quick look at the Column Types.
P1S7_7The Store Name is set to the “Name” type. This is actually not very different compared to being set to the Text type, but the AppSheet documentation indicates that setting to the Name type means that in certain View types the name is given priority to other data types. Don’t worry too much about this though.

The Event is the event name which is set to Text. This is just regular text, which is perfect.

The Date and Time columns have been correctly identified as Date and Time column types.

The Description has been set as LongText. What this means is that it can accept and display longer inputs than the name and Text column types which have limits on the number of characters that these columns can include.

AppSheet does its best to try guess what sort of data it can expect to see by using the data in the sheet and the column names. Generally AppSheet is pretty good at identifying these but you should always double check them.

 

Look at the View

AppSheet has gone ahead and automatically added an Events view for you.
Because Views have to do with how the User interacts with the app and data, you will find the view options under the UX tab. UX is short for ”User eXperience” – a common software development term

Click on the UX menu and you should be on the Views Tab.P1S7_9

Here you can see the Events view has already been added and is listed alongside the Store and Map views.

From the summary data listed below the View name, you can see that it is drawing data from the Events table.

The View type selected is Table – that name can be a little confusing but it means that the data is displayed as a Table i.e. in rows and columns. There are a bunch of other ways data can be displayed, including Maps, Slideshows and a few others – which one you use will depend on the data available and what you want the app to look like & how you want it to behave.

Click on the Events menu item in the preview pane and you will see the list of Stores where events are happening.

You can click on any of the store names to see more details about the event at that store, and you can click the back arrow to return to the list of events.P1S7_10Now, this table view for the Events isn’t set up to be useful at a glance. The summary information presented here looks a bit sparse. It would be great to add the date column to this summary view to give a bit more information and make it easier for the User to see when events are happening. Let’s also tidy up the View button and placement.

 

Click the Events view in the Editor.

For starters, go ahead and change the button icon. A little Calendar icon will be more visually descriptive than the table icon.P1S7_11Start typing in Calendar in the icon search bar and then select the Calendar icon.

 

Let’s also change the position of the Events menu button. You will see that the button is currently placed in the Menu – that’s the Hamburger Menu dropdown button. Not very handy for this tutorial because you’ll need to first access the Hamburger menu each time to find it. Let’s change it’s position to Left.P1S7_12

Now you’ll see it appears on the bottom menu of the device. There is space for 3 icons, with the left “Share”  icon and right “Sync” icon being system icons which are fixed in place – you cannot move these.

In between the two system icons you can have anywhere between 0 and 3 icons for quick access to Views.

 

 

Ok, back to how the data is being displayed. You can play around with different options to see what happens, but once you are done, make sure that you have:

  1. “Sort by” Store name and have is sort in Ascending order. This will sort alphabetically.
  2. “Grouped by” Store name selected – this will group the data on the sheet by the Store Name – so multiple events at the same store will now fall under one store heading. Also set this to Ascending.

P1S7_14

Next setting is the “Column order”. AppSheet may have already displayed the Event Name and date in the view, but sometimes things change unexpectedly, so it’s best to explicitly sort by the things you want to view – that will prevent changes elsewhere affecting your Views.

So go ahead and have the Column Order Display as Event and then Date – these being the Event and Date columns from your table.

 

There you go – now that looks a lot clearer! The User will easily be able to get an overview of which events are happening and when they are happening at each store.
Go ahead and click “Save Changes” to lock these in.

 

Easy data updating - Add another event

Pretty cool right so far, right? Let’s see how awesome AppSheet is.

Remember Jenna, CEO of EZ Hardware? Her team wants to add another event to the EZ Mallet Store.

Because AppSheet lets you build apps from Spreadsheets, adding new data is as easy as adding rows to the spreadsheet.

Head over to your Events listing and add another event for EZ Mallet store. It can be anything – feel free to use my, the “Wallpaper Dos and Don’ts” event.P1S7_15

 

Now head back to the app preview window and hit the Sync button. P1S7_16

 

Tada! The data is now in the app!P1S7_17

This works the same for deleting or editing data on the back-end.
Users won’t have to always press the Sync button – the app will sync as the User switches between views, loads the app and so on. The sync button is a handy way to force the app to sync with the database right away.

Well done – you already have an app that has two tables and three Views!

I’m sure by now you are seeing how powerful AppSheet is.

 

Concept: Flow of data

Let’s take a moment to cover an important concept in AppSheet – the link between the spreadsheets (your data), Tables and Views.

Your spreadsheets are where data is stored.

In order to load that data into AppSheet – you create Tables. Each Spreadsheet tab in your workbook is loaded as a single Table.

In order to View the data in the Tables, you need to add Views which link to the Tables. As you saw with the Store_Listings table – you can have more than one View connect to the same table e.g. the Store View and Map View both link to the Store_Listing Table..

Technically, what’s happening when a User adds or edits data is that the data in the Table is first being changed on the device and then AppSheet syncs that Table on the device with the Spreadsheet via AppSheet’s server- it’s two step process.

For this EZ Hardware app, it doesn’t really matter in terms of app design that there is a two-step syncing process, but for other apps it’s useful. There are options for how often data syncs, or you can even make the app work offline so that the app is usable offline(using the Table on the local device) and syncs with the spreadsheet once a connection become available.

So remember – Data flows from Spreadsheet to Table to View & from View to Table to Spreadsheet.

P1S8_0

Great, onto the next Views!

 

Add the spreadsheets

Create a new sheet called How_To with headers How To and Video Link.Head over to Youtube and find a few videos of DIY tips- paste their links in the Video Link Column and give each video a simple title in the How To column.

Create a new sheet called News.Add the headings Headline, Date, Summary and Read More.

Do a quick google search for “Building Contractor News” and grab a few articles links – paste them in the Read More coulmn. Then add the date  it was published, the headline and write a short description of the story in the Summary.

Now add a new sheet called Featured_Products.Add the following column headers: Product, Image, Description, Review URL.

Head over to www.protoolreviews.com and choose a few tools to add as Featured Tools in the app. In practice, Jenna’s team will be able to do this themselves, linking to other site’s reviews or their own. Add the Product Name, a short description, a link to the main image of the review and a link to the review itself.

If you are not sure how to insert the image URL into the image column, just right click in Windows (or double tap on Mac) on the image you want ot include from the review site and Select “copy image address” and then paste that into the Image column.” This is one way of adding images to apps – you’ll see a different way to add images stored in the Google Drive account when you add the Digital Accounts View later on in this tutorial.

There you go. You have added the data needed for the three Views that you’re about to add to the app.

 

Add the Tables

Now head back to the Data menu in the Editor, so that you can add these new spreadsheets to your App as Tables.

First, you’ll add the “How_To” table.

 

Click on Add new Table. Then click on the source – and if you need to – navigate to the “How_To” spreadsheet in your Google Sheet and click Next.

 

Remember, you don’t want contractors to be able to add or change the data, only view it – so make sure all the permissions are disabled.Click Create Table.

Now do the same again for the News and Featured_Products sheets.

When you are done, you should see a list of all these 5 tables in your Data tab.

 

And scroll down to check that all the permissions under “Are Updates Allowed?” are set to Read Only.

 

Now go to the Column Structure tab in the Data menu and check that AppSheet has correctly identified the data types in each column.
Head to the How_To table and click “Show all properties”, they should look like this.

 

The News column structure should look like this.

 

And the Featured Product Table column structure should look like this.

 

You’re doing great! Onto the Views!

 

Configure the Views

AppSheet automatically creates new Views for Tables you add. You can delete these or add more views for the same Tables if you want. In this case, just leave them in place because you do need them.
The Views you have just created might look a bit different to this tutorial because AppSheet is always updating and improving the platform, but now we’ll make sure all the setting and icons are right to get things to display correctly.

Go to the UX menu and the Views tab. Click on the How To View to expand the options.
Make sure the View Type is set to Table and then search for and select the “film” icon.

 

Now go to the next view – Featured Products. In this tutorial, AppSheet automatically called the View “List”, instead of “Featured Product”, so we’ll change this in a moment.

If for some reason AppSheet hasn’t named the view correctly, you can always name it whatever you want. Your Editor may not have done this.

This is a good example of how the name of the View doesn’t have to match the table it is based on. However, here we’ll change the name to be consistent and so that it makes sense to the User.

“Featured Products” might be a bit long to display under the menu button on a phone, so just rename it from”List” to “Featured”.In this example, the view type is set to Deck view.

Have a look in the Preview pane. You will see it’s not that great – a slideshow will look much better in this case. Change the View Type to Slideshow.To change the icon, search for and select the Star icon.

Now for the News view.
Make sure the view is also set to Table, then search for and select the “News” icon.

 

Now, if you go to the News view in the preview and you click on the topmost article, you will see that the oldest article is listed first.

Wouldn’t it be better if the newest article, based on the date column, was listed first? That’s where sorting comes in!

In the UX > View menu in the Editor, make sure the News View is still selected, then scroll all the way down to the “Sort By” option and make sure Date > Descending is selected.Now if you go the the News tab and click on the first news item, you will see it’s the latest one.

All the icons in your Preview window may be in different places from what you see in this tutorial, but that will be cleaned up after you’ve added the final view in the next section.
Hit the big blue Save Changes button to lock these changes in.

Looking good – your app is really starting to come together now.

 

Add the Digital Accounts View

Time to add the final View for your app! This will be a view that links to EZ Hardware’s Twitter and Facebook profiles, as well as the website.


You’ll also get the chance to see how link to images that are stored on Google Drive, rather than using a link to an image on the web, as you did for the Featured Products.

First, you’ll need some images.

Next to where you created the original spreadsheet in Google Drive, create a folder called “images”. You can call it something else if you want, it won’t matter – but go with “images” for now.

 

Now download and place the following three logos inside the “images” folder that you’ve just created in Googel Drive. 

Here they are: one is for Twitter, one for Facebook and one for the Website.

 

Create the spreadsheet

In your google sheet workbook, create a new spreadsheet called Digital_Accounts. Add headers for the Account, Image and Url.Now populate the rows as you see here. I’ve used placeholders for EZ Hardwares accounts’ Urls, but you can use whatever you want.

Note how the images are referenced. Click on the screenshot above to get a better view of the image filename.
/image/<filename> – it’s saying look in the image file in this directory and find this filename.

 

Create the Table & configure the View

Now you add the Digitial_Accounts table as you did before.
Go to Data > Tables  and click on New Table.

 

Select the Digital_Accounts spreadsheet.
Make sure Users don’t have permission to change the data.

 

Head to Data > Column Structure and have a look at the Digital Accounts column types, they should match these.

 

In this tutorial,  AppSheet didn’t add the new Digital Accounts View – if that happened to you, then follow these steps…

Make sure you are on UX > Views (because that’s where Views are controlled from).
Click Add new View at the bottom of the Views list.

 

Change the name of the view to “Digital Accounts” and select the source data as the Digital_Accounts table which you just added.

Make sure the View Type is set to “Deck View” because that will look good in this context, showing the Logo as a small square image next to the text of the Url.

Search for and select the “world” icon for the button.

 Hit Save Changes and ta-da! You have a Digital Accounts View, just like that.

Take a second to pat yourself on the back and check it out!

 

A note on References

Ok, so how do References work? We’ll touch briefly on an important concept in databases, that of Key values.

Every table needs a Key column, that is, some column that will have Unique values in each row. This is a way of identifying unique records. Think of it as a Record ID which says “This is the ID for this Row, and the data in this row is associated with this record.”

AppSheet will attempt select which column is the Key for the table. If it can’t fine one, it will use the Row Number as the Key column. You can however change this if needed, but the Key column must have unique values, it can’t have any duplicates because then AppSheet won’t know which row the data in the duplicated keys belongs to.

In this app, the Store_Listings key column is the Store Name. That is because the store name will only be listed once this column and won’t be repeated. The Store Name column will only contain unique values. 

Note that the Store_Listing table now has a new Virtual Column added below the list of columns. This column shows that it is linked to the Events table via a Ref. Only Tables that have an incoming link get this new virtual column added.

In the Events table, the Event is the key column, because the event name won’t be repeated. The Event column will only contain unique values.

Ok, so you probably have thought, “But what if the same event name is repeated over again?” Then yes, you will have a problem. Generating Unique keys is beyond the scope of this tutorial, but in this case a simple fix will be to set the Row Number column as the key, which will work because this is a Read Only table, as are all Tables in this app.

The Store Name in the Events table can’t be the key for that Table, because of course the Store name might appear more than once in that column if there is more than one event assigned to that store.

So, the Events table can link to the Store_Listing table via a Reference, because AppSheet is able to recognise that the Store Name column in the Events table has the same values as is found in the Key column (the Store Name) in the Store_Listing table.

In short, the tables you want to link must have a common column in both (e.g. Store Name) and that column must be a Key column in the table being referenced.

 

Improve the Store Listing View / How to update a Table’s structure

The Store Listing View could be improved a bit. Wouldn’t it be great if stores were grouped according to the city they’re in? That way, a contractor could more easily find the information about the Store that’s relevant without having to go into each store to see the address and city.

The problem is, we don’t have a City Column in the Store_Listing Table by which to group the stores. There is an address column, but it’s not possible to sort by a full address. You need to have a new column called City in the table and then you can sort by that.

This next exercise will show you what to do when you need to change the structure of your spreadsheet after having already created a Table from it. Like most things in AppSheet, this is quite easy to do.

 

Update the Store_Listing spreadsheet

First, head over the the Store_Listing spreadsheet. Insert a new column anywhere called City and then fill the the City of each address.

 

In the Editor, go to Data > Column Structure and click on”Show” in the Store_Listing table.

You’ll see that nothing has changed. Where is the City column? You have to let AppSheet know that the spreadsheet structure has changed.

To do so, click on Regenerate next to the Table name. This tells AppSheet to go look at the spreadsheet and to restructure the Table to match.

 

After regeneration the Table, it will likely change position in the list of both the Table and Column Structure view. Go look at the Column Structure for the Store_Listing table. You will see that it now includes the City column. Well done!
Your Preview Pane may show that the Store View now includes the City column. This is helpful but as you can see here, the Stores could still be grouped together better.

 

Group Stores by City

So, where do we change how things look? That’s right, the UX menu!

Head over to UX > Views and expand the Store View options. Scroll down and where it says “Group By” , click the little “+” and select the City column, Ascending.Great work! Have a look at the Preview window – each store is now nicely grouped by the City the store is in.
Hit Save Changes. Next up, you’ll be making sure the Buttons on the app are all ordered correctly.

 

Clean up the Button menu orders

Now to make make sure that all the buttons in the app always remain in the correct order, you are going to sort them in the UX > View settings.

There are two places that the Buttons can be positioned – either at the bottom of the screen between the two system icons, or in the menu, access on the top left of the screen.

The bottom menu only has 3 available slots, called Left, Center and Right.

If more than one View has the same position set then your buttons won’t always be ordered correctly, so it’s best to make sure everything has a unique position listed.

You can specify where a View appears by heading to UX > Views and then setting the position of the bottom menu buttons in the Position setting. You will see a Ref option, but we won’t cover that in this tutorial.

For any buttons you want placed in the Menu, you will select Menu and then an additional setting for the Menu order will be displayed. You’ll get to that in a second.

While re-ordering button positions, you may find that buttons aren’t appearing where you expect. This is likely is you have other views that already have that position. Don’t worry – just work your way through the View settings for each view, and by the end everything should be where you expect.

Sort the Bottom and Dropdown menu orders

For the Events Menu, set the Position to “center”.

Set News to “left”.

Set Featured to “right”.

 

For the Stores View, set the Position to Menu.

Then scroll down and set the Menu Order to “1”.

Do the same for the Map View, and set the Menu Order to “2”.

Set the How To View to Menu Order “3” and the Digital Accounts View to Menu Order 4.
Hit Save Changes and now all your buttons are correctly configured!

 

Customise the App

The app is NEARLY done. Last thing to do is to customise it a little bit.

You’ll see that AppSheet has added a Default app icon (yours will look different) and default sync screen. Let’s change this to better fit EZ Hardware’s brand.

 

In the drop-down menu, in the “About” view, there is also just default information. Let’s change that too.

 

First you’ll need images. Download the following images in place them the same folder in Google Drive as you stored the Facebook, Twitter and Web images for the Digital Accounts view.

hardhat logo.png and contractor_bg.jpg

 

Now head to UX > Brand and Localize. In the App logo settings, click on the document icon and browse to find the “hardhat logo.png” image and select it.For the Launch image, do the same but select the “contractor_bg.jpg” image. Hit Save changes.

Now the App icon has been updated. If you hit the Sync button in the Preview window, you’ll see the Sync screen has also changed.

 

A Word of Warning – because of the way AppSheet works, updating the App’s icon won’t reflect on the devices of Users who have already installed the app .
You’ll see there are quite a few customization options available in this pan, but we won’t cover them in this tutorial.  

 

Set the Starting View

An important consideration for any app is which is the default view that the User sees when launching a fresh instance of the app. It’s never a good idea for the User to see an empty screen, so avoid setting the default View to a screen that might not have any data.

However, Jenna has assured us that the Events view will always have events listed and wants this to be the main attraction of the app, because it is what gets contractors into the store.

To make sure this is the default View, head over to UX > Options and set the Starting View to the Events View and hit Save Changes.Easy right?

 

Add some App information

Now let’s update some of that information in the Info menu.

Head to Info > Properties.  If the App name needs to be changed, change it to EZ Hardware.Enter some text in the Short Description and Description boxes. That’s all we’ll cover in this tutorial when it comes to these settings.
Hit Save Changes.

Congratulations - you're now an App Developer!

That’s it – the app is complete! In just a couple of hours you went from absolutely nothing, to a complete App with NO CODE. This would have been impossible just a few years ago.

What’s more, this isn’t an app that’s merely displaying static information. Updating the dat is as simple as changing data in a spreadsheet – a skill regular employees already have!

Ok so you aren’t done quite yet – you want to see this app on your phone right? And you need to send it to Jenna for review. Let’s do that next.

 

Install the prototype App onto phones

These instructions will show you how to install the app onto mobile devices.

The app is currently Not Deployed and is in a “prototype” state. You can confirm this by looking in the upper-left corner of the screen. 

 

First, download the AppSheet app from the Google Play Store or iOS App Store by searching for “AppSheet”. Here’s what the AppSheet Icon will look like once installed on the phone.

 

Once installed, you can send an install link to your email address (or Jenna’s) by going to the Users Menu and entering the email address you want to send the install link to and hit “Send install link”.If you want to, you can also copy the install link and send it via email.

 

The recipient will receive an email with the install link and will be provided with instructions on how to install the app. The process differs slightly depending on which operating system and browser is being used. For more info, see the AppSheet documentation. Here’s an example of what the email will look like.

 

Clicking on the install link will present the User with instructions for installation. If the User doesn’t have AppSheet installed yet, they will be instructed on how to do so.

Once installed – the recipient will be able to access the app! Here’s what it looks like in the home screen.

 

When the App is in prototype phase, you can have a handful of users testing the app without getting charged (at the time of writing this tutorial).

There you go. Now any time that you make changes to the app in the Editor and Save the changes, the app will update. When you edit data in the spreadsheet, this will be reflected across all the devices that the app is installed on as well.

 

Deploying your app

It’s at this stage that you will be asked to provide payment for your app – so we won’t go through the whole process, but you can see how to deploy the app from a Prototype to Production version.

Go to Manage > Deploy and then expand the Deployment Check drop-down. Select Run Deployment Check.

Now AppSheet will make sure everything is in place for the app to be deployed correctly.

In this case, the app is not ready. Details of all the checks are given in the checklist. What is causing this message is that this Account does not have payment details entered.
If you do want to deploy your app for real, have a look at  the AppSheet pricing page and the AppSheet Pricing Guide on this site and if you have any trouble, there’s a very helpful community of developers and the AppSheet team in the AppSheet Google+ Community.  

 

A note on Container Apps

The AppSheet app is a “Container App” which is a common method for app-building platforms to get apps onto devices in a practical way.

In AppSheet’s case, the container icon forms the “base” app that allows AppSheet to function and when you send install links, what is actually happening is that shortcuts are being placed on the home screen, not actual apps.

This means that you can have multiple AppSheet apps installed on the same phone at the same time, but only 1 app can run at any one time. It also means that if the AppSheet app is deleted, then the shortcuts won’t work anymore.

If you don’t want to have the AppSheet container app, and only want to have the EZ Hardware icon as a stand-alone icon, then what you are looking for is a Whitelabel app. The pricing page has details on the Whitelabeling price option. Note you will also then be able to submit the app to the Google Play Store and iOS App Store directly. Bear in mind that for iOS, you need to do so via an Apple Mac.
For details on how to submit Whitelabel apps, check out the AppSheet Video tutorials for Android and for iOS.

To Infinity, and beyond!

If you’ve enjoyed this tutorial, please let me know on the Google+ community or send an email to oscar@stepsheetcourse.com to say hi.

If you’d like to donate some money to say thank you, that would be great too :). You can donate any amount via Paypal below.

So, where to from here? While you are waiting for the video tutorial series (sign up to the StepSheet newsletter to be notified of when it’s released), here are some resources to help you in the meantime.

Head over to the AppSheet YouTube channel for a bunch of helpful reference videos and webinars.

Be sure to check out the Google+ Community if you can’t find what you need in the official documentation.

 

 

 

One final thing...

Pat yourself on the back – you’ve done well 🙂

 

======

End

Sign up to the StepSheet blog newsletter and be notified when new resources are released!
* = required field