Toper Turor

In just a few hours at the recent Apps Script hackathon in Los Angeles, we saw attendees build everything from website monitoring to room booking to financial tracking apps. For those of you who couldn’t make it, attendees were given a brief introduction to Apps Script and a few hours to let their imaginations run wild. Apps Script’s ease of use enabled them to quickly create fully functioning, useful apps. Here are a few interesting things we saw from local developers:

Website Monitor by Eduardo Arino de la Rubina

These days, small businesses are quickly increasing their online presence; a website outage during a critical period can be devastating to a mom-and-pop shop. Eduardo realized that existing network-monitoring solutions require a significant investment in technology and infrastructure that is beyond the reach of many small-business users. Using Apps Script’s UrlFetch and Spreadsheet services, he was able to quickly create a website monitor packaged in an easy-to-use spreadsheet that, given a list of URLs, tries to fetch each one and records the latency and content length.

The code is available here.

Get A Room by Danny Favela

Get A Room allows users to book meeting rooms by taking advantage of Apps Script’s tight integration with Google Calendar and events. The app, built entirely on a Chromebook utilizing Apps Scripts cloud friendliness, displays building floorplans with buttons that users can click to book a room. In response to a booking request, the app fetches the room’s calendar and creates a new event. It also updates the UI by replacing the floor plan with a modified image to show the newly booked room. Here is a snippet of the booking code:

// Click handler for the interaction to book a room
function bookBoardroomHandler(e) {
 var app = UiApp.getActiveApplication();

 // Perform the calendar-booking operations
 bookBoardroom();
 
 // Swap the images as visual confirmation
 app.remove(app.getElementById(imageDefaultLayout));
 app.add(app.getElementById(imageBoardroom));

 app.close();
 return app;
}

function bookBoardroom(e) {
 var calendarBoardroom = CalendarApp.getCalendarsByName("Boardroom");
 calendarBoardroom[0].createEventFromDescription("Boardroom Meeting");  
}

Stock Info by Matt Kaufman

Matt decided to build a web service that provides information about publicly traded stocks. The app’s backend consists of a spreadsheet with stock symbols of interest. Using Apps Script’s FinanceService, Matt loops through the spreadsheet on a timed trigger and appends the latest stock information for each symbol. He then uses HtmlService to create a web app that outputs an XML page of the stock info based on a symbol parameter in the URL. Here’s a picture of his script in action:

These are just some examples of how quickly useful apps can be created with Apps Script. Thanks to all the attendees for coming out! If you couldn’t make it to the hackathon, check out these tutorials to see how you can get started making great apps.

Kalyan Reddy profile | Stack Overflow Kalyan is a Developer Programs Engineer on the Google Apps Script team based in NYC. He is committed to increasing developer productivity by helping them fully utilize the power of Apps Script. In his free time, he enjoys participating in the Maker community and hacking together robots.

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].

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.

Ashraf Chohan Guest author Ashraf Chohan works at the Government Digital Service (GDS), part of the UK Cabinet Office. GDS’s role is to deliver digital transformation of government services. Ashraf is a Product Analyst for GOV.UK, a new site for public services and information.

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.

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.

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);

Ronald Dahrs Ronald combines his knowledge of project management and software solutions at his company, Forscale. He believes Google Apps is an excellent platform for online project management. He uses Google Apps Script to integrate the services to manage a wide range of projects.

Editors note: This is a guest post by Alex Steshenko. Alex is a core software engineer for Solve360, a highly-rated CRM in the Google Apps Marketplace which was recently chosen as a Staff Pick. Solve360 offers many points of useful integration with Google Apps. Today, in contrast to the conventional Data API integrations, Alex will showcase how he extended Solve360 using Google Apps Script. --- Ryan Boyd

Choosing Google Apps Script

Solve360 CRM integrates with Google services to provide a two-way contact & calendar sync, email sync and a comprehensive Gmail contextual gadget. We use the standard Google Data APIs. However, some of our use cases required us to use Google documents and spreadsheets. Enter Apps Script!. What brought our attention to Google Apps Script was that it allows you to run your application code right within the Google Apps platform itself, where documents can be manipulated using a wide range of native Google Apps Script functions, changing the perspective.

