Google Apps Script to Export Spreadsheets to mySQL

Question

Ahoy!

How can I export all of the Google Spreadsheet's data to a MySQL, I have the basics of an export script but All of my Spreadsheets have 1,500+ rows and there is 41 of them, next my question is Can I execute these scripts on all of the Spreadsheet Files at once, perhaps in a folder? because I don't fancy trawling through all 41 and assigning a script to each.

Thanks in advance :)

Answer 1

How can I export all of the Google Spreadsheet's data to a MySQL

There are several ways you can do this. Which one to use depends on how your MySQL instance is configured.

If your MySQL instance is a closed local network-only instance, then you can't connect to it from outside your local network, so google apps script will not be able to connect to it. In this case your only option is to export your google spreadsheets data as CSV files (i.e. using File->Download as->Comma-separated values menu), then import those into your MySQL db table. See Load Data Infile MySQL statement syntax for details.

If your MySQL instance is a public-facing instance, accessible from outside your local network, you could use Google Apps Script JDBC Service to connect to your MySQL instance and insert/update data from your google sheets. Please read the Setup for other databases section of JDBC guide for details on setting up your database for connection from Google Apps Script.

Can I execute these scripts on all of the Spreadsheet Files at once, perhaps in a folder?

In the second case (public-facing MySQL instance) you can definitely automate this with a bit of scripting. You can have one script that loops through all spreadsheets in a given folder (or a list of spreadsheet ids, if they are in different folders) and inserts data from each into your MySQL database. The Drive Service and Spreadsheet Service will be your friends here. However, keep in mind that maximum execution time for a google script is 10(?) minutes, so if your sheets contain a lot of data and/or your connection to your db instance is slow, such script may run into a timeout. You may have to implement some back-off/resume functionality in your script so it knows where it finished previous run and picks up from there on next run.