Democratizing Application Development with AppSheet E-Book

Koichi Tsuji

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 434

Veröffentlichungsjahr: 2023

Beschreibung

Many citizen developers regularly use spreadsheets in their business and day-to-day jobs. With AppSheet, you can take your spreadsheets to the next level by enhancing their ease of use. The platform allows you to run your business efficiently and manage it in the field outside of an office or indoor environment. This book enables you to create your own simple or medium to complex hybrid apps for business or personal use.
As a beginner to AppSheet, this book will show you how the AppSheet Editor works and how it is used to configure, test, and deploy an app and share it with others as users or co-authors. You’ll learn about widely used features such as how to use data sources, create app views and actions, construct expressions with AppSheet functions, and make your app secure through security and UX options. Next, you’ll create email/attachment templates and develop reports/documents based on templates, store in the cloud, and send files through emails. You’ll also understand how to integrate third-party services and monitor various usage statistics of your app. As you progress, you’ll explore various features with the help of sample apps that you create using the book.
By the end of this book, you’ll have learned how to make the most of AppSheet to build powerful and efficient applications.

Leseprobe

Democratizing Application Development with AppSheet

A citizen developer's guide to building rapid low-code apps with the powerful features of AppSheet

Koichi Tsuji

Suvrutt Gurjar

Takuya Miyai

BIRMINGHAM—MUMBAI

Democratizing Application Development with AppSheet

Copyright © 2022 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Alok Dhuri

Publishing Product Manager: Harshal Gundetty

Senior Editor: Nisha Cleetus

Technical Editor: Maran Fernandes

Copy Editor: Safis Editing

Language Support Editor: Safis Editing

Project Coordinator: Manisha Singh

Proofreader: Safis Editing

Indexer: Pratik Shirodkar

Production Designer: Alishon Mendonca

Business Development Executive: Puneet Kaur

Marketing Coordinators: Rayyan Khan and Deepak Kumar

Production reference: 1301122

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK.

ISBN 978-1-80324-117-3

www.packt.com

To my whole family and all my friends, who understand and support me all the time. Special thanks to my wife, Yoshiko, for being my loving partner throughout our joint life journey.

– Koichi Tsuji

To my mother, Sujata, and to the memory of my father, Prabhatkumar. To my wife, Ujwal, and my daughters, Anishka and Anushree. They all have been my strongest support system.

– Suvrutt Gurjar

To my mother, Michiyo, and father, Kazuhiro, without whom this book would not have been completed.

– Takuya Miyai

Contributors

About the authors

Koichi Tsuji is the leading author of this book and is a self-proclaimed “world-leading” AppSheet citizen developer. He founded Vendola Solutions LLC, based in Japan, and has provided extensive services dedicated to AppSheet to support his clients. Through his company, he runs the AppSheet online educational platform to train new AppSheet app creators and also builds feature-rich apps on behalf of his clients. He strongly believes that “nothing is impossible with AppSheet.” To prove this, he is always considering new ideas, tips, and tricks to take AppSheet as a platform to the next level. He has been active in the AppSheet global community for many years and has introduced a number of new ideas, which are admired by app creators.

I want to thank the people who have supported me in growing my own business, especially all my friends working in the Google AppSheet team who closely support me daily. Thanks also to the editors and the team at Packt for their help and support throughout the process.

Suvrutt Gurjar has worked in the IT industry since 1998. Up until 2014, he played various roles, starting as project lead. Later, as a delivery manager, he managed software deliveries for Fortune 500 client accounts, managing teams of 300+ software professionals. Prior to his IT career, he worked in the industrial automation field for over 13 years. He has commissioned several prestigious industrial automation projects and has written a great deal of industrial automation software. He has worked on the AppSheet platform since late 2016 as a freelance developer and has developed complex AppSheet apps for global clients. To date, Suvrutt has been among the top five all-time contributors in terms of solutions and likes in the official AppSheet community.

I would like to thank my wife, Ujwal, for her continuous support and encouragement. I thank my coauthors, Koichi and Takuya, and technical reviewer, Thierry D’hers, for their excellent collaboration and guidance. Finally, I thank the technical editor and the team at Packt for their help and support throughout the process.

Takuya Miyai has worked in the IT industry for years, with expertise in Salesforce. He holds a number of Salesforce certifications. After encountering AppSheet and being fascinated by its future possibilities, his direction in life completely changed. Since joining Vendola Solutions LLC, he has actively worked as an AppSheet professional consultant to help AppSheet communities and clients. He also acts as an active community member for no-code solutions.

I thank my coauthors, Koichi and Suvrutt, for their excellent collaboration and guidance. Thanks to the Packt team for giving me a great opportunity in joining this book project and for their help throughout the process.

About the reviewer

