Introducing new AppSheet database feature in public preview

[ShirleyN]

Please see updated GA announcement --> here

Hi everyone! 

Over the past year, we’ve been conducting user research with app creators, end users and some of you to better understand the app creation experience. Through this research we’ve uncovered challenges with the existing external data sources and noticed a gap in datastore options for citizen developers. With Sheets being the most common connected source for AppSheet apps, we noticed frustrations around:

• Formatting a spreadsheet for app creation can be time consuming
• Changes to the data schema after initial app creation are challenging and can easily break apps
• Unstructured data leaves room for human error in data entry 
• Scalability and sync speed starts to deteriorate as Sheet size increases 

With these challenges in mind, we set out to build a native database for citizen developers to easily and securely manage their data. We believe this will also improve the experience for app creators. 

Today, I’m happy to announce the release of the new AppSheet database feature for public preview! During public preview, access to AppSheet databases is enabled by default for everyone but it will not affect existing apps unless you explicitly add a blank table or connect an AppSheet database inside the AppSheet editor. Use of this public preview feature will be free to everyone but limited to 10k rows per table and 20 tables per database. Note that these limits will change for our public launch. 

To get started, you can create a blank database from the My Apps page. If you’d like to start building an app from scratch, there is also a new Blank app option. This blank app will create a new AppSheet database to use as the data source. 

Within the database editor, you can set the same column types as in the AppSheet app editor for your data. 

After that, you can create an app directly  from the database. This will create an app on the current table only. If you have references to other  tables, you will need to add them using the AppSheet app editor. This is something we’re working on improving. 

Since this feature is in public preview, we’re still making improvements to it and appreciate your patience with issues you may face. Some features that are coming include a smoother import/export of data, database recovery, and email notifications after sharing a database. 

We will also be updating our support documentation to include this new feature. For those who would like to disable access to this feature, see the Disable AppSheet databases policy. 

Thank you and happy app building, 

Shirley

Dec 8 Update: Sheets import is now enabled on the My Apps page! More details on import and other ways to create a database here.

Feb 1 Update: Column types set inside the editor were getting reset every users regenerated schema twice. This has been fixed.

Feb 14 Update:  Virtual columns can now be set as labels inside the AppSheet app editor.

Apr 26 Update:  Deleted tables can now be restored through the new history experience inside the database. Please see support article here.

May 3 Update: You can now share a database with an entire AppSheet team as a shared datasource. AppSheet teams are only available to AppSheet enterprise users. 

May 24 Update: Audit logs for AppSheet databases are now available for admins and database editors. You can also restore deleted databases. More info here. 

[ShirleyN]

Hello @Aku , when you copy the values, do you see a toast saying "values copied" ? Are you pasting the dates into another date column? 

[Aku]

Hello Shirley

I don't see "values copied". I'm not pasting anything, I'm just using the embbed "Copy table" feature with "Copy table rows" option checked. All the other columns are copied with their datas. Date type columns are well created, but datas are not duplicated in.

[ShirleyN]

I see, thanks. I'm not able to repro on my end but does the data appear after refreshing the page? 

[Aku]

It still the same after refreshing. But maybe this will help: I just found that my source table is configured with a french locale and the destination one, created by the proces, is configured with an english one.

[ShirleyN]

I see, thanks for the additional context. We'll continue investigating. 

[Marlon]

When you enable the option for a column to refer to another AppSheet database, currently it only allows you to refer to another table within the same database.

[Erik_Ferm]

I am just starting to play with databases and it looks promising. As others here, I am primarily interested in the potential performance improvement compared to google sheets.

Apart from improving performance, I think that having a the option of using a proper database but with a simple quick interface is very attractive. It saves time compared to using a "normal" SQL database.

The area I find a bit confusing is that parameters can be entered both the database tool and in the appsheet editor. It doesn't seem that changes in the appsheet editor are fed back to the database editor.  

1: If I create a reference column in the database, assign a table to it and then change that table to another table (stored in sheets), the database editor will say one thing and the appsheet editor another. This is not a big deal but it can certainly create confusion

