Editor's note: Posted by Romain Vialard, a Google Developer Expert and developer of Yet Another Mail Merge, a Google Sheets add-on.
Yet Another Mail Merge is a Google Sheets add-on that lets users send multiple personalized emails based on a template saved as a draft in Gmail and data in a Google Sheet. It can send hundreds of emails, but this kind of operation usually takes a few minutes to complete. This raises the question: what should be displayed in the user interface while a function is running on server side for a long time?
Firebase is all about real-time and became the answer to that issue. Last December, the Apps Script team announced a better version of the HtmlService with far fewer restrictions and the ability to use external JS libraries. With Firebase, we now had a solution to easily store and sync data in real-time.
Combined, users are able to know, in real-time, the number of emails sent by an Apps Script function running server-side. When the user starts the mail merge, it calls the Apps Script function that sends emails and connects to Firebase at the same time. Every time the Apps Script function has finished sending a new email, it increments a counter on Firebase and the UI is updated in real-time, as shown in the following image.
Inside the loop, each time an email is sent (i.e. each time we use the method GmailApp.sendEmail()), we use the Apps Script UrlFetch service to write into Firebase using its REST API. Firebase's capabilities makes this easy & secure and there’s no need for an OAuth Authorization Flow, just a Firebase app secret, as shown in the following example:
function addNewUserToFirebase() { var dbUrl = "https://test-apps-script.firebaseio.com"; var secret = PropertiesService.getScriptProperties().getProperty("fb-secret"); var path = "/users/"; var userData = { romainvialard:{ firstName:"Romain", lastName:"Vialard", registrationDate: new Date() } }; var params = { method: "PUT", payload : JSON.stringify(userData) } UrlFetchApp.fetch(dbUrl + path + ".json?auth=" + secret, params); }
On the client side, thanks to the improved Apps Script HtmlService, we can use the official JS client library to connect to Firebase and retrieve the data stored previously. Specifically, the on() method in this library can be used to listen for data changes at a particular location in our database. So each time a new task is completed on server side (e.g. new email sent), we notify Firebase and the UI is automatically updated accordingly.
var fb = new Firebase("https://test-apps-script.firebaseio.com"); var ref = fb.child('users/' + UID + '/nbOfEmailsSent'); ref.on("value", function(data) { if (data.val()) { document.getElementById("nbOfEmailsSent").innerHTML = data.val(); } });
In addition to the example above, there are other places where Firebase can be useful in Google Apps Script add-ons.
Those are just a few examples of what you can do with Apps Script and Firebase. Don’t hesitate to try it yourself or install Yet Another Mail Merge to see a live example. In addition, there is a public Apps Script library called FirebaseApp that can help you start with Firebase; use it like any other standard Apps Script library.
For example, you can easily fetch data from Firebase using specific query parameters:
function getFrenchContacts() { var firebaseUrl = "https://script-examples.firebaseio.com/"; var base = FirebaseApp.getDatabaseByUrl(firebaseUrl); var queryParameters = {orderBy:"country", equalTo: "France"}; var data = base.getData("", queryParameters); for(var i in data) { Logger.log(data[i].firstName + ' ' + data[i].lastName + ' - ' + data[i].country); } }
Build your own add-ons via Google Apps Script. Check out the documentation (developers.google.com/apps-script) to get more information as well as try out the Quickstart projects there. We look forward to seeing your add-ons soon!
Romain Vialard profile | website
Romain Vialard is a Google Developer Expert. After some years spent as a Google Apps consultant, he is now focused on products for Google Apps users, including add-ons such as Yet Another Mail Merge and Form Publisher.
final RateLimiter driveApiRateLimiter = RateLimiter.create(QUOTA_RATE_PER_SECOND);
public Result performDriveApiCall(driveApiRateLimiter, otherParams){ driveApiRateLimiter.acquire(); // blocks according to rate // make API call...
1, 2, 4, 8, 16, 32, 60, 60, 60
ExponentialBackOff backoff = new ExponentialBackOff.Builder() .setInitialIntervalMillis(ONE_SECOND) .setMultiplier(2.0) .setMaxIntervalMillis(ONE_MINUTE) .setMaxElapsedTimeMillis(FIVE_MINUTES) .build();
1.04, 1.9, 4.23, 7.8, etc.
.98, 2.04, 4.1, 8.15, etc.
builder.setRandomizationFactor(RANDOMIZATION_FACTOR);
private HttpBackOffUnsuccessfulResponseHandler handler = new HttpBackOffUnsuccessfulResponseHandler(backoff); public void initialize(HttpRequest request){ request.setUnsuccessfulResponseHandler(handler); }
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
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; }
Editor’s Note: Guest author Niels Buekers is a Google Apps consultant at Capgemini Belgium. — Arun Nagarajan
During a recent Google Apps migration project, we received several requests to create custom groups of contacts so that users could more easily email frequent collaborators. Before switching to Google Apps, users created their own private distribution lists — but this approach led to overlapping groups that quickly fell out of sync.
The problem was a perfect case for Google Apps Script. We built a great solution that gives users as much power as possible with just a quick administrator review.
The situation before: either manually adding each contact or using a private contacts group.
To start the process, a user adds a specific label to a Gmail message. A script that runs on a timed trigger then generates a request to create a group for all the addresses in the message. The script writes this data to a spreadsheet that tracks group names and administrator approval.
/** * Retrieves all 'group_request' threads and creates a request. */ function processInbox() { // Get threads that have the group_request label. var groupRequestLabel = GmailApp.getUserLabelByName('group_request'); var threads = groupRequestLabel.getThreads(0, 10); // For each thread, retrieve all recipients and create a group request. for (var i = 0; i < threads.length; i++) { var firstMessage = threads[i].getMessages()[0]; var sender = firstMessage.getFrom(); var recipients = []; // Add sender. recipients.push(parseAddresses(sender)); // Add recipients. if (threads[i].getMessages()[0].getTo()) { var toRecipients = parseAddresses(firstMessage.getTo()); recipients.push(toRecipients); } // Add CCs. if (threads[i].getMessages()[0].getCc()){ var ccRecipients = parseAddresses(firstMessage.getCc()); recipients.push(ccRecipients); } // Write all recipients to a cell in the spreadsheet // and send emails to ask for group name and approval. createGroupRequestForRecipients(recipients, Session.getActiveUser().getEmail()); // Remove label from this thread now that it has been processed. threads[i].removeLabel(groupRequestLabel); } };
Once the request has been processed and written to the spreadsheet, the script sends the user an email that asks her to suggest a name for the group in an Apps Script web app. A second email asks the administrator to visit the web app to approve or decline the request. The results are again stored in the spreadsheet.
The spreadsheet contains a second script, which is triggered for each modification. Once the script confirms that the request has been approved, it uses the Apps Script Domain Service to create the new group.
/** * Creates a new group in the Google Apps cPanel with the provided name * and members. */ function createGroupWithAddresses(addresses,groupName){ var group = GroupsManager.createGroup(groupName, groupName, groupName, GroupsManager.PermissionLevel.DOMAIN); var splitAddresses = addresses.split(','); for (var i = 0; i < splitAddresses.length; i++) { Logger.log('Adding ' + splitAddresses[i]); group.addMember(splitAddresses[i]); } };
The result after successfully running the script.
This solution provides a simple way for users to request new Google groups, without all the overhead of manually creating an admin-managed distribution list.
Editor’s Note: Guest author Jason Gordon is a co-founder of Beth Macri Designs — Arun Nagarajan
Beth Macri Designs creates jewelry from the point of view of a structural engineer. The forms are designed using generative 3D software systems and materialized using 3D printing technologies. Our company understands that to make beautiful fine jewelry, 3D printing is only the first step; traditional jewelry craft is then employed for final production. After our first product, The Hidden Message Necklace, was recently featured on The View as part of its Valentine's Day Gift Guide, we had a lot of orders to ship out. As soon as the mail leaves the building, though, the process is literally out of our hands: something unexpected was bound to happen to at least one or two packages. Several package-tracking services exist, but getting the names and tracking numbers into them was a cut-and-paste operation.
I knew that all of the tracking numbers were being delivered by email and I had already set up a Gmail filter to archive them and apply a label. With a little help from Google Apps Script, I knew I could automatically parse those emails and add them to my account on PackageTrackr (which syncs to their newer service, Fara).
The script supports reading emails from multiple shipping providers and is set up so one could easily add more. Every 30 minutes on a time-driven trigger, using the Gmail service, the script runs and looks through unread emails from the shipping provider label, then parses the name and tracking number out of each one. The provider, tracking number, and recipient are stored in a JavaScript array.
function getUSPSConversations(){ return GmailApp.search("in:usps is:unread subject:(Click-N-Ship)"); } function matchUSPSHTML(data){ var out = []; var track_num = data.match( /TrackConfirmAction\Winput\.action\WtLabels\=(\d+)/g); var to = data.match(/Shipped.to.*[\r\n]*.*>([a-zA-Z\s-_]*)<br>/g); for(i in track_num){ var o = new Object(); var track = track_num[i].match(/(\d+)/g); var person = to[i].match(/>([a-zA-Z\s-_]+)<br>/); var myPerson = person[1].replace(/(\r\n|\n|\r)/gm,"") o["number"]=track[0]; o["carrier"]="USPS"; o["person"]=myPerson; out.push(o); } return out; }
You can parse all of your different shipping providers in one run of the script. After all of the shipment emails are read, it composes an email to PackageTrackr to give it all of the tracking numbers it just harvested.
var user = Session.getActiveUser().getEmail(); if(data.length > 0){ for(d in data){ body += this["formatForPackageTrackr"](data[d]["number"], data[d]["carrier"], data[d]["person"]); } GmailApp.sendEmail("track@packagetrackr.com", "Add Packages", body, {bcc: user}); } function formatForPackageTrackr(tracking_num, service, person){ return "#:" + tracking_num + " " + service + " " + person + "\n"; }
Down the line, other shipping providers could be added such as UPS and Fedex. Additionally, more tracking services could be added instead of just PackageTrackr.
Editor’s Note: Guest author Martin Hawksey is an advisor at the Jisc Centre for Educational Technology and Interoperability Standards. — Dan Lazin
When I started looking at Google Apps Script in 2010, one of the things that attracted me was the ease with which a non-developer like me could start customising Google Apps with only a few lines of code. Since then, the rich community of users and examples has continued to grow, and I’ve built event booking systems, entire student feedback solutions, and even integrated with Mozilla Open Badges.
Recently, Justin Marckel, the assistant principal at Cornatzer Elementary School in North Carolina, asked for help in modifying one of my existing Apps Script examples. Justin was recording teachers’ classroom activities using a Google Form, then manually copying and pasting data into separate spreadsheets for each teacher to review. Justin wanted to know whether there was a way for a Google Form to store the results in a master spreadsheet, then filter results to each teacher’s spreadsheet.
The basic pseudocode would be:
on form submit if teacher’s spreadsheet doesn’t exist, then create spreadsheet add teacher as viewer store id else get id open teacher’s spreadsheet copy values to teacher’s spreadsheet
Here’s a closer look at each of the steps.
Apps Script offers three triggers specific to Google Sheets: “on open,” “on edit,” and “on form submit.” Looking at the Understanding Events documentation, we can see that a form submit trigger gives us a few options for how to pull the submitted values out of the event parameter (usually called e). We can get the data as an array via e.values, a Range object via e.range, or a JavaScript object that pairs the form questions with the respondent’s answers via e.namedValues. In this project, the e.values array is most convenient, and it will look something like this:
['2010/03/12 15:00', 'bob@example.com', 'Bob', '27', 'Susan', '25']
First, though, we have to add the form-submission trigger. The user could add it manually from the script editor’s Resources menu, but in this case, let’s manage triggers programmatically:
function setup(){ if (ScriptApp.getScriptTriggers().length === 0) { ScriptApp.newTrigger('doOnFormSumbit') .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()) .onFormSubmit() .create(); } }
One of the big advantages Apps Script is that you’re automatically working in a Google-authenticated environment. The result is that you can programmatically create a new spreadsheet with one line of code, then add a teacher as a viewer in just one more line:
var newSS = SpreadsheetApp.create('Spreadsheet Name'); newSS.addViewer('email-address-of-teacher');
Writing data to a sheet requires more than a one-liner just because we need to specify which cells to write to. The Range.setValues() method expects a 2D array; because we’ve already retrieved the response to the form as an array, it’s easy to throw those values into a row of cells:
Range.setValues()
var destSS = SpreadsheetApp.openById(id); // open teacher spreadsheet var destSheet = destSS.getSheets()[0]; // grab first sheet var insertRow = destSheet.getLastRow() + 1; // next row to enter data destSheet.getRange(insertRow, 1, 1, e.values.length) .setValues([e.values]);
The completed project is here. The bulk of the form-submission handling (including error logging) happens in around 50 lines of code, and I was able to complete the project within an hour. Now Justin no longer needs to copy, paste, and set up separate spreadsheets, potentially saving him hours of work. Justin recently contacted me to say:
“We have successfully used our program over the past couple of months to provide teachers with meaningful and efficient feedback. It has been successful at several other schools as well, and I got word today that our school district is looking at adopting it as a district-wide tool.”
This is just one of a growing number of examples of how Google Apps Script is directly benefitting educators by allowing custom solutions with the security, convenience, and power of Google Apps.
Editor’s Note: Guest author Mark Showalter is a Senior Research Scientist at the SETI Institute. — Arun Nagarajan
In 2011 and 2012, while studying the region around Pluto with the Hubble Space Telescope, I discovered the dwarf planet’s fourth and fifth known moons. Like all new astronomical objects, they started out with rather prosaic names — “S/2011 (134340) 1” and “S/2012 (134340) 1”, or, for short, P4 and P5.
I soon found my inbox stuffed with hundreds of naming suggestions. With so much interest, it didn’t seem fair to leave the job to just a handful of scientists. Instead, we decided to let the public propose and vote on the names of Pluto’s moons.
We knew that the web servers at the SETI Institute, my research home, could never handle the bandwidth required for such a task. However, the Institute has built strong relationships with Google through our extensive use of G+, and our friends there were thrilled to let us use Google services for the demanding task. I asked my husband Frank Yellin, who works on the Gmail team, for help in setting up the forms and collecting the data. Google Forms and Google Sheets were obvious choices, but with the volume of contributions and votes we were expecting, we knew we’d need programmatic help checking for duplicate nominees, filtering out inappropriate names, and tallying the votes.
Frank is a longtime Java engineer, so he tried a Java solution first. As the votes started to pour in at the rate of several per second, however, it became clear that the program could barely keep pace. Votes were coming in almost as fast as they were being downloaded and tallied. In a panic, Frank realized it was time to learn Apps Script — in fact, time to learn JavaScript altogether.
With some help from his colleagues (“How do I split a string?” “How do I make a hash table?”), he turned the project around in a few hours. Processing that had taken tens of minutes using Java took mere seconds in Apps Script, since nothing but the results ever had to leave the data center.
We were right to be prepared. By the time we closed the write-in ballot, we had received 30,000 write-in nominees and more than 450,000 votes.
We are now using the results of the poll to support our proposal for the formal names of P4 and P5. That decision is currently in the hands of the International Astronomical Union. When the final decision is made, Pluto and Charon and Nix and Hydra will be joined by two more representatives of the ancient underworld.
Editor’s Note: Guest author Andrew Stillman is a teacher who works at New Visions for Public Schools, a non-profit that provides direct support services to 76 New York City high schools. — Arun Nagarajan
On March 16th, as a green tide tide of college students flowed into Manhattan for a day of rousing revelry, more than 50 young coders from New York-area computer science programs and 30 teachers were drawn instead to Kean University in New Jersey by the gravity of St. Hacktrick’s Day, our first Apps Script for EDU Codeathon. Inspired by the viral popularity of the Flubaroo, Doctopus, and autoCrat scripts for teachers, St. Hacktrick’s Day aimed to pair coders with educators to produce more free, smart tools for education.
Most of the student scripters were on their first day of spring break, making our huge turnout for this event all the more remarkable. Product designers — all working educators who took time out on a Saturday — traveled from as far north as Ulster County, NY and as far south as Virginia, while we had others who joined teams via G+ Hangouts from Singapore, Montreal, Vancouver, and London.
Unlike a typical hackathon, teams weren’t simply building their own ideas — instead, to ensure their scripts would be truly useful in the classroom, we solicited project proposals through a Google Moderator board. By the day of the event, we had 48 ideas with 187 votes from educators around the world.
In all, 17 teams built demo-ready prototypes in less than 6 hours of coding. The Apps Script team rounded up a few Nexus 7 tablets for the winners below and invited them to present their projects to the Google Docs engineering team:
Popular vote: Picture Prompt Generator Summary: Inserts kid-friendly pictures from Google Image Search into student documents. Elementary students then write stories based on the visual prompts. Design: Daniel Scibienski Code: Ashish Nandwani and Krutika Shah
Judges' choice: Plagiarism Detector Summary: Uses a similarity algorithm to rank Google Documents by originality. Design and code: Alice Lin, Basim Baig, and Jackie Wei (Stony Brook University)
Judges' choice: Unpivot Google Form Data Summary: Removes duplicates from Google Form data and transforms it for use in a pivot table. Design: Ron Turchyniak Code: Andrew Ireland, Sangwook Lee, and Steve Byung Park (Stony Brook University)
Teams have been asked to open-source their code and donate it to New Visions for Public Schools, the support organization I work for, and to consider improving their projects for use by educators everywhere. We’ll keep you posted as these resources become available.
Big thanks to our participants, to organizers Meredith Martin, Dave Zirkle, Daniel Scibienski, Emily Graves, Diana Potts, Lisa Thumann, Andrew Carle, and to Google’s Arun Nagarajan, Saurabh Gupta, and Zach Yeskel.
Editor’s Note: Guest author John Gale is a Solutions Developer at Appogee, a Google Cloud Service Partner. — Arun Nagarajan
Ever since we launched Appogee Leave — the first tool in the Google Apps Marketplace for tracking employees’ absences and time off — customers have been asking, “Can you support my native language?”
Our partners offered to help crowdsource the translation, but it was a challenge to know where to begin. We started by identifying a few needs:
With just a couple days’ effort in Google Apps Script, we created a complete application for crowd-sourced localization that handles each of those requirements. You can get a glimpse of the system in the screenshot below.
Source: Appogee
Let’s take a look at a few specific Apps Script tricks we used to make the whole thing work.
Like many Apps Script users, we store almost all of the data for our translation system in Google Sheets, including both the list of English terms we want to translate and users’ translations.
During testing, we found that if two users submitted translations at the same time, the spreadsheet wrote both sets of changes to the same place, causing us to lose one user’s updates. To solve this, we use Apps Script’s semaphore-based Lock Service. In the code below, a public lock ensures that a user has temporary exclusive use of the spreadsheet so that their correction is added even if another user also submits a correction.
function submit(e){ /* get the fields from the UI callback */ var incorrect = e.parameter.foreignWordIncorrectTxt; var correct = e.parameter.foreignWordCorrectTxt; var reason = e.parameter.reasonTxt; var lang = e.parameter.hiddenLang; /* validate the input; return the user a message if invalid */ /* open the spreadsheet */ var active_user_email = UserProperties.getProperty('user_email') || ""; var master_spreadsheet = SpreadsheetApp.openById(MASTER_SPREADSHEET_KEY); var correction_sheet = master_spreadsheet.getSheetByName('Corrections'); /* get a lock and update the spreadsheet */ var lock = LockService.getPublicLock(); lock.waitLock(30000); correction_sheet.appendRow([ lang, incorrect, correct, reason, active_user_email ]); SpreadsheetApp.flush(); lock.releaseLock(); /* reset the UI */ return reset(); }
You’ll note that this code opens the spreadsheet before obtaining a lock. At this point, we are only reading, not writing, and thus do not yet require a lock. We then tell Apps Script we are prepared to wait up to 30 seconds for our turn to lock the worksheet. On the rare occasion that a lock is not available within 30 seconds (usually because somebody else has an exclusive lock), the code throws an exception and stops execution.
Once we have acquired the lock, we quickly write the correction to the spreadsheet — including a call to SpreadsheetApp.flush() to ensure the data is written immediately — and release the lock.
SpreadsheetApp.flush()
Because the translations are stored in a spreadsheet along with information about who provided them, it’s easy to recognize our top contributors through a leaderboard. The leaderboard data is a good candidate for caching because it’s shown to a large number of people, but only changes when we receive new updates from top-ranking users.
Like the Lock Service described earlier, the Cache Service provides both public and private variants. The public cache is useful for storing data that should be available to all users, such as the leaderboard. The private cache is more appropriate for storing information about a user, such as the translations they have submitted so far.
Since the Apps Script cache can only store strings, complex objects must first be converted. Lucky for us, Apps Script provides JSON utilities that make this conversion easy, as shown in this example:
function getBoardData(){ var cache = CacheService.getPublicCache(); var leaderboard_data = cache.get('leaderboard_data'); if (leaderboard_data == null) { leaderboard_data = getTopTen(); cache.put('leaderboard_data', Utilities.jsonStringify(leaderboard_data), 3600); } else { leaderboard_data = Utilities.jsonParse(leaderboard_data); } return leaderboard_data; }
Our hope is that the leaderboard will encourage users to provide more translations by introducing some friendly competition.
Thanks to Google Apps Script and the techniques shown above, we built a powerful crowdsourcing translation system without unnecessary complexity or development effort. If you’d like to help translate Appogee Leave, we’d love to have your contribution.
Editor’s Note: Guest author Ronald Dahrs runs Forscale, an IT and project management company based in the Netherlands. -- Arun Nagarajan
Google Apps is well-suited for project management because it’s a cloud-based productivity suite that helps you and your team connect and get work done from anywhere on any device. Using Google Apps Script, we can push the capabilities even further to create advanced scheduling and management tools. A common tool in project management circles is the Gantt chart: a schedule of the tasks in the project and how they relate to each other over time.
The spreadsheet that generated that Gantt chart is available in the template gallery today. In this post, we’ll explore the basics of how the template works and explain a few of the Apps Script techniques that transform Google Sheets into such a powerful project management tool.
When you open the template, you’ll see stubs for each type of task, but the screenshot above shows an example of a slightly larger project plan — in fact, the same data used to generate the Gantt chart below.
The template’s sophisticated formulas rely on the structure of the table to enable schedule awareness and task dependencies. However, we still ensure that the user can rename, rearrange, or add columns by using a hidden header to identify each column. This diagram demonstrates the spreadsheet’s structure:
In Apps Script, we use the spreadsheet’s onEdit() event to monitor user interaction with the schedule portion of the spreadsheet and update the Gantt chart accordingly. The powerful JavaScript language does all the required summary calculations based on the provided dates and completion percentages.
onEdit()
We have also used Apps Script’s addMenu() method to build a custom menu that calls row-oriented functions like indenting tasks to get a so-called Work Breakdown Structure with summary tasks. If you just want to see an overview, the custom menu allows you to collapse tasks, which we accomplished through the hideRows() method.
addMenu()
hideRows()
For changes that do not trigger an onEdit() event (for example, clearing a row), the user can use the menu’s Refresh command to recalculate the schedule.
The template stores user preferences as Script Properties and offers an interactive user interface built in UiApp to change those settings:
Finally, to render the Gantt chart, we use cell background colors to visually group and highlight the appropriate cells. This creates the effect of a continuous calendar with clearly visible start and finish dates for each task.
var ganttColors = ganttRange.getBackgroundColors(); var ganttValues = ganttRange.getValues(); // update Gantt colors and values ganttRange.setBackgroundColors(ganttColors).setValues(ganttValues);
Editor’s Note: Guest author David Fothergill works at QueryClick, a search-engine marketing company based in the UK. — Eric Koleda
Working in Paid Search account management, I've often found tremendous wins from making reports more useful and efficient. Refining your analytics allows you to streamline your workflow, allowing more time for strategic and proactive thinking — and that's what we're paid for, not endless number-crunching.
The integration between Google Analytics and Apps Script has opened up lots of opportunities for me to make life easier through automation. In a recent blog post on my agency's website, I outlined how an automated report can quickly “heatmap” conversion rate by time and day. The aim of the report is to provide actionable analysis to inform decisions on day-part bidding and budget strategies.
In that post, I introduce the concepts and provide the scripts, sheet, and instructions to allow anyone to generate the reports by hooking the scripts up to their own account. Once the initial sheet has been created, the script only requires the user to provide a Google Analytics profile number and a goal for which they want to generate heatmaps. In this post, we’ll break down the code a bit.
This is a slight amendment to the code that queries the Core Reporting API. Apart from customising the optArgs dimensions to use day and hour stats, I have modified it to use goal data from the active spreadsheet, because not all users will want to measure the same goals:
function getReportDataForProfile(ProfileId, goalNumber) { //take goal chosen on spreadsheet and select correct metric var tableId = 'ga:' + ProfileId; if (goalNumber === 'eCommerce Trans.') { var goalId = 'ga:Transactions' ; } else { var goalId = 'ga:goal' + goalNumber + 'Completions'; } // Continue as per example in google documentation ... }
Once we’ve brought the Google Analytics data into the spreadsheet in raw form, we use a pivot table to plot the hour of the day against the day of the week.
For this type of report, I'd like to use conditional formatting to heatmap the data — but conditional formatting in Google Sheets is based on fixed values, whereas we want the thresholds to change based on cell values. However, thanks to the flexibility of scripts, I was able to achieve dynamic conditional formatting.
The script needs to know the boundaries of our data, so I’ve set up several cells that display the maximums, minimums, and so forth. Once these were in place, the next step was to create a function that loops through the data and calculates the desired background color for each cell:
function formatting() { var sheet = SpreadsheetApp.getActiveSpreadsheet(). getSheetByName('Heatmap'); var range = sheet.getRange('B2:H25'); range.setBackgroundColor('white'); var values = range.getValues() //get boundaries values for conditional formatting var boundaries = sheet.getRange('B30:B35').getValues(); //get range to 'heatmap' var backgroundColours = range.getBackgroundColors(); for (var i = 0; i < values.length; i++) { for (var j = 0; j < values[i].length; j++) { // Over 90% if (values[i][j] > boundaries[1][0]) { backgroundColours[i][j] = '#f8696b'; } // Between 80% and 90% if (values[i][j] < boundaries[1][0] && values[i][j] >= boundaries[2][0]) { backgroundColours[i][j] = '#fa9a9c'; } // Between 60% and 80% if (values[i][j] < boundaries[2][0] && values[i][j] >= boundaries[3][0]) { backgroundColours[i][j] = '#fbbec1'; } // Between 40% and 60% if (values[i][j] < boundaries[3][0] && values[i][j] >= boundaries[4][0]) { backgroundColours[i][j] = '#fcdde0'; } // Between 20% and 40% if (values[i][j] < boundaries[4][0] && values[i][j] >= boundaries[5][0]) { backgroundColours[i][j] = '#ebf0f9'; } // Less than 20% if (values[i][j] < boundaries[5][0]) { backgroundColours[i][j] = '#dce5f3'; } } } // set background colors as arranged above range.setBackgroundColors(backgroundColours); }
Calling the functions based on the profile ID and goal number specified in the main sheet gives us a quick, actionable report that can easily be adapted for use across multiple accounts.
function generateHeatmap() { try { var profileId = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName('Heatmap').getRange(4,10).getValue(); var goalNumber = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName('Heatmap').getRange(7,10).getValue(); if (profileId === '') { Browser.msgBox('Please enter a valid Profile ID'); } else { var results = getReportDataForProfile(profileId, goalNumber); outputToSpreadsheet(results); formatting(); } } catch(error) { Browser.msgBox(error.message); } }
This was my first foray into the slick integration between the Core Reporting API and spreadsheets, but has proven a valuable test case for how effective it will be to roll this method of reporting into our daily process of managing accounts.
We have now started the next steps, which involves building out “client dashboards” that will allow account managers access to useful reports at the press of a button. This moves us toward the goal of minimizing the time gathering and collating data, freeing it up to add further value to client projects.
Editor's Note: If you're interested in further scripting your AdWords accounts, take a look at AdWords Scripts, a version of Apps Script that's embedded right into the AdWords interface.
Editor’s Note: Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. -- Arun Nagarajan
Recently, when we were preparing the launch of GOV.UK, my team was tasked with creating a series of high-level metrics reports which could be quickly compiled and presented to managers without technical or analytical backgrounds. These reports would be sent daily to ministers and senior civil servants of several government departments, with the data customised for each department.
We decided to use Adobe InDesign to manage the visual appearance of the reports. InDesign’s data-merge functionality, which can automatically import external data into the layout, made it easy to create custom departmental reports. The challenge was to automate the data collection using the Google Analytics API, then organize the data in an appropriate format for InDesign’s importer.
In a previous post on this blog, Nick Mihailovski introduced a tool which allows automation of Google Analytics Reporting using Google Apps Script. This seemed an ideal solution because the team only had basic developer knowledge, much of the data we needed was not accessible from the Google Analytics UI, and some of the data required specific formatting prior to being exported.
We started by building the core reports in a Google spreadsheet that pulls in all of the required raw data. Because we wanted to create daily reports, the start and end dates for our queries referenced a cell which defaulted to yesterday’s date [=(TODAY())-1].
[=(TODAY())-1]
These queries were dynamically fed into the Google Analytics API through Apps Script:
// All variables read from each of the “query” cells var optArgs = { 'dimensions': dimensions, 'sort': sort 'segment': segment 'filters': filters, 'start-index': '1', 'max-results': '250' }; // Make a request to the API. var results = Analytics.Data.Ga.get( tableId, // Table id (format ga:xxxxxx). startDate, // Start-date (format yyyy-MM-dd). endDate, // End-date (format yyyy-MM-dd). endDate, // Comma seperated list of metrics. optArgs);
Next, we created additional worksheets that referenced the raw data so that we could apply the first stage of formatting. This is where storing the data in a spreadsheet really helps, as data formatting is not really possible in the Google Analytics UI.
For example, the final report had a 47-character limit for page titles, so we restricted the cells in the spreadsheet to 44 characters and automatically truncated long URLs by appending “...”.
Once the initial formatting was complete, we used formulas to copy the data into a summary sheet specially laid out so it could be exported as a CSV file that merges seamlessly into InDesign.
Below is an example of how a report looks on publication. Nearly everything on the page was extracted from the API tool, including the department name and the day number. Because most of the data was automated, it required minimal effort on our part to assemble these reports each morning.
We discovered that an added bonus of pulling data into a Google spreadsheet was that it also allowed us to publish the data to a Google site. This helped us display data to stakeholders without adding lots of users to our Google Analytics account.
The tools let us present Google Analytics data in deeper, more creative ways. That’s really important as we share information with more and more non-technical people, whether they’re inside GDS or beyond.
Editor’s Note: Guest author Romain Vialard works at Revevol, an international service provider dedicated to Google Apps and other Cloud solutions. -- Arun Nagarajan
There are many tools available to help you manage a task list and Google Apps comes with its own simple Tasks app. But sometimes it is more convenient and collaborative to simply manage your task list in a shared spreadsheet. This spreadsheet can be a simple personal task list or a project-management interface that requires team-wide coordination.
Google Spreadsheets come with a set of notification rules that might come in handy. For example, you can be notified each time someone adds a new task to the list or each time the status of a task is updated. Furthermore, it is very easy add to add basic reminders through Apps Script with just a few lines of code:
function remindMe() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var data = sheet.getDataRange().getValues(); for(var i = 1; i < data.length; i++){ if(data[i][2] > new Date()){ MailApp.sendEmail(message); } } }
The simple remindMe function performs a standard JavaScript-based date comparison on every row and sends an email for tasks that are due. You can then schedule the remindMe function via a programmable trigger based on the settings.
remindMe
This script is already available in the Script Gallery today. Try it out for yourself!
Once you have installed the script, you get a new menu option in the spreadsheet that opens a simple user interface to set the options you want. As a developer, you can extend this interface further to provide more options and capabilities.
Editor’s Note: Guest author Bruce McPherson is a contributor to the Excel Liberation website and blog. -- Eric Koleda
If you are new to Google Apps Script and the JavaScript programming language, migrating legacy automation code written in Microsoft's Visual Basic for Applications (VBA) can be a daunting task. This blog post describes a Google Apps Script library which mimics the behavior and calling structure of common VBA functions, allowing you to more easily convert your existing applications.
If you are planning to continue to use VBA, you can minimize both the work involved in maintaining the same capability in both platforms, and in porting from one to the other, by preserving backwards compatibility with VBA. This means breaking a few JavaScript conventions, but the result is worth it.
For example, JavaScript variables are normally written in lowerCamelCase, with classes being in UpperCamelCase. VBA is not case sensitive, and uses hungarian notation by convention, except for the built-in functions, which have a capitalized first letter. Since the objective here is to minimize change, I have decided to retain this capitalization for VBA functions replacements (for example CStr(), Mid() etc. ).
CStr()
Mid()
In VBA, indices (normally) start at 1, while in JavaScript they start at 0. Since these functions are to minimize change in application written in VBA, they also start at 1. For example Mid(x, 1, 2) means the first 2 characters of string x in both VBA and JavaScript versions.
Mid(x, 1, 2)
JavaScript does not have a collection object. The vEquivalents library provides an implementation of a collection class so that continuity for migrated code that relies on the collection can be maintained. But how to enumerate through that collection? There are a number of ways, but the forEach() method of the collection most closely resembles the 'For Each member in collection' approach VBA developers are familiar with. The syntax may seem a little fiddly at first, since it passes the code you want executed against each member of the collection as an anonymous function.
forEach()
var coll = new collection(); // by index for (var i=1; i <= coll.count() ;i++) { DebugPrint (coll.item(i)); } // by key for (k in coll.keys()) { DebugPrint (coll.item(k)); } // forEach coll.forEach( function (item, index) { DebugPrint (item, index); } );
With the great new libraries functionality, you can now include these scripts in your project by using the project key "MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j", or you can make a copy of the scripts directly to include in your own project. You will find a selection of other scripts in the library, but the VBA equivalents are all in the module vEquivalents. Note that as you include external libraries in your project (see here for how), you need to prefix the functions with the library identifier (for example mcpher.CStr())
mcpher.CStr()
You can access the documentation here, and you will see that most of the common VBA functions are included. Some examples are
var x = Trim(s); var x = Len(s); var a = Split(s); var x = Instr(1, s1, s2); var d = DateSerial(y, m, d); MsgBox(s); var x = InputBox(s); DebugAssert (b, s); var w = ActiveSheet();
Using the same approach, I have converted many other VBA utility classes and procedures built over time and added them to this library. This means that the implementation of something on either platform not only looks the same, but can be accomplished in hours or even minutes. For example, on my blog I publish a daily API, implemented in both VBA and Apps Script (both versions even use ScriptDB for the same parameter data). Here's a recent one.
function testUkPostcodes() { mcpher.generalDataSetQuery ("uk postcodes", "uk postcodes", "postcode"); } Public Sub testUkPostcodes() generalDataSetQuery "uk postcodes", "uk postcodes", "postcode" End Sub
You can find more projects that have been migrated this way here.
Editor’s Note: Guest author Romain Vialard works at Revevol, an international service provider dedicated to Google Apps and other Cloud solutions. -- Jan Kleinert
In a previous post, Improving Revevol’s Productivity with Google Apps Script, we demonstrated how Apps Script helped us handle a lot of training requests. For any given client, using tools we built with Google Apps Script, we are able to quickly find the perfect trainer depending on variables like the date, the place, the language and the training scope. To ensure that the training we do meets a consistent quality bar, we send a survey to all the participants at the end of the training. This post discusses how we use Google Apps to conduct these surveys to glean insight into the quality of our training.
We started our survey project by using simple Google Forms to poll our users. Each form creates a spreadsheet per language, each with thousands of submissions. From this data, we need to create visualizations to quickly make sense of all the information we gather. We want our international clients to each be presented with a unique dashboard for trainings in all their subsidiaries, our change managers to be able to see the results of any specific training to be sure that everything went well, and our trainers to see only the data they need.
We used Apps Script to tie all the pieces together to fulfill these requirements. We created a translation table in a spreadsheet to automate the translation of each survey, and persist the results using JSON two-dimensional arrays in a spreadsheet cell. Using this data, we present a web based front-end to show several charts and bring controls to filter the data in many ways. Each client is provided with a special access key that allows them to view the dashboards relevant to their organization. Clients log in via their existing Google Accounts, and the application presents and enforces appropriate access control rights that are depending upon their role in the organization.
With the recent addition of libraries in Apps Script, we were able to build this dashboard in a very short amount of time using a few of the notable script libraries linked to from the Apps Script documentation.
With ArrayLib’s method filterByText(data, columnIndex, value), we are able to implement filtering to enforce access controls by role:
filterByText(data, columnIndex, value)
if (e.parameter.selectedTrainer != 'All' && e.parameter.selectedTrainer != undefined) data = ArrayLib.filterByText(data, 1, e.parameter.selectedTrainer); if (e.parameter.selectedClient != 'All' && e.parameter.selectedClient != undefined) data = ArrayLib.filterByText(data, 2, e.parameter.selectedClient.split(','));
With PivotChartsLib, we can create charts based on our survey results in only a few lines of code:
var grid = app.createGrid(3, 2); var chart = PivotChartsLib.createColumnChart(data, 10); grid.setWidget(0, 0, chart); var chart = PivotChartsLib.createPieChart(data, 9); grid.setWidget(0, 1, chart); var chart = PivotChartsLib.createColumnChart(data, 6); grid.setWidget(1, 0, chart);
Apps Script is all about Google Apps. Applications running on Apps Script handle authentication as well as integrating seamlessly with spreadsheets as well as other parts of Google Apps. With Apps Script, we have a powerful tool to tie together all of the more general services from Google Apps and build rich, domain specific applications for our clients.