Thierry D’hers is a seasoned engineering and product executive in the self-service, business intelligence, and visual analytic software industries, with over two decades of experience. Thierry’s career has spanned companies as diverse in size and growth maturity as Hyperion Solutions (now Oracle), Microsoft (for over 15 years), and Tableau Software. Most recently, Thierry served as chief product officer for FocusVision and AppSheet (acquired by Google in 2020). Thierry currently advises start-ups and nonprofit organizations on product strategy and data analytics systems. Thierry has recently joined Vivun, a SaaS start-up, to lead its product and engineering team.

Table of Contents

Preface

Part 1 – Introduction and Getting Started

1

Getting Started with AppSheet

How AppSheet apps are generally built

Prerequisites

Converting your ideas into an AppSheet app

Summary

Part 2 – App Editor and Main Features

2

Understanding App Editor and Data Sources

Preparing your data in the right format

Creating a new app from your own data

Exploring the basics of the AppSheet editor

Analyzing the table and column settings

Checking table settings

Configuring column types

Understanding REF-type columns

What is dereference and dereference list?

Creating drop-down lists to assist data entry

Making a slice from a table

Summary

3

Presenting App Data with UX/Views

Technical requirements

Reviewing the basics of the UX pane

What is a view in AppSheet?

UX – view types

Single-sheet-type views

List-type views

Other view types

Displaying views

Controlling actions associated with view events

Changing behavior in the table, gallery, and deck views

Format rules to highlight important data

Creating a new format rule

Branding with theme colors and icons

Theme

Primary color

App logo

Launch image

Header and footer

Managing the app’s internal terminology through the Localize tab

Summary

4

Manipulating Data with Functions and Expressions

Technical requirements

Understanding functions, expressions and AppSheet Expression Assistant

Type-casting

Making decisions with Yes/No functions and expressions

Making decisions with conditional functions and expressions

Manipulating text with text functions and expressions

Manipulating multiple values with list functions and expressions

List functions and expressions

Performing arithmetic calculations with math functions and expressions

Performing date, time, and duration calculations with date and time expressions

Extracting components of a datetime value

Performing duration calculations

Understanding week-based calculation functions

Calculating duration in decimal value results

Performing record-level operations with table functions and expressions

Understanding the difference between column- and row-level functions

Understanding important miscellaneous functions

Using functions related to the app user

Using the CONTEXT() functions

Using the TEXT() function

Using multiple functions in single expressions

Summary

5

Manipulating Data with Behaviors and Actions

Technical requirements

Understanding actions and action types

What are actions?

Action types and their usage

Important settings in the Actions settings pane

Using app actions for navigation and working with CSV files

Deep link expressions for navigation actions

Unconditional navigation

Conditional navigation

CSV export or import actions

Configuring data change actions

Actions for setting the values of some columns in a row

Using bulk actions to change data in multiple rows

Changing data in summary views with bulk actions

Adding a row to another table using values from the current row

Reference actions

Using external actions for external communications

External actions for starting an email, a message, or a phone call

External actions for navigating to a website and opening a file

Sequentially executing multiple actions with grouped actions

Event actions

Offline or sync behavior of an app

Sync – app to cloud

Offline use

Sync – cloud to data source

Summary

6

Controlling App Users and Data Security

Controlling who can access an app

Adding app users to your apps and defining editing permissions

Adding new users as normal users

Adding new users as a co-author

Defining the app role for each user

Controlling permission levels with expressions

Using the userrole() expression to control permissions

Using the useremail() expression to control permissions

Passing an expression to the table permission control

Controlling what data is sent to a user’s device and security options

Creating a new security filter per table

Implementing additional security measures for safety

Summary

7

Managing the App Environment

Analyzing app features and transferring the app ownership

The Plan requirements tab

Transferring the app ownership

Setting editor settings, copying, and deleting apps

Performing app version management

The version history of an app

Upgrading the app version for all users

Creating a stable version of an app

Deploying an app with all features

Checking an app’s deployment readiness

White labeling an app

Additional options under the Deploy tab

Monitoring app usage and app sync performance

Knowing the user’s app usage pattern

Using Audit History for the row-level user activity

Analyzing an app’s sync performance

Sending on-demand messages to app users

Summary

Part 3 – Advanced Features and External Services

8

Automating Recurring Data Changes and Scheduling Tasks

What is AppSheet Automation?

Understanding a Bot and its components

Event – Data change or Schedule

Creating a new Bot

How is a Process constructed?

Steps for a Task or Data change action

Adding a new step to a Process

Setting up a task in a Step

Data change actions

Learning about Bot setup through a practical example

Setting up the column used to trigger the Bot for a data change event

Creating a new Bot by adding a new Event

Adding a new Step to send an email

Running a data change action from a Step

Trying and testing Automation

Summary

9

Using Intelligence and Advanced Features

Enabling advanced features

Searching data from an app with Smart Assistant

Building your own predictive models

Creating a new predictive model

Building an OCR model

