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. 

[Koichi_Tsuji]

@ShirleyN 

As soon as we turn on option to disable (ban) AppSheet database from root account setting (see figure below), all the app creator under the account no longer able to add new table to the app. Obviously a critical bug, please fix this problem thanks.

On table setting, hiting add table action will not prompt the window after we turn this option on.]

@takuya_miyai 

[takuya_miyai]

@ShirleyN 

Help me!😅

@Koichi_Tsuji 

[ShirleyN]

Thanks for reporting this, looking into it now. 

[macastan]

This should be fixed now. Please let us know otherwise.

[Koichi_Tsuji]

@ShirleyN @macastan 

Thanks for addressing this quickly.

We tested again by enabling option and we confirmed the new table can be added, thanks.

@takuya_miyai 

@Aurelien 

[Aurelien]

@takuya_miyai and @Koichi_Tsuji  I filed a ticked yesterday, and I was suggested that it was a bug related to my browser's cache.

Good catch !

@ShirleyN if that helps into solving this issue, I have various session under Google Chrome browser, and they are all affected with this issue despite having the disable option under only one session (the one with that belongs to an Enterprise Plan)

[dalekirkwood]

When will this be public? any ideas on pricing/limitations?

[Chern]

I set up an AppSheet Database with several tables and all the columns set up. I then linked my App to an AppSheet Database.

Two of the columns ("Workday" and "NWDIntl") were initially configured to be enum data types in the database  but I later on realized I could configure them as Lookup within the AppSheet Database and link the data to another table without having to use App Formula.

When I tried to change the datatype in the database from "Text" to "Lookup", the app generated an additional column "NWDIntl(1)". I have since tried to remove the columns from the database and regen the table structure in app but the "NWDIntl(1)" column still exists. I have also tried deleting the entire table in the app and readding it but to no avail. I have also tried clearing the cookies and cache from my browser.

Look forward to any advice on how to remedy this thanks!

#AppSheetDatabase #Database

[Jedigran]

I'm new to AppSheet and the first contact I had with the tool was the Database Tool. To be honest, being able to create a database as I was used to in Ms Access (did I say MS ...?) was my reason to stay. It has been extremely easy for me to build the data scheme . I know I still need to learn the "Columns" magic, but for now it's been really rewarding...

Question...any plan to create "views", so we can mix and match several tables in an easy way?