Our first experience with Google Apps Script was writing a "Contact Us" form. We decided to use the power and flexibility of Apps Script again to generate different kinds of reports.

Generating Solve360 Reports using Apps Script

Google Spreadsheets can produce rich reports leveraging features such as filters, pivot tables, built-in functions and charts. But where’s the data to report on? Using Google Apps Script, users can integrate Google Spreadsheets with a valuable source of data - the Solve360 CRM - completing the solution.

Solve360 Google Apps Reporting script lets users configure the reporting criteria while pulling reports into a Google Spreadsheet.

Heres a video demonstrating a real use case for Solve360 Reporting:

Designing Solve360 Reporting using Apps Script

User meet “Script”

For this script, we realized, simply providing spreadsheet functions would not be good enough. We needed a user interface to let users configure their account details and define what kind of data to fetch from the Solve360 CRM. Google Apps Script’s Ui Services came in handy. For instance, here is the function responsible for showing the “Solve360 account info” dialog:

/*
 * Creates new UI application and opens setting window
 */
function settingsUi() {
  var app = UiApp.createApplication();
  app.setTitle(Solve360 account info)
     .setWidth(260)
     .setHeight(205);

  var absolutePanel = app.createAbsolutePanel();

  absolutePanel.add(authenticationPanel_(app));

  app.add(absolutePanel);
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

Working with Solve360’s API

Solve360 CRM has an external API available so the system can be integrated with custom business applications and processes. Reporting script use case is a good example of what it can be used for.

One of the first tricks learned was creating our own Google Apps-like “service” to encapsulate all those functions responsible for interacting with Solve360 CRM’s API. What is the most interesting is that this service’s code isn’t a part of the distributed Google Apps script. Instead the library is loaded from within the script itself directly from our servers. Why? Let’s say we found a bug or added new functions - if we had many copies of the service we would need to update them all, somehow notifying our clients, and so on. With one source, there’s no such problem. You may think of it as a way to distribute a Google Apps Script solution, or, in our case, a part of the solution. The service is called Solve360Service and its usage looks like this:

var contact = Solve360Service.addContact(contactData);

There were two problems with getting such an external service to work: Google Apps Script permissions restrictions and actually including it in the script.

The issue with permissions is that the Google Apps Script environment can’t see which Google Apps Script services are used inside the external service - that’s why it doesn’t ask you to grant special permissions for them. To force the authorization request for those permissions we added this to the onInstall function (called once when script is added to the spreadsheet):

function onInstall() {
   // to get parseJS permissions
  Xml.parseJS([solve360, 1]);

   // to get base64Encode permissions
  Utilities.base64Encode(solve360);
// ...
}

Here is the solution we used to load our centralized code into the script:

eval(UrlFetchApp.fetch("https://secure.solve360.com/gadgets/resources/js/Solve360Service.js").getContentText());

The Solve360Service is loaded from a single source - no copy-paste. All the functions for accessing the Solve360 API aka “the plumbing” are abstracted and hidden in inside this service, while the essentials of the reports script itself can be modified and tweaked to a particular client’s case. Inside of Solve360Service we use UrlFetchApp:

/**
 * Request to the Solve360 API server
 * data should be an Array in Short Hand notation
 */
request : function(uri, restVerb, data) {
  if (this._credentials == null) {
    throw new Error(Solve360 credentials are not set);
  }

  if (typeof(data) != undefined) {
    if (restVerb.toLowerCase() == get) {
      var parameters = [];
      for each(var parameter in data) {
        parameters.push(encodeURIComponent(parameter[0]) + = + encodeURIComponent(parameter[1]));
      }
      uri += ? +  parameters.join(&);
      data = ;
    } else {
      data.unshift(request);
      data = Xml.parseJS(data).toXmlString();
    }
  } else {
    data = ;
  }

  var options = {
    "contentType" : "application/xml",
    "method" : restVerb.toLowerCase(),
    "payload" : data,
    "headers" : {"Authorization" : "Basic " + this._credentials}
  };
  return Xml.parse(UrlFetchApp.fetch(this._url + uri, options).getContentText()).getElement();
}

As the result is always XML, in order to remove any extra work we call Xml.parse() right inside the request function and always return a XmlElement so you can iterate through it, access nodes and attributes. Here is a simplified version of how we load some items when building a report:

/*
* Builds a search config from user preferences and loads a slice of data from Solve360
* To configure how many items should be loaded at a time, change ITEMS_LOAD_REQUEST_LIMIT constant
*/
function retrieveItems_(parameter, offset) {
  initSolve360Service_();
  // ...
  var searchParameters = [
    [layout, 1],
    [sortdir, ASC],
    [sortfield, name],
    [start,  + offset],
    [limit,  + ITEMS_LOAD_REQUEST_LIMIT],
    [filtermode, filtermode],
    [filtervalue, filtervalue],
    [searchmode, searchmode],
    [searchvalue, searchvalue],
    [special, special],
    [categories, 1]
  ];
  if (parameter.showAllFieldsCheckbox != true && fields.length > 0) {
    searchParameters.push([fieldslist, fields.join(,)]);
  }
  // ...
  var items = Solve360Service.searchProjectBlogs(searchParameters);
  // ...
  return items;
}

To simplify the usage of the service we added another function which initializes the service object, named Solve360Service:

/*
* Loads external Solve360Service
* For the service functions available refer to the source code here:
* https://secure.solve360.com/gadgets/resources/js/Solve360Service.js
*/
var Solve360Service = null;
function initSolve360Service_() {
  if (Solve360Service == null) {
    eval(UrlFetchApp.fetch("https://secure.solve360.com/gadgets/resources/js/Solve360Service.js").getContentText());
    var user = UserProperties.getProperty(USERPROPERTY_USER);
    var token = UserProperties.getProperty(USERPROPERTY_TOKEN);
    if (user == null || user.length == 0 || token == null || token.length == 0) {
      throw new Error(Use Solve360 spreadsheet menu to set email and token first);
    }
    Solve360Service.setCredentials(user, token);
  }
}

As you can see, it uses the email/token pair previously saved in the “Solve360 Account Info” dialog or signals an error if the credentials were not yet saved.

Conclusion

There are many use cases where you can apply the Google Apps Script. The fact that you can work and implement solutions right from “inside” one of the greatest and most universal web applications available is amazing.

You can integrate your own software with Google Docs or even learn from us and build a reporting script for any other system accessible online. Try to look at solving business tasks from a different perspective, from the Google Apps point of view. We encourage it!

The code of the new script is available for use and study here:
https://secure.solve360.com/docs/google-apps-reports.js.

Alex Steshenko profile | twitter | blog Alex Steshenko is a core software engineer for Solve360, a CRM application on the Google Apps Marketplace.

The Apps Script team held a hackathon in Washington DC on March 7. Over 80 developers attended and we had some great demos at the end of the evening. One of the demos was from Rusty Mellinger, who explains his script in this blog post. If you missed the DC hackathon, sign up for our next one in Chicago on April 19. -Jan Kleinert

I was lucky enough to attend Google’s Apps Script Hackathon at their office in DC, recently, and got a chance to play with Apps Script. After a quick walk-through tutorial, Jan gave us a couple of hours to hack around with it.

Scripts in Apps Script are written in JavaScript and stored, edited, and run on Googles servers, interfacing with a big list of included services. You can call the scripts from spreadsheets, Google Sites, or from hits to a generated URL.

Roommate Payment Spreadsheet

My roommates and I keep a spreadsheet on Google Docs to track who owes what, but since we’re a house full of software engineers, it’s gotten pretty complicated. Each row records the details of a single transaction: who paid, the total, and what percentages of the payment are on behalf of which roommates. All these interpersonal debts are added up into the (J5:M8) matrix, cancelled out across the diagonal into (P5:S8) to get a single debt for each roommate pairing, and then those are totalled into the final "Shake Out", (F4:F7), which says whether you owe or are owed. Maybe Apps Script could make my life simpler here?

Automatic Emails

First, I’m currently owed a fair amount of money, so I set up automated reminder emails to the roommates who are behind:

// Send emails to everybody with their current status.
function emailDebtors() {
  var ss       = SpreadsheetApp.getActiveSpreadsheet();
  var results  = ss.getRange( "G4:G7" ).getValues();
  var emails   = ss.getRange( "O3:R3" ).getValues();
  var numUsers = 4;
  
  for(var i = 0; i != numUsers; i++) {
    var val = Math.round(results[i][0]);
    
    if (val > 0) {
      // This guy owes money in the shake-out.
      MailApp.sendEmail(
        emails[0][i], "Youre a deadbeat!",
        "You owe $" + val + ". Please pay it!");
    }
  }
}

This just pulls the current totals from the (G4:G7) "Shake Out", as well as their respective email addresses from (O3:R3). When this function is called, if any of them owe more than $0, they get a friendly reminder!

Custom Menus

I could set that up to trigger daily or weekly, but it only really needs to happen when somebody needs to collect what they’re owed, so I’ve added it as an option to the sheet’s menu on start-up.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ { name: "Email debtors", 
                        functionName: "emailDebtors"}];
  ss.addMenu( "SkyCastle", menuEntries );
}

