Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 1: set up the tools
______________________
This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.
Part One here will describe the application and explain the steps needed to configure the Cloud SQL instance and the MySQL development tool.
Part Two will explain the MySQL stored procedures behind the application.
Part Three will show how the front-end Google Apps Script / Google Spreadsheet application and engineering work.
Download the two Google Apps Script component files and scripts for the four MySQL stored procedures from the GitHub repository.
At the March 19, 2015 GDGLA Meetup, I made a presentation covering this article. See the YouTube vid here.
I'd like to say thank you to Mr. Matt Kaufman, the Google Developer Group Los Angeles organizer. At the December 10, 2014 Chromecast Tech Talk, he suggested use of the getScriptProperties object to hold spreadsheet URL and database connection values.
________________
For a recent project, I saw that a web front-end solution over a Google Cloud SQL database would work the best. The customer then mentioned spreadsheets, so the solution I built awhile back, to front-end BigQuery with Google Spreadsheets, came in handy. It turned out that some recursive stored procedures would really help. Don't ask - it's a long story. Fortunately, I have some experience with this, and everything worked out. The whole solution became the core idea for this article.
Except for description of a few important details, this article will focus more on the software I built, and less on how to set up the MySQL / Google Cloud SQL / Google Apps Script machinery. A YouTube video helped me set up Cloud SQL, and I described how to set up Google Apps Script for Google Spreadsheets in my earlier articles here at the BitVectors blog.
After some research and the video, I set up a Cloud SQL project and then I installed MySQL locally to front-end that project. The Developer's Console made it quite clear that it expected me to use MySQL 5.5 - anything past that simply won't work.
I found a Windows MySQL 5.5 download and I proceeded with that installation.
Next, I opened the local MySQL workbench and built a new connection to the Cloud SQL project. I placed the IP address of the Cloud SQL instance found here
in the MySQL workbench connection configuration
as shown here. Then, in the Developers Console ACCESS CONTROL tab
I configured the authorized networks as suggested in the Setup for Google Cloud SQL section here. I made sure I included the IP address for the machine I used for development as one of those authorized networks.
This completed the tool configuration for the application. Part 2 will look at the MySQL stored procedures behind the application.
This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.
Part One here will describe the application and explain the steps needed to configure the Cloud SQL instance and the MySQL development tool.
Part Two will explain the MySQL stored procedures behind the application.
Part Three will show how the front-end Google Apps Script / Google Spreadsheet application and engineering work.
Download the two Google Apps Script component files and scripts for the four MySQL stored procedures from the GitHub repository.
At the March 19, 2015 GDGLA Meetup, I made a presentation covering this article. See the YouTube vid here.
I'd like to say thank you to Mr. Matt Kaufman, the Google Developer Group Los Angeles organizer. At the December 10, 2014 Chromecast Tech Talk, he suggested use of the getScriptProperties object to hold spreadsheet URL and database connection values.
________________
For a recent project, I saw that a web front-end solution over a Google Cloud SQL database would work the best. The customer then mentioned spreadsheets, so the solution I built awhile back, to front-end BigQuery with Google Spreadsheets, came in handy. It turned out that some recursive stored procedures would really help. Don't ask - it's a long story. Fortunately, I have some experience with this, and everything worked out. The whole solution became the core idea for this article.
Except for description of a few important details, this article will focus more on the software I built, and less on how to set up the MySQL / Google Cloud SQL / Google Apps Script machinery. A YouTube video helped me set up Cloud SQL, and I described how to set up Google Apps Script for Google Spreadsheets in my earlier articles here at the BitVectors blog.
After some research and the video, I set up a Cloud SQL project and then I installed MySQL locally to front-end that project. The Developer's Console made it quite clear that it expected me to use MySQL 5.5 - anything past that simply won't work.
 |
| 1. Cloud SQL expects MySQL 5.5 |
Next, I opened the local MySQL workbench and built a new connection to the Cloud SQL project. I placed the IP address of the Cloud SQL instance found here
 |
| 2. The Cloud SQL instance IP address in the Developers Console |
 |
3. In the local MySQL workbench, set up a new connection
and type the Google Cloud SQL project IP address
in the Hostname textbox
|
 |
| 4. In the Developers Console, configure the authorized networks |
This completed the tool configuration for the application. Part 2 will look at the MySQL stored procedures behind the application.