ONLY issue I had (that i solved with the help of Google support) was that the table (at no request by user as far as I'm aware of) get hidden. Now I know I can use the +Add Table to "reload" them, but that mix (hiding tables unsolicited + putting the re-load inside the addition (new table) option) are kind of not really intuitive to me... But that's a small thing compared to the speed is provides.!

Congrats!

[TedM]

---

@TedM wrote:

In calendar view when I change the Title View column it doesn't stick past 24hrs and reverts to its original column

---

Ignore me regarding this 😫

I simply got the calendar looking how I want it and then saved it as a new view!! 🙄

[TedM]

---

@TedM wrote:

Should the map layout have a Configure button? 
I have a column called Geolocation that contains Lat/Long but nothing appears in the map view - does the column need to be called Location or should we be able to select the column to use?

---

Map is now working also 👍
(although it would be nice it it auto-zoomed in to the country where the pins are 😉)

Although I'm still being asked for confirmation when I click Cancel in the Edit Row and Record views even when nothing has been changed.

[neovinu]

Hello,

How to use [_RowNumber]+1 function in new database? [_RowNumber] is not recognized by the new database.

[takuya_miyai]

Hi @ShirleyN 
It seems that the AppSheet database can be shared by any number of people.

Will this be the same specification after GA?
If it is a Database of the AppSheet product family, I think it would be desirable that users who are sharing in the AppSheet app automatically share it.

 

 

[ShirleyN]

@takuya_miyai Sorry do you mean the database should be shared with the same people as the app? or people who are sharing their app should also have an option to share the database along with it?

The database can only be shared by those who have edit access (including the owner) but there isn't a limit to the number of people you can share it to. 

[takuya_miyai]

@ShirleyN 

For example, if we want the timeline view of the AppSheet Database to be accessible to app users, it will be shared as a user of the AppSheet Database.
Therefore, I pointed out the point of user sharing.

However, I actually do not consider the above reasons to be very important.
Essentially, I don't know whether the AppSheet Team thinks of the AppSheet Database as just a data source or as a new platform.

For example, if we have a user who needs a Kanban View app.
They first purchase only 1 Lic of AppSheet Core and create a Kanban app on the AppSheet Database.
Then, they share it with 100 users in their company to use the app.
Is AppSheet going to allow this kind of usage?

@Koichi_Tsuji 

[ShirleyN]

Hi @takuya_miyai we're still thinking of Appsheet databases as a data source for apps and we'll be working on making the transition from database to app building easier throughout GA. The sharing use case you brought up is a great one . However, one of the main benefits of sharing the app and not the entire database is using expressions and security filters to selectively show/hide data. This is one of the most common feature requests from Tables Beta users. Another big benefit is charts and forms which we can only be accomplished through sharing an app. 

[zito]

FWIW, the expectation is that database users will count as AppSheet users- so whatever licensing applies to AppSheet apps will apply to database users as well.  

[takuya_miyai]

Thanks @ShirleyN @zito 

When I first used AppSheet Database, I thought it was only part of AppSheet's platform functionality.
However, my confusion deepened the moment a timeline layout appeared in the AppSheet Database that did not exist in AppSheet's View type.The direction of the AppSheet Database in the AppSheet Team was lost on me.

However, your replies have helped me to sort out my understanding. The first question about sharing is not a major issue. It may be inappropriate here as this is not a support chat, but you may close it.

I understand that AppSheet Database is a product with Google Tables in its background.
Therefore, I understand that there are cases where it is difficult to smoothly incorporate it into AppSheet milestones.

However, as an App Creator, I would have liked to see a timeline added to the View type of the AppSheet, rather than adding a timeline layout to the AppSheet Database.
That way, as Shirley pointed out, it would be easy to implement a View that takes user permissions into account, such as my timeline.

I continue to look forward to future enhancements to AppSheet and AppSheet Database.🤗

[Aurelien]

Hi @ShirleyN 

Here is a little glitch that I would like to bring to your attention.

Context

The company is running under Microsoft/AzureAD, and use Cloud Identity for some use cases.

- Team policy: no AppSheet Database

- At a team level (Settings), AppSheet Database are disabled.

Situation

I get an app shared from external user, and this app uses an AppSheet Database.

1) copied from my O365 account

This throws an error from my team policy ==> app cannot work, and I see this when I go in the app editor:

2) App copied from my Google Account

This throws NO error from my Policy, and I see this:

3) I tried one more scenario: the app copied from my Google account, that I share to my O365 account...and the copy works.

In my humble opinion, there is an inconsistency.

I should be either:

- NOT able to copy an AppSheet database based (too many "base" words here !) from my O365 or Google account, because my team policy forbids me so

- or, being able to copy an AppSheet database based from both accounts.

What is your opinion about these scenarios ?

Many thanks in advance for your consideration !

[Gustavo_Eduardo]

I am going to make a single comment [THIS COMMENT] and on this comment I will make all my opinions regarding the new feature of Appsheet, The Apsheet DataBase. I am grateful for all that they have given us with this functionality so I think that on my part they at least deserve a round trip regarding the use.

1. New columns that cannot be deleted are created when we make some changes in the Appsheet database, these columns are clones of the columns to which we changed their nature. This occurs, among other possible causes, when we had an application made in google sheets with a reference column and then from the appsheet database we added the table to which it refers. Right at that moment the column breaks and disappears from the database but not from the editor.

2. Inconsistency with the order of columns. There are times when the new columns created from the database appear in the editor in the position in which they were created, but there are times when they are seen at the end, next to the virtual columns.

Although there are columns that are removed within the database, this does not happen in the editor (at least visibly). If we go into the history of deleted columns, we see that there are many inside. If the deleted column history behaves like an "internal Appsheet recycle bin", you should have two possibilities (besides saving the columns for a week as stated there).
A. Delete permanently: I see that this option not exist and it would be ideal to delete it permanently and not have to wait for it to be deleted only after a week.
B. That the columns that have gone to the recycle bin are not visible in the editor (especially when it comes to lookup or ref type columns)

3. The Ref type columns are not enabled to be label fields and for me that is necessary (at least in the App that I am developing).

