Google Apps Script JDBC Service and Google Cloud SQL

[Laurent Rousset]

Hi,

First of all : Google Cloud SQL is great !

While Google Cloud SQL is a MySQL implementation, is it possible to access an instance of a domain "xxxxx.com" from a Google Apps Script hosted by a SpreadSheet in the same domain ? Using the Google Apps Script JDBC Service.

I tried but it seems not working.

Any help ? It would be a great feature to build richer Google Apps Script application.

Regards

Laurent

[Joe Faith]

Hi Laurent

its a really nice idea, and we're looking into it.

The obstacle you are probably running into is that the spreadsheet is not authorised to access that instance.

j

--
Joe Faith | Product Manager | Google Cloud SQL

[Laurent Rousset]

Hi Joe,

Thanx for the answer and for this product.

Anyway I don't where is the problem.
Because it seems that we can't use the "JDBC Cloud SQL driver" with
Google Apps Script JDBC Service. And when I try to use the "MySQL
one", I don't connect. But I may not have the good connection URL.

Thx in advance.

Regards

Laurent

[geoggle]

I'm definitely interested in this as well.  We have a client who is easily going to exceed the max number of cells in a Google spreadsheet with their Google sites collaboration application. 

So we're looking at going directly into a MySQL database and would prefer to use Google Cloud SQL accessible from Google appscript and using the JDBC services: http://code.google.com/googleapps/appsscript/class_jdbc.html to access the tables.

How can we do this as it appears you're currently imposing a limitation of only Google App Engine instances being able to access Google Cloud SQL instances?  It appears to be simply a matter of Google enabling JDBC access from their Google Apps servers. It's an internal change so shouldn't be a drama. The JDBC services for Appscript are fully MySQL compliant so their shouldn't be any technical issues.

P.S. And yes, we have to use Google sites/appscript as this is imposed by the customer. GAE is not an option (currently!)

Regards,

Geoff

[Joe Faith]

http://groups.google.com/group/google-cloud-sql-discuss/browse_thread/thread/3e083faf69109cc3

[Laurent Rousset]

Sorry Joe but what is your answer.
I don't get it.

Regards

Laurent

On 28 nov, 23:41, Joe Faith <joefa...@google.com> wrote:
> http://groups.google.com/group/google-cloud-sql-discuss/browse_thread...

[Saurabh Gupta]

Integrating Cloud SQL with Apps Script is being considered. In the meantime, you can write an App Engine service as a wrapper that exposes Cloud SQL data to your Scripts.

-saurabh

[geoggle]

Just watching http://www.google.com/events/io/2011/sessions/enterprise-workflow-with-apps-script.html and during the Q/A, Evin Levey comments on the fact that trusted testers on Hosted SQL can use Apps Script to access a database. "He'll hook them up!"
So that was in May 2011. I'm guessing Hosted SQL was the name used for Cloud SQL before it was released officially in November.

Is there a timeframe for 2012 when we might see this?

Geoff

[Zig Mandel]

Its been a long time since this post was commented. This is a very important missing feature. Any word on its status?

[Ken Ashcraft]

On Mon, Mar 12, 2012 at 10:30 PM, Zig Mandel <zig.m...@cloudware360.com.pe> wrote:

Its been a long time since this post was commented. This is a very important missing feature. Any word on its status?

No update, sorry.  As Saurabh suggested earlier in the thread, you can make an app to wrap your Cloud SQL data.

Ken

[Alexander Sokolov]

It would be really a GREAT idea to point out any code samples of such a sql wrapper servlet. If any...  Not to mention placing something like that in tutorials section.

[JohnSuper]

Any update on Cloud SQL access from Google Apps Script JDBC?

In the mean time has anyone written a App Engine Wrapper that has an example code to post?

I don't want to waste time writing a wrapper if Apps Script JDBC access is coming out in a couple of weeks or something.  But if it going to be a while or not in the immediate future it might be worth the time...

