I'm looking at moving from Google sheets to S...

[John_Gardner]

I’m looking at moving from Google sheets to SQL.

Are there any How-tos for this? Or does anyone have lots of experience with SQL and AppSheet that could help me out?

Thanks John

[Pim_Sauter]

Hey John, I have a lot of apps running on SQL databases, let me know if you need any help.

Just changing is quite easy:

• Copy your app - Create a database with the same column structure as your Google Sheets - Change the data source for each table to the corresponding database table

[RezaRaoofi]

I am just curious; can you tell me why you decided to move to SQL?

[Grant_Stead]

@RezaRaoofi does it also create the references, and valid criteria?

[RezaRaoofi]

I don’t exactly remember; I have only done it once, but it did copy all tables and the new table names were pretty long names reflecting app name too to prevent duplicate names I believe; @Harry should comment on the details, but it would make sense everything that is technically defined by and part of the create table statement to be copied over too, such as indexes, and references; I mean when you copy from a SQL database to another SQL database.

[Pim_Sauter]

Wow I didn’t know AppSheet would create the database for you

[Harry2]

This article should be helpful:

help.appsheet.com - Copying an existing app to a SQL Database Copying an existing app to a SQL Database help.appsheet.com

[Harry2]

@Pim_Sauter Just to clarify, AppSheet does not create the database for you. The database must be an existing data source in your account. AppSheet will only copy your app tables to the database.

[RezaRaoofi]

@Harry

Wow! Article fresh off the grill!

Nicely written and adequately advised about data types and the fact that further review and adjustments might be needed.

[Grant_Stead]

How does it handle partitions? @Harry

[Harry2]

@Grant_Stead Right now only the first partition of the table will be copied over to the database.

@praveen Since users use partitioning mostly for large data sets, I think we should support copying partitions to databases.

[John_Gardner]

@Pim_Sauter Thanks very much! I’m hoping to make some progress this week and will reach out to you if I need to .

@RezaRaoofi 2 main things: 1. We have a reasonably large database for one app. (For testing, I recently put all the tables into one Workbook, and hit the are 2000000 Google Sheets limit). We’re trying to improve performance and hope this will help. 2. We’re trying to keep more control of our data. We’re looking into entering sensitive data into future apps, and want to have data on one of our servers.

[Harry2]

@John_Gardner Hi John, we currently do not provide any data import/export capability for moving data between providers. However, you can “transfer” your data by cloning your app.

Assuming that you already have an existing app that uses data in your Google spreadsheet, in the app editor, go to Manage > Author, click “Copy App”. In the pop-up, the “Save app to” setting will allow you select the data source where you want to create the cloned app. If you have already added the desired SQL database as a data source, you can select this database and AppSheet will copy the app as well as all of the spreadsheet’s data to the database.

However, please note that since this is a copy operation, AppSheet will create a new table in your database instead of importing data into an existing table.

[John_Gardner]

@Harry Thanks for the info. I’ve done some of this before, but not all at once.

[David_Ackling-J]

@Harry

That is amazing! Are you telling me appsheet will generate the entire schema in SQL Azure from scratch, with the right data types?

[Harry2]

@David_Ackling-Jones Hi David, yes AppSheet will generate the entire table schema. However, whether the data type is “right” or not depends on several things. For instance, for columns of type Number in AppSheet, our server will generate a corresponding SQL column of type INT by default. However, since Number columns can also work with non-integer numbers, the owner of the database might actually want a different SQL column type instead of INT. Most likely, once the schema has been generated, it is highly recommended that you manually check the schema and make the necessary modifications to tailor it to your needs.

[RezaRaoofi]

Also a side note; if your existing source database is large, the copying to destination database could take quite a long time, you might want to export/import data yourself using csv file or something to speed up the process, if necessary.

[Grant_Stead]

@Harry holy appsheet Batman… I had no idea, that appsheet would spin up a corresponding SQL DB based on my Google app! You’re awesome!

[RezaRaoofi]

To be exact your DB must exist; AppSheet will do the rest; copies the tables and data.

[Dan_Oliphant]

Does this work in the opposite direction. Copying an app from a database (my sql) to google sheets?

[Koichi_Tsuji]

It should work for sure.

[Dan_Oliphant]

one app worked like a charm. The other says its taking too long and times out. Should I truncate the tables to make sure it transfers over ?

[Jonathon]

When I have had issues with apps timing out during copying, it has been related to large amount of media files (photos), or transferring such files from one datasource to another (e.g. OneDrive -> Google Drive).

[Dan_Oliphant]

is there a way to push it through? This is from My SQL to google drive