Close
  • Latest News
  • Artificial Intelligence
  • Video
  • Big Data and Analytics
  • Cloud
  • Networking
  • Cybersecurity
  • Applications
  • IT Management
  • Storage
  • Sponsored
  • Mobile
  • Small Business
  • Development
  • Database
  • Servers
  • Android
  • Apple
  • Innovation
  • Blogs
  • PC Hardware
  • Reviews
  • Search Engines
  • Virtualization
Read Down
Sign in
Close
Welcome!Log into your account
Forgot your password?
Read Down
Password recovery
Recover your password
Close
Search
Logo
Subscribe
Logo
  • Latest News
  • Artificial Intelligence
  • Video
  • Big Data and Analytics
  • Cloud
  • Networking
  • Cybersecurity
  • Applications
  • IT Management
  • Storage
  • Sponsored
  • Mobile
  • Small Business
  • Development
  • Database
  • Servers
  • Android
  • Apple
  • Innovation
  • Blogs
  • PC Hardware
  • Reviews
  • Search Engines
  • Virtualization
More
    Subscribe
    Home Applications
    • Applications
    • Development
    • Networking

    Scripting Google with Google Apps Scripts

    Written by

    Jeff Cogswell
    Published June 2, 2010
    Share
    Facebook
    Twitter
    Linkedin

      eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

      When Google Apps Scripts was first introduced, about a year ago, it was available only to premium members. Then in January, Google made a majority of the features, such as classes for accessing the contact list, classes for accessing the folders, and classes for manipulating the spreadsheets down to the cell level (like what you would find with desktop-based spreadsheet software), available to standard customers. Then in May, Google added several updates such as database integration support via JDBC (Java Database Connectivity), custom user interfaces (still reserved for the paid “Premier” edition of Apps), standalone invocation of scripts (letting you run the scripts from non-Google Websites), additional integration into Google properties such as the Documents list and Google Maps, and installable event handlers.

      Presently, the scripting system is available primarily from the spreadsheet application, although there’s integration into other Google applications such as the e-mail system and the maps system. Included in the scripting system is a rich API filled with objects that let you automate almost any task you can imagine. As examples of its power, Google has made available examples such as one that lets you simulate a stock trading strategy in the spreadsheet application, one that generates fractals and one that’s an entire course registration system (OK, that last one seems a bit out of place; I can’t imagine why somebody would use an online spreadsheet program as a course registration system, but it does demonstrate the wide range of things you can do).

      Personally, I’m still a bit skeptical. There are certainly some handy features here, but I’m not sure how popular the scripting system will be. The JDBC part is cool, and if you’re a premium member you’ll be able to build some decent applications on top of the spreadsheet system. But look at it this way: That functionality has existed in Microsoft Excel for many years, but how many people actually build applications in Excel compared to the number of people who simply use Excel for its basic spreadsheet features? Perhaps that will change, and indeed the Google Apps Script system has some nice features, but we’ll have to see just how popular it becomes.

      JDBC Integration

      If you’re familiar with JDBC from the Java world, you’ll be right at home with the JDBC integration. The set of classes is smaller than the official Sun JDBC API that runs under Java (remember, we’re talking JavaScript here, not Java, which is very different). But the base functionality is there through classes supporting connections, statements, prepared statements and result sets.

      The API documentation is a bit weak in that it just shows the function prototypes and some quick descriptions, but not really any examples. For example, I wanted to see some actual SQL in the JdbcStatement class, but there was none. (I imagine there will be more documentation in the future, however, as well as more examples from the community.)

      Trying It Out

      The scripting system is basically a JavaScript wrapper around a set of Java classes on the server. How do I know that? Because during testing, I often received errors that were obviously coming from Java. (For example, one time I tried to pass a JavaScript array into a function and received the error message “Cannot convert NativeArray@78037b60 to java.lang.Object[][].”) Further, the JavaScript classes that you interact with seem to have a lot of methods that aren’t documented in the API-which is actually quite normal when you consider that JavaScript doesn’t have much of a mechanism for public and private members. (There are ways to hide members, thereby making them private, but the spec itself doesn’t support access levels.) But this means you have a whole slew of functions you’re free to call-although I would be careful if they’re not documented because Google could easily change them at any time. Again, how do I know this? Because the script editor is object-aware and will show you a list of members available to an object when you type the object name followed by a dot.

      Of course, JavaScript isn’t strongly typed (you just declare variables with var, and they can hold any type), and this means the code editor is doing some sophisticated work to follow the chain of calls. For example, if you type

      var ss = SpreadsheetApp.getActiveSpreadsheet();

      the editor knows that the getActiveSpreadsheet method returns a Spreadsheet object (or, more accurately for the JavaScript purists, an object that contains the members that define it as a Spreadsheet object).

      Because the documentation was a bit weak, I had to figure out how to get a JDBC connection going. But once I got it figured out, it worked very well. For the programmers reading this, here’s the short code that opens the JDBC connection, executes some SQL and copies the resulting data into the active spreadsheet:

      “function testJDBC() {var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheets()[0];var connstr = ‘jdbc:mysql://www.example.com:3306/test’; // subst your own IP/database name.var conn = Jdbc.getConnection(connstr, ‘username’, ‘password’);var stmt = conn.createStatement();stmt.execute(‘select * from names’);var res = stmt.getResultSet();var line = 2;var stuff;while (res.next()) {var fname = res.getString(‘fname’);var lname = res.getString(‘lname’);sheet.getRange(‘A’ + String(line) +’:B’ + String(line) ).setValues([[fname,lname]]);line++;}conn.close();}“

      The code execution was a bit slow; it took about four seconds to connect, and then you can visibly see a row fill, then a very tiny pause, then the next row fill. Compare this to connecting remotely via the mysql command-line interface (from my local computer to the same remote mysql server), which connected instantly, and retrieved the rows a bit more quickly. It’s hard to say exactly what the holdup is on Google’s end, but I would suggest doing some tests to see if the access is fast enough for your uses. If not, you’ll want to do some streamlining (e.g., use stored procedures and views on the database end).

      Standalone Invocation of Scripts

      According to the official blog, “it’s now possible to call a script from any Website, so you’re able to build Web pages where users can submit entries that will be collected in a Google spreadsheet.” Unfortunately, that’s about all the information we have at this point. The docs so far say nothing about this feature, and it sounds intriguing. I’m speculating that there will be a JavaScript library that you’ll include on your Website that gives you access to the scripts, much the same way you can include Facebook scripts on your own site. We’ll have to wait and see on this one. And the privacy and security issues should be interesting as well.

      Jeff Cogswell
      Jeff Cogswell
      Jeff Cogswell is the author of Designing Highly Useable Software (http://www.amazon.com/dp/0782143016) among other books and is the owner/operator of CogsMedia Training and Consulting.Currently Jeff is a senior editor with Ziff Davis Enterprise. Prior to joining Ziff, he spent about 15 years as a software engineer, working on Windows and Unix systems, mastering C++, PHP, and ASP.NET development. He has written over a dozen books.

      Get the Free Newsletter!

      Subscribe to Daily Tech Insider for top news, trends & analysis

      Get the Free Newsletter!

      Subscribe to Daily Tech Insider for top news, trends & analysis

      MOST POPULAR ARTICLES

      Artificial Intelligence

      9 Best AI 3D Generators You Need...

      Sam Rinko - June 25, 2024 0
      AI 3D Generators are powerful tools for many different industries. Discover the best AI 3D Generators, and learn which is best for your specific use case.
      Read more
      Cloud

      RingCentral Expands Its Collaboration Platform

      Zeus Kerravala - November 22, 2023 0
      RingCentral adds AI-enabled contact center and hybrid event products to its suite of collaboration services.
      Read more
      Artificial Intelligence

      8 Best AI Data Analytics Software &...

      Aminu Abdullahi - January 18, 2024 0
      Learn the top AI data analytics software to use. Compare AI data analytics solutions & features to make the best choice for your business.
      Read more
      Latest News

      Zeus Kerravala on Networking: Multicloud, 5G, and...

      James Maguire - December 16, 2022 0
      I spoke with Zeus Kerravala, industry analyst at ZK Research, about the rapid changes in enterprise networking, as tech advances and digital transformation prompt...
      Read more
      Video

      Datadog President Amit Agarwal on Trends in...

      James Maguire - November 11, 2022 0
      I spoke with Amit Agarwal, President of Datadog, about infrastructure observability, from current trends to key challenges to the future of this rapidly growing...
      Read more
      Logo

      eWeek has the latest technology news and analysis, buying guides, and product reviews for IT professionals and technology buyers. The site’s focus is on innovative solutions and covering in-depth technical content. eWeek stays on the cutting edge of technology news and IT trends through interviews and expert analysis. Gain insight from top innovators and thought leaders in the fields of IT, business, enterprise software, startups, and more.

      Facebook
      Linkedin
      RSS
      Twitter
      Youtube

      Advertisers

      Advertise with TechnologyAdvice on eWeek and our other IT-focused platforms.

      Advertise with Us

      Menu

      • About eWeek
      • Subscribe to our Newsletter
      • Latest News

      Our Brands

      • Privacy Policy
      • Terms
      • About
      • Contact
      • Advertise
      • Sitemap
      • California – Do Not Sell My Information

      Property of TechnologyAdvice.
      © 2024 TechnologyAdvice. All Rights Reserved

      Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.