Messy jots on AppSheets

Introduction

These are the jots I wrote down as I learned my way through AppSheet. So this a messy and not so coherent post. Info is scattered here, not necessarily in a sensible order. This is not a tutorial.

My interest comes from the fact that I’m looking for an apartment, and I want to go through a lot of details in each apartment I visit. So I looked for a tool that allows me to organize the information I collect, from the ad, through the thoughts I have about the apartment before visiting it, and most importantly, to efficiently examine the apartment.

This can be done with a plain paper form, of course, but when there are many details and stages, it’s easy to miss one. So the crucial part of my application is to get a quick final view that ensures me I’ve done everything, just before thanking for the visit. This is the most important part, and it’s also the most difficult to implement.

In hindsight, it took 9 days, which is much more time and effort than I intended and wanted to invest on this. I had the “almost done” feeling more or less from the beginning, but every next step turned out much more difficult (and annoying) than I imagined. AppSheet makes you feel like a code monkey that doesn’t know coding. And if AppSheet is used for a long-term project that is maintained over time, be sure to have a QA team that tests everything in every possible combination. The framework invites silly bugs everywhere.

General

• In a nutshell, AppSheet takes data from a database (preferably represented as a spreadsheet in Google Sheets) and presents this data in different Views, allowing both displaying and changing the data. The other side of AppSheet is called Automation, its ability to generate files, send emails / SMSes, submit HTTP requests etc. with the information in the database, based upon a template. This can be a pdf for an invoice, or a summary report of an inspection. Or an XML or JSON file, pushed to a web server to initiate some action on the information. So Views are primarily intended to feed data into the system, and Automation covers the consumption of the data for a useful purpose.
• AppSheet at no cost is unlimited in time and covers every feature of the platform with up to 10 test users. One user, myself, is enough. In free mode, the app can is limited to Prototype status, which has no significance when used only by myself.
• AppSheet reminds of Drupal in many ways, and it’s not necessarily a good thing. Lots of click-click-click around, everything is graphical, and one ends up feeling like a machine doing a silly task a gazillion times because it’s the same as configuring a Windows Server. Every. Single. Detail. Must. Be. Configured. Manually. With a prayer that there will never be a need to change all those settings that have been repeatedly configured. Plus, small bugs creep in everywhere, as there are a whole lot of details that one must keep track on when making every little change.
• Another thing common with Drupal is that every little task takes a lot of time, and sometimes the answer is simply “no, that’s impossible”. At least with Drupal, one can hack the PHP code. For example, the one thing that I considered important, but didn’t implement, was to get a list of the items in a checklist that I didn’t check. I didn’t want to implement the checklist as individual Yes/No (boolean) columns, because adding an item would require modifying the table, regenerating the schema, editing the View and then the report. So I did that with an EnumList, but the only information it emits is what was checked. So all in all, there is no way to implement this rather important feature without turning the checklist into a nightmare to maintain in the long run.
• There are certain limitations on databases and tables, in particular 1000 rows per database in the Free plan and for all plans there are 20 tables per database and 100 columns per table. Don’t worry, you’ll lose your sanity before getting even near these limits.
• Press the “Save” button to the upper right to make changes visible (e.g. when checking with mobile app). This also clears existing warnings, whether they were tended to or not.
• Offline use works great: No problem using the mobile phone even with no Intenet coverage. Update to cloud occurs later (may be required to open the app for that). Actually, the only reason I use AppSheets instead of a JavaScript home-cooked application is the ability to work offline.
• Backing up an app: Impossible. There’s no way to download the app’s own settings, and deleting an app accidentally is easy. The best solution I found for this is to share the app (or better, a copy of it) with another user (being yourself) and make a copy of the app as the other user. And never log in as the other user again. This way, the copy at the other user is safe from accidental tampering. I speculate that the reason for not being able to download the app itself is that AppSheet’s business model is pay-per-user, and it’s free for less than 10 users. So had it been possible to download and restore the app from a file, a lot of no-cost single-user accounts would have been created and deployed.