[Ricky_Chua]

I might have spotted a bug while using AppSheet DataBase. When pasting cells from Sheets to Database, single cells that have "line breaks" in them do not transfer properly. Database would parse the line break and paste anything that comes after into a new row. 

I realized this was happening when I copied 100 rows but DataBase prompted that the end result would have 120+ rows.

I can confirm that using a Sheets function such as SUBSTITUTE(cell,CHAR(10),", ") to remove all line-breaks will work. But for cells that are meant as Long Text and would benefit from the line-break, this is an undesirable trade-off.

[alphacp]

The issue I have experienced the most with the new Appsheet database is the fact that a VC cannot be the label field. I guess I did not realize just how frequently I used a VC as a label, but it really is becoming a problem for me.

Does this restriction really have to be there?

[ShirleyN]

Hey everyone, we have enabled Sheets import into a database from the My Apps page (screenshot added to original post). Currently the import limit is 5 tabs. More details and other ways to transfer data here: https://support.google.com/appsheet/answer/12635312?hl=en&ref_topic=12632222 . Thank you everyone for submitting feedback and helping us catch bugs!

[Ricky_Chua]

I imported my sheet into the Database. As my sheet has more than 5 tables, I was expecting an option to select the 5 tables I want to import. That option did not appear, and Database attempted to import the entire sheet.

The end result were multiple copies of the first 2 tables, eg: Table A, Table A (1), Table A (2), Table B, Table B (1).

Interestingly, with reference to earlier comments that mentioned only the column headers were imported but not the data,  some of the duplicated Tables in Database actually contained Data and not just headers. 

Unfortunately though, as it stands, the import function is not usable for me to test Database vs Sheets on my existing app.

[PeterW]

I have scrolled and read most of the replies, but did not come across a similar question:

can the Database be populated via Google Forms.

Most of my Apps are populated via Forms.

[MikeJones]

I have seen the answer in this thread somewhere and it was a "no" 

[dbaum]

As you're apparently already doing, Google Forms can be used directly as a data source for an AppSheet app. If you need that data replicated to a different data source, such as an AppSheet database, you could create automations within the app to do that (e.g., Example: Copy newly added records - AppSheet Help).

[turbineBMW]

Hi! As someone that works at a company that has a death grip on Sharepoint, I'm REALLY excited to use this feature to save all my generated documents on Sharepoint but keep my data on a more efficient system. I wanted to migrate immediately for my beta copies, but there appears to be a 100 column limit with everything I try. Is that a preview limitation or will that be permanent when AppSheet Database goes live?

[DMiles]

I've run into what seems to be an error - I can't use Virtual Columns as row labels when working from a database. I've been experimenting with converting an app from a sheets base to a database, but the need to have the row label be [Full name] which is a concatenated field in a virtual column is stopping me from moving forward.

[MikeJones]

I am using the appsheet database rather than a google sheet.  I have a bookings table that has  a "Booked By" column that references the Contacts table in the database.   Form views  on the Bookings table that include this column work fine ... but if I use a calendar view and set the description field to the "Booked By" column it displays the ID value rather than the contacts name.

[neovinu]

Error in Action: App: Copy this row and edit the copy

This Action is not copying entire row while trigger, in other word it is copying only the columns which is visible in current view.

Key and Unique ID columns are blank in copy

[DanteSparda]

The best feature in so many time. Will test it out. Thanks

[dbaum]

@ShirleyN, I recommend adding a refresh button to the database UI--maybe even at the table level. Ideally, with an indicator that the currently displayed data is out-of-date and the table needs to be refreshed. Right now, using the browser refresh is the only way to check whether expected data changes have been made. This is turning out to be common during the app development process.

[ShirleyN]

Thanks @dbaum can you share which actions (copy, paste, edits or deletes from the database UI or app) that are slow to appear motivating a page refresh? 

[dbaum]

I'll try to pay attention and report back, @ShirleyN. Much of my relevant work over the last few days has been invoking the AppSheet API from outside of AppSheet; so, my recent observations may have been in that context.

[dbaum]

