3-2-1 Contact!

Today we're pushing a new release of Google Apps Script with a great new feature, and a couple of more minor improvements. The biggest new feature is access to your Google Contacts. For those of you unfamiliar with Google Contacts, it is an address book that ties into your GMail account and can automatically sync to many mobile devices.

You can now create, read, update and delete contacts, and contact groups right from a script. Along with the usual properties like address, phone and email, we’ve also got the ability to create a User Defined Field. This allows you to extend Google Contacts, and customize it to your own style of working - it’s the perfect place to flag that a contact has opted out of receiving your email newsletter, or even simply recording that they’ve been sent the latest issue (that way you can avoid sending duplicate messages). For full details see the Contacts docs.

Another recent update relates to formatting your Spreadsheet content. We’ve exposed a host of new functions in the SpreadsheetApp (specifically in the Range class) that allow you to set formatting properties such as font size, style, weight, color and more.

Pushing so many functions into the Range class left us with another problem - our auto-complete was becoming unmanageable. This release features a new auto-complete system in the code editor:


We’ve got a lot more features coming over the next few months, so make sure to check back regularly, and as always, we’re keen to hear your feedback.

Google Finance for Apps Script

Today we're pushing out a new release of Google Apps Script, with quite a few general enhancements, and one significant new feature: Google Finance!

The new Finance Service in Apps Script exposes two function calls (for full details see the documentation):

• getStockInfo: given a stock symbol it returns current market information about that stock (delayed by up to 20 minutes - see disclaimer)
  
  
• getHistoricalStockInfo: given a stock symbol it returns historical market information about that stock over a specific date range, allowing for all kinds of interesting analysis

We've put together a small demo - you can copy the spreadsheet in order to play around with the code. At heart, it is a very simple algorithm that calculates the 20-day average price for the stock, and makes simulated trading decisions (buy/sell) based on the ratio of current price to the 20-day average.


We make just two calls to Google Finance - one to get the data that allows us to calculate the 20-day average at the Start Date, and then another to get the actual data describing the stock activity between the Start Date and End Date.

Finance offers far more than just stock price, so you have scope for making quite complex analysis models. Available stock attributes include:

• high: the highest price the stock traded for the current day
• low: the lowest price the stock traded for the current day
• volume: number of shares traded of this stock for the current day
• marketcap: the market cap of the stock
• pe: the Price-to-Earnings ratio for this stock
• eps: the earnings-per-share for this stock
• high52: the 52-week high for this stock
• low52: the 52-week low for this stock
• beta: the beta value of this stock
• shares: the number of shares outstanding of this stock
• currency: the currency in which this stock is traded
  

We hope that you'll enjoy this new functionality - be sure to let us know what you come up with, and check back regularly for more new features!

Google Apps Script Launched to Google Apps Premier and Education

Back in May we announced a pilot release of Google Apps Script, and after thousands of hours of use, and lots of valuable feedback, we're excited to launch Google Apps Script to all Google Apps Premier and Education Edition customers today.

Google Apps Script provides the ability to automate a variety of features in Google Apps, such as reading and changing values in Spreadsheets, creating Calendar appointments, sending email and much more. Check out the Google Apps Script Overview, below, taken from our May announcement.

Our favorite user example is a bureaucracy-busting Expense Approval Script, which expands one of the tutorials we've assembled to help people see how to use Google Apps Script. Submitting and approving expense reports can be tedious. Adding a script automates the drudgery by doing the following:

• automatically converting foreign currency amounts to local currency
  
• looking up historical exchange rates from external web services
• catching basic errors – things like missing dates, descriptions, and amounts
• triggering business logic to flag certain expense categories 'For Review'
  

Even more useful, the script can figure out whose approvals are needed, and automatically request approval from those people. And once approved, the script sends a note to the finance department for payment – all without writing a single email!

This is just one of hundreds of business processes that can be customized and streamlined using Google Apps Script within Google Apps Premier and Education editions. From expenses and timesheets to trouble tickets and order tracking – really, anything that you might want to calculate, organize and automate – is now easier than ever with Google Apps Script.

Starting today, all Google Apps Premier and Edu domains will now see "Scripts" in the Google Spreadsheets "Tools" menu – so check it out.

We're looking forward to seeing how users put Google Apps Script to creative (and valuable) use, so start scripting, and let us know what you come up with. We'll feature some of our favorite user submissions over the coming weeks!

Posted by Evin Levey, Product Manager, Google Apps team

Sample Custom Function: Convert()

The URL Fetch service can be quite useful to create custom functions out of simple capabilities on other websites. Here's a sample custom function I wrote which will convert practically anything by using the conversion capability of Google Search in the background. So, for example:
=convert(100, "feet", "meters")
or
=convert(52, "weeks", "hours")
or
=convert(3, "tsp", "tbsp") (that's teaspoons to tablespoons!)

Those all work... as do many (many!) others...

The sample spreadsheet is here:
http://spreadsheets.google.com/ccc?key=r7PRNG-DcO3Qk33l8cVgYCQ

You can create your own copy of that to see the script code if you have Google Apps Script enabled on your domain.

New release - new features enabled

We have released changes to Google Apps Script including the following new features:

- Control "editor" access to scripts: a new check box was added at the bottom of the editor window called "Inherit Privileges". If checked, all collaborators on the current document can edit the script. Otherwise, only the script author can make changes to the script.

- New Language Service: support was added for automated translations to multiple languages. Here's an example: Browser.msgBox(LanguageApp.translate("This is a test", "en", "es"));

- New URLFetch Service: this allows scripts to communicate with other applications or access other resources on the web by fetching the content at any URL. Here's an example: Browser.msgBox(URLFetchApp.fetch("http://www.google.com").getContentText());

Please continue to give us feedback on any issues you find or on the Google Apps Script features you are using... post your comments and questions in our forum at http://www.google.com/support/forum/p/apps-script?hl=en - as that's a large part of our process for prioritizing changes and new feature requests. Thanks!

Old tool, new tricks

I like to think I can do practically anything in a spreadsheet. But it's probably more accurate to say that I like to *try* to do anything in a spreadsheet whether or not I can succeed.

Of course, my spreadsheet of choice is the kind I can access from anywhere and share in real time – in Google Docs. So, some would say that even when it makes no sense, I try to do things in a spreadsheet (case in point – my poker simulator – why'd I do that?) But there are some things I never thought possible, like sending an email or reading my calendar.

That's why we're excited to announce a limited test of a new feature which lets people add customization and automation to Google Apps, starting with spreadsheets in Google Docs. We call this feature Google Apps Script.

Google Apps Script provides the ability to automate a variety spreadsheet actions, such as reading and changing values in cells and ranges, changing formats and formulas, and creating custom functions. It also reaches outside of spreadsheets to allow you to send email or create calendar entries.

Check out the Google Apps Script overview, below, and watch our example of sending email from Google Spreadsheets.

As Google Apps Script is in limited test, we see it as a puppy who's still in training. In fact, just this week, he learned to fetch (URL content) and speak (translate to other languages). But we think he's ready now for some time outside his yard.

So we're inviting a limited number of Google Apps domains – about a thousand organizations – to start playing with Google Apps Script and giving us feedback so we can quickly understand which tricks would be the most beneficial to learn next.

If you're like me and like to stretch spreadsheets to their limit, and don't mind working with early release features, you can get involved in Google Apps Script now by applying to join this limited test phase.