What are AppSheet OCR models?

What types of images are scannable by OCR?

How do you set and train an OCR model?

Summary

10

Extending App Capabilities with Third-Party Services

Technical requirements

Displaying images with dynamic text

Services to use

A quick guide on using the URL service for a basic text label image

Dynamically changing the label contents

Customizing the styles

Using QuickChart to generate a dynamic chart as an image

Building a radial gauge chart

Bonus tips – a working sample expression for a progress bar chart

Generating a QR code for the image dynamically

Integrating with Google Workspace products

Adopting a card-style message rather than plain text

Google Apps Script and use cases

Displaying the latest currency exchange rate

Google Apps Script sample code

Reading data from a spreadsheet

Summary

Part 4 – App Templates and Tricks for App Building

11

Building More Apps with App Templates

Creating your apps portfolio for other app creators

Sharing your apps with teams and others

Using others’ app templates to help with your app development

Using app templates from the AppSheet website

Building templates for your self-reference

Summary

12

Tips and Tricks

Technical requirements

Formulas (expressions)

Creating a URL to access images and other files

Calculations between time and duration

Number division (decimal to be used)

Actions

Forcing sync upon navigating to the target view

Setup guide

Reference action to update the parent based on the child

Updating child rows based on a data change to the parent row

Column settings

Establishing many-to-many relationships

App settings

Adding a table with a large number of rows

Reading a Google Drive folder as a table

Bots and automation

Creating sequential numbers by using a bot

Creating a dynamic URL to open files created by a bot

Setup guide

Views

Creating a chart or map view

Summary

13

Appendix

Joining the AppSheet community to be a citizen developer

AppSheet is growing endlessly

Summary

Index

Other Books You May Enjoy

Preface

This book enables citizen developers to create their own simple or complex hybrid apps for business or personal use. It teaches you how to combine features so that an app is efficient and easy to use for the end user. Many citizen developers regularly use spreadsheets in their business and day-to-day jobs. They can use the AppSheet platform to take their spreadsheet work to the next level with its ease of use. The AppSheet platform allows citizen developers to run their businesses more efficiently and manage them in the field, beyond the office.

As the book progresses, you will learn how the AppSheet editor works and how it is used to configure, test, and deploy an app. You will learn how to effectively use data sources, create app views, and deploy actions, and discover how you can make your app secure through security options and user-friendly through UX options. You will learn how to create, store in the cloud, and send files through emails by using AppSheet Automation bots. You will learn how to integrate third-party services. Also, you will build an app throughout the book so that you get hands-on experience.

By the end of this book, you will be able to build medium-complexity AppSheet apps using the AppSheet features discussed in the book.

Who this book is for

This book is aimed at beginner- and intermediate-level citizen application developers in small- or medium-sized businesses, and business users who want to develop their own business apps. You are expected to have a basic knowledge of Google Sheets or Excel and an understanding of different spreadsheet formulas. Knowledge of SQL and some basic software development knowledge is beneficial but not necessary.

What this book covers

Chapter 1, Getting Started with AppSheet, introduces the AppSheet platform. Before creating our first app, we first need to understand some basics about the platform so that you know what you are doing and why. We will go through the overall process and what is important to know.

Chapter 2, Understanding App Editor and Data Sources, explores the basic skills needed to create a new app and use the app editor. We will also discuss data sources, such as tables, columns, and sliced tables.

Chapter 3, Presenting App Data with UX/Views, describes how an app is presented to app users, what kind of views you can use, and how to format your data with colors and icons. This chapter also discusses branding and localizing if your app requires these features. We will also discuss how to control an app with UX options.

Chapter 4, Manipulating Data with Functions and Expressions, dives into the major AppSheet functions with which you can create expressions to manipulate data in your apps. You will learn to construct expressions with the help of practical use cases. You will learn different types of functions, such as yes/no, conditional, text, list, math, date, time, and duration functions. If you are already familiar with Google Sheets or Excel formulas, that will be beneficial. You will also learn to combine functions of different types to construct more complex expressions.

Chapter 5, Manipulating Data with Behaviors and Actions, examines actions, which enable you to implement app behaviors such as making quick data changes, in-app or out-of-app navigation, and CSV uploads/downloads. With external actions, you can navigate out of an app to a website, compose an email/text message, or make a phone call. You can directly invoke actions by tapping on an action icon or indirectly through events, such as record save or record select. In this chapter, you will learn how you can use different action types to improve app behavior. You will also learn how to best configure an app’s offline and sync behavior.

Chapter 6, Controlling App Users and Data Security, explains how to control who can use an app and at what permission level. You can also control data itself in different ways, such as filtering data on the server side by applying a security filter.

