[MUSIC PLAYING]
CHRISTIAN SCHALK: Hello, and welcome back
to the Building with AppSheet series,
where I discuss building apps with AppSheet and Google
technologies.
I'm Christian Schalk, a Google Developer advocate.
And in this episode, I'll discuss
how to prepare your Google Sheet for use with AppSheet.
Let's get started.
In this episode, we'll cover the following--
how to name your Sheet columns so they
work best with AppSheet, how to use AppSheet keys to enable
AppSheet to automatically ensure unique rows,
and how you can structure your data in a relational format
with multiple tabs so that AppSheet can automatically
generate a relational app.
I'll also touch on some basic Sheets tips and tricks
on how to easily clean and format
your Sheets data for best results when
bringing into AppSheet.
One of the most important things to consider
when prepping a sheet for AppSheet is column names.
You can actually help AppSheet a lot
by providing helpful column names that
provide guidance to AppSheet when it generates an app.
Column name should be short and descriptive,
with no special characters such as spaces, exclamations,
hash symbols, et cetera.
You'll also want to avoid numeric or repeated column
names.
For each column of data in a Sheet,
AppSheet assigns a specific underlying data
type, such as date, address, email, and so on.
You can directly influence how AppSheet assigns data types
by using column names that imply a specific type of data.
For example, using a column named
Date or Due Date will automatically
generate an underlying Date data type in AppSheet.
Other literal column names will also work,
such as Address for an address, Photo for an image, and so on.
In general, column names serve as helpful hints for AppSheet
to assign the appropriate data types when generating apps.
A key difference between Sheets and AppSheets
is that Sheets don't require each row or record
to be unique, whereas AppSheet is an application development
environment, so its data must have unique record identifiers.
To accomplish this, AppSheet uses
keys to identify each record in the Sheet.
AppSheet defines keys automatically
when an app is generated, and automatically designates
which column or columns to serve as a key.
For example, Task combined with Description
could be used as a key.
You can also specify which column you wish
to use as a key in AppSheet.
Similar to column names, you can also provide hints
to AppSheet for key generation.
For example, having a column named ID,
along with unique data in that column,
will tell AppSheet to use that column as a key.
AppSheet will also ensure that value for this column
remains unique by automatically providing a unique ID
function as a default initial value for that column when
records are created.
You can also set the unique ID manually in AppSheet.
Before switching to the demo, here's
a few tips and tricks in Google Sheets
that you can also use to best prepare your Sheet for 2.
First off, the Explore feature in Sheets
can auto-analyze your data with AI to help refine your data
and remove duplicates and inconsistencies.
You can also use Sheets functions
such as Query, Unique, and Import
Range for further data cleanup.
It's also important to note that you would only use
these functions temporarily.
Once data is queried from a function,
you would copy and then paste just the output values
into a new column, and then remove the functions
before moving to AppSheet.
Also, using the built-in Sheets tools
such as Data Validation, Conditional Formatting, Cleanup
Suggestions, and Remove Duplicates
are also very helpful for data cleanup
before bringing to AppSheet.
So for the demo, I'll start with a familiar-looking example
Sheet with tasks, projects, and managers data.
However, one of the problems will
be that the data is all crammed together on a single Sheet,
with poorly named columns and inconsistent data.
I'll then update the Sheet to prepare it for AppSheet.
Once the data is prepared and separated into related tabs
and column names are improved and such,
it will resemble a traditional relational database,
which is where AppSheet can then take over and easily
generate an app from.
Let's jump to the demo.
OK, so switching to the demo, what we're looking at here
is an extreme example of a very poorly formatted Google
Sheet, which will not work well at all with AppSheet.
So for example, here we can see that we have poorly
named column headers.
We also have invalid characters and such.
There's also inconsistent data in some of the columns
here that I've highlighted in red.
And also, you'll notice that the records here, there's
repeated values in the different records.
And so this is actually a sign that the data is not really
set up correctly.
We should actually have all the data broken out
into distinct, different tables.
And so that's what we'll do to clean this up moving forward.
Another thing I want to show you is
that there is a column here that has formulas that
generates an email address.
And this is actually using a lookup
from a table over here on the right side here.
So that's another thing that we'll clean up.
And then finally, as you can see,
this is a very poorly named tab, which doesn't actually
work well with AppSheet as it creates a table name off
of that.
All right, so let's go ahead and switch
to a cleaned up view of this table here, or this Sheet.
And as you can see, I've cleaned up the column names.
I've removed inconsistencies.
I've also added validation so that only those specific values
can be added into those columns there.
And this is actually a hint for AppSheet to keep track of this.
And most importantly, though, is I've
broken out the data into three distinct tables or tabs.
And the way that these tabs or tables work
is that there is a project or a column
here with a name of a reference table.
So in this case, for the Tasks table,
there's a project column here that's
named Project that refers to the Projects table.
And likewise, there's a Manager column that
refers to the Manager table.
So these are all hints to AppSheet in order
to generate an underlying relational data model.
OK, so let's go ahead and generate an app now
that we've cleaned up our data.
So create an app.
And so while that's working, I'm going
to go ahead and give you a sneak preview of the app
that we're going to be building.
So this app essentially has the tasks, projects,
and the managers, as well as like a budget chart and a map
chart, as well.
And so for the tasks, for any one of these forms,
anytime you enter any data, you can
see that it's validated input.
So you can select different buttons, the date picker,
and dropdowns and so forth.
All right?
So this is the app that we're going to be building.
So let's go ahead and switch over to our generated app.
So there we are.
We have a quick preview of the app in that window.
And of course, here's the app over on the right.
So first off, you'll notice that we only
have a single table for tasks.
And so I'm going to go ahead and add tables
for projects and managers.
And here's the one for managers.
All right, so let's take a look at one of these tables here.
And we'll look at the columns.
And so as I mentioned, because we've selected good column
names, it actually selected the appropriate underlying data
type.
And also, because we've selected distinct
or we've added validation to the Sheet,
it has set it up as an enum data type,
and provided those distinct values as values
to enter into this particular application.
And the same holds true for the Priority column, as well.
All right?
We can also notice that the Project column is of type Ref.
That means that it's a reference to the Projects table.
So that's how it sets up the linkage
between the different tables.
So as you can see here, this Projects table
has a Manager column of type Ref.
It also has a Related Tasks of type List.
So it's actually looking back for the different relations
here.
So at this point, we have a fully relational data model
that we're good to go with.
The only updates I'm going to do now
is just update the UI a little bit.
So for UX, I'm going to go ahead and update this Statistics
thing here and make it a menu item.
So instead of it appearing on this bar on the bottom,
it will only be accessible via the menu on the top left there.
And then for the map, I'll just push that over to right-most.
So now the map icon is on the bottom right.
And then for tasks, I'm going to switch that
to a table, which is like the nice, compact format
that we want.
And I'll make that left-most.
So now the task is definitely going to stay on the left side.
All right?
So now let's go ahead and create two new views
for projects and managers.
So I'll click New View.
I'll set Projects as the name, or the data source.
And then we'll go ahead and--
let's see if I want to keep that as-- yeah, keep that as a deck.
And for this one, I'm going to change the display
icon to that icon right there.
So now we can distinguish it from the other Tasks icon.
And then now let's go ahead and create
a new view for the managers.
[MUSIC PLAYING]
And for this one, the deck is actually preferred here
because we have the images, as well,
as opposed to like a table view type.
All right?
And then again, for this one, I'm
going to update the display icon.
Have a nice group-looking icon.
And that's it for that one.
So the only thing we're going to do, as well,
is create a budget chart.
And I happen to know that there is a Budget
column in this Projects table.
So I set the view type as Chart.
And I set the chart type as Pie Chart.
And I select a column here, which
is the Budget column in that chart.
In that table, that is.
And then I'll select the display as the pie icon,
because everyone likes pie.
And that's it.
So our app is essentially complete.
So as you can see, we have tasks, projects, managers.
We have a budget there, showing the different budget values.
And we also have our map there.
And more importantly, we have built-in validation.
So if I want to enter in any data,
I could select from only those distinct values.
I have a date picker, as well as a priority
that has all the specific enumerated values.
So as you can see, in just a matter of minutes,
I was able to take a cleaned up Sheet and generate an app
and, with minimal updates, make it fully functional.
This concludes the Sheets and AppSheet episode.
Stay tuned for more coverage of this important topic
in future episodes.
And for more info on AppSheet and Google Workspace,
check out the links in the description below.
And for more videos like this, make
sure you subscribe to this series so you don't miss out.
As always, thanks for watching.
[MUSIC PLAYING]