G Suite Developers Blog

\n * var chances = WeatherService\n * .getPrecipitation(\"New York, NY\");\n * var fridayChance = chances[“Friday”];\n * Logger.log(fridayChance + “% chance of rain on Friday!”);\n *

Big Updates for Apps Script

June 27, 2012

Today at Google I/O, we announced many enhancements to Google Apps Script to help you build richer applications and share your apps with users. Apps Script began as a tool for helping users get more done with their Google spreadsheets. Over time, Apps Script has grown to handle much more. It's a platform to extend Google spreadsheets and Sites, and a convenient way to create web applications. We launched script.google.com as a destination for Apps Script developers. You can now create scripts from script.google.com or from Google Drive. Plus, your projects are now stored in Google Drive, and you can share them just like a Google document or spreadsheet. HtmlService can help you create beautiful interfaces using Apps Script. HtmlService allows you to create web apps using HTML, client-side JavaScript, and CSS. You can also use jQuery to build web apps. HtmlService uses Google Caja to ensure that the HTML content served by your web apps is safer for you and your users. We also launched a better way to store your application's data, ScriptDb . Every script now has an embedded JSON database. You no longer have to rely on a spreadsheet to store the data. Using ScriptDb, you can store a larger volume of data and search easily. We designed ScriptDb to be easy to use. It doesn't need connection strings or special passwords, and you can directly store and search your JavaScript objects without the need to convert them to a different format. You can learn more about ScriptDb on the Google Apps Script Developers page. There are also now more options for deploying your web app. Your apps can now, with authorization, run as the user behind the keyboard, not just the script owner. This brings a new level of versatility to web apps built with Apps Script. Finally, we wanted to make it easy to distribute your apps. You can now publish your apps in the Chrome Web Store . Register and package your app directly from the Publish menu in Google Apps Script. Then customize your listing from the Chrome Web Store and publish your app to the world. We added a lot of new functionality to Apps Script, so in addition to our developer reference documentation , we've also created a new user guide . If you need further help you can reach out to us on Stack Overflow . You can also make feature requests and report issues on the Apps Script page on Google Code. Building with Google Apps Script has become a lot easier and more powerful. We can't wait to see what you build. Happy scripting!

Saurabh Gupta profile | twitter

Saurabh Gupta is Product Manager of Google Apps Script. In his spare time, he loves to play racketball and read.

Programmatically Managing Charts in Spreadsheets

June 19, 2012

Editor’s Note: Kevin Winter is a guest author from the AdWords API Developer Relations team. He implemented these features in his 20% time. - Jan Kleinert Google Spreadsheets provides powerful charting functionality to let you analyze your data many different ways. We recently added the ability to programmatically create, modify and delete Spreadsheet charts using Google Apps Script.What are Embedded Charts?Charts that a user creates in a Google Spreadsheet are called Embedded Charts. Apps Script can be used to manage these types of Spreadsheet Charts. Each EmbeddedChart belongs to a Sheet. Embedded charts are named this way because they are embedded onto a Sheet within the spreadsheet. Embedded charts align to the upper left hand corner of the specified column and row in the spreadsheet.An Example of an Embedded Spreadsheet ChartLet’s say I want to track my gas mileage using Google Spreadsheets. First, I create a Spreadsheet, and set up columns and formulas. Next, I add a form interface to make it easy to add new entries and a chart to visualize my mileage. Now I can start recording data - but each time I add enough entries to go past the ranges the chart uses, I need to manually increase them. How about we use some Apps Script magic to solve this?

The script below iterates through all charts on this sheet and determines if any of the ranges need to be expanded (i.e. if there are more rows with data to display). It then updates the title, builds the new EmbeddedChart object and saves it to the sheet. It could also add a menu interface or a trigger to execute this periodically or when the spreadsheet is edited.function expandCharts() { var sheet = SpreadsheetApp.getActiveSheet() // Get a list of all charts on this Sheet. var charts = sheet.getCharts(); for (var i in charts) { var chart = charts[i]; var ranges = chart.getRanges(); // Returns an EmbeddedChartBuilder with this chart’s settings. var builder = chart.modify(); for (var j in ranges) { var range = ranges[j]; // rangeShouldExpand_ is defined later. if (rangeShouldExpand_(range)) { // Removes the old range and substitutes the new one. builder.removeRange(range); var newRange = expandRange_(range); builder.addRange(newRange); } } // Update title. builder.setOption('title', 'Last updated ' + new Date().toString()); // Must be called to save changes. sheet.updateChart(builder.build()); } } function rangeShouldExpand_(range) { var s = range.getSheet(); var numColumns = range.getNumColumns() var values = s.getSheetValues(range.getLastRow(), range.getColumn(), 2, numColumns); for (var i = 0; i < numColumns; i++) { // If the next row has the same pattern of values, // it’s probably the same type of data and should be expanded. if (!values[0][i] && !values[1][i] || !!values[0][i] && !!values[1][i]) { continue; } else { return false; } } return true; } function expandRange_(range) { var s = range.getSheet() var startRow = range.getRow(); var startCol = range.getColumn(); var numRows = range.getNumRows(); var numCols = range.getNumColumns(); while (rangeShouldExpand_(range)) { numRows++; range = s.getRange(startRow, startCol, numRows, numCols); } return range; }

Creating New Charts in SpreadsheetsWhat if you wanted to create a new chart from scratch? You can do that too!var sheet = SpreadsheetApp.getActiveSheet(); var chart = sheet.newChart() .setPosition(5, 6, 5, 5) .setChartType(Charts.ChartType.AREA) .addRange(sheet.getActiveRange()) .build(); sheet.insertChart(chart); In the above code example, we’ve gotten a reference to an EmbeddedChartBuilder, set its position within the sheet, change the chartType, add the currently selected range and insert the new chart.Modifying Charts in SpreadsheetsThe EmbeddedChartBuilder allows you to modify the chart in a couple ways:

Add/Remove the ranges this chart represents via addRange and removeRange.

Set options that modify how the chart will be rendered as well as change the chart type with setOption and setChartType.

Change where the chart will be displayed (the cell and cell offset of the chart container) via setPosition.

Embedded charts use more options than regular Apps Script charts, so options are handled slightly differently. Developers can pass a dotted field path to change options. For example, to change the animation duration, you can do this:builder.setOption("animation.duration", 1000);Now that we’ve created a bunch of charts, your spreadsheet is probably pretty cluttered. Want to clear it and start afresh with charts? Just remove them all:var sheet = SpreadsheetApp.getActiveSheet(); var charts = sheet.getCharts(); for (var i in charts) { sheet.removeChart(charts[i]); }Take Your Charts with YouJust like standalone charts, you can use embedded charts elsewhere. You can add them to a UIApp or a sites page as well as sending them as an email attachment:// Attach all charts from the current sheet to an email. var charts = SpreadsheetApp.getActiveSheet().getCharts(); MailApp.sendEmail( "recipient@example.com", "Income Charts", // Subject "Here's the latest income charts", // Content {attachments: charts }); We hope you found this blog post useful. Enjoy editing embedded charts using Google Apps Script!

Kevin Winter profile

Kevin is a Developer Programs Engineer in the AdWords API Team. He maintains the latest version of the Python and Java client libraries for the AdWords API. You can find him in the New York office.

More Power to Google Apps Domain Administrators with Apps Script

June 15, 2012

At the end of last year we launched the UserManager Apps Script service, allowing Google Apps domain administrators to write scripts to programmatically create, delete and edit their user accounts.We are now extending the family of Domain services with two new additions: NicknameManager and GroupsManager.The NicknameManager service allows domain administrators to define alternate email addresses (i.e. “nicknames”) with a single line of code, as in the following example:var nickname = NicknameManager.createNickname("user", "nick");With the GroupsManager service, Google Apps domain administrators can create and delete groups, and manage their members and owners. The following example shows how to list all members of a group given its unique groupId:function listMembers(groupId) { var members = GroupsManager.getGroup(groupId).getAllMembers(); for (var i in members) { var member = members[i]; Logger.log(i + ": " + member); } }With the same service, one line of code is enough to add a member to a group:GroupsManager.getGroup(groupId).addMember(memberId);If you want to know more about the new NicknameManager and GroupsManager services, please check our documentation, and don’t hesitate to get in touch with us if you have questions or suggestions.

Claudio Cherubino profile | twitter | blog

Claudio is an engineer in the Google Drive Developer Relations team. Prior to Google, he worked as software developer, technology evangelist, community manager, consultant, technical translator and has contributed to many open-source projects. His current interests include Google APIs, new technologies and coffee.

Using the Google Picker to upload files to Drive

June 11, 2012

The Google Picker API provides developers with an easy-to-use file dialog that can be used to open Google Drive files directly from their web app independently of the Drive UI. The Drive SDK documentation includes an example showing how to incorporate the Google Picker with just a few lines of JavaScript.Another powerful use case for the Picker API is to allow users to upload files to Drive with the same consistent UI. A single Picker dialog can incorporate multiple views and users can switch from one to another by clicking on a tab on the left:

The following code sample opens the Picker dialog and registers a simple callback function to handle the completed upload event:<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"/> <title>Google Picker Example</title>  <script src="http://www.google.com/jsapi"></script> <script type="text/javascript"> // Use the Google Loader script to load the google.picker script. google.setOnLoadCallback(createPicker); google.load('picker', '1'); // Create and render a Picker object for searching images // and uploading files. function createPicker() { // Create a view to search images. var view = new google.picker.View(google.picker.ViewId.DOCS); view.setMimeTypes('image/png,image/jpeg'); // Use DocsUploadView to upload documents to Google Drive. var uploadView = new google.picker.DocsUploadView(); var picker = new google.picker.PickerBuilder(). addView(view). addView(uploadView). setAppId(YOUR_APP_ID). setCallback(pickerCallback). build(); picker.setVisible(true); } // A simple callback implementation. function pickerCallback(data) { if (data.action == google.picker.Action.PICKED) { var fileId = data.docs[0].id; alert('The user selected: ' + fileId); } } </script> </head> <body> </body> </html> There is an important difference between this upload example and the code used to open files: in addition to the standard view, an instance of DocsUploadView is added to the Picker object, thus providing upload capability.For more information about this component and all other available views, please refer to the Google Picker Reference Guide.

Claudio Cherubino profile | twitter | blog

Fire Up Your Computer: The 2012 Google Apps Developer Challenge is Here!

June 6, 2012

Updated to add links to the #gappschallenge hashtag and to Google Apps Script. In the past year, the Google team has been engaging with local developers by running various Google conferences and Google+ Hackathons, showcasing creative applications, and supporting Tech Hubs. Since we are always looking for opportunities to encourage (and challenge!) you, we are looking forward to giving developers the opportunity to take on this year’s challenge, which will focus on Google Apps Script, Google Apps and Google Drive APIs.With the Google Apps Developer Challenge, we hope developers across the globe will find new and innovative ways to use Apps Script, Apps and Drive APIs to build cool apps. This challenge is particularly unique as the APIs are available to a large community of developers who code in a variety of languages that include Java, PHP, Python, and .Net.

We will be working in collaboration with our Google Developer Groups (also known as GTUGs) and Google Business Groups to organize events and prepare for this challenge. Make sure to join your local community so that you are aware of meet ups.How familiar are you with the various Google Apps and Drive APIs? If you aren’t familiar, make sure to read up about Google Apps Script, Google Apps and Drive APIs on Google Developers. Use the Chrome Web Store as a source of inspiration. Create an innovative application using Google Apps Script, Google Apps, and Drive APIs. If your application is the best within one of the three categories defined below in your region, you could win a prize of $20,000 dollars! Google is also committed to nurturing the next generation of computer scientists as well as encouraging more women to get into coding, so we have special prizes for all-student or all-female teams that make the second round — $1,000 dollars.

Fans Connect Online, one of the winners of last year’s Android Developer ChallengeThe first round of submissions will start on the 24th of August 2012. The categories are

Enterprise / Small Business Solutions e.g., Accounting, Sales, Workflow, Collaboration

Social / Personal Productivity / Games / Fun

Education / Not for Profit / Water / Food & Hunger / Health

Make sure you read all the details about the competition on the Google Apps Developer Challenge page and follow the hashtag #gappschallenge on Google+ for any additional updates.What are you waiting for? Get coding!

Chukwuemeka Afigbo profile | blog

Chukwuemeka is a Program Manager at Google with the Emerging Markets Outreach team where he works closely with developers, startups, businesses and IT professionals in the region to successfully grow their business around Google Developer Tools and APIs. His current mission is to drive developer internet content in Sub Saharan Africa.

Email Overload!

May 31, 2012

Editor’s Note: This blog post is authored by Blair Kutzman, who developed the Gmail Delay Send script. - Eric Koleda Update: To start using this script simply open this page and follow the instructions. In today’s connected world, when you get your email could be just as important what’s in it. In 2011 over 107 trillion emails were sent to 1.97 billion internet users. Studies have shown that the average person can only effectively process 50 emails in a day. That leaves 100 emails per person per day that are not processed effectively. How can you be sure that the emails you send fall into the former category and not the latter?Luckily, there are tools to assist with email overload. One such tool is Gmail Delay Send.What is Gmail Delay Send?Gmail Delay Send is a Google Apps Script that allows you to schedule emails to be delivered on a specified date and time. Using this tool you can ensure your email is sent to its destination at a time when you can capture your recipient’s full attention. For example, receiving an email at 4:59 PM on friday might not receive the same attention as an email received on Monday at 10:00 AM.Designing Gmail Delay SendA primary requirement of Gmail Delay Send was that it needed to work everywhere Gmail is available. There are already many browser add-ons and services available to enhance Gmail with similar functionality, so the purpose was not to duplicate that work. In order for the service to be available on all platforms, it needed to utilize native Gmail features.We needed a native way that Gmail could:

Store a composed, but unsent message.

Store in that composed message two pieces of metadata:

Whether or not the message meant to be sent at a later time
If it is, when the message should be sent

Gmail already contains a 'Draft' folder which is exactly what is required for item 1. The real problem was where and how to store the metadata for item 2, without any new Gmail functions. I chose to encode the metadata in the subject of the message because it would contain less text, which would mean a smaller chance of mis-parsing. Text such as “5 hours” and “next tuesday” were turned into a date-time using an open source library called datejs and a few modifications. See below for details of how this potentially cumbersome process was improved.Basic structureThe script works as follows:

The user composes an email, noting the date-time they want the message to be sent in their subject line after a delimiter (eg. "Lets go surfing -- 3:30PM")

The email is then saved as a draft and optionally a label is applied marking it as a message that we want to send at a later time.

When the script executes, all draft messages are checked (optionally looking at messages that have a specific label applied) to see if they should be sent now.

Usability improvementsAlthough using datejs to parse the dates from the subject line was easy to implement, it introduced some usability issues. First, how would a user know if a certain string can be parsed by datejs (eg. is “5 minutes before 4PM” parsable)? To assist the user in knowing which dates datejs supports, the script offers a mechanism to test a given string directly in the spreadsheet that Gmail Delay Send is installed inside of. In this way a user can test various strings to see if they are valid and, if so, when they would be sent. A wiki page is dedicated to helping people through this process.Another possibly confusing part of using Gmail Delay Send was setting up triggers. Thanks to some recent improvements of the Script Services, this is now done automatically for users as they install.Improving script reliabilityAdding retry logic to the script was another important step in improving its reliability and user experience. Occasionally, users were getting emails from their trigger informing them that a certain Google Script API could not be contacted. Some retry logic was required to make things work correctly. As shown in the snippet below, the function executeCommand() takes any function and will try to execute it a specified number of times, retrying if an error is thrown:function executeCommand(fp) { var msg; var ret_val; var last_error; for(var retries = NUM_RETRIES; retries > 0; retries -= 1) { try { ret_val = fp(); break; } catch(err) { last_error = err; msg = "Exception:" + err + " thrown executing function:" + fp; debug_logs.push(msg); Logger.log(msg); Utilities.sleep(SLEEP_TIME); } } if(retries == 0) { msg = "Attempted to execute command:" + fp + " " + NUM_RETRIES + " times without success. Error message: " + last_error + ". Aborting :-("; Logger.log(msg); throw(msg); } return ret_val; }Using this method, statements like those below will automatically retry if the service is not available.executeCommand( function() { GmailApp.send( … ) }); executeCommand( function() { UrlFetchApp.urlFetch(url) } );SummaryGmail Delay Send was a fantastic project for learning about Google Apps Script and I hope that it will continue to prove useful to its users. If you’re interested in using Gmail Delay Send or just interested in the development process please check out the homepage or source.

Blair Kutzman profile

Blair currently works on scan firmware at Hewlett Packard. When not scanning he likes to hang out with family, run and surf. Check out his profile for other projects.

Introducing Versions and Libraries in Apps Script

May 22, 2012

Have you ever written a particular piece of code over and over again? Or used scripts to do something that you thought others might want to do as well? Starting today, you’ll be able to share and reuse those scripts as libraries, right from inside Google Apps Script.Why use a Script LibraryI often write scripts which check the National Weather Service for relevant weather-related information. This allows me to send myself an email if it’s going to rain, reminding me to bring an umbrella to work, or to annotate my spreadsheet of running workouts with the temperature of the day.Remembering how to query the National Weather Service every time I write a script is a daunting task, however. They have a complicated XML format that is tricky to parse. As a result, I end up just copying and pasting code each time. This is not only error-prone, but also has the big disadvantage that I have to fix all of my scripts one by one whenever the Weather Service’s XML format changes.The code I use to query the National Weather Service is a perfect use case for a library. By using a library, I no longer have to copy and paste code in my script project. Since logic is centralized, updates need to be applied just once. And now I am able to share my library with other developers who can benefit from the work I’ve already done.Writing a LibraryLibraries are written just like any other Apps Script project. A good library has a clean API which is also well documented. Here’s a code snippet from my WeatherService library:/** * Queries the National Weather Service for the weather * forecast of the given address. Example: * * <pre> * var chances = WeatherService * .getPrecipitation("New York, NY"); * var fridayChance = chances[“Friday”]; * Logger.log(fridayChance + “% chance of rain on Friday!”); * </pre> * * @param {String} address The address to query the * temperature for, in any format accepted by * Google Maps (can be a street address, zip * code, city and state, etc) * * @returns {JsonObject} The precipitation forecast, as * map of period to percentage chance of * precipitation. Example: * * <pre> * { Tonight: 50, Friday: 30, Friday Night: 40, ... } * </pre> */ function getPrecipitation(address) { // Code for querying weather goes // here... } Notice how detailed the documentation is. We know that good documentation makes for a great library. So, for every library Apps Script will also auto-generate a documentation page based on the code comments using the JSDoc format. If you want a method in your code to not be exposed to users, simply end its name with an underscore. Saving Versions Before code can be used as a library, a version of it needs to be saved. Versions are a new concept in Apps Script, and they represent a snapshot of your project which won’t change even as changes are made to the script code. Versions are useful because they allow you to change your library code without breaking existing users. Once you’re happy with the changes you’ve made, you can then save a new version. Please see the user guide for s aving a version and sharing your code as a library is easy. Using Libraries Using a library only takes a few steps. To be able to use a library, the owner of the library must share the library and its project key with you. You can follow these instructions to then use a library. To use this National Weather Service library, please visit this page for project key.

Useful Features of LibrariesScript Libraries come with three interesting features.

Documentation - In the Script Libraries dialog, you can click on the title link to navigate to documentation page for the library. See example of a generated documentation.

Development Mode can be used to test changes to a library without saving a new version. See our User Guide for more details

Autocomplete in Script Editor - Typing in the editor will auto-complete your library function names.

Interesting Libraries You Can Use To get started on using Script Libraries, you can find a list of useful libraries contributed by two of our top contributors - James Ferreira and Romain Vialard. You can also find a detailed user guide on managing versions and libraries. We hope you enjoy using libraries.

Gustavo Moura

Gustavo has been a Software Engineer at Google since 2007. He has been part of the Google Docs team since 2009. Prior to that, he worked on AdWords. In his free time he plays soccer.