Random insights

• Each time “Save” is clicked, a new version is saved. It’s possible to go back to older versions. Version history is saved 7 days back by default.
• It’s possible to run a preview of the app by right-clicking the three dots to the right of the app’s description in the list of apps. Doing that, the specific version of the app is previewed, ignoring subsequent new saved versions of the app. There’s a misleading “refresh” icon on the app preview. It probably only updates data, not the app itself.
• If a “Map” view is generated automatically, delete it. Otherwise it will show up as the default view for everything (push marketing, it’s called), and it’s as annoying as it’s pointless.

Sources of info

• List of functions for use in expressions. There isn’t so many of them.
• Boolean expression (and that “not equal” is <>, not !=).
• About Templates
• About expressions and built-in variables. For columns that are references, an expression like [colA].[ColB] is used (official docs about dereferencing).
• If-expressions in templates

Creating a new app

This describes how to create an app based upon a spreadsheet. The spreadsheet’s first row consists of the description of each column, and the following rows contain data. Multiple sheets are treated as separate tables, each sheet’s name being the AppSheet table name (so don’t rename the sheets in the spreadsheet).

Upload the .ods or .xlsx file to Google Drive, or start a new Google Sheet.

if you’re already registered with AppSheet, right-click the file in Google Drive, pick Open With and choose AppSheet. An app is generated automatically from the data in the file. The name of the app will be the same as the originating spreadsheet’s file name. The column types (text, number, price etc.) are automatically assigned, but can be changed later on in the Data section. It’s even possible to change the type to Image, in which case pictures that are uploaded or taken with the mobile phone are stored in a subdirectory to where the Google Sheet item is saved with a name like appname_Images/. The value in the spreadsheet is the path to the image file, e.g. appname_Images/J1.Silly.140258.jpg.

It’s best to delete the .ods or .xlsx from Google drive at this stage to avoid confusion: A Google Sheets entry is created in the same directory. Changes made with AppSheet go to this entry, not to the file used to create the app.

Doing the same from within AppSheet (not clear if this is better):

• Navigate to the page listing apps (AppSheet’s main page), click “+ Create”, pick “App” and then “Start with existing data”.
• Give the app a name. For category, I choose “Inspection & Surveys”, and then click “Choose your data”.
• Select an existing Google Sheets entry in your Google Drive account as the database (Google Sheets).
• Trying to use an .ods file as the data source caused AppSheet to hang (wait indefinitely to complete creating the app).

It’s also possible to use an AppSheet database for this purpose, but that seems to be a leftover from the days before AppSheet became part of Google. There’s no reason I’m aware of to prefer AppSheet database, and there’s no obvious way to make a backup of it, nor to restore. Possible with exporting to CSV with Automation, I suppose, but never tried it.

A directory named “appsheet” is created in Google Drives’ home directory. It contains a directory structure with an empty file called “empty.txt”. A subdirectory is created for each AppSheet app, and files generated by the App go into that subdirectory. It’s possible to select a different target for output files, however (see below).

When an app is created, you get an email with an invitation to use it or develop it.

To do after a while

After playing around with an app for a while, it’s a good idea to make a few fixes. Go to the Views section (click on the third icon from the top) and then on the settings icon (a flywheel).

• In Views > General, choose the starting View.
• Also in Views > General, set the Image Upload size to the desired level (High for 1600x900 with my phone, of Full).
• In Offline mode, enable “Store content for offline use”.
• Go do the Data view, choose the main table, and opt out deleting rows (the flywheel icon to the top right for a table’s settings). Delete rows directly on the spreadsheet if needed.
• Change the Default app folder to /theprojectname/appsheet-data or something like that, which should be a valid path in Google Drive. So that the files land somewhere sensible. This is done in the Setting section (flywheel icon to the left), under “Information”.

Views