I'm assuming  a wrapper entails an apps script URLFetching an app engine app that takes a SQL statement as an incoming parameter, calls a Google Cloud SQL database and outputs a table of results that the  apps script URL fetch receives as return output into a text?   Or am I confused?

John

[Joe Faith]

Hi John

we don't have plans to release an Apps Script JDBC connector in the near future.

J

[Laurent Rousset]

Hi Joe,

In last november, in answser of my first post you said :

"its a really nice idea, and we're looking into it."

Why isn't it so nice from now ?

A lot of people is looking for that feature wich would really Open the "apps script world" for two-way communication with App Engine.

Regards.

Laurent 

[Ken Ashcraft]

On Sat, Jun 16, 2012 at 10:05 AM, Laurent Rousset <laurent...@superchampi.com> wrote:

Hi Joe,

In last november, in answser of my first post you said :

"its a really nice idea, and we're looking into it."

Why isn't it so nice from now ?

It is still a great idea.  We just haven't had time to implement it.

Ken

[Laurent Rousset]

Sorry,

I allways forget that Google is only a small company, with just few developpers.

Too bad

Regards

Laurent

Le lundi 18 juin 2012 18:55:45 UTC+2, Ken Ashcraft a écrit :

[Geoff Newell]

LOL!

--
Geoff Newell
Development and Integration
mob: 07595914378

Appsbroker Consulting Ltd

[SS]

Also waiting here to support for Google Cloud SQL from the Apps Script.
Already Our Spreadsheet is reaching the max. limits.

We need an option to export all these sheets from here to the Cloud SQL?


Any help soon from help?

Cheers,
Som

On Tuesday, June 19, 2012 3:53:52 PM UTC+8, geoggle wrote:

LOL!

[JohnSuper]

Ya, based on previous statements I thing everyone was just sitting back waiting thinking it was being worked on and will be coming down the pipes in a rollout.

Since it is not on the radar I think those, like me, who have just been sitting quietly waiting, need to voice out.   I know that even if there is tons of interest, nothing gets in the pipeline unless the interest is voiced massively. 

It's odd that there is a Google BigQuery connector and access API's for apps script but not for CloudSQL

John

[SOMAYA SARATHBABOU]

Ya, y cnt we all just create a FORM/Link to get the MASSIVE VOTES fr this subject & SEND it to Google, so tht they can take ACTION promptly fr this App Engine Wrapper?
Also, I want them to make them Cloud SQL as a free trial on a User Basis, atleast fr a month.

They really have to improve Standards in terms of marketing New Products fr Entreprise!!

--Som

[Jacob van den Berg]

So  this does not work with Google Cloud SQL and Google Apps Scripts?

https://developers.google.com/apps-script/jdbc 

I have to continue using the spreadsheets??

It is the last missing link for Google Apps Script (UI) and Sites to be complete!  

Cheers,

Jacob 

[Geoff Newell]

Hi Jacob. 

Google announced at IO some big changes for Appscript. 

http://googleappsdeveloper.blogspot.co.uk/2012/06/big-updates-for-apps-script.html?m=1

ScriptDB is the key one. You've now got a scalable, searchable nosql style datastore you can use. Much better than using a spreadsheet. 

Agree it makes sense to connect through to CloudSql. Guess it's not a priority for Google at the moment. 

Geoff. 

[Jacob van den Berg]

Yeah I saw the presentation of saurabh gupta! I'll checknthat out... any ideas where to find examples?
The thing is that it seems that apps script is never an priority within google and isn't it strange that you can connect just about anything from anywhere but not a proper Google product?

Cheers
Jacob

[Jacob van den Berg]

I´ve tried the Scriptdb and it serves very well for the Google apps script. 

It seems much faster, and as long as you stay bellow the 100 mb you´ll be fine.

So I definitly liked this one! 