2. If assign a column as a label in the database editor (which I think I have to do) and then create a virtual column and assign that as the label in the appsheet editor, the original label column becomes the label again when I save the app definition. Unless I am missing something, this means that you can't use a virtual columns as a label for a table in an appsheet database which would be a shame.  

[ShirleyN]

Hi @Erik_Ferm thank you for trying the feature and providing feedback.

For 1) we should be preserving your column type and  reference selection on the appsheet editor side, even after schema regeneration. Were there any other errors when you regenerated the schema? Was this database imported from Sheets? 
For 2) There are two known issues here that we are working on addressing: virtual columns cannot be set as labels. Label selection is overriden to match what's in the database. In the future, you will be able to do both. 

[Erik_Ferm]

@ShirleyN

Hi Shirley,

Sorry for being slow in replying.

The reference column problem seems to have been solve, thanks!

For the labels, I can change the label in the appsheet editor and the selection is kept but if I set a virtual column as the label column, I get an error message telling me I can't do that.

[ShirleyN]

@Erik_Ferm No worries, thanks we will be working on allowing virtual columns to be set as labels. 

[Erik_Ferm]

@ShirleyN 

I just had a look now and the ref column behaviour has gone back to not working. 

So if I re-point a ref column to a table stored in a google sheet and then regenerate the structure, the ref column goes back to pointing at the database table again. 

[ShirleyN]

Hi @Erik_Ferm thanks for reporting this again, we found in some circumstances column types were indeed getting reset. We're actively working on fixing this. 

[ShirleyN]

Hi @Erik_Ferm small update, virtual columns can now be set as labels inside the app editor. Thanks. 

[Erik_Ferm]

As mentioned, I think the idea behind databases makes a lot of sense.

I am not sure if I am meant to be able to do this but I am adding a new table which is a table in an appsheet database to an existing app where the other tables are in google sheets.

A couple of columns in the database table are ref columns. In the database editor, I can't see the other tables in the app so I can't point the reference to the right table and I therefore pointed it at the example table in the appsheet database.

In the appsheet editor, I can then change the ref column in the database table to point to the correct google sheet table.

However, when I regenerate the column structure in the database table, the ref columns are reset to the value from the database editor.

If this the intended behaviour?

If it is, it effectively means you can't mix google sheet data sources and appsheet databases. That would be a real shame.

[V-A]

I copied an app and its database and am trying to connect them.  I then changed the name of the app and the database's name and table names, due to a problem I mention here regarding de-linking the old and new tables.

I now get an error reported in AppSheet, "The given key was not present in the dictionary."  The key fields for each table have remained the same as the original database.

The app also produces an error, "Unable to fetch app definition. Error: Data table 'X' is not accessible due to: Requested item from AppSheet database not found or data may have been deleted."  The data table 'X' appears in AppSheet.

Can someone help me troubleshoot this error?

[Gustavo_Eduardo]

Hello, I get this error when copying my app from google sheet to AppsheetDatabase

Error copying table to google_tables (Google Tables):

{"método":"POST","url":"/api/template/copyAppTable","fuente":{"src":"fetch","response": {},"text":"{\"Mensaje\":\"Ha ocurrido un error.\"}","json":{"Mensaje":"Ha ocurrido un error."}},"status": 500,"statusText":"","responseText":"{\"Mensaje\":\"Ha ocurrido un error.\"}","responseJSON":{"Mensaje":"Ha ocurrido un error."} ,"nombre":"r"}

Does anyone know what it is about?

[Alex_Kemsley]

Hi. 

What is the actual database behind the Ui. 

Will there be API or query language access. 

Thanks. 

Alex

[dbaum]

ICYMI: The AppSheet API can be used to manage an app's data regardless of the app's data source.

[williangyn]

Hello @ShirleyN 

I built an Appsheet API, which through the browser includes images in the Appsheet application.

On Android phones it's working perfectly, on iPhone phones it doesn't work.

Can you tell me why?

ERROR 500

Ple

ase...

 

Test our application here:

catiasantos.adn3.com.br

Order number: 96a6452e

[ShirleyN]

Hi there, sounds like this could be related to https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Error-taking-photos-directly-in-app-after-updat... ? 