• The View displays (or allows editing) a subset of column values of a row of a table. In some cases, several rows are listed in one View. Which columns are displayed, how they are displayed, and if they are editable, is what the View’s configuration is about. But no matter how you twist and turn it, a View shows values of columns (possibly along with the column names). If you want to display anything, turn it into a column (possibly a Virtual column, defined only inside AppSheet, see below).
• The relation between the AppSheet’s table, which is used in Views and expressions, is not 1:1 with the table in the spreadsheet: Virtual columns are added, either by AppSheet itself (e.g. _RowNumber and Row ID), or by the user. In the latter case, the value of the Virtual Column is an expression that the user supplies. This can be a constant, for example if the purpose is to create a menu button in a View. The expression can also be a formula, which depends on the value of other columns of the same or other tables. In these expressions, [X] is the value of column X of the same row.
• Clicking / tapping on an displayed item on a View initiates an Action, which often consists of opening a view for a specific row in a table for read-only access or editing. These are system-generated view with names like “main_detail”, “main_form” and “main_inline” for a table named “main”. These Views can be modified like any other view. And a whole lot of other actions can be added and used as the response.
• It’s often difficult to figure out which View appears in response to clicks, as it’s often a system-generated one. Be sure to have the Edit option on (top-right on the development screen). Hover over the relevant area, wait for the pencil icon to appear, click it and pick e.g. “Edit View”.
• If a system-generated view is deleted, it’s created anew after saving, apparently with default settings.
• If a column is added to the spreadsheet’s table, it’s not available for use in the AppSheet immediately. The AppSheet’s table schema needs to be regenerated for this to happen (click the Data icon, click the table, three dots, and then “Regenerate Schema”). Virtual columns are not deleted, despite the scary warning one gets.
• When clicking / tapping an item can result in an detailed view or the possibility to edit, these are system-generated views, that appear at the bottom left in the Views subsection. These Views can be modified.
• Views listed under “Primary Navigation” are accessible directly at the bottom part of the screen. Those under “Menu Navigation” are accessible through the menu. “Reference Views” are invisible to the user, except for when requested from other views, for example in an Action (i.e. a response to clicking / tapping an item).
• There are Format Rules allowing to change colors of items etc depending on boolean expressions. In most cases, they apply to columns and how they are displayed (with a different color, or with a colored icon added). Unfortunately, it’s impossible (as far as I know) to write catch-all rules for several columns, as the expression for activating the rule doesn’t play ball with the [_THIS] expression, which means “this column”. So for example, if you want a rule that marks unfilled columns with a red dot, add (or duplicate) one rule for each and every column, and be sure that the rule for column X doesn’t by mistake change the display format of column Y. It’s just an invitation to make silly bugs.
• The column’s name is displayed above the column’s value in forms and detail Views by default. This can be changed by opening the column’s settings in the Data section, and change the “Display name”. So use concise names in the spreadsheet.
• A column of type Show is useful for text, URLs, images etc, that appear in forms for instructions, setting page breaks etc. These are best added as virtual columns with literal values in the expression. Don’t forget to turn off the “Editable” attribute of this column. It’s quite unfortunate that instruction text appears as an extra column along with the spreadsheet’s data, but that’s the way it’s done.
• To navigate to another view as a result of clicking on a column (possibly a Virtual Column acting as a menu entry):
  • First, an Action needs to be created. Click on the Action icon (looks like electric zap), click on the “+” at the top left, and add an action
  • Pick the table related to the view that this action shall work on, and pick “App: go to another view within this app”.
  • As for the Target, choose the expression LINKTOVIEW([targetview]) (given that “targetview” is the name of the column containing the name of the view to navigate to, as a plain text column).
  • Go to the relevant item in the View, and choose the newly created action under the Behavior section.

There are several types of Views, but the most important for a plain spreadsheet-based app are:

• Form: All columns listed in the “Column order” are shown and editable. The title of each input (text box, drop-down menu etc.) is the name of the column in the database or spreadsheet, unless overridden in the column’s configuration (in the Data section). Unfortunately, a back-swipe on a mobile phone is interpreted as “Cancel”, and there’s no way around this. One must explicitly tap on Save, or else all is info is lost.
• Details: Like Form, but read-only, with an icon at the bottom right for editing the content, which switches to a Form with the same set of fields. Unfortunately, blank fields are not shown unless their Show property is set to the expression TRUE (just checking the checkbox isn’t good enough, it has to be done with an expression).
• Table: As the name implies, like Details, but with the data shown as a table (with rows being rows, columns being columns). Selecting a row brings to a Details view of it. It’s possible to configure which columns appear in the table, possibly only one. So this can be a concise way to display a list of rows.
• Card and Deck: Each row gets a small pane with two or a few selected column values shown. Selecting one brings to a Details view.
• Gallery: Shows the image associated with each row (if such exists) and the value of the column marked as Label.
• Dashboard: A View containing other Views.

Adding a new table

• In Appsheet, click the Data icon (the second icon from the top).
• Click on the “+” (Add new data)
• Navigate to the relevant Google Sheet.
• Navigate to the related table, and add it.

Settings for each column in a table

In the Data section, there are several attributes one should look at in a table’s configuration for a table:

• Name: Must match the name in the spreadsheet’s header (or the column name of a database’s table). The app fails to load otherwise.
• Type: Number, text, image, reference, price, there are a lot of options. The automatic choice is usually correct for the simple textual columns. For more sophisticated input (drop-down menus, images, references etc.) this is the place to configure that.
• Key: This checkbox is checked for one column only, selecting the key column in the database sense. Relevant in particular when using references.
• Label: This checkbox is checked for one column only (plus, possibly, an image), selecting which column appears in several views as the row’s main description.
• Formula: Left blank for columns taken from databases, must contain something for a Virtual Column. When set to something, the value of the column is the expression in this formula.
• SHOW?: As its name implies, it controls if the column should be shown in views. This checkbox should usually be checked: If a column isn’t desired in a view, it should be removed from there. If the column should be displayed in Details Views even when blank, checking this isn’t enough: The Show propery must be set to TRUE as an expression.
• EDITABLE?: Can the value of the column change?
• REQUIRE?: Must the column have a value to allow finishing an edit session containing this column?
• Initial value: The column’s initial value when a new row is created.
• Display name: If left blank, the Name field from above appears above the value of this column in forms etc. Display name overrides this otherwise.
• Description: For internal documentation, and is also used instead of the column name if “Display Name” is left blank.
• Search: Is this column involved in searches?
• Scan, NFC, PII: Can this column accept data from these input methods?(scan means barcode scanning)

Modifying the spreadsheet

It’s possible to modify the spreadsheet even after the app has been created, including adding a column in the middle. The immediate effect is an error (“unable to fetch app definition” on the mobile app, or just an error on the development console). To resolve this, pick the “Data” icon (second from the top”) on the development console, and then click the round arrow (as in “reload”) to regenerate the structure. A scary warning pops up in response to that, but just go ahead.

Note that the connection between AppSheet and the spreadsheet is through the names of the columns as given in the first row, as well as the name of the sheet. These should not be modified (unless the manipulation is intentional).

References etc.

AppSheet definitely supports relational databases. In fact, by choosing names of columns where one is the plural form of the other (“owner” column and “owners” table) the relation is possibly set up automatically. This is however not necessarily a good idea, because the tools choose which column’s value identifies the entire row — and getting this wrong could mess up things later, and it’s difficult to fix it afterwards.

Rather, if a spreadsheet (or virtual) column (in “table A”) is assigned the type “Ref”, it’s also required to assign the table referred to (“table B”). The value of the column selects the row that has a key column equal to it. In the View showing this column, the label column of the referred table is shown. Clicking / tapping on the label leads to a View of that row: its columns’ values as well as other rows referencing it. In order to access a specific column, add a virtual column to Table A with a formula like [the_refcol_in_table_A].[the_col_in_table_B]. It’s not as difficult as is sounds, as the GUI starts suggesting completions when it sees the brackets and the dot.

