2011 was the year of momentum for Google Apps Script. As 2012 dawns upon us, let us take a moment to reflect on the past year.
We started 2011 with a bang! In January we released a cloud-based Debugger into Apps Script’s IDE that proved to be very useful for developers. The Script Editor was upgraded bringing about many features and bug fixes. In March we implemented a very powerful feature of embedding Apps Script in Google Sites pages as Gadgets, making it easy to enhance Sites in amazing ways. We also improved Contacts Services, making it more stable with an improved API.
At Google I/O in May we launched Document Services, Gmail Services and the drag ‘n’ drop GUI Builder. These were major steps forward in making sure that Apps Script provides a full set of APIs to allow developers to build rich workflow and automation solutions.
We were very busy during the summer months preparing for a series of launch for later part of 2011. In September, we launched Charts Services. It allows users to dynamically create Charts and embed them in emails, UiApp or export as images. We also released three Google API services for Prediction, UrlShortener and Tasks APIs.
Lock and Cache Services launched in October. These services are important for building performant and scalable applications. We also improved the Script Editor by adding Project Support and made other UI improvements. November brought about the launch of Client Handlers and Validators. This is only the beginning of our commitment to allow developers to build more advanced UI using Apps Script.
In December we continued to improve the reliability and stability of Apps Script runtime. We capped the year by releasing Groups and Domain Services. And who can forget the very useful AdSense Services for AdSense advertisers!
Throughout the year we expanded our outreach channels. There were Apps Script sessions at Google I/O and Bootcamp, and several attendees got their last minute tickets through the Apps Script I/O challenge. We were at Google Developer Day and DevFest events, met with GTUGs, and hosted hackathons throughout the world. Our blog also featured scripts like Revevol’s Trainer Finder, Corey’s Gmail Snooze, Dave’s Flubaroo, Top Contributor’s Mail Merge, Saqib’s Idea Bank, and Drew’s Calorie Counting that showed the power of Apps Script.
Recently we started Office Hours in G+ hangout. These hangouts proved to be very popular, personal and effective means to share ideas with Apps Script community. Join us some day!
In our efforts to help educators, we worked with a New York city school to help them make most out of Google Apps. We also hosted many EDU focused webinars and workshops. In great Google tradition, Apps Script team participated in CAPE and Google Serve.
2012 is going to be an even more exciting and promising year. Tighten your seat belts because we intend to keep firing on all cylinders!
Managing the user accounts in a Google Apps domain can be a daunting task when you have hundreds or thousands of them and you have no tools to automate the process. The Google Apps Provisioning API allows developers to write user management applications in the programming language of their choice, but many system administrators prefer a script-based solution instead. The recently launched UserManager Apps Script service fills the gap, providing Google Apps domain administrators an easy way to automate tasks such as batch user creation or update.
With the new Apps Script service, creating a user will be as easy as writing a single line of code:
var user = UserManager.createUser("newuser", "John", "Smith", "mypassword");
The UserManager service also makes it easy to perform the same task on each account in the domain. The following sample shows how you can force all users to change their passwords at the next login:
var users = UserManager.getAllUsers(); for (var i in users) { users[i].setChangePasswordAtNextLogin(true); }
Calls to the UserManager service can also be scheduled to run hourly or daily, or in response to certain events thanks to Apps Script Triggers.
Interested in what else you can do with the UserManager service? Please check the documentation and get in touch with us on the the forum for any questions about its usage or to share more info about your project with the community.
Google Groups is a great way to foster communication over email and on the web, connecting people and allowing them to participate in and read archived discussions. Today, we are introducing the Google Groups Service in Google Apps Script. Groups Service will allow a script to check if a user belongs to a certain group, or to enumerate the members of a particular group. The Google Groups Service works with groups created through the Google Groups web interface as well as groups created by enterprise customers with their own domain using the control panel and the Google Apps Provisioning API.
This opens a wide range of possibilities, such as allowing a script with Ui Services to show additional buttons to the members of a particular group - for example teachers or managers - and sending customized emails to all the members of a group.
Here are a few sample scripts to help you get started with the new API. To try out these samples, select Create > New Spreadsheet and then Tools > Script Editor from the menu. You can then copy the code into the script editor. The scripts’ output will appear back in the spreadsheet.
The Groups Services can be used to fetch a list of the Google Groups of which you’re a member.
Below is a function which returns all the groups of which you’re a member. Copy and paste it into the script editor and run it. The editor will prompt you to grant READ access to the Google Groups Service before the script can run successfully.
If you receive a message stating that you’re not a member of any group, open up Google Groups and join any of the thousands of groups there.
function showMyGroups() { var groups = GroupsApp.getGroups(); var s; if (groups.length > 0) { s = "You belong to " + groups.length + " groups: "; for (var i = 0; i < groups.length; i++) { var group = groups[i]; if (i > 0) { s += ", "; } s += group.getEmail(); } } else { s = "You are not a member of any group!"; } Browser.msgBox(s); }
Brendan plays trumpet in a band. He also runs the band’s website and updates its Google+ page. He’s created a web application with Google Apps Script and now he wants to add to it some additional features for members of the band. Being a model Google user, he’s already subscribed each band member to a Google Group. Although building a complete UI with Google Apps Script is beyond the scope of this article, Brendan could adapt the following function to help make additional features available only to members of that Google Group.
Of course, this is not just useful for tech-savvy trumpet players: schools may wish to make certain features available just to teachers or others just to students; businesses may need to offer certain functionality to people managers or simply to show on a page or in a UI operations of interest to those in a particular department. Before running this example yourself, replace test@example.com with the email address of any group of which you’re a member.
test@example.com
Note: the group’s member list must be visible to the user running the script. Generally, this means you must yourself be a member of a group to successfully test if another user is a member of that same group. Additionally, group owners and managers can restrict member list access to group owners and managers. For such groups, you must be an owner or manager of the group to query membership.
function testGroupMembership() { var groupEmail = "test@example.com"; var group = GroupsApp.getGroupByName(groupEmail); if (group.hasUser(Session.getActiveUser().getEmail())) { Browser.msgBox("You are a member of " + groupEmail); } else { Browser.msgBox("You are not a member of " + groupEmail); } }
Sending an email to the group’s email address forwards that message to all the members of the group. Specifically, that message is forwarded to all those members who subscribe by email. Indeed, for many users, discussion over email is the principal feature of Google Groups.
Suppose, however, that you want to send a customised message to those same people. Provided you have permission to view a group’s member list, the Google Groups Service can be used to fetch the usernames of all the members of a group. The following script demonstrates how to fetch this list and then send an email to each member.
Before running this script, consider if you actually want to send a very silly message to all the members of the group. It may be advisable just to examine how the script works!
function sendCustomizedEmail() { var groupEmail = "test@example.com"; var group = GroupsApp.getGroupByEmail(groupEmail); var users = group.getUsers(); for (var i = 0; i < users.length; i++) { var user = users[i]; MailApp.sendEmail(user.getEmail(), "Thank you!", "Hello " + user.getEmail() + ", thank you for joining this group!"); } }
The Google Groups Service lets you query a user’s role within a group. One possible role is MANAGER (the other roles are described in detail in the Google Groups Service’s documentation): these users can perform administrative tasks on the group, such as renaming the group and accepting membership requests. Any user’s Role can be queried with the help of the Group class’ getRole() method.
This sample function may be used to fetch a list of a group’s managers. Once again, you must have access to the group’s member list for this function to run successfully:
function getGroupOwners(group) { var users = group.getUsers(); var managers = []; for (var i = 0; i < users.length; i++) { var user = users[i]; if (group.getRole(user) == GroupsApp.Role.MANAGER) { managers.push(user); } } return managers; }
Let us know what you end up building, or if you have any questions about this new functionality, by posting in the Apps Script forum.
Editor's note: this announcement is cross-posted from the Google Ads Developer Blog, which caters to AdWords, AdSense, DoubleClick and AdMob developers. We hope you enjoy this latest addition to Google Apps Script — Ryan Boyd
Starting today, the AdSense Management API is available as part of AdSense Services in Google Apps Script. This means that you’ll be able to do things like:
Accessing the API from Google Apps Scripts is very easy. The following snippet of code shows how to generate a report and populate columns of a spreadsheet with the data retrieved:
function generateReport() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Reports'); var startDate = Browser.inputBox( "Enter a start date (format: 'yyyy-mm-dd')"); var endDate = Browser.inputBox( "Enter an end date (format: 'yyyy-mm-dd')"); var args = { 'metric': ['PAGE_VIEWS', 'AD_REQUESTS', 'MATCHED_AD_REQUESTS', 'INDIVIDUAL_AD_IMPRESSIONS'], 'dimension': ['MONTH']}; var report = AdSense.Reports.generate(startDate, endDate, args).getRows(); for (var i=0; i<report.length; i++) { var row = report[i]; sheet.getRange('A' + String(i+2)).setValue(row[0]); sheet.getRange('B' + String(i+2)).setValue(row[1]); sheet.getRange('C' + String(i+2)).setValue(row[2]); sheet.getRange('D' + String(i+2)).setValue(row[3]); sheet.getRange('E' + String(i+2)).setValue(row[4]); } }
function generateLineChart() { var doc = SpreadsheetApp.getActiveSpreadsheet(); var startDate = Browser.inputBox( "Enter a start date (format: 'yyyy-mm-dd')"); var endDate = Browser.inputBox( "Enter an end date (format: 'yyyy-mm-dd')"); var adClientId = Browser.inputBox("Enter an ad client id"); var args = { 'filter': ['AD_CLIENT_ID==' + adClientId], 'metric': ['PAGE_VIEWS', 'AD_REQUESTS', 'MATCHED_AD_REQUESTS', 'INDIVIDUAL_AD_IMPRESSIONS'], 'dimension': ['MONTH']}; var report = AdSense.Reports.generate(startDate, endDate, args).getRows(); var data = Charts.newDataTable() .addColumn(Charts.ColumnType.STRING, "Month") .addColumn(Charts.ColumnType.NUMBER, "Page views") .addColumn(Charts.ColumnType.NUMBER, "Ad requests") .addColumn(Charts.ColumnType.NUMBER, "Matched ad requests") .addColumn(Charts.ColumnType.NUMBER, "Individual ad impressions"); // Convert the metrics to numeric values. for (var i=0; i<report.length; i++) { var row = report[i]; data.addRow([row[0],parseInt(row[1]),parseInt(row[2]), parseInt(row[3]),parseInt(row[4])]); } data.build(); var chart = Charts.newLineChart() .setDataTable(data) .setTitle("Performances per Month") .build(); var app = UiApp.createApplication().setTitle("Performances"); var panel = app.createVerticalPanel() .setHeight('350') .setWidth('700'); panel.add(chart); app.add(panel); doc.show(app); }
When it comes to writing UI applications in Apps Script, we get a lot of requests to support event callbacks that are handled in the user’s browser. For example, if your application has a form, you may want to disable a button after it is clicked the first time. Until now, the only way to do that would be by using an event handler on the server to disable that button. Using Client Handlers, your application can now respond to events in the browser without the need to perform a round trip to Google Apps Script servers.
By cutting out the round trip to the server, your app can respond instantly to user input. Imagine, for example, you want to provide your users with instant feedback within your app when a user types text where a number is expected. Ideally, you would want to warn users as they type the value, instead of waiting until the form is submitted. Having a server event handler for each keystroke is definitely overkill for such a simple and common task. Luckily, these use cases are now supported with Apps Script’s new Client Handlers and validators!
Let’s take a look at some code.
A Client Handler allows you to react to any event in a browser without connecting to the server. What you can do in response to an event is limited to a set of predefined common actions, but you have a lot of flexibility in making your app more responsive.
You can use Client Handlers in any UiApp regardless of whether you are embedding in a Spreadsheet or a Sites Page or publishing as a service. This simple application enables the user to click a button to display the classic “Hello world” message:
function doGet() { var app = UiApp.createApplication(); var button = app.createButton("Say Hello"); // Create a label with the "Hello World!" text and hide it for now var label = app.createLabel("Hello World!").setVisible(false); // Create a new handler that does not require the server. // We give the handler two actions to perform on different targets. // The first action disables the widget that invokes the handler // and the second displays the label. var handler = app.createClientHandler() .forEventSource().setEnabled(false) .forTargets(label).setVisible(true); // Add our new handler to be invoked when the button is clicked button.addClickHandler(handler); app.add(button); app.add(label); return app; }
The Client Handlers in the above example are set up in two steps:
forTargets
forEventSource
In the above example, we set the handler’s target to be the event source, so that it will apply to the button that is clicked. Finally, we define the action that the handler should take, in this case disabling the button using setEnabled(false). Aside from setEnabled, you can also change styles using setStyleAttribute, change text using setText, and so on. One Client Handler can perform multiple actions — just chain them together - and you can even change the target so that some actions apply to one set of widgets and some actions to another set. In our example, along with disabling the button, we set the handler to display the label when it is invoked, using setVisible.
setEnabled(false)
setEnabled
setStyleAttribute
setText
setVisible
Another new addition to Apps Script is support for validators in handlers. Validators allow handlers to check simple and complex conditions before they are invoked. For example, the following application adds two numbers given by the user, while using validators to make sure the server is only called if both of the text boxes contain numbers.
function doGet() { var app = UiApp.createApplication(); // Create input boxes and button var textBoxA = app.createTextBox().setId('textBoxA').setName('textBoxA'); var textBoxB = app.createTextBox().setId('textBoxB').setName('textBoxB'); var addButton = app.createButton("Add"); // Create a handler to call the adding function // Two validations are added to this handler so that it will // only invoke 'add' if both textBoxA and textBoxB contain // numbers var handler = app.createServerClickHandler('add') .validateNumber(textBoxA) .validateNumber(textBoxB) .addCallbackElement(textBoxA) .addCallbackElement(textBoxB); addButton.addClickHandler(handler) app.add(textBoxA); app.add(textBoxB); app.add(addButton); return app; } function add(e) { var app = UiApp.getActiveApplication(); var result = parseFloat(e.parameter.textBoxA) + parseFloat(e.parameter.textBoxB); var newResultLabel = app.createLabel("Result is: " + result); app.add(newResultLabel); return app; }
There’s a variety of validators to choose from that perform different tasks. You can verify the input to be a number, an integer, or an e-mail address. You can check for a specific length, or for any numerical value in a defined range. You can also use general regular expressions. Lastly, each validator has its negation.
Note that validators work with both client and server handlers.
Of course, validators and Client Handlers work best together. For example, in our addition application above, the “Add” button should be disabled as long as the current input is not numeric. We would also like to let the user know why the button is disabled by displaying an error message. To do so, we combine the power of server handlers, Client Handlers, and validators in the following way:
function doGet() { var app = UiApp.createApplication(); // Create input boxes and button. var textBoxA = app.createTextBox().setId('textBoxA').setName('textBoxA'); var textBoxB = app.createTextBox().setId('textBoxB').setName('textBoxB'); var addButton = app.createButton("Add").setEnabled(false); var label = app.createLabel("Please input two numbers"); // Create a handler to call the adding function. // Two validations are added to this handler so that it will // only invoke 'add' if both textBoxA and textBoxB contain // numbers. var handler = app.createServerClickHandler('add') .validateNumber(textBoxA) .validateNumber(textBoxB) .addCallbackElement(textBoxA) .addCallbackElement(textBoxB); // Create handler to enable the button well all input is legal var onValidInput = app.createClientHandler() .validateNumber(textBoxA) .validateNumber(textBoxB) .forTargets(addButton).setEnabled(true) .forTargets(label).setVisible(false); // Create handler to mark invalid input in textBoxA and disable the button var onInvalidInput1 = app.createClientHandler() .validateNotNumber(textBoxA) .forTargets(addButton).setEnabled(false) .forTargets(textBoxA).setStyleAttribute("color", "red") .forTargets(label).setVisible(true); // Create handler to mark the input in textBoxA as valid var onValidInput1 = app.createClientHandler() .validateNumber(textBoxA) .forTargets(textBoxA).setStyleAttribute("color", "black"); // Create handler to mark invalid input in textBoxB and disable the button var onInvalidInput2 = app.createClientHandler() .validateNotNumber(textBoxB) .forTargets(addButton).setEnabled(false) .forTargets(textBoxB).setStyleAttribute("color", "red") .forTargets(label).setVisible(true); // Create handler to mark the input in textBoxB as valid var onValidInput2 = app.createClientHandler() .validateNumber(textBoxB) .forTargets(textBoxB).setStyleAttribute("color", "black"); // Add all the handlers to be called when the user types in the text boxes textBoxA.addKeyUpHandler(onInvalidInput1); textBoxB.addKeyUpHandler(onInvalidInput2); textBoxA.addKeyUpHandler(onValidInput1); textBoxB.addKeyUpHandler(onValidInput2); textBoxA.addKeyUpHandler(onValidInput); textBoxB.addKeyUpHandler(onValidInput); addButton.addClickHandler(handler); app.add(textBoxA); app.add(textBoxB); app.add(addButton); app.add(label); return app; } function add(e) { var app = UiApp.getActiveApplication(); var result = parseFloat(e.parameter.textBoxA) + parseFloat(e.parameter.textBoxB); var newResultLabel = app.createLabel("Result is: " + result); app.add(newResultLabel); return app; }
All of these features can be used to create more advanced and responsive applications. Client handlers can be used to change several attributes for widgets, and validators can help you check a variety of different conditions from well formed email addresses to general regular expressions.
If you'd like to chat about these new features or have other questions about Google Apps Script, please join several members of the Apps Script team in the Google Apps Developer Office Hours on Google+ Hangouts tomorrow, Wednesday November 16th at 10am PST. You can also ask questions at any time in the Apps Script forum.
Sometimes you want to cache data in your script. For example, there’s a RSS feed you want to use and a UiApp that you’ve built to view and process the feed. Up until now, each operation to work on the feed would require re-fetching the feed, which can get slow.
UiApp
Enter the newly launched CacheService which will allow for caching resources between script executions. Like the recently announced LockService, there are two kinds of caches: a public cache that is per-script, and a private cache which is per-user, per-script. The private cache should be used to store user-specific data, while the public cache is used to store strings that should be accessible no matter who calls the script.
So for our example feed viewer/processor, you’d already have a function to retrieve and process the feed. In order to use the CacheService, you’d wrap it like this:
function getFeed() { var cache = CacheService.getPublicCache(); var value = cache.get(“my rss feed”); if (value == null) { // code to fetch the contents of the feed and store it in value // here (assumes value is a string) // cache will be good for around 3600 seconds (1 hour) cache.put(“my rss feed”, value, 3600); } return value; }
The cache doesn’t guarantee that you won’t have to fetch it again sooner, but will make a best effort to retain it for that long, and expire it quickly after the time passes. Now you can call getFeed() often and it won’t re-fetch the feed from the remote site on each script execution, resulting in improved performance.
getFeed()
Check out the CacheService documentation for more information.
Here’s the scenario: you create a form, you have a script that triggers onFormSubmit and all is well... until it gets popular. Occasionally you start having interlacing modifications from separate invocations of your script to the spreadsheet. Clearly, this kind of interlacing is not what you intended for the script to do. Up until now, there was no good solution to this problem -- except to remain unpopular or just be lucky. Neither are great solutions.
onFormSubmit
Now, my friend, you are in luck! We’ve just launched the LockService to deal with exactly this problem. The LockService allows you to have only one invocation of the script or portions thereof run at a time. Others that would’ve run at the same time can now be made to wait nicely in line for their turn. Just like the line at the checkout counter.
The LockService can provide two different kinds of locks-- one that locks for any invocation of your script, called a public lock, and another that locks only invocations by the same user on your script, called a private lock. If you’re not sure, using a public lock is the safest bet.
For example, in the scenario in the previous paragraph you would want something like this:
function onFormSubmit() { // we want a public lock, one that locks for all invocations var lock = LockService.getPublicLock(); lock.waitLock(30000); // wait 30 seconds before conceding defeat. // got the lock, you may now proceed ...whatever it used to do here.... lock.releaseLock(); }
It’s best to release the lock at the end, but if you don’t, any locks you hold will be released at the end of script execution. How long should you wait? It depends on two things mainly: how long the thing you’re going to do while holding the lock takes, and how many concurrent executions you expect. Multiply those two and you’ll get your timeout. A number like 30 seconds should handle a good number of cases. Another way to pick the number is frankly to take an educated guess and if you guess too short, the script will occasionally fail.
If you want to avoid total failure if you can’t get the lock, you also have the option trying to get the lock and doing something else in the event of not being able to get it:
function someFunction() { var lock = LockService.getPublicLock(); if (lock.tryLock(30000)) { // I got the lock! Wo000t!!!11 Do whatever I was going to do! } else { // I couldn’t get the lock, now for plan B :( GmailApp.sendEmail(“admin@example.com”, “epic fail”, “lock acquisition fail!”); } }
So now your scripts can be as popular as they can get with no worries about messing up shared resources due to concurrent edits! Check out the LockService documentation for more information.
Update (August 2014): Try the Yet Another Mail Merge add-on for Google Sheets.
Editor’s Note: This blog post is co-authored by James, Steve and Romain who are Google Apps Script top contributors. -- Ryan Boyd
The Google Apps Script team is on a roll and has implemented a ton of new features in the last few months. Some of us “Top Contributors” thought it will be a useful exercise to revisit the Mail Merge use case and discuss various ways in which we can do Mail Merge using Apps Script. Below are several techniques that tap into the power of Google Apps Script by utilizing Gmail, Documents and Sites to give your mailings some zing. Mail Merge is easy and here is how it can be done.
The Simple Mail Merge tutorial shows an easy way to collect information from people in a Spreadsheet using Google Forms then generate and distribute personalized emails. In this tutorial we learn about using “keys,” like ${"First Name"}, in a template text document that is replaced by values from the spreadsheet. This Mail Merge uses HTML saved in the “template” cell of the spreadsheet as the content source.
${"First Name"}
The Gmail Service is now available in Google Apps Script, allowing you to create your template in Gmail where it is saved as a draft. This gives us the advantage of making Mail Merge more friendly to the typical user who may not know or care much about learning to write HTML for their template. The mail merge script will replace the draft and template keys with names and other information from the spreadsheet and automatically send the email.
To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Yet another Mail Merge” and you will be able to locate the script. Then, click Install. You’ll get two authorization dialogs, click OK through them. Add your contact list to the spreadsheet, with a header for each column. Then compose a new mail in Gmail. Follow this syntax for the “keys” in your template: $%column header% (see above). Click Save now to save your draft. Go back to your spreadsheet and click on the menu Mail Merge. A dialog pops up. Select your draft to start sending your emails.
$%column header%
You can add CCs, include attachments and format your text just as you would any email. People enjoy “Inserting” images in the body of their emails, so we made sure to keep this feature in our updated mail merge. To automate this process we will use a new advanced parameter of the method sendEmail, inlineImages. When the script runs it looks in the email template for images and make sure they appear as inline images and not as attachments. Now your emails will look just as you intended and the whole process of mail merge got a whole lot simpler.
sendEmail
inlineImages
The next Mail Merge will use a template that is written in a Google Document and sent as an attachment. Monthly reports, vacation requests and other business forms can use this technique. Even very complex documents like a newsletter or brochure can utilize the automation of Google Apps Script to add the personal touch of having your patron’s name appear as a salutation.
Like in the Mail Merge for Gmail, the Google Docs template will use “keys” as placeholders for names, addresses or any other information that needs to be merged. Google Apps Script can add dynamic elements as well. For example you may want to include a current stock quote using the Financial Service, a chart from the Charts Service, or a meeting agenda automatically fetched for you by the Calendar Service.
As the code sample below demonstrates, the Google Apps Script gets the document template, copies it in a new temporary document, opens the temp document, replaces the key placeholders with the form values, converts it to PDF format, composes the email, sends the email with the attached PDF and deletes the temp document.
Here is a code snippet example to get you started. To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Employee of the Week Award” and you will be able to locate the script.
// Global variables docTemplate = “enter document ID here”; docName = “enter document name here”; function sendDocument() { // Full name and email address values come from the spreadsheet form var full_name = from-spreadsheet-form var email_address = from-spreadsheet-form // Get document template, copy it as a new temp doc, and save the Doc’s id var copyId = DocsList.getFileById(docTemplate) .makeCopy(docName+' for '+full_name) .getId(); var copyDoc = DocumentApp.openById(copyId); var copyBody = copyDoc.getActiveSection(); // Replace place holder keys, copyBody.replaceText('keyFullName', full_name); var todaysDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"); copyBody.replaceText('keyTodaysDate', todaysDate); // Save and close the temporary document copyDoc.saveAndClose(); // Convert temporary document to PDF by using the getAs blob conversion var pdf = DocsList.getFileById(copyId).getAs("application/pdf"); // Attach PDF and send the email MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf}); // Delete temp file DocsList.getFileById(copyId).setTrashed(true); }
For the last example let’s assume you have a great Google Site where you create new letters for your followers. However, you have had some feedback suggest that while many users don’t mind visiting your site, some would prefer to have the newsletter emailed to them. Normally this would require copying and pasting into an email or doc. Why not simply automate this with Google Apps Script?
The body section of a site, the part you edit, can be captured as HTML by the Sites Service and placed in the body of an email. Because the return value is HTML, the pictures and text formatting come through in the email.
Here is a simple example for you to try out:
function emailSiteBody() { var site = SitesApp.getPageByUrl('YourPageURL'); var body = site.getHtmlContent(); MailApp.sendEmail('you@example.com', 'Site Template', 'no html :( ', {htmlBody: body}); }
It really is that simple. Add a for loop with email values from a spreadsheet and this project is done.
for
Happy merging!
Updated 10/28: fixed instructions for accessing the complete script source for solution 3.
“@FOOD”
“@WEIGH”
Editor’s Note: This post written by Ferris Argyle. Ferris is a Sales Engineer with the Enterprise team at Google, and had written fewer than 200 lines of JavaScript before beginning this application. --Ryan Boyd
I started with Apps Script in the same way many of you probably did: writing extensions to spreadsheets. When it was made available in Sites, I wondered whether it could meet our needs for gathering roadmap input from our sales engineering and enterprise deployment teams.
At Google, teams like Enterprise Sales Engineering and Apps Deployment interact with customers and need to share product roadmap ideas to Product Managers. Product Managers use this input to iterate and make sound roadmap decisions. We needed to build a tool to support this requirement. Specifically, this application would be a tool used to gather roadmap input from enterprise sales engineering and deployment teams, providing a unified way of prioritizing customer requirements and supporting product management roadmap decisions. We also needed a way to share actual customer use cases from which these requirements originated.
This required bringing together the capabilities of Google Forms, Spreadsheets and Moderator in a single application: form-based user input, dynamically generated structured lists, and ranking.
This sounds like a fairly typical online transaction processing (OLTP) application, and Apps Script provides rich and evolving UI services, including the ability to create grids, event handlers, and now a WYSIWYG GUI Builder; all we needed was a secure, scalable SQL database backend.
One of my geospatial colleagues had done some great work on a demo using a Fusion Tables backend, so I did a little digging and found this example of how to use the APIs in Apps Script (thank you, Fusion Tables Developer Relations).
Full sample code for this app is available and includes a test harness, required global variables, additional CRUD wrappers, and authorization and Fusion REST calls. It has been published to the Script Gallery under the title "Using Fusion Tables with Apps Script."
/** * Read records * @param {string} tableId The Id of the Fusion Table in which the record will be created * @param {string} selectColumn The Fusion table columns which will returned by the read * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists * @param {string} whereValue The value to search for in the Fusion Table selectColumn; can be '*' * @return {string} An array containing the read records if no error; the bubbled return code from the Fusion query API if error */ function readRecords_(tableId, selectColumn, whereColumn, whereValue) { var query = ''; var foundRecords = []; var returnVal = false; var tableList = []; var row = []; var columns = []; var rowObj = new Object(); if (whereValue == '*') { var query = 'SELECT '+selectColumn+' FROM '+tableId; } else { var query = 'SELECT '+selectColumn+' FROM '+tableId+ ' WHERE '+whereColumn+' = \''+whereValue+'\''; } var foundRecords = fusion_('get',query); if (typeof foundRecords == 'string' && foundRecords.search('>> Error')>-1) { returnVal = foundRecords.search; } else if (foundRecords.length > 1 ) { //first row is header, so use this to define columns array row = foundRecords[0]; columns = []; for (var k = 0; k < row.length; k++) { columns[k] = row[k]; } for (var i = 1; i < foundRecords.length; i++) { row = foundRecords[i]; if( row.length > 0 ) { //construct object with the row fields rowObj = {}; for (var k = 0; k < row.length; k++) { rowObj[columns[k]] = row[k]; } //start new array at zero to conform with javascript conventions tableList[i-1] = rowObj; } } returnVal = tableList; } return returnVal; }
Now all I needed were CRUD-type (Create, Read, Update, Delete) Apps Script wrappers for the Fusion Tables APIs, and I’d be in business. I started with wrappers which were specific to my application, and then generalized them to make them more re-usable. I’ve provided examples above so you can get a sense of how simple they are to implement.
The result is a dynamically scalable base layer for OLTP applications with the added benefit of powerful web-based visualization, particularly for geospatial data, and without the traditional overhead of managing tablespaces.
I’m a Fusion tables beginner, so I can’t wait to see what you can build with Apps Script and Fusion Tables. You can get started here: Importing data into Fusion Tables, and Writing a Fusion Tables API Application.
Editor’s Note: This is a guest post by Saqib Ali. Saqib is a Google Apps evangelist at Seagate. He has used Apps Script to create a number of applications that leverage Google Apps. His other interests include the curation of Lewis Carroll’s letter and translation of Urdu poetry to English. -- Ryan Boyd
Idea Banks are repositories for innovative ideas that Seagate employees can submit, and others can vote on those ideas. Before Google Apps Script we had a custom built Idea Bank on the LAMP stack. With the release of the UI Services in the Google Apps Script, we wanted to port that Idea Bank to Google Apps to easily manage idea submissions in a Google Spreadsheet.
A typical Idea Bank consists of three basic functions:
A traditional application would probably use a Relational Database like MySQL to store the ideas. However we found that using Google Spreadsheet to store the ideas provides two inherent benefits:
The number of votes, and the voters are tracked using cells in the spreadsheet. For voters we used the Session.getUser().getEmail() to get the email address of the logged in user, and store them in the spreadsheet.
Session.getUser().getEmail()
Since the Ideas Bank is embedded in a Google Site, we were able to simply use the Google Sites Page as a place holder to add description and comments to the ideas. Once the idea is submitted, a Google Sites page gets created corresponding to that idea from predefined template using the createPageFromTemplace() function. The submitter can then add detailed description in the template. Others can add comments to that Site pages.
createPageFromTemplace()
All the data is stored in a Google Spreadsheet, which makes it easy for the Idea Bank manager to manage (delete, remove, modify) the ideas using the Spreadsheets Editor.
Code snippet for adding new ideas to the spreadsheet:
var ss = SpreadsheetApp.openById(""); // Spreadsheet id goes here SpreadsheetApp.setActiveSpreadsheet(ss); ideas_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ideas"); var last_row = ideas_sheet.getLastRow(); var next_empty_row = last_row+1; ideas_sheet.setActiveCell("A"+next_empty_row); ideas_sheet.getActiveCell().setValue(e.parameter.ideadescription); ideas_sheet.setActiveCell("B"+next_empty_row); ideas_sheet.getActiveCell().setValue(Session.getActiveUser().getUserLoginId()); ideas_sheet.setActiveCell("E"+next_empty_row); ideas_sheet.getActiveCell().setValue(Session.getActiveUser().getEmail());
Code snippet to read the ideas from the Spreadsheet and display them:
var ss = SpreadsheetApp.openById(""); // Spreadsheet id goes here SpreadsheetApp.setActiveSpreadsheet(ss); ideas_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ideas"); var last_row = ideas_sheet.getLastRow(); var last_column = ideas_sheet.getLastColumn(); var sheet_array = ideas_sheet.getRange(2, 1, last_row, last_column).getValues(); var submitIdeaButton = app.createButton("I have another idea"); var submitIdeaButtonHandler = app.createServerClickHandler('showSubmitIdeaDialog'); submitIdeaButton.addClickHandler(submitIdeaButtonHandler); applyCSS(submitIdeaButton, _submitideabutton); var ideaContents = app.createGrid().resize(last_row,3); ideaContents.setId("ideacontents"); ideaContents.setWidth("100%"); ideaContents.setCellSpacing(0); scrollPanel.add(ideaContents); app.add(scrollPanel); for (var row_i = 0; row_i < last_row-1; row_i++) { var ideaDescriptionLabel = app.createLabel(sheet_array[row_i][0]).setStyleAttribute("font","16px Sans-serif").setWordWrap(true); var submitter = sheet_array[row_i][1].split("@"); var ideaAuthor = app.createLabel(submitter[0]).setStyleAttribute("font","10px Courier New, Courier, monospace").setStyleAttribute("color", "#CCC") ideaContents.setWidget(row_i, 0, app.createVerticalPanel().add(ideaDescriptionLabel).add(ideaAuthor)).setStyleAttribute("overflow","visible").setStyleAttribute("white-space","normal !important"); //Button to display the voters var numberOfVotesForm = app.createFormPanel().setId('numofvotesform'); var numberOfVotesFormContent = app.createVerticalPanel() numberOfVotesForm.add(numberOfVotesFormContent); numberOfVotesFormContent.add(app.createTextBox().setName('ideaID').setText(row_i + "").setVisible(false).setSize("0","0")); numberOfVotesFormContent.add(app.createTextBox().setName('voters').setText(sheet_array[row_i][4]).setVisible(false).setSize("0","0")); var numberOfVotesButton = app.createButton(countVotes(sheet_array[row_i][4]) + " vote(s)").setId("numberOfVotesButton"+row_i); applyCSS(numberOfVotesButton, _numofvotesbutton); var numberOfVotesButtonHandler = app.createServerClickHandler('showVotersDialog'); numberOfVotesButtonHandler.addCallbackElement(numberOfVotesFormContent); numberOfVotesButton.addClickHandler(numberOfVotesButtonHandler); numberOfVotesFormContent.add(numberOfVotesButton); //Button to cast a vote var voteForm = app.createFormPanel().setId('voteform'); var voteFormContent = app.createVerticalPanel(); voteForm.add(voteFormContent); voteFormContent.add(app.createHidden('ideaID', row_i + "").setSize("0","0")) // Identify the function schedule as the server click handler var voteButton = app.createButton('I like this!').setId("voteButton"+row_i) var voteButtonHandler = app.createServerClickHandler('casteVote'); voteButtonHandler.addCallbackElement(voteFormContent); voteButton.addClickHandler(voteButtonHandler); if (sheet_array[row_i][4].indexOf(Session.getActiveUser().getEmail())>-1) voteFormContent.add(voteButton.setText("Thanks!").setEnabled(false)); else voteFormContent.add(voteButton); ideaContents.setWidget(row_i, 1, numberOfVotesForm); ideaContents.setWidget(row_i, 2, voteForm); } app.add(submitIdeaButton);
Ui Service was used to build the front end for the app. UI Services are based on GWT, so it is a good idea to have a basic understanding of the GWT framework. The following were used in building this app:
A real live working example is available here. Full source code is available here.
So why did I choose Google Apps Script? Well for one it is at no extra cost, comes with your Google Account, it is in the cloud (i.e. no servers required), integrates well with Google Sites and Spreadsheets, and most importantly it uses GWT UI widgets. Google Apps Script’s UI Services and the ability to easily integrate with any REST interface make Apps Script an easy choice.
Saqib is a Google Apps evangelist at Seagate. He has used Apps Script to create a number of applications that leverage Google Apps. His other interests include the curation of Lewis Carroll’s letter and translation of Urdu poetry to English.
function doGet() { // Populate the DataTable. We'll have the data labels in // the first column, "Quarter", and then add two data columns, // for "Income" and "Expenses" var dataTable = Charts.newDataTable() .addColumn(Charts.ColumnType.STRING, "Quarter") .addColumn(Charts.ColumnType.NUMBER, "Income") .addColumn(Charts.ColumnType.NUMBER, "Expenses") .addRow(["Q1", 50, 60]) .addRow(["Q2", 60, 55]) .addRow(["Q3", 70, 60]) .addRow(["Q4", 100, 50]) .build();
SpreadsheetApp
UrlFetch
// Build the chart. We'll make income green and expenses red // for good presentation. var chart = Charts.newColumnChart() .setDataTable(dataTable) .setColors(["green", "red"]) .setDimensions(600, 400) .setXAxisTitle("Quarters") .setYAxisTitle("$") .setTitle("Income and Expenses per Quarter") .build();
setDataTable()
build()
// Add our chart to the UI and return it so that we can publish // this UI as a service and access it via a URL. var ui = UiApp.createApplication(); ui.add(chart); return ui;}
// Save the chart to our Document List var file = DocsList.createFile(chart); file.rename("Income Chart"); file.addToFolder(DocsList.getFolder("Charts")); // Attach the chart to the active sites page. var page = SitesApp.getActivePage(); page.addHostedAttachment(chart, "Income Chart"); // Attach the chart to an email. MailApp.sendEmail( "recipient@example.com", "Income Chart", // Subject "Here's the latest income chart", // Content {attachments: chart });
var MARK_UNREAD = false; var ADD_UNSNOOZED_LABEL = false;
setup()
function getLabelName(i) { return "Snooze/Snooze " + i + " days"; } function setup() { // Create the labels we’ll need for snoozing GmailApp.createLabel("Snooze"); for (var i = 1; i <= 7; ++i) { GmailApp.createLabel(getLabelName(i)); } if (ADD_UNSNOOZED_LABEL) { GmailApp.createLabel("Unsnoozed"); } }
function moveSnoozes() { var oldLabel, newLabel, page; for (var i = 1; i <= 7; ++i) { newLabel = oldLabel; oldLabel = GmailApp.getUserLabelByName(getLabelName(i)); page = null; // Get threads in "pages" of 100 at a time while(!page || page.length == 100) { page = oldLabel.getThreads(0, 100); if (page.length > 0) { if (newLabel) { // Move the threads into "today’s" label newLabel.addToThreads(page); } else { // Unless it’s time to unsnooze it GmailApp.moveThreadsToInbox(page); if (MARK_UNREAD) { GmailApp.markThreadsUnread(page); } if (ADD_UNSNOOZED_LABEL) { GmailApp.getUserLabelByName("Unsnoozed") .addToThreads(page); } } // Move the threads out of "yesterday’s" label oldLabel.removeFromThreads(page); } } } }
Posted by John Gale, Solution Developer, Appogee
Want to weigh in on this topic? Discuss on Buzz
function doGet() { var app = UiApp.createApplication(); var component = app.loadComponent(“MyGui”); app.add(component); return app;}
loadComponent
app.getElementById()
var button = app.getElementById(“Button1”);button.setStyleAttribute(“color”, “red”);
function doGet() { var app = UiApp.createApplication(); var component = app.loadComponent(“MyGui”); app.add(component.getElementById(“Pane1l”)); return app;}
app.add(app.loadComponent(“MyGui”, {prefix: “a”})); app.add(app.loadComponent(“MyGui”, {prefix: “b”}));
sheet.show()
