0

As the thread title asks, I would like to know if within Google Apps Script, it's possible to invoke the Structured Query in Spreadsheets API. Or is that not doable? The documentation has examples for Protocol, Java and .NET but none for GAS. If it is possible, how does one do it?

Thank you.

sabansm
  • 115
  • 1
  • 4
  • 14
  • Look at the code in [this answer][1] and modify it to suit your needs [1]: http://stackoverflow.com/questions/11063749/how-do-i-get-an-access-token-using-urlfetchapp-with-gas – Srik Jun 24 '12 at 17:04
  • Hi Srik, Thanks for the reply. As I'm still a GAS newbie, I have to absorb this. Not sure if I'd understand how to modify that example. Thanks again anyway. – sabansm Jun 27 '12 at 03:44

3 Answers3

1

I already created Spreadsheet List API Wrapper library for GAS. Please see it. Link

Anirudh Ramanathan
  • 46,179
  • 22
  • 132
  • 191
keisuke oohashi
  • 167
  • 5
  • Cool. Do you have a working demo? What's the speed compared to using the regular Spreadsheet services in GAS? Is your wrapper library faster? Thanks for this! – sabansm Jun 26 '12 at 10:38
  • Sorry.I don't have demo and I don't compare speed. umm... I'll try it. – keisuke oohashi Jun 26 '12 at 22:00
-1

I am new to Google Apps Script. But playing around with the Debugger, I noticed that the following object is anchored to the "this" object.

Jdbc     (the methods are listed below)
  Types
  ResultSet
  Statement
  Connection
  ParameterMetaData
  DatabaseMetaData
  ResultSetMetaData
  RowIdLifetime

To me this says that you can at access any database that interfaces with JDBC. Considering that the scripts run on a Google server, you are probably very limited to which database management system you can use/access.

I don't know the actual setup for an individual database management system. I have not had to use one up unto now.

Hope this helps.

just.a.guy
  • 192
  • 1
  • 2
  • 12
  • I think the question is regarding what is described here https://developers.google.com/google-apps/spreadsheets/#sending_a_structured_query_for_rows and not about JDBC access from GAS – Srik Jun 24 '12 at 17:02
  • Sorry your question is way beyond my knowledge. I have tried to do some research. The best I found was a reference to a problem with the title: "Problem using structured query to pull data from Google spreadsheets into Google Maps API" ( https://groups.google.com/forum/?fromgroups#!topic/google-maps-api/x78h9narRnI) – just.a.guy Jun 24 '12 at 19:07
  • I found this documentation (https://developers.google.com/apps-script/class_urlfetchapp). It contains an example interfacing with Twitter. I wonder if you substitute a Structured Query request, would this give you what you want? To me it looks like the Structured Query functions at an HTTP level. – just.a.guy Jun 24 '12 at 19:32
  • Here is a script I used to execute a structured query from a script function. – just.a.guy Jun 24 '12 at 21:01
-1

Here is a function I used to execute a query from a script and retrieve its results:

function myFunction() 
{
   var ss = SpreadsheetApp.getActive();
   var sh = ss.getActiveSheet();
   var qry = "=QUERY(E1:e12,\"select E\")"
   var frng = sh.getRange("C14:C14");
   frng.setFormula(qry);
   var startRow = 14;  
   var lastRow = sh.getLastRow();  
   var msg = "values:";
   for (var i = 14; i <= lastRow; i++)
   {
      var cx = sh.getRange("C" + i);
      var val = cx.getValue();
      var msg = msg + ", " + val;
   }
   Browser.msgBox(msg);
 }
just.a.guy
  • 192
  • 1
  • 2
  • 12
  • your example is too contextual, it probably works in you specific spreadsheet but need too much adaptation to be useful. It is also not efficiently written (see [doc for good practice](https://developers.google.com/apps-script/guide_common_tasks)) – Serge insas Jun 25 '12 at 08:33
  • The question is "Can it be done?". Yes it can. I did not know that when I started. I hope this answers the question. Sorry for coming up with the wrong answer at the beginning. I did not understand what you were asking. I realize the solution is not polished. – just.a.guy Jun 25 '12 at 12:29
  • Serge thanks for your feedback. If you have the time, can you present a better version, so that I can learn from you? – just.a.guy Jun 25 '12 at 12:36