Re: Read Data from MySql and importing to Google S...

atDesign · 04-02-2020 04:40 AM

Hello everyone,

I read a ton of posts but never found a solution that fits to my case.
I have a website that uses a MySql database. I linked one table of this DB to my appsheet application. I want to preserve this DB in order to not creating any problem to my website.
I would like to pull informations from this “read only DB” and put them in a google sheet where I can freely develop the application. I would like to do it automatically to every sync or daily/time dependent.

I just created all the references between my “Order MySql DB” and “Order Google Sheet”.

I was only able to create a “Sync” action that do this for me, but I have to trigger it manually and select every order.

I would like to sync everything every time there’s a new order on my Mysql DB

Is there any way???

Thanks in advance

Aleksi

Unfortunately the automatic update is reading data with only 30minutes intervals.

atDesign

I would be ok! How can I activate it?

Aleksi

Set both options Behavior > Offline/Sync > Delayed sync and Automatic updates as ON.

atDesign

Thanks for your help but that’s not what I want.
I try to explain better.

I have one mysql table that is a read-only table (let’s call it “mysql table”) and that is updated by another software running on a website.

I created a support table on google spreadsheet that we can call “google table”.

My objective is: every time there is a new row in “mysql table” (added externally of appsheet) -> create a new row in “google table” copying the same datas

Can I do it?

WillowMobileSys

Yes you can. I haven’t done this and I understand there is no SQL support in Google sheets. However, you can create Google scripts that use JDBC libraries to make SQL connections to pull data from the database into the Google sheet.

EDITED:  I just re-read you post from above.  It sounds like you already are feeding data from SQL into
 Google?  If so then its just a matter of scripting it so it happens automatically on updates  OR  on a set 
timer within the script.  I believe either are doable but you'll need to research how.

This is outside of AppSheet functionality. The link below can help get you started and then you can go from there.

atDesign

I’m still not feeding Google Sheet from SQL, I thought to do it through appsheet.
Maybe the correct solution is to do it externally? I’m going to study the link you posted.

@Aleksi really got the point, the easiest thing for me would be a trigger that copies every new record in MySQL to a spreadsheet automatically!

WillowMobileSys

Yes understood - copy to Google sheet automatically once row inserted into SQL.

AppSheet does have the capability of connecting to your MySQL database but it is expensive to do that.

I assume you are looking for ways to avoid the db connection costs?

If so and if you are looking for a way to update G-sheet from your MySQL database, you WILL have to rely on external services to handle that for you.

Aleksi

John has a point… you need to use for example Scripting.

FREDY_ORTEGON

creo que te puedo ayudar haciendo un robot.

Aleksi

So, you want to have a trigger that copies a new record from your MySQL to a spreadsheet automatically?

atDesign

exactly!

atDesign

The scripting thing is quite difficult, moreover I don’t want a complete copy every time, I’d like an update “only new rows” .

I think I’m gonna do like this:
2 views in the app

First View is the MySql table view. If I tap on an order, I activate an action that “adds a new row in another table using the values of this row”.
Then set the columns that I need in the other table.
Second view is the spreadsheet table view, where I can work without affecting the original Mysql table.

Thank you for helping me coming to this solution.
If something isn’t clear and you are interested in it I can explain it better.

Thanks

Read Data from MySql and importing to Google Spreadsheet daily