Chapter 7, Managing the App Environment, shows you how to use multiple options in AppSheet’s Manage tab to monitor and set up an app’s environment. Here, you can select team colleagues as app co-authors and transfer app ownership. You can see the app’s version history, define a stable version, and upgrade an app version. You can check an app’s deployment readiness for any environmental errors. You can monitor an app’s usage by various users, automation, and an app’s sync time performance, even at table and column levels. You will learn how to deploy an app and also how you can send on-demand messages to app users.

Chapter 8, Automating Recurring Data Changes and Schedulinge Tasks, conducts an extensive review of AppSheet Automation. Automation is built to handle more complex challenges such as sending emails, creating files with templates, and manipulating data in the background. It can also run business processes quicker than directly from an app.

Chapter 9, Using Intelligence and Advanced Features, explores three AI-driven features and the basics of each feature. AppSheet is generally a data-driven platform for developing feature-rich business applications, but it is also powered by AI to give additional and advanced functionalities, such as data searching, predictions, and data scraping out of image files.

Chapter 10, Extending App Capabilities with Third-Party Services, discusses open source services, which are in abundance these days. We will select a few external services that will add some flavor to your app views, such as a dynamic image with text on a colored background and a dynamic image-based static chart, and also look at how to set up integrations with Google Workspace applications such as Google Chat.

Chapter 11, Building More Apps with App Templates, shows you how you can share apps as “public” apps on your own portfolio page for any AppSheet user to view or copy them. Portfolio apps demonstrate a feature or business functionality that other app creators can learn from. Additionally, the AppSheet website has a collection of template apps that demonstrate certain business functionalities. We will learn how you can further build your app by copying the template or portfolio apps of other app creators. We will also discuss how you can build your own template apps for future reuse and reference.

Chapter 12, Tips and Tricks, offers you the most practicable and useful tips and tricks of AppSheet to help you develop custom functionalities to satisfy the unique needs of business applications. As AppSheet app creators spend more time with AppSheet, they will naturally find out that the learning curve to develop their own functionalities is a step up when they reach a certain skill or knowledge level. This is because app creators are expected to take the advanced step of implementing their own business logic in a single application. The tips in this chapter are not only related to data manipulation but also advanced UX settings to bring customized user experiences, as well as the advanced usage of bots to automate complicated business processes with ease.

Chapter 13, Appendix, concludes the book. When you have reached the end of book, you will have a good understanding of how to build an app with most of the AppSheet features. The next step is to join the AppSheet community and discuss with other citizen developers how they solved their challenges. The community is a good source to gain ideas and increase your own skill level. We will also briefly discuss what you can expect in the future from Appsheet.

To get the most out of this book

Before you start to build your apps, you need to create a Google account, as you need to have access to Google Drive, Google Sheets, and other Google services, such as Google Docs. You can create a new account at https://accounts.google.com/signup.

When the Google account is created (skip creation if you already have an account, of course), it’s time to create your AppSheet account. Follow these steps to create an AppSheet account:

Go to www.appsheet.com and log in with your Google account from the top-right corner.Click the Get started button. Then, select the name of the cloud service to be used for authentication. Select Google. After that, follow the guide so that you give permission for AppSheet to have access to your data by using your Google account.

Figure 1.1 – The AppSheet home page

Now, your account is generated and tied to your Google account.

You will also now have your AppSheet account ID, which is a seven-digit number. This account ID is used when a new app is created. Your unique app name will look similar to Inventory-5689905. As you created your AppSheet account with your Google account, all your images, files, and templates will be stored in Google Drive by default.

When creating any kind of app, it needs a data source. It could be a spreadsheet, such as a Google sheet or Excel file in OneDrive, or even a real database, such as a cloud SQL database. Whatever the data source is, it always needs to be in the cloud. For example, trying to connect an Excel file from your laptop or phone won’t be possible, as that file is not in the cloud. These are the data sources you can use with an AppSheet app:

GoogleGoogle Calendar/DriveMicrosoft Excel OnlineSmartsheetAirtableSalesforceDropboxBoxApigeeODataBiqQueryA cloud database (SQL, for example)

In this book, we will use Google Sheets as a data source because it’s one of the most common ones. Therefore, please make sure to access AppSheet using a Google account to follow along with this book.

AppSheet editor styles used in this book

The style and layout of the app editor we refer to in the book along with the screenshots are all taken from the app editor at the time of writing (October 2022). AppSheet changes the style and layout of its editor quite frequently, so you may see a different layout.

Downloading/referring the sample apps

While we are explaining AppSheet’s features and functionalities to you throughout the book, you can refer to the sample apps that we prepared for this book. All of the sample apps can be found on the portfolio page at https://www.appsheet.com/portfolio/5689905. In each chapter, we will name the relevant sample app, which you can check for reference. These sample apps will help you to see how things are set up. You can even copy the sample apps to your account. We hope these sample apps will help you get familiar with AppSheet quickly.

Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots and diagrams used in this book. You can download it here: https://packt.link/FigT2

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: “Mount the downloaded WebStorm-10*.dmg disk image file as another disk in your system.”

