2

I am hoping someone can assist me in getting the JDBC script Reading from a Database example here... https://developers.google.com/apps-script/jdbc ...to work with my db.

    function foo() {
      var conn = Jdbc.getConnection("jdbc:mysql://<host>:3306/<instance>", "user", "password");
      var stmt = conn.createStatement();
      stmt.setMaxRows(100);
      var start = new Date();
      var rs = stmt.executeQuery("select * from clients");

      var doc = SpreadsheetApp.getActiveSpreadsheet();
      var cell = doc.getRange('a1');
      var row = 0;
      while(rs.next()) {
        cell.offset(row, 0).setValue(rs.getString(1));
        cell.offset(row, 1).setValue(rs.getString(2));
        cell.offset(row, 2).setValue(rs.getString(3));
        cell.offset(row, 3).setValue(rs.getString(4));
        row++;
      }
      rs.close();
      stmt.close();
      conn.close();
      var end = new Date();
      Logger.log("time took: " + (end.getTime() - start.getTime()));
    }

I plugged in my server and user info and changed the select * statement to the table "clients" in my db. Connection is successful but I get the following error...

TypeError: Cannot call method "getRange" of null. (line 9)

The table "clients" in my db has the following columns...

     id, clientname, clientcontact, clientphone, clientnote

Can someone help me understand what needs to be changed in the example script for it to work with my clients table? I am not a programmer by far... but I would like to get this working and use it as a template to get data from mysql into google spreadsheets so I can then publish the sheet on my google site.

Thanks!

Rubén
  • 34,714
  • 9
  • 70
  • 166
user1763612
  • 23
  • 3
  • The error message says that the variable 'doc' is null... are you running this script from within a spreadsheet ? (sorry if my question seems trivial but I can't be sure without asking) – Serge insas Oct 21 '12 at 21:36
  • No I'm running the script by opening it directly in my Drive. I created the script in Drive and saved it. I then went to insert the script into a spreadsheet I created named "mysqltest" but i dont see my script in the gallery, i only see scripts others have created. – user1763612 Oct 21 '12 at 22:57

1 Answers1

1

This script is supposed to run from a spreadsheet container or at least to have access to a spreadsheet to write its results.

So you have 2 possibilities:

  1. Copy /paste your script in the spreadsheet script editor and run it from there.

  2. Replace the spreadsheetApp.getActive with openbyId(spreadsheet id) (See this other post for more information) your are indeed in almost the same situation since your script is not directly related to the spreadsheet it uses.

Community
  • 1
  • 1
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Thank you so much!!! I used the openbyid(spreadsheet id) as suggested and the code is now working without error and is populating the test sheet! – user1763612 Oct 22 '12 at 15:02
  • This was indeed probably the best choice... (at least the one I 'd choose too! ) glad it helped :-) – Serge insas Oct 22 '12 at 15:07
  • Thanks Serge. Yep, that openbyid was the cure! I was calling it from a timer trigger, so obviously no active spreadsheet! Which explains why it was working when I had it open in debug mode but not running stand-alone in "prod". – harvest316 Feb 19 '13 at 04:13