Note that in Appsheet, a reference actually means the whole row. In many practical situations, only the label column is displayed, but it’s important to remember that conceptually, a Ref column represents the entire row it refers to.

And this brings me to back-reference. When a table B is referenced by a column somewhere in table A, a virtual column is automatically added to table B. This column has the type List and its value is something like REF_ROWS(“table A”, “referring_column”). The arguments are the name of table A and the name of the column with type Ref in that table. So this column lists all rows in table A that refer to the row in table B. Consider deselecting its “SHOW” checkbox in the Data view, if this back and forth is undesired.

Also, note that the back-reference is shown as a list of inline elements on the View of Table B. In order to determine how it appears, configure the system-generated view with name e.g. tableA_Inline (for example, if it’s a table or a deck). Even more important, it allows choosing what happens when one of the elements is clicked: View details (e.g. tableA_Detail view), edit (e.g. tableA_Form) or do something completely different with an Action?

And when references exist, it’s important to get the keys right. It’s possible to choose anything that is guaranteed to be unique, but because references relate to the key, there might be weird side-effects if the value of the key column changes. For example, if we choose a person’s email address as the key, all references to that row are lost if this person changes this address.

What we really want is a solid reference to the row itself. This is best done by adding a dedicated column for that in the table (i.e. in the spreadsheet), and assign it with a unique value when a new row is created: Use UNIQUEID() as this column’s initial value. When AppSheet adds a new row, it will calculate a unique ID and put the value there. Make this column non-editable (possibly also turn off “SHOW” too). In fact, AppSheet does this automatically when I give a column the name “key_id”.

Note that calculating UNIQUEID() in a virtual column is pointless. That’s not a solution for creating a unique ID for a row.

Other insights:

• Allowing upload of multiple images (or adding other items): Create a table consisting of “key_id” (used as key), “ref”, “image” and “weight” columns. The “ref” column is declared as a Ref type to the table in which the images will be stored. Enable “is part of”, which makes it possible to add new items from the main view. So all in all, there is a list of images in the main view, (of table B). Each row in the view points to a row in table A. This makes it possible to add an arbitrary number of items from a view of a row in the main view. Each item becomes a new row in table A, with a reference to table B. “weight” is a number, allowing to control the order when displaying the image. Using “key_id” as the key, and not “image” (which is the name to the file containing the image) makes it possible to show the same image in different Views, even from different tables.
• Drop-down menus with text taken from somewhere else: Prepare a table (a sheet, and import it as a table) with keys as numbers and text (or anything else) as values. Then, on the table where we want the drop-down, set the column’s type to Ref, and point to the said tables as the reference. Select the input type to Buttons or Drop-down. In order to allow adding new possible values, set it to Enum (or EnumList for multiple values), and the Base type to Ref. Choose the said table as the target for the reference. Note however that in the latter method, all already existing keys must be added manually to the column’s definitions, exactly like any Enum type. The only difference is that the keys (possibly numbers) are fed manually, not the text. So if adding new possibilities is required, plain Enum is probably the best way.
• It’s impossible to get a list of options not chosen in an EnumList. It’s possible to write an expression that reduces the chosen elements from a given list with those selected by an EnumList, but that requires keeping this expression and the EnumList’s value in sync. And if one forgets to update the expression after adding it to the EnumList, it’s a nasty bug.

One long row or relational database?

As there are a lot of pieces of information about an apartment, which can be divided into categories and will be handled by different Views, my database manager self was inclined towards a relational database approach. One row with a few columns for each apartment in the “main table”, and then several tables, one for each information category (i.e. View), referenced by the “main table”.

The alternative approach is one table for all information (except for images and other utilities) and to make one long row with all info. It’s up to the Views to select which columns to show: All columns in the original table (or spreadsheet sheet) don’t have to be visible. It’s possible to make several views of the same table. But this means a lot of columns to handle (add virtual columns to that), making it difficult to keep track of the whole thing.