[dbaum]

It can be a hassle that the system-generated "Row ID" column includes a space character in the column name. For app creators who follow a convention of excluding spaces in column names because it can make aspects of app editing more efficient, it's especially annoying that a column so commonly used in expressions includes a space. I imagine that for app creators who don't follow that convention, it's probably not problematic for this default column's name to not include a space--e.g., "RowID", "Row_ID", etc. I recommend changing this system-assigned column name.

[sckaruna]

Is the AppSheet database comparable to a SQL database in terms of performance? Can a AppSheet database handle more than 100,000 records without causing performance issues to the app? Thank you.

[12seth12]

Is there a limit to 100 columns? I've gotten an error after trying to add that many columns

[Beta7]

Is there a version that allows more than 20 tables per database?

[ShirleyN]

Hi there, 20 tables is currently the limit, but you can connect multiple databases to the same app if you run out of tables in one database. 

[Naftuli_Singer]

I love the AppSheet database, when will this be released officially, and will we be able to have more than 10K rows per table?

Also, maybe we can use this opportunity to add some printing functions to Appsheet. I am building a pretty complex application with multiple users but printing is holding me up. What I used to do (using Google Sheets as a data resource) is set up some external sheets using IMPORTRANGE in Google Sheets and make them a user-friendly printable versions.

[Marc_Dillon]

The built-in [Row ID] column is not available to be selected as the column to lookup records on inside of a "call a process" type Bot Step.

[ShirleyN]

Hi @Marc_Dillon , can you elaborate on your usecase? What's included in your process and what steps are configured? Thanks. 

[Marc_Dillon]

1. I'm utilizing the built-in [Row ID] column as the key for a table.
2. I have a Process defined on that table.
3. I want to use a "call a process" Step in another Bot to call the already-defined Process on an existing record in the first table.
4. This seemingly is not a possible thing to do, because the [Row ID] column is not an available lookup selection from the call-a-process Step's config. (see screenshot).
5. In order to use call-a-process on an Appsheet-Database, it seems I would have to use a different column as the key (?) , which seems duplicative and counter-intuitive.

   @ShirleyN 

[Marc_Dillon]

Hi @ShirleyN , just wanted to bring this to your attention again. Seems to be a pretty severe oversight to me. There seems to be no way to "call a process" on an appsheet-db table where [Row ID] is being used as the key.

[ShirleyN]

Hey @Marc_Dillon sorry forgot to update the thread. We are actively working on addressing this. 

[Sully]

Hello, Sorry if this has been covered, but I've been unable to find an answer. Is there a way to export the entire contents of a database? I know I could export to CSV from individual views, but I would like to make sure I had the means to make hard backups of everything.

[HP1]

This is a workaround, but you can create an action within the app to do a CSV export.

[Nick_Haschka]

That works. You can also go into table settings in AppSheet app builder and copy to a new data source and it will drop your AppSheet database into sheets.

[HP1]

The database UI lets you select multiple rows, but not delete those rows as a group.  Frequently during the development phase you're going to want to delete a bunch of rows (or all rows) at once.

[HP1]

This is a great addition, and I love the instant updating!  That solves a lot of problems for my application.  Any idea of when it might be released for real?  Hard to know if it's safe to develop against this feature.

[andyBKK]

Hi,

I am facing a problem syncing from mobile, the dreaded "Unable to add row to table 'XXX' --invalid request provided to Appsheet database. I have gone through every possible solution. The app works fine in the development environment but wont sync on iphone. Do I need to abandon the database or is this a mobile cache type issue?

[Tommaeh]

I am experiencing the same error for ONE of my tables in the database, but on all devices.

[Tommaeh]

I might have just solved my issue.

I had created and assigned a ref-column directly in the database.
When I deleted the column, re-created it but configured it in Appsheet (instead of the database), it fixed the problem.

Maybe you have a similar situation...

[Nick_Haschka]

I’ve been getting this same issue. Seems to be related to a ref field I changed. Had to delete the ref field and create as a text and build my validation inside the app builder.

separately but related, Not being able to load ref fields in bulk in ASDB is a really tough barrier to adoption for us though.