Easy! Now when somebody wants to collect, they just click the “SkyCastle -> Email debtors” option and the appropriate reminder emails are sent out, from their own Gmail address.

Historical Charting

I still had a couple of hours, and wanted to play with the UI and Google Charts services, so I decided to chart the “Shake Out” values over the history of the spreadsheet. The existing cells are hard-coded to operate on the total sums from the full sheet, so I had to re-implement the math to track it line-by-line. (This isn’t all bad, because I can use it to double check the existing arithmetic, which was sorely needed.)

The basic sketch is as follows:

var data = Charts.newDataTable()
                 .addColumn(Charts.ColumnType.NUMBER, "Row");
for (var i = 0; i != 4; i++) {
  data.addColumn(Charts.ColumnType.NUMBER, names[i]);
}

for (var i = 0; i != NUMROWS; i++) {
  var row = Array(5);
  // …
  // Process the current line here, and compute the shake-out.
  // …
  data.addRow(row);
}
data.build();

I’ve omitted the actual calculation, because it’s just a bunch of hacks specific to our spreadsheet formulas. Each row contains the row number, and the accumulated shake-out thus far, and gets added to the `data` table. I break out of the loop once I go off the end of my data and start hitting `NaNs`.

To create the line chart and add it to a new UI window:

var chart = Charts.newLineChart()
      .setDataTable(data)
      .setDimensions(700, 400)
      .setTitle("Debt History")
      .build();

var uiApp = UiApp.createApplication()
                 .setWidth(700)
                 .setHeight(400)
                 .setTitle("Payment History");
uiApp.add(chart);
ss.show(uiApp);
return uiApp;

After adding this function as another option in our custom `SkyCastle` menu and clicking it, we see a nice graph. (I’m almost always on the bottom, but that’s because I make the actual rent and utility payments.) The final entries are equal to the original "Shake Out" cells, so our old arithmetic seems correct, too.

Lessons Learned

The built-in debugger isn’t bad; use the `Select function` dropdown and click the bug icon. I also used Logger.log() liberally while trying to get things working right. (Go to `View -> Logs` in the Script Editor to view that output.)

Apps Script seems to work well, overall, and hooks into a nice and expanding array of Google products and data sources. The GWT-backed UI service is a clever idea, though I barely had a chance to touch it.

Thanks again to Jan and Google for hosting this Hackathon; I can’t wait for the next one!

Rusty Mellinger Rusty Mellinger co-founded Illogic Inc, making heavy use of Google Apps and GWT.