A block of code is set as follows:

IFS(       WEEKDAY([DATE)=1, "Weekly Holiday",       WEEKDAY([DATE])=7, "Half Working Day",       AND(WEEKDAY([DATE])1,  WEEKDAY([DATE])7),  "Working        Day")

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

IFS(        WEEKDAY([DATE)=1, "Weekly Holiday",        WEEKDAY([DATE])=7, “Half Working Day”,        AND(WEEKDAY([DATE])1,  WEEKDAY([DATE])7),  "Working        Day" )

Any command-line input or output is written as follows:

$ mkdir css $ cd css

Bold: Indicates a new term, an important word, or words that you see on screen. For instance, words in menus or dialog boxes appear in bold. Here is an example: “Select System info from the Administration panel.”

Tips or important notes

Appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, email us at [email protected] and mention the book title in the subject of your message.

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata and fill in the form.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Share Your Thoughts

Once you’ve read Democratizing Application Development with AppSheet, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere?

Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application.

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

Scan the QR code or visit the link below

https://packt.link/free-ebook/9781803241173

Submit your proof of purchaseThat’s it! We’ll send your free PDF and other benefits to your email directly

Part 1 – Introduction and Getting Started

In this part, we will show you how you can get the most out of this book. We will review the general steps involved in building AppSheet apps and also discuss the prerequisites for becoming an effective AppSheet citizen developer.

This part has the following chapter:

Chapter 1, Getting Started with AppSheet

1

Getting Started with AppSheet

AppSheet is a leading low-code platform for creating business apps. The platform was originally created to develop apps for mobile devices back in 2014 and has been growing ever since. AppSheet became a reputable Digital Transformation (DX) platform and helps app creators to develop business apps compatible with devices such as mobile phones, tablets, and laptops, most importantly, without writing a single line of code.

AppSheet will offer you a path to develop a robust application that could solve a challenge you face in your business without asking for coding skills and knowledge. AppSheet prepares a set of template apps with which you can start to build your own apps, and you can also create a new app from a scratch using data such as Excel and/or Google Sheets. The beauty of AppSheet is that everyone can get on board easily and start for free.

Google constantly keeps adding new features to enhance the functions of AppSheet. This means AppSheet can be used to create business applications with tons of advanced features to facilitate the daily jobs in your offices and solve your own business problems. However, once we start a journey with AppSheet by building our first app, you may notice that it is not easy to handle this platform to customize app behaviors to meet your own requirements. In the context of the comparison between no-code and full-code development, a no-code approach to building an application is far more efficient and productive than full coding in terms of the required number of man-hours to complete a project. However, to validate this comparison, app creators must be proficient as well as confident in no-coding tools such as AppSheet.

This story applies to AppSheet. An understanding of AppSheet is a key to success for efficient business applications. Hence, Learn AppSheet is an essential first step for all app creators who want to freely develop their own unique apps.

This book is designed for absolute beginners to AppSheet as well as those who have a small amount of experience to assist them in learning the platform in a structured manner. The process of development with AppSheet is generally consistent regardless of the type of app you build. It is vitally important to understand the AppSheet way of development when building your apps by learning the basics.

Throughout this book, you will learn the overall processes to develop apps from the beginning to the end to increase your level of confidence in handling the AppSheet Editor. Furthermore, we will touch upon some advanced skills and techniques for you to develop feature-rich applications with AppSheet.

By the end of this book, you should be able to grasp the full process of building an AppSheet app with basic features.

Your path as an AppSheet app creator is endless. Each time you learn something new, you will have something else to aim for ahead of you. We hope this book will provide you with a gateway to being a successful AppSheet creator.

In this chapter, we will cover the following topics:

How AppSheet apps are generally builtPrerequisitesConverting your ideas into an AppSheet app

How AppSheet apps are generally built

Whenever you create a new app, these basic steps are always followed:

First, you prepare the data source, usually a spreadsheet on a cloud platform such as Google Drive.Next, you connect the data source to AppSheet. After you connect the data, AppSheet will automatically generate the app for you.Once the data is connected, the app development process moves to the AppSheet Editor. Through the AppSheet Editor, you define how the connected data will be used and configure various app settings, including creating actions for data manipulation and navigation, designing views and layout, and adding automation bots.You can invite test users to try the app for further improvement.When the app is ready, AppSheet provides an easy way to deploy the app to your team and colleagues.

In summary, those five steps are as follows:

Connecting the data sourceEditing your app definitionDeploying the app

By reading this book, you will learn the basic sequence of this app development process. All app developments occur in the AppSheet Editor. We will be covering all the important details of these steps to learn how to configure your app through the AppSheet Editor in the upcoming chapters.

Prerequisites

To work with AppSheet, it is helpful if you are familiar with spreadsheets such as Google Sheets and Microsoft Excel; AppSheet uses its own expressions but most of them are identical to Google Sheet formulas. The syntax can sometimes be slightly different, but the idea is mostly the same. Also, having an understanding of the basic concepts of how spreadsheets work will help you to build your app’s foundation. However, don’t worry if you are not familiar with Google Sheets – you will get to know it in the following chapters. In fact, there are a number of AppSheet expressions that are unique to AppSheet and not found in other spreadsheet services. We will cover AppSheet expressions throughout this book, especially in Chapter 4, Manipulate Data with Expressions and Formulas.

In addition to familiarity with spreadsheets and formulae, it is helpful if you know a thing or two about databases. AppSheet is a database-driven platform; the concept of the relational database is one of the basic foundations of AppSheet. However, this is not absolutely required, as you learn what you need to know while learning AppSheet.

Converting your ideas into an AppSheet app

We wish to highlight some important concepts before we begin looking at AppSheet.

Whenever you develop apps with AppSheet, your requirements for the application come first. Building apps means that your ideas are converted to AppSheet apps. You should always have an idea of what you want and expect from your app. For example, you may wish to develop an app with a function to help a company’s internal approval process for applications for holidays. You also may wish to add a function to send notifications to concerned parties once the applications are approved.

Once you have a clear idea of what you want, think about what you need to do with the AppSheet Editor. AppSheet provides a wide range of options to customize the behavior of your app. To achieve your goals using the AppSheet Editor, you need to know where in the AppSheet Editor to go and how you need to define your own logic. Finding the shortest paths through the AppSheet Editor is key to making your app development process efficient. To gain that skill, you need to understand AppSheet’s basic processes.

Summary

We hope that you now have an AppSheet account prepared and are ready to start the journey with us. As stated in this chapter, the app-development process consists of certain routine steps, regardless of the kind of app you are trying to create. By learning these essential steps in detail, you will be able to master AppSheet.

In the following chapter, we will start with data preparation and creating a new app, which is always the first step in AppSheet app development. Let’s start learning now!

Part 2 – App Editor and Main Features

In this part, we will describe the whole app-building process. We will create an app together with a lot of features so that you will have a good understanding of what capabilities you have when using AppSheet.

This part has the following chapters:

Chapter 2, Understanding App Editor and Data SourcesChapter 3, Presenting App Data with UX/ViewsChapter 4, Manipulating Data with Functions and ExpressionsChapter 5, Manipulating Data with Behaviors and ActionsChapter 6, Controlling App Users and Data SecurityChapter 7, Managing the App Environment

2

Understanding App Editor and Data Sources

With a Google account in hand, you are now ready to start building your first app. In this chapter, we will discuss the process of creating a new app from your own data and how to configure table and column settings. An AppSheet app is built from your own data sources, usually a Google Sheets or Excel spreadsheet. Therefore, the first step of building a new app is to prepare your data. Once the data is ready, you are fully ready to create a new app with AppSheet.

Once a new app is created, the whole development process will happen in the AppSheet editor. With this chapter, you will learn how your spreadsheet data should be organized so that it can be used as a data source with AppSheet. We often see cases where the app creator wrongly models data tables, which will cause a number of problems when you develop your apps. To avoid this problem, proper data modeling is important.

You will learn the basics of setting table and column configurations. We will also conduct a quick run-through of the app editor where all of the app development activities are going to happen. By the end of this chapter, you will know how to easily navigate inside the editor.

In this chapter, we will cover the following topics:

Preparing your data in the right formatCreating a new app from your own dataExploring the basics of the AppSheet editorAnalyzing the table and column settings

Data modeling and table schema are the foundation of app development; therefore, it is vitally important that you know the basics of the data settings and how to correctly configure them because it will directly affect the app’s behavior in various places. By the end of this chapter, you will know the basics of adding new tables from your own sources and defining column settings.

Preparing your data in the right format

Before building a new app with AppSheet, the data source for your app needs to be in place. Throughout this book, we are using Google Sheets as the data source for our sample app and demonstration. To proceed, please make sure you have a spreadsheet for your own exercise to start to build your application, and ensure the data is stored in a tabular format. A tabular format is simply information presented in the form of a table with rows and columns, where the first row at the top always represents the name of columns and each row represents a subset of the data. A quick way to see whether your data is in a tabular format is to check whether a new subset of the data will be appended at the last row as a new record, instead of being added as a new column.

Figure 2.1 – Monthly income and cost table in a tabular format

Figure 2.1 is an example table, presenting the monthly income and cost for a company in simplified terms. As you can see, each row represents a subset of data for a particular month. If a new record is added to this table – that is, data for 2022-05 – it will be added to this table as a new row. So, this data will grow downward. Therefore, this table is in a tabular format, which is suitable for AppSheet.

Now, let’s see another possible format of the table for the same dataset in the following figure.

Figure 2.2 – Data in a non-tabular format

You can now see the difference in the format compared with Figure 2.1. When a new subset of data for a new month comes in, it should be added as an additional column entry under 2022-05. In other words, the table will grow to the right in the form of an additional column. This format does not fit with AppSheet.

The very first step of app building is to prepare your own data. To move to the next step, please check your data is in tabular format. If not, you need to convert your data before adding it to AppSheet.

If your existing data is not in a tabular format, the easiest solution to convert it into a proper tabular format is to use the Google Sheets function to transpose data with copy and paste.

Figure 2.3 – Transpose data with copy and paste in Google Sheets

Figure 2.3 demonstrates how to transpose the copied data. Using an example of a non-tabular format table in Figure 2.2, you copy the whole table by selecting the range of A1 and E4 cells, and then go to Edit | Paste special | Transposed. You can transform the non-tabular format into the tabular format table easily. The same function is also available in MS Excel as well.

If you have suitable data, then you are ready to move to the next step.

Creating a new app from your own data

Considering your data is in the right format as a Google Sheets spreadsheet, let’s create your first app using it. Log in to AppSheet, and you will arrive on the My Apps page. AppSheet quite frequently changes its UX; the following figure shows what it looks like at the time of writing:

Figure 2.4 – The My Apps page

As the name suggests, this page presents the list of the apps you have created, or apps that you have been added to as a co-author.

Now, let’s create a new app by following these steps:

Click the Create button in the upper-left corner of this page. When you click on it, you will see two different options, Start with existing data and Start with a template.Select the Start with existing data option. Then, a dialog box appears where you are asked to name the app you are going to create and to select a category out of the list.Click the Choose your data button at the bottom. Another new dialog box will appear, asking you to select the data source. When you newly start AppSheet with your Google account, you should see a single option for Google Sheets with your account’s email. Select Google Sheets and select the spreadsheet from the file selector.

Figure 2.5 – The AppSheet editor is launched after generating a new app

After waiting for a while, AppSheet will create a new app from the spreadsheet you selected, and you will land on a page that looks like Figure 2.5.To proceed, click on the Start customizing button.

Now you have successfully created a new app and landed on the AppSheet editor to edit the app in detail.

In the following section, we will walk you through the AppSheet editor to learn its anatomy.

Exploring the basics of the AppSheet editor

We have successfully created the app by connecting to the data source. To customize the app, we need to change the settings for the data, but before discussing this process in detail, let’s briefly walk you through the AppSheet editor you just landed on.

Please have a look at the following figure, where we have outlined the main elements of the AppSheet editor.

Note

The style and layout of the AppSheet editor we quote and the screenshot taken from the editor are all taken from the editor at the time of writing, October 2022. AppSheet makes changes to the style and layout of the AppSheet editor quite frequently, so you may see a different layout.

Figure 2.6 – Anatomy of the app editor

Basically, the app editor page has a fixed layout. Let’s begin with the main menu, which is located in the upper left of the editor:

Navigation bar: to Whenever you take any action to build your app, you need to select one of the options from this bar. It is located on the left side of the window, and you will see several items. When you select an option from the navigation bar, a sub-menu will appear at the top and the content inside the main space will dynamically change based on your selection from the sub-menu.These are the navigation bar options:Home: This is the welcome page, where you can find guides to help you build your application, among other things.Info: As the name suggests, we can find information about this app, such as various properties and a link to a dashboard that displays the usage and performance of your app.Data: This helps with managing table and column configurations. We will dive into this menu in more detail later in this chapter.UX: This sets up the pages of your app, which are called views in AppSheet, including applying format rules to change the styles, such as font colors and sizes. We will deal with views in detail in Chapter 3.Behavior: This helps with adding and controlling AppSheet actions. Actions are one of the key elements for AppSheet apps to manipulate data and assist navigation. We will learn about actions in detail in Chapter 5.Automation: AppSheet provides a feature to automate processes such as sending emails, creating files, and other useful automation functions. We will deal with AppSheet automation in Chapter 8.Security: This is a menu for controlling the security aspects of your app in terms of defining the required authentications for app users and other security-related permissions. We will cover this topic in detail in Chapter 6.Intelligence: AppSheet provides a few advanced features driven by artificial intelligence, such as advanced data searching inside the app and optical character recognition. We will cover this menu in Chapter 9.Manage: This menu provides miscellaneous options across the app. We will cover the Manage menu in Chapter 7.Tabs for sub-menu: This is a list of the available sub-menus for the selected option under the main menu of the navigation bar. Once you select one from the main menu, the sub-menu associated with the selected option will be presented.Edit main space: This is the main field where your work on the definition of your app will happen. Based on the selection from the main and sub-menus, the content displayed in this space will change.Emulator | Preview pane: You can access your app based on the definition you have set through this preview pane. You can preview the latest app live as you develop. This is one of the most powerful tools in AppSheet because you can immediately access the latest app after making changes to the app definition to see how your changes will affect the physical apps.

Please refer to the following figure to see what the preview pane looks like.

Figure 2.7 – The preview pane

You can preview your latest app in different modes by switching from mobile to tablet and vice versa through the icons at the top. You can open the app in full-screen mode on your browser as well. At the bottom of the pane, there is a hyperlink to view the settings and to customize the view you are currently previewing. Another hyperlink will take you to the table or slice settings of the view that it comes from.

Action icons: Various action icons and links to take you to other pages are in the top-right corner of the editor. Most importantly, the icon for sharing your app is here. Sharing apps will be discussed in Chapter 6.Now that we have explored the menu options, you can go ahead and build your app.

In the following section, we will talk about the table and column settings after you add your data to the app.

To end this section, we will call your attention to one of the important actions inside the AppSheet editor. Whenever you make any changes to the app definition through the AppSheet editor, your changes are pending. Please make sure to save your changes. Before you save, you can undo and/or redo the change by clicking on the icons beside the SAVE button.

Figure 2.8 – Saving your changes in the AppSheet editor

When you work through the editor and have made any unsaved changes, the button is highlighted. Please ensure you save changes constantly so that you don’t lose your work by closing the page without saving.

Analyzing the table and column settings

When you are working on a spreadsheet, such as Google Sheets or Excel, you may not pay attention to the data types. However, data types in AppSheet are quite important to define because they will affect the other behaviors inside the app, such as actions and expressions you may use inside the app. AppSheet is a database-driven application and platform, so it is important to define how your data is going to be dealt with inside AppSheet.

There are two different places you need to visit to change settings if required:

Table settingsColumn settings

Checking table settings

In the navigation bar, go to Data and click on the Tables tab. We have started to build a new app from a Google Sheet, and you will see one tile in this Tables tab. Expand the tile by clicking on it. The expanded tile will look like the following screenshot:

Figure 2.9 – Table settings

In this table settings tile, there are a few optional items, but most importantly, we need to determine the permission level for editing this table.

Figure 2.10 – Permissions to edit data for a table

In this section, we define how the app users will interact with this table. As you can see, there are four different options to select:

If you select Read-Only, then, as the name suggests, the data in this table cannot be edited at all, meaning users can only view the contents.If Updates is selected, users can edit the data in the app.If you want to allow users to add new records, then the Adds option should be highlighted.Similarly, if you want to allow users to delete records, the Deletes option should be selected.

You need to select these options based on the requirements of your app. Since this is the start of your app development journey, just leave them as they are. As we will see in Chapter 5 of this book, AppSheet provides actions to manipulate data and assist navigation from one view to another. You have to bear in your mind that the selection of the permission level in this section will affect the available actions as well. For instance, if you allow users to add records to this table, then the action to add new records will be automatically generated, and vice versa if you deselect the Adds option.

If your spreadsheet has multiple sheets, then you need to add them one by one. At the top of the Tables tab, there is a button called New Table. To add a new sheet as a table in AppSheet, please click this and select the file and sheet to add new tables.

The next step is to move to the Columns table to check and change the column definitions.

Configuring column types

One of the most important steps when building an app is to configure the data types for all the columns. When we are working on a spreadsheet such as Google Sheets or Excel, we are not really paying attention to the data type when we apply formatting to the cells, such as text, date, and number type.

In AppSheet, the app behavior will be automatically aligned with your definition of the columns in the table. Defining the appropriate data types for all the columns in the table is vitally important. Let’s review how you define data and learn how it will affect your apps.

Available data types

To define the data type, go to Data | Columns. Then, you can expand the table you are going to work on.

Figure 2.11 – Column settings

This page presents a list view of the columns that are coming from the spreadsheet, which is the data source you added to the app. We can see the following in the preceding figure:

The first column on the left lists the names of the columns coming from your data.The second column contains the data type settings, where we define the data type for each column. When you click the drop-down menu beside the data type for this column, you will see a list of data type options. When you’re developing with AppSheet, it is necessary to ensure the right data type is selected for all the columns.

Figure 2.12 – Selecting a data type for each column

The list of the available data types is quite long, so we will not run through them here, but you can visit the AppSheet documentation to find out more: https://support.google.com/appsheet/answer/10106435?hl=en.

For instance, if you are going to capture numbers, then naturally the type for this column should be the Number type. If you are going to capture the location data in terms of latitude and longitude, then the LatLong type would be the right choice.

If you want to upload and display image files, then your choice for this column should be the Image type. For files other than images, such as PDF or XLSX, the File type is appropriate. You first need to review your requirements for the type of data you want to capture for each column, and then you select the right type and assign it to each column.

Note that AppSheet app behavior changes with your selection of data type. For instance, if you select the Image type, then you will see the action icon to assist the process of uploading the file. If you select the Phone