@ShirleyN, I continue to work with a scenario where an external script invokes the AppSheet API to add rows to a table in my app for which the data source is an AppSheet database. As I initially developed the functionality I was adding up to ~200 rows at a time, and it reliably worked fine. Now, I'm testing with adding 1000+ rows at a time (while still well within the preview period's 10K row limit for a table), and I invariably encounter errors, which differ slightly depending on how many rows I'm adding. As far as I know how to interpret the audit log, nothing suggests it's a timeout issue. There certainly could be mistakes in my own work, but my test cases using the exact same script with only hundreds of rows continue to work fine. Also, in other apps I've long successfully used the AppSheet API to add thousands of rows at a time to app tables using both spreadsheet and database (although not AppSheet Database) data sources.

So, I'm posting here in case it's helpful as you comb out the kinks in this preview feature. Following are some error messages copied from the app audit log. If it would be of use to give you the app ID, let me know.

Test cases with about 1K-5K rows

"Errors": "Unable to find the specified row from AppSheet database"

"Exception": "DiagnosticFrameworkWrapper.CustomerFacingException: Unable to find the specified row from AppSheet database\r\n at DatabaseProviders.TablesDBProvider.GetRows(Context context, SqlTable table, RowKeys rowKeys)\r\n at DatabaseProviders.SqlTable.FetchRows(Context context, RowKeys rowKeys)\r\n at ProviderBase.RowKeys.Fetch(Context context)\r\n at ProviderBase.ExternalApiProviderData.AppendRows(Context context, Op op, PhysicalSchema physicalSchema, ImmutableArray`1 colTypes, List`1 keyPositions, Boolean writeFullTable, Boolean tryUpsert)\r\n at ProviderBase.ExternalApiProviderData.AppendRows(Context context, Op op, PhysicalSchema physicalSchema, ImmutableArray`1 colTypes, List`1 keyPositions, Boolean tryUpsert)\r\n at ProviderBase.APIBasedProvider.AppendTableRows(Context context, Op op, String name, Int32 ownerId, String userToken, PhysicalSchema physicalSchema, DataFormat fileFormat, ImmutableArray`1 colTypes, List`1 keyPositions)\r\n at Nirvana.Data.DataLayer.AppendTableRows(Context context, Op op, Int32 ownerId, AppTable appTable, ImmutableArray`1 colTypes, List`1 keyPositions)\r\n at Nirvana.Internal.InternalAPI.AddTableRowInternal(Context context, Op op, Int32 userId, Int32 executeAs, ChangeEventWorkflowRuleEvaluator changeEventWorkflowRuleEvaluator)\r\n at Nirvana.Internal.InternalAPI.PerformOperation(Context context, Op op, Int32 userId, String appTemplateVersion, UpdateModeEnum requiredUpdateMode, RowOperation rowOperation)\r\n at Nirvana.Internal.InternalAPI.AddTableRow(Context context, Op op, Int32 userId, String appTemplateVersion)\r\n at Nirvana.Internal.RestApiHandler.RestAdd(Context context, IRestApiOp restApiOp)"

"StatusCode": "InternalServerError"

Test cases with about 5K-9K rows

"ErrorCode": 1

"ErrorMessage": "SyntaxError: Unexpected end of JSON input"

"Errors": "Failed to get rows due to: 'withheld' is not a valid table column name.."

[ssaezgarcia]

Hello, it is a great option to have a database engine available to develop the applications in appsheet, thank you very much for that.
Will it be possible in the future to be able to consult the data with SQL language? I ask this because for data engineers it is very important to be able to consult the data model to find relationships and conclusions that allow better decisions to be made.

Thank you very much for the help

[Gustavo_Eduardo]

Hi Shirley, I need a button "Add table from google sheet" in to the Appsheet Database!!

Since the quintessential databases that Appsheet has had in mind throughout its life are google sheets tables, there should be the ability to add a googlesheet table to the appsheet database. Often when I copy my app to the appsheet database, there are tables that are not copied and I have to include them manually. This takes me a long time. I think it would be a good idea to add an option "add table from google sheet"

Regards

[TN9]

I'm having this issue while trying to add a row to AS database as an user. Could someone please help resolve? Thanks!

Error: Unable to add row to table 'Job1'. → Invalid request provided to AppSheet database

[andyBKK]

Me too, and I cannot resolve this issue. There appears to be no reason why this is happening on mobile but not in the app development page, which syncs fine.