I went for the one long row approach. The main reason was that this approach makes it easier to reorganize the Views if needed. For example, if fields are added over time, and a View becomes too crowded, it’s not a big deal to split it into two Views, if they both refer to the same table anyhow. Or to move a field to another View.

That said, the separate table table approach would definitely have worked: When a new item is added, that means a row in the main table. A new row is created on each separate table by virtue of a menu button (a virtual column with a specified Action). LINKTOFORM() allows opening a form with specific initial values, so the key column can be set in the child table to the parent table’s key column value, ensuring a 1:1 relationship (and prevents duplicate rows).

For reports, the child table is shown as a back-reference to the main tables with a Start/End pair of template tags (maybe there’s a simpler way to do this).

In hindsight, I should have taken the separate table approach, but in a way I didn’t think about at first: There should have been a table with one row for each room, regardless of its type. This would have reduced the number of columns in the main table, and the amount of work and headache is linear to the number of columns. The trick would have been not to show all columns of a room’s table in the form View, but only those suitable for the specific type of room. The rules for which types of rooms exist, and which columns should be shown in the View would be listed in a separate table. Hence the “Show if” rule would look up the column in this rule table, in relation to the room’s type.

Use slices?

The question is how to display a subset of the columns in different Views.

The seemingly natural (and less preferred) way is by using slices, which is a virtual table, calculated on the fly, consisting of a subset of rows and columns as configured. This allows displaying only a subset of rows, columns and actions, filtered with a condition on the data (or just not show all columns). Slices are configured with the “+” icon on each data set (inside the “Data” section) as they are considered some form of table. Alternatively, slices are hinted on the View’s configuration as well.

This would have been the preferred way, had it not been for an annoying caveat: If a new column is added to the database (i.e. sheet), it appears in all slices. This makes slices a no-go for the purpose of selecting columns for a view.

Instead, the columns to display should be selected in the “Column order” part, which can be found in the “View Options” section for each View configuration. Note that “Column order” isn’t present for Views that don’t display columns, e.g. Deck and Gallery.

It makes sense to start from generating an empty view, and copying it for each time a new view is created.

Creating a pdf file from the data of a row

…or for that matter, a CSV / JSON / XML / HTML / XLSX file.

The important point is that this is done with Automation, and that the execution is triggered by an Event, and not as an Action. In other words, clicking something on a View won’t trigger the generation of the report directly. Rather, clicking the entry in the View causes an Action that modifies a row in a table, or to add a row a table, and that, in turn, triggers the Event.

There is no direct connection between the Action and the task it requests — the generation of a file, in this case. It can take 20-30 seconds from the triggering event until the file is created.

One option to request the generation of a file from an app: First, add two columns to the table for which the report is desired: “do_report” and “last_report”. In AppSheet, regenerate the schema if necessary, and set the columns as follows:

• do_report: Type is “Yes/No” (sweet talk for Boolean), turn off “show”, initial value “FALSE”
• last_report: Type is “Text”, and set Initial Value to “Never”. “Editable” must remain enabled, even though it shouldn’t be accessible by forms: Its intention is to be set by the Bot, and this is possible only if the column is editable.
• Add a virtual column named “generate_report”, “Text” type, with expression as follows:

  CONCATENATE("Generate report
  Last report: ", [last_report])

  Note that there is a new line in the middle of the string.

• Create a new Action (Bzzt icon): For “Do this” pick “Data: set the values of some columns in this row”. Pick the “do_report” column and set the expression to TRUE. I’m going to use the Virtual Column defined above as a menu item, so for Position, pick “Inline” and at “Attach to column”, pick “generate_report”. In Behavior, set “Only if this condition is true” NOT([do_report]), and optionally also enable the request for confirmation.
• Add the “generate_report” column to the View (or Views) from which the report should be requested.
• Optionally (and recommended), add a Format rule for generate_report. The condition is [do_report] and if so, the text should be grey (color #888888) and in italics. For “Format these columns and actions” pick generate_report, and possibly also the action created just above. Without this, there is no immediate visual feedback to “pushing the button”.

Now to the part that actually generates the report.

• Go to the Automation section (robot icon) and create a new Bot.
• Click “Configure Event” and give it a name.
• In the pane to the right, the Event source should be “App”. Pick the table from which the Event is triggered.
• Select only “Updates” for Data change type.
• Condition: [do_report]
• Next, define what should be executed — the Process. Add Steps, which is more or less like a line in a computer program: Each of them can perform something, it can check a condition and branch, or wait for the condition to be true. Or call a process. These are steps to define (give each some name, as usual) and configure them on the pane to the right.
  • Type: “Run a data action”. In the right pane, pick “Set row values”, and set “do_report” to FALSE.
  • Type: “Run a task”. Set up the task as follows in the right pane:
    • Pick Create a new file
    • HTTP Content Type: pdf
    • Template: Click Create (or pick an existing template, if you have one from previous sessions). A “View” button appears after this is completed. More on templates in a separate section below.
    • Optional: Change File Folder Path to “/” (with the quotes), File Name Prefix to “report” (once again, with the quotes) and opt-in Disable Timestamp. This way, report.pdf is created at the project’s root directory (not Google Drive’s root directory in most cases). Expressions can be used for these, of course.
  • Type: “Run a data action”. In the right pane, pick “Set row values”, and set last_report to TEXT(NOW(), “DD/MM/YYYY HH:MM:SS”) for British time format.

Note that it’s possible to create a pdf file, but the template file is an HTML file. This makes it much easier to handle the template, and it’s always possible to go back to the HTML file to see what went wrong. The disadvantage with an HTML template is that the file generation fails on the tiniest HTML syntax error. For example, a missing </tr> tag. Or even more ridiculous, a missing </p> tag.

In order to view the generated file by clicking a menu item in a View, create a Virtual Row for this purpose, and assign it with an Action when selected. This action should be “External: Open a file”. The “File” entry is a URL to where the file is stored on Google drive. Obtain this link with the Share feature, so the expression becomes something like “https://drive.google.com/file/d/1d8AWHjLJdK9cyhYGBPnuVJJ0asmLmZVw/view?usp=sharing” (the quotes included). This is not a security risk as one chooses to share only with oneself (when obtaining the link from Google Drive).

If the execution fails due to a malformed template file (something inside the markups didn’t work fine), the app’s View doesn’t always get updated until a restart of the app (or reload the entire page on a web browser).

Actually, the way the whole thing behaves is that the menu button gets greyed out immediately after being pressed, and then gets back to normal after the file has been generated. But the color is determined by do_report’s status, which is turned back to FALSE before the attempt to generate a file. So if the file generation takes time, it may return to normal before the file is updated — but this has never had any practical consequence for me. But why doesn’t it go back to normal when the file generation fails? do_report is FALSE either way.

To view error messages: Go to the “Monitor” section (icon at the left side, one from the bottom), and pick “Launch Log Analyzer” in the “Audit History” part. After some graphs, there’s a table with the last tasks initiated. Where there is an error, click the binocular icon, for details. A JSON text entry appears. The “Errors” key is where the error message is shown. It can take a few minutes before the entry with the error is shown, and before than, there may be entries showing success even if an error has occurred. So wait a few minutes before ruling out a failed run.

Templates

A template is a file in Doc or HTML format. Everything is passed through to the output document, except for markups with the <<expression>> form (or, for an HTML template, &lt;&lt;expression&gt;&gt;. The expression is evaluated exactly in the same way as the value of a Virtual Column, and the markup is substituted with the expression’s result. Hence to obtain a substitution with the value of a column named “thecol”, just insert <<[thecol]>>.

No matter if the output is pdf or HTML, when using HTML templates, be sure that the HTML file has all the classic wrapper tags: <html>, <head>, <body> etc. or else AppSheet ignores all <<something>> (or actually, &lt;&lt;something&gt;&gt;) markups altogether and the output is the template file itself.

There are special kinds of markups:

• Start/End for looping on a list of keys to a table
• If/EndIf markups, allowing a segment to be included or not. Apparently not available with HTML templates, as it’s not listed explicitly, and I failed to use it (so I used the IF() function instead).

The automatically created template is simple and not necessarily very helpful: This template encompasses all real columns (but not the virtual ones), showing the column’s name (not its display name) and their plain values. Its most valuable part is where it shows the use of Start/End (when relevant, i.e. when there are back-references to the displayed table). Note that as shown in the template, the Start and End tags must be inside a <p> block. They can’t be just a floating piece of code, or within another couple of tags (<div> doesn’t work, for example). Otherwise one gets a misleading error message saying the table with name “Start:” can’t be found + a lot of error messages for everything between the two tags.

The template is annoying in particular in relation to Ref types: The actual value, which is the key itself, is shown for references, and not the referenced value, as seen on the app’s Views. It would have been sweet of AppSheet to look up which of the referenced table’s columns is displayed in the View, and show that. But there’s another reason for this: Say, that the Ref column is “choice” and the column to display on the referenced table is “label”. The expression for the value to display is [choice].[label]. But if “choice” happens to be blank, and this expression appears anywhere in the template, the file generation fails. So be sure to assign an initial value to all Ref columns when creating a new row. Plus, possibly make a simple sanity check on the column, just in case:

<<IF(ISBLANK([choice]), "Error!", [choice].[label])>>

Not sure if this is bullet-proof, but it solves the most obvious problem.

I’ve also got the impression that if the result of the expression of an AppSheet markup expression returns anything containing HTML markups (e.g. <b> and </b>) the file generation fails completely.

Notes to self

These are cryptic notes to myself, for my own apps’ housekeeping tasks.

Adding/removing a new item to a checklist

• Update the related checkcnt expression with the new number of items, so that the report says all is done when it really is.

Adding a new column

• Be sure to regenerate the schema of the related table in AppSheet.
• Possibly add (or duplicate) an “Unfilled X” Format rule (e.g. ISBLANK([streetaddress]) ), if it matters that the field is empty. Mark with Cyan dot, and don’t forget to set the affected column correctly.
• Set the SHOW property of the column to the expression TRUE if it’s supposed to appear in the detailed view, even if it’s blank. Just clicking on the “SHOW” checkbox isn’t good enough.
• Set the Display property to something friendly

The striked-out bullets were relevant when I thought about using Detail view as the summary of all. I used a pdf report now, so they’re not relevant.

Adding a new View Form

• There should be a status_* column in relation to this in the spreadsheet. Its setting inside AppSheet: Ref to const_group_status with Buttons (not “part of”), enable Show, Editable, Require, Initial Value is “0″ (with quotes), Display name is “Status”. The four checkboxes to the right are left unchecked.
• In the main table, add a Virtual Column with the same name as the Form View it relates to, e.g. view_base for status_base (so it’s easier to match between them later). Its type is Text, and its value is the relevant menu item’s description.
• Only now, create a new Form view. In Column order, pick Manual, select all, unselect those not required. Be sure to keep the relevant status_* column.
• Go to the “Actions” section (Bzzzt icon) and add a new action (actually, duplicate a similar one). Name it e.g. “Jump to base form”. For a record of “main”, Do this is “App: go to another view within this app”. Set Target to LINKTOROW([key_id], “view_base”) where “view_base” is the name of the View. Set Position to Inline, and Attach to column as the related Virtual Column. Pick a suitable icon too. Unfortunately, there is no catch-all action for navigating.
• Duplicate a couple of “Mark incomplete” format rules, and adapt them to the new View: Change the expression to match the correct status_* column, and also the “Format these columns” to the relevant “view_*” with the same name. And test it. There’s no way around a couple of rules for each View.