(still though I'd like to access the sql cloud with jdbc from the Apps script :-) 

[SOMAYA SARATHBABOU]

THnks, Jacob fr the feedback, I m still trying out this product.
Will soon give u a feedback abt this, I shld b having atleast a 50 MB of DB to do in ScriptDB.

I also dnt know y its restricted to 100-200 MB?

BT good told, we still need the direct CLoudSQL connectovity from Apps Script fr sure & of course flexible migration from ScriptDB to CloudSQL!

Cheers,
Som

[DaBe]

For what it's worth. Agreed, we need the direct CLoudSQL connectivity from Google Spreadsheet Query and Apps Script.

[Joe Faith]

I'm very pleased to say that Apps Script now has a JDBC to Cloud SQL

https://developers.google.com/apps-script/jdbc

Hope you can make good use of it, and let us know how you get on.

j

On Thu, Nov 1, 2012 at 12:12 AM, DaBe <rup...@kromco.co.za> wrote:

For what it's worth. Agreed, we need the direct CLoudSQL connectivity from Google Spreadsheet Query and Apps Script.

[Jacob van den Berg]

Thats great news +Joe Faith 

I´ll try to give it a spin the next few days :-) 

[SS]

Hi Joe,
           Any direct tool/method/function to import DATA from ScriptDB/Spreadsheet into Google Cloud SQL?

If so, plz refer me to the lnk!

1000 Thnks,
Som

[Joe Faith]

Hi Son

You could use an Apps Script to gather the data from your spreadsheet then use the new JDBC to insert the values into your Cloud SQL database.

Joe Faith | Google Cloud Product Manager
Sent from my phone, so please excuse my brevity.

[SS]

Sorry Joe,
                       Bt then using JDBC, how will u import the Data directly into GC SQL from the Spreadsheet Data?
Sorry I m a bit lost!

Hw to get the values from the SpreadSheet using JDBC?
I want all my Spreadsheet data to be migrated to GC SQL?

Thnks fr ur help!

--Som

[Joe Faith]

Hi Sarathbabou

The Apps Script Range class includes a getValues() method for getting the values out of a spreadsheet. See

https://developers.google.com/apps-script/class_range#getValues

For example, this will get the values for the range A1:B10 in the active spreadsheet. 

var values = SpreadsheetApp.getActiveSheet().getRange(0, 0, 10, 1).getValues();

You can then create a connection to a Cloud SQL database and insert the values. See https://developers.google.com/apps-script/jdbc

var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://instance_name/database_name");

conn.setAutoCommit(false);

var stmt = conn.prepareStatement("insert into person (lname,fname) values (?,?)");

for (var i = 0; i < 10; i++) {

    // Objects are accessed using 1-based indexing

    stmt.setObject(1, var[i][0]);

    stmt.setObject(2, var[i][1]);

    stmt.addBatch();

}

var res = stmt.executeBatch();

conn.commit();

conn.close();

[Jacob van den Berg]

Any tip/idea for a work around to make GC SQL available for people outside of the project?

[Joe Faith]

This gives some tips on how to use access tokens to authorize external access:

https://developers.google.com/cloud-sql/docs/external

[Jacob van den Berg]

+Joe 

The link you send, that informantion would also account for the jdbc connection from a G Apps Script?

As in the documentation it is written :

"Scripts that execute as the active user will not be able to connect to the database unless the end users are also members of the project. That may not always be possible or desirable, especially for consumer-facing web apps, and we are working on alternative solutions."

Thanks for your attention! 

Jacob 

[Joe Faith]

Sorry, I didn't realize you were talking about allowing access by other users from Apps Script.

Unfortunately the only way to allow them access at the moment is to give them access to the project. See:

https://developers.google.com/cloud-sql/docs/access_control#individuals

We understand its not an ideal solution and are looking at alternatives

J

[Jacob van den Berg]

Okay, that is fine. 

I created a request for this feature in Google Apps Script Issues on Google Code 

http://code.google.com/p/google-apps-script-issues/issues/detail?id=2086

Cheers,

[SS]

+Joe,
               Thnks a lot, we ll look into it!!

--Bb