Editor’s Note: Guest author Alex Moore is the CEO of Baydin, an email productivity company. --Arun Nagarajan
var d = new Date(); d.setDate(d.getDate() - DAYS_TO_SEARCH); var dateString = d.getFullYe ar() + "/" + (d.getMonth() + 1) + "/" + d.getDate(); threads = GmailApp.search("in:Sent after:" + dateString);
var userEmailAddress = Session.getEffectiveUser().getEmail(); var EMAIL_REGEX = /[a-zA-Z0-9\._\-]+@[a-zA-Z0-9\.\-]+\.[a-z\.A-Z]+/g; # if the label already exists, createLabel will return the existing label var label = GmailApp.createLabel("AwaitingResponse"); var threadsToUpdate = []; for (var i = 0; i < threads.length; i++) { var thread = threads[i]; var lastMessage = thread.getMessages()[thread.getMessageCount()-1]; lastMessageSender = lastMessage.getFrom().match(EMAIL_REGEX)[0]; if (lastMessageSender == userEmailAddress) { threadsToUpdate.push[thread]; } } label.addToThreads(threads)
In the last few months, we've added a number of new features to Google Apps Script, including add-ons for Sheets and Docs and 7 new advanced services.
We're eager to maintain that momentum — focusing on new features that help you do more with Google Apps. As a result, we're deprecating two Apps Script services for which good replacements exist elsewhere: ScriptDB (a NoSQL database that has been marked as experimental since it was introduced) and the Domain service (which encapsulates the GroupsManager, NicknameManager, and UserManager global objects).
GroupsManager
NicknameManager
UserManager
Both ScriptDB and the Domain service will be turned off on November 20, 2014.
Before then, you'll need to port any ScriptDB projects to another data store, like Google Cloud SQL or a third-party NoSQL database. We've created a migration guide that explains how to export your data from ScriptDB and suggests a few alternate data stores. We have also improved the documentation for connecting to external databases through JDBC to make it a little easier for you to set up Cloud SQL with Apps Script.
The Domain service, which only Google Apps domain administrators can use, is replaced by the recently added Admin SDK Directory and Admin SDK Reports advanced services. Those advanced services also provide many new features that the Domain service does not — like managing users' devices, OAuth tokens, and application-specific passwords — so we expect that you'll prefer using them in the future.
We've just announced Google Docs and Sheets add-ons — new tools created by developers like you that give Google users even more features in their documents and spreadsheets. Joining the launch are more than 60 add-ons that partners have built using Apps Script. Now, we're opening up the platform in a developer-preview phase. If you have a cool idea for Docs and Sheets users, we'd love to publish your code in the add-on store and get it in front of millions of users.
To browse through add-ons for Docs and Sheets, select Get add-ons in the Add-ons menu of any document or spreadsheet. (Add-ons for spreadsheets are only available in the new Google Sheets).
Docs and Sheets add-ons are powered by Google Apps Script, a server-side JavaScript platform that requires zero setup. Even though add-ons are in developer preview right now, the tools and APIs are available to everyone. The only restriction is on final publication to the store.
Once you have a great working prototype in Docs or Sheets, please apply to publish. Scripts that are distributed as add-ons gain a host of benefits:
Thanks to hard work from our developer partners, the add-ons in the store look and feel just like native features of Google Docs and Sheets. We're providing a couple of new resources to help all developers achieve the same visual quality: a CSS package that applies standard Google styling to typography, buttons, and other form elements, and a UI style guide that provides great guidance on designing a Googley user experience.
Add-ons are available in the new version of Google Sheets as a replacement for the older version's script gallery. If you have a popular script in the old gallery, now's a great time to upgrade it to newer technology.
We can't wait to see the new uses you'll dream up for add-ons, and we're looking forward to your feedback on Google+ and questions on Stack Overflow. Better yet, if you're free at noon Eastern time this Friday, join us live on YouTube for a special add-on-centric episode of Apps Unscripted.
Developers like you have built amazing scripts with Apps Script, and we want to make Apps Script even more useful. We've been working hard to add a variety of new APIs and features in Google Apps Script. Today, we're ready to share a few of them with you.
Named ranges are a popular feature of the Spreadsheet service; and now, you’ll be able to do something similar in the Document service. With named ranges, your scripts can tag a section of a Google Doc for later reference. For example, a bibliography script could set a named range on every citation in a document, then easily update the citations in the future.
You can use a similar API to manage bookmarks, too. Unlike named ranges, bookmarks are visible to the user and allow you to link to a particular place in a document.
Also for Google Docs, we've added the server-side methods setCursor(position) and setSelection(range) to change the user's cursor position or active selection, plus a client-side method, google.script.host.editor.focus(), which switches the browser focus from a sidebar or dialog back to the document.
setCursor(position)
setSelection(range)
google.script.host.editor.focus()
Oh, and hey: the Undo command in Google Docs can now revert changes made by a script.
If you've used HTML service much, you'll know that the Caja security sandbox has two modes. NATIVE mode imposes fewer restrictions than EMULATED mode and generally runs faster. As of today, NATIVE is now the default if you have not specified which mode your script should use. In a few edge cases, this may affect how existing web apps operate; if so, simply append .setSandboxMode(HtmlService.SandboxMode.EMULATED) to your HtmlOutput object to restore the old behavior.
NATIVE
EMULATED
.setSandboxMode(HtmlService.SandboxMode.EMULATED)
HtmlOutput
In preparation for future improvements to Apps Script, we've revamped script properties and user properties, combining them into a unified Properties service and adding the notion of document properties, which are (surprise!) specific to a particular document, but shared among all collaborators. The biggest change is that user properties are no longer shared between scripts, but our guide to the Properties service provides all the details. As part of the change, the old ScriptProperties and UserProperties services have been deprecated, although they will continue to function in existing scripts until a sunset date is announced.
We’re excited to bring you these new tools. With these new additions, we have also deprecated Finance service. It will remain available for the next six months but will be turned off on September 26, 2014.
Rails.application.config.middleware.use OmniAuth::Builder do provider :google_oauth2, ENV["GAM_OAUTH_KEY"], ENV["GAM_OAUTH_SECRET"] end
# Subclass the GoogleOauth2 Omniauth strategy for # Google Apps Marketplace V2 SSO. module OmniAuth module Strategies class GoogleAppsMarketplace < OmniAuth::Strategies::GoogleOauth2 option :name, 'google_apps_marketplace' end end end
Rails.application.config.middleware.use OmniAuth::Builder do provider :google_oauth2, ENV["OAUTH_KEY"], ENV["OAUTH_SECRET"], {:scope => ENV["OAUTH_SCOPE"]} provider :google_apps_marketplace, ENV["GAM_OAUTH_KEY"], ENV["GAM_OAUTH_SECRET"], { :scope => ENV["GAM_OAUTH_SCOPE"], :access_type => 'online' }end
Gmail.connect!(:xoauth, 'ben@example.com', { token: authentication.token, secret: authentication.secret, consumer_key: google.key, consumer_secret: google.secret, read_only: true })
key = Google::APIClient::PKCS12.load_key( google_apps.service.p12path, # this is a constant value Google uses # to password protect the key. 'notasecret' )service_account = Google::APIClient::JWTAsserter.new( google_apps.service.email, 'https://mail.google.com/', key )client = Google::APIClient.new( :application_name => APPLICATION_NAME, :version => APPLICATION_VERSION ).tap do |client| client.authorization = service_account.authorize('ben@example.com')end Google.connect!(:xoauth2, 'ben@example.com', { :oauth2_token => client.authorization.access_token, })
# Subclass the GoogleOauth2 Omniauth strategy for # Google Apps Marketplace V2 SSO. module OmniAuth module Strategies class GoogleAppsMarketplace < OmniAuth::Strategies::GoogleOauth2 option :name, 'google_apps_marketplace' def request_phase # Store the opensocial_viewer_id in the session. # this allows us to bind the Google Apps contextual # gadget to a user account. if request.params['opensocial_viewer_id'] session[:opensocial_viewer_id] = request.params['opensocial_viewer_id'] end super end end end end
Since we launched the Admin SDK in May 2013, we’ve been constantly improving it based on your feedback and feature requests. Our first API updates of 2014 bring some features at the top of your requests: push notifications for users, push notifications for activities, and structured user search.
Tired of polling user resources to detect changes? With push notifications for users, you can watch for changes to user resources and receive notifications from the Directory API whenever a watched user resource changes.
Check out our user notifications developer guide to learn how to improve the performances of your application by eliminating the extra network and compute costs involved with polling user resources.
Do you want to be notified when changes to a certain document occur or specific events such as the change of a user’s password happen? With push notifications for activities you can watch for changes to activities resources, and receive notifications from the Reports API whenever a watched activity resource changes.
Check out our activities notifications developer guide to learn how to receive notifications for the activities of your interest.
Do you want to know which users in your domain are members of a certain organization, or which users have a common manager in their reporting chain? The new query parameter we’ve just added to the users.list method of the Directory API allows you to perform rich queries over most attributes from the user profile.
For example, you can now retrieve all the users in the Human Resources org by using the query:
orgName='Human Resources'
manager='janesmith@example.com'
Check out our user search developer guide for a more comprehensive guide on how to build your queries and more examples of what you can do.
public void changeOwner(String user, String fileId, String newOwner) { // Find what is the current permission of the new owner on the file Permission newOwnerPermission = null; PermissionList permissionList = RetriableTask.execute(new DrivePermissionListTask(drive.permissions().list(fileId))); newOwnerPermission = findPermission(permissionList, newOwner); if (newOwnerPermission == null) { // New owner is not in the list, we need to insert it newOwnerPermission = new Permission(); newOwnerPermission.setValue(newOwner); newOwnerPermission.setType("user"); newOwnerPermission.setRole("owner"); Drive.Permissions.Insert insert = drive.permissions().insert(fileId, newOwnerPermission); RetriableTask.execute(new DrivePermissionInsertTask(insert)); } else { // New owner is already in the list, update the existing permission newOwnerPermission.setRole("owner"); Drive.Permissions.Update update = drive.permissions().update(fileId, newOwnerPermission.getId(), newOwnerPermission); update.setTransferOwnership(true); RetriableTask.execute(new DrivePermissionUpdateTask(update)); } }
public class RetriableTask implements Callable { [...] private final Callable task; [...] @Override public T call() { T result = null; try { startTime = System.currentTimeMillis(); result = task.call(); } catch (NonFatalErrorException e) { if (numberOfTriesLeft > 0) { // Wait some time, using exponential back-off in case of multiple attempts Thread.sleep(getWaitTime()); // Try again result = call(); } else { // Too many failed attempts: now this is a fatal error throw new RetryException(); } } catch (FatalErrorException e) { // This one should not be retried Throwables.propagate(e); } return result; }
// Launch user interface and allow user to select file IntentSender i = Drive.DriveApi .newOpenFileActivityBuilder() .setMimeType(new String[] { “text/plain” }) .build(mGoogleApiClient); startIntentSenderForResult(i, REQ_CODE_OPEN, null, 0, 0, 0);
One of things that makes Apps Script great is its ability to act as a hub for various types of Google data. In addition to our built-in services for popular products such as Gmail, Drive, Docs, and Calendar, we also provide a line of advanced Google services that let you use existing Google APIs such as Analytics and Tasks. Today, we're expanding that family of advanced services to include the following:
From Google Apps administrators and data-heads to Glass Explorers and YouTube content creators, this collection of new services has something for everyone. Getting started with advanced services is easy, since we take cake of the authorization for you and even provide autocomplete in the script editor.
While our built-in services are hand-crafted for ease-of-use, our advanced services are automatically generated from existing public Google APIs. They provide access to the full power of the underlying API but can be slightly more difficult to use. Let's look at some sample code that searches for YouTube videos with the keyword "dogs".
function searchByKeyword() { var part = 'id,snippet'; var optionalArgs = { q: 'dogs', maxResults: 25 }; var results = YouTube.Search.list(part, optionalArgs); for (var i = 0; i < results.items.length; i++) { var item = results.items[i]; Logger.log('[%s] Title: %s', item.id.videoId, item.snippet.title); } }
This function uses the YouTube.Search.list() method, which has a required parameter part and optional parameters q and maxResults, among others. Required parameters are passed individually as method arguments, while optional parameters are passed as one key-value map. The full list of parameters this method accepts can be found in the YouTube API's reference documentation.
YouTube.Search.list()
part
q
maxResults
We're also changing our advanced services to behave more like vanilla JavaScript, so that it's easier to reference the APIs' existing documentation. You can now pass native JavaScript objects into these services' methods, and access the results using regular dot-notation. Below is some sample code that adds a new user to a Google Apps domain.
function addUser() { var user = { primaryEmail: 'liz@example.com', name: { givenName: 'Elizabeth', familyName: 'Smith' }, // Generate a random password string. password: Math.random().toString(36) }; user = AdminDirectory.Users.insert(user); Logger.log('User %s created with ID %s.', user.primaryEmail, user.id); }
Notice that the user resource is constructed as a plain object literal, and the ID of the created user is accessed via dot-notation. The legacy getter/setter notation will continue to work but will no longer appear in autocomplete.
Finally, it's worth reminding that advanced Google services must be enabled in each script that uses them. This involves toggling them on once in the script editor under Resources > Advanced Google services and again in the associated Google Developers Console project.
We launched the Admin SDK in May as a new way for developers to build customized administrative tools for organizations that use Google Apps. A top priority for most administrators is keeping their users safe. Today, we're adding new security management features to the Directory API to help administrators manage:
As an example, FlashPanel, a popular tool used by Google Apps administrators, is using these new features to allow domain admins to review installed applications and manage or revoke access to them. The Apps Explorer in FlashPanel allows admins to see which are the most installed apps in his domain or use a filter to review applications by type of permissions (Drive, GMail, etc). It also allows admins to review the number of users who have granted access to a particular application.
The screenshot below shows an example of FlashPanel’s customized view of third-party application installs.
In FlashPanel’s integration, admins have the power to whitelist or blacklist apps, as shown below.
The Directory API now also provides the ability to manage admin notifications that are delivered to the Admin Console. Currently, admins receive notifications for events that affect their domains such as users approaching their storage limits or monthly bills that are due. Now you can use the API to list notifications, update their read status, or pull them into custom tools.
If you are interested in using these new endpoints, please refer to the Directory API documentation.
The APIs for three of Apps Script's advanced services — Analytics, BigQuery, and Prediction — will undergo breaking changes on Monday, November 18. If you don't update your code to the new syntax before then, you'll receive error messages such as Required parameter is missing.
Required parameter is missing
Advanced services allow you to easily connect to certain public Google APIs from Apps Script. We're working to expand and improve our advanced services, and as a side effect some methods and parameters that were incorrectly listed as optional are now required.
On November 18, these services will switch to use the new method signatures shown in the tables below. To learn how new arguments should be structured, refer to the documentation for the underlying API. For example, the documentation for the BigQuery service's Jobs.query() method shows the valid properties for the resource object in the "Request body" section of the page.
Jobs.query()
resource
Analytics.Management.Uploads
.deleteUploadData( accountId, webPropertyId, customDataSourceId, optionalArgs)
.deleteUploadData( resource, accountId, webPropertyId, customDataSourceId)
BigQuery.Datasets
.insert( resource, optionalArgs)
.insert( resource, projectId)
.update( resource, optionalArgs)
.update( resource, projectId, datasetId)
BigQuery.Jobs
.insert( resource, mediaData, optionalArgs)
.insert( resource, projectId, mediaData)
.query( projectId, query)
.query( resource, projectId)
BigQuery.Tabledata
.insertAll( projectId, datasetId, tableId, optionalArgs)
.insertAll( resource, projectId, datasetId, tableId)
BigQuery.Tables
.insert( resource, projectId, datasetId)
.update( resource, projectId, datasetId, tableId)
Prediction.Hostedmodels
.predict( project, hostedModelName, optionalArgs)
.predict( resource, project, hostedModelName)
Prediction.Trainedmodels
.insert( project, optionalArgs)
.insert( resource, project)
.predict( project, id, optionalArgs)
.predict( resource, project, id)
.update( project, id, optionalArgs)
.update( resource, project, id)
If you want to prepare your code ahead of time, you can add a try/catch around your existing code that retries with the new method signature if the old one fails. For example, the following sample applies this approach to the BigQuery service's Jobs.query() method:
try/catch
var result; try { result = BigQuery.Jobs.query(projectId, query, { timeoutMs: 10000 }); } catch (e) { // Refer to the BigQuery documentation for the structure of the // resource object. var resource = { query: query, timeoutMs: 1000 }; result = BigQuery.Jobs.query(resource, projectId); }
We apologize for inconvenience and look forward to sharing exciting news about advanced services in the coming weeks.
We launched Actions in the Inbox at Google I/O 2013 as a quick way for users to get things done directly from Gmail. Integrating with this technology only requires adding some markup to an email to define what the message is about and what actions the user can perform.
We support a variety of action types covering common scenarios such as adding a movie to a queue, product reviews, or other even pre-defined requests. Especially popular with senders is the One-Click Action to validate a user’s email address, as shown below:
If you are using Mandrill, the email infrastructure service from MailChimp, writing a Python app to send one of those emails, only takes a few lines of code! Take a look at this example:
import mandrill # Replace with your own values API_KEY = 'YOUR_API_KEY' FROM_ADDRESS = 'YOUR_FROM_ADDRESS' TO_ADDRESS = 'YOUR_TO_ADDRESS' SUBJECT = 'Please validate your email address' HTML_CONTENT = """ <html> <body> <script type='application/ld+json'> { "@context": "http://schema.org", "@type": "EmailMessage", "action": { "@type": "ConfirmAction", "name": "Confirm Registration", "handler": { "@type": "HttpActionHandler", "url": "https://mydomain.com/validate?id=abc123" } } } </script> <p>Please click on this link to validate your email address:</p> <p><a href="https://mydomain.com/validate?id=abc123">https://mydomain.com/validate?id=abc123</a></p> </body> </html> """ # Instantiate the Mandrill client with your API Key mandrill_client = mandrill.Mandrill(API_KEY) message = { 'html': HTML_CONTENT, 'subject': SUBJECT, 'from_email': FROM_ADDRESS, 'to': [{'email': TO_ADDRESS}], } result = mandrill_client.messages.send(message=message)
To run this app, just replace the API key with the credentials from your Mandrill account and configure the sender and recipient addresses. You should also edit the HTML content to provide your action handler URL, and customize the messaging if you want.
You can use Actions in the Inbox to reduce the friction and increase the conversion rate. Please note that you are not limited to validating email addresses: you can also review products and services, reply to event invitations, and much more.
For more information, please visit our documentation at https://developers.google.com/gmail/actions. You can also ask questions on Stack Overflow, with the tag google-schemas.
Apps Script started out as a simple tool to let developers add new features to Google Apps, but it’s grown into a programming platform that thousands of professional coders use every day. We hear a couple common requests from developers when they’re building complex projects with Apps Script: they want a full-featured IDE, and they want to sync to external version-control systems like GitHub.
Today, we’re introducing support for Apps Script in the Google Plugin for Eclipse. You can now sync with your existing Apps Script files on Google Drive, edit them in Eclipse — offline, if necessary, and with all the benefits of autocomplete — then write your code back to Drive so you can run it in the cloud. Because the plugin stores a copy of each script in a local workspace, you can manage Apps Script projects with your favorite version-control system.
Getting started is easy:
For step-by-step instructions on installation and use, see the documentation on using Apps Script with the Google Plugin for Eclipse.
Just in case you were wondering, the plugin uses the public Google Drive SDK to sync Apps Script files between your local file system and Google’s servers. We’ve previously covered the techniques it uses in our documentation on importing and exporting projects and the recent episode of Apps Script Crash Course on Google Developers Live below.
Last month, we announced several new ways to customize Google Forms. As of this week, three of those options are also available in forms created from Apps Script — embedding YouTube videos, displaying a progress bar, and showing a custom message if a form isn’t accepting responses.
Adding a YouTube video is as simple as any other Google Forms operation in Apps Script — from the Form object, just call addVideoItem(), then setVideoUrl(youtubeUrl). Naturally, you can also control the video’s size, alignment, and so forth.
Form
addVideoItem()
setVideoUrl(youtubeUrl)
To show a progress bar, call setProgressBar(enabled). Don’t even need a second sentence to explain that one. The custom message for a form that isn’t accepting responses is similarly easy: setCustomClosedFormMessage(message), and you’re done.
setProgressBar(enabled)
setCustomClosedFormMessage(message)
Want to give it a try yourself? Copy and paste the sample code below into the script editor at script.google.com, then hit Run. When the script finishes, click View > Logs to grab the URL for your new form, or look for it in Google Drive.
script.google.com
function showNewFormsFeatures() { var form = FormApp.create('New Features in Google Forms'); var url = form.getPublishedUrl(); form.addVideoItem() .setVideoUrl('http://www.youtube.com/watch?v=38H7WpsTD0M'); form.addMultipleChoiceItem() .setTitle('Look, a YouTube video! Is that cool, or what?') .setChoiceValues(['Cool', 'What']); form.addPageBreakItem(); form.addCheckboxItem() .setTitle('Progress bars are silly on one-page forms.') .setChoiceValues(['Ah, that explains why the form has two pages.']); form.setProgressBar(true); form.setCustomClosedFormMessage('Too late — this form is closed. Sorry!'); // form.setAcceptingResponses(false); // Uncomment to see custom message. Logger.log('Open this URL to see the form: %s', url); }
Security is a top priority for Google, just as it is for many of our Google Apps customers. As a domain administrator, a big part of keeping your users safe is knowing when and how they are using their accounts. Since we launched the Admin SDK Reports API in June, we've continued to add features to let you more easily visualize Google Apps' usage and security in your domain. These new features include:
Security Reports
Usage Reports
Refer to the Reports API documentation for more details on how to use the Reports API and to see what is possible with all of our entire Admin SDK.
Drive is a great drop zone for incoming files -- no matter if they’re coming from cameras, scanners, faxes, or countless other devices or apps. But throwing files into the root folder makes it difficult for users to find and organize their content.
I’ve seen developers create a folder when the user first connects the app with Drive to keep files organized by the app that created it. It’s a simple technique that is easy to implement and good default behavior for most applications.
With the Picker API, we can go a step further and offer users the choice of destinations too. For example, I’d like my scanner to put work files in one folder and personal files in another, or even configure multiple destinations depending on the type of document I’m scanning.
For this particular use case, the picker needs to be configured to show folders and only show folders. That requires customizing the DocsView just a little.
var docsView = new google.picker.DocsView() .setIncludeFolders(true) .setMimeTypes('application/vnd.google-apps.folder') .setSelectFolderEnabled(true);
By enabling folders & the ability to select them while simultaneously filtering out everything else, users can quickly and easily select one of their existing folders as a destination. The rest of the code to show the picker is par for the course.
// Handle user actions with the picker. var callback = function(data) { if (data.action == google.picker.Action.PICKED) { var doc = data.docs[0]; alert("You picked " + doc.id); } }; var picker = new google.picker.PickerBuilder() .addView(docsView) .setCallback(callback) .build(); picker.setVisible(true);
Not only is this easy to implement, it’s safer for users. By offloading this functionality to the Picker API, an app only needs the drive.file scope to write files into the user’s preferred location.
You can learn more about the Picker API at developers.google.com or ask questions at StackOverflow with the google-drive-sdk tag.
Google+ make it easy for Google Apps customers to connect and share within their organisation and encourage collaboration between teams. Today we’re launching an update to the Google+ Android app that includes a number of new features for Google Apps customers, and a new developer offering, the Google+ Domains API.
The Google+ Domains API allows Google Apps customers to integrate Google+ into their existing tools and processes, and allows enterprise software vendors to access Google+ from their products. Applications using the Google+ Domains API can act on behalf of Google Apps users to share posts within the same domain, comment on posts shared within the domain, and manage Circles. In addition, the Google+ Domains API enables Google Apps domain administrators to pre-populate the Circles of new employees, or review sharing activity.
For example, Ocado is building a tool that uses the Google+ Domains API to regularly sync team membership stored in Active Directory with the circles of their employees. This will ensure that every employee always has an up to date circle containing the other members of their team. Cloudlock is using the Google+ Domains API to add support for Google+ to its suite of data loss prevention, governance, and compliance applications.
Any developer can begin developing with the Google+ Domains API today. However only members of a Google Apps domain can use Google+ Domains API applications. To get started check out the documentation. If you have any questions, you can consult the google-plus tag on Stack Overflow, or join the “Developing with Google+” Google+ Community.
Google Apps Script is, first and foremost, a tool for making Google Apps more powerful — and today’s addition of programmatic control over data-validation rules in Google Sheets is a perfect example. For a quick demo, make a copy of this spreadsheet, then follow the instructions provided.
For the last few months, scriptable access to the data validation feature in Sheets has been the most requested feature on the Apps Script issue tracker. A common use for data-validation rules is to require that a cell’s value match one of the values in a different range. The following example shows how to achieve that goal with Apps Script. First, we use the newDataValidation() method to construct a DataValidationBuilder, then set the appropriate options and apply the final DataValidation with setDataValidation().
newDataValidation()
DataValidationBuilder
DataValidation
setDataValidation()
// Set the data-validation rule for cell A1 to require a value from B1:B10. var cell = SpreadsheetApp.getActive().getRange('A1'); var range = SpreadsheetApp.getActive().getRange('B1:B10'); var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range) .build(); cell.setDataValidation(rule);
It’s also possible to modify existing data-validation rules. The next example changes rules that require a date in 2013 to require a date in 2014 instead. You’ll see that the script calls getDataValidations() to retrieve the existing rules, then uses getCriteriaType(), getCriteriaValues(), and the DataValidationCriteria enum to examine the rules before applying the new date restriction via the advanced withCriteria() method.
getDataValidations()
getCriteriaType()
getCriteriaValues()
DataValidationCriteria
withCriteria()
// Change existing data-validation rules that require a date in 2013 // to require a date in 2014. var oldDates = [new Date('1/1/2013'), new Date('12/31/2013')]; var newDates = [new Date('1/1/2014'), new Date('12/31/2014')]; var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()); var rules = range.getDataValidations(); for (var i = 0; i < rules.length; i++) { for (var j = 0; j < rules[i].length; j++) { var rule = rules[i][j]; if (rule != null) { var criteria = rule.getCriteriaType(); var args = rule.getCriteriaValues(); if (criteria == SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN && args[0].getTime() == oldDates[0].getTime() && args[1].getTime() == oldDates[1].getTime()) { rules[i][j] = rule.copy().withCriteria(criteria, newDates).build(); } } } } range.setDataValidations(rules);
With this new feature in Apps Script, you should find it much easier to manage complex data-validation scenarios. Please keep the feature requests coming!
