Trick and Tips

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

Retaining compatibility

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

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.

Enumeration of collections

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.

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

Including vEquivalents in your Google Apps Script Project

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

Examples

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

Going beyond the built-in VBA functions

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). Heres 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.

Bruce McPherson   profile | twitter Bruce McPherson is a contributor to Excel Liberation website and blog, and advocate for open data.

Editors note: this announcement is cross-posted from the Google Ads Developer Blog, which caters to AdWords, AdSense, DoubleClick and AdMob developers. We hope you enjoy this latest addition to Google Apps Script — Ryan Boyd

Starting today, the AdSense Management API is available as part of AdSense Services in Google Apps Script. This means that you’ll be able to do things like:

• Create AdSense performance reports for your AdSense accounts in a Google spreadsheet
• Create a chart based on your AdSense reporting data and display it in a Google Spreadsheet
• Embed your scripts in a Google Sites page, for instance to import a chart
• Use triggers to schedule the execution of your scripts, for instance to periodically update the chart imported in the Google Sites page

Accessing the API from Google Apps Scripts is very easy. The following snippet of code shows how to generate a report and populate columns of a spreadsheet with the data retrieved:

function generateReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(Reports);
  var startDate = Browser.inputBox(
      "Enter a start date (format: yyyy-mm-dd)");
  var endDate = Browser.inputBox(
      "Enter an end date (format: yyyy-mm-dd)");
  var args = {
    metric: [PAGE_VIEWS, AD_REQUESTS, MATCHED_AD_REQUESTS,
               INDIVIDUAL_AD_IMPRESSIONS],
    dimension: [MONTH]};
  var report = AdSense.Reports.generate(startDate, endDate, args).getRows();
  for (var i=0; i<report.length; i++) {
    var row = report[i];
    sheet.getRange(A + String(i+2)).setValue(row[0]);
    sheet.getRange(B + String(i+2)).setValue(row[1]);
    sheet.getRange(C + String(i+2)).setValue(row[2]);
    sheet.getRange(D + String(i+2)).setValue(row[3]);
    sheet.getRange(E + String(i+2)).setValue(row[4]);
  }    
}

If you want to generate a chart from your data instead of populating the spreadsheet, that’s very easy as well:

function generateLineChart() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var startDate = Browser.inputBox(
      "Enter a start date (format: yyyy-mm-dd)");
  var endDate = Browser.inputBox(
      "Enter an end date (format: yyyy-mm-dd)");
  var adClientId = Browser.inputBox("Enter an ad client id");
  var args = {
    filter: [AD_CLIENT_ID== + adClientId],
    metric: [PAGE_VIEWS, AD_REQUESTS, MATCHED_AD_REQUESTS,
               INDIVIDUAL_AD_IMPRESSIONS],
    dimension: [MONTH]};
  var report = AdSense.Reports.generate(startDate, endDate, args).getRows();
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, "Month")
      .addColumn(Charts.ColumnType.NUMBER, "Page views")
      .addColumn(Charts.ColumnType.NUMBER, "Ad requests")
      .addColumn(Charts.ColumnType.NUMBER, "Matched ad requests")
      .addColumn(Charts.ColumnType.NUMBER, "Individual ad impressions");
  
  // Convert the metrics to numeric values.
  for (var i=0; i<report.length; i++) {
    var row = report[i];
    data.addRow([row[0],parseInt(row[1]),parseInt(row[2]),
        parseInt(row[3]),parseInt(row[4])]);  
  }
  data.build();
  
  var chart = Charts.newLineChart()
      .setDataTable(data)
      .setTitle("Performances per Month")
      .build();
  
  var app = UiApp.createApplication().setTitle("Performances");
  var panel = app.createVerticalPanel()
      .setHeight(350)
      .setWidth(700);
  panel.add(chart);
  app.add(panel);
  doc.show(app); 
}

A shiny line chart will be displayed in your spreadsheet, as shown in the following picture:


You can start using the service by checking out the reference documentation, that contains also some sample scripts, and this tutorial that implements the use cases mentioned above.

Happy Google Apps Scripting with the AdSense Management API!

Silvano Luciani   profile Silvano Luciani joined Googles London office in 2011 to make the AdSense API developers happier people. Before that, he has worked in Finland, Italy, Spain and the UK, writing web based configuration management tools for ISPs, social networks, web based training materials, e-commerce apps and more. He has recently discovered that he loves charts, and has finally started to play the drums in the London’s office music room. If you can call what he does "playing the drums".

So you’re an agile tester and wonder why you should care about story maps. You may already be convinced that modelling your backlog in two dimensions is useful for helping the whole team visualize the big picture. However, story maps are also a valuable testing tool, providing two additional testing avenues. In the first case, the map itself offers the ability to test the validity of a solution. In the second, a story map improves a team’s ability to identify story slices and then test them.

Testing What to Build

User story maps are a representation: they provide a means to visualize a system that might be built and are useful for testing the validity of that system before investing significant time and money. A story shared at a recent Agile Winnipeg event demonstrated this principle well. The company involved used story mapping to test an idea before building any software. The team had a project idea that they thought would serve their client well. After quickly building a story map around that idea, they presented the map to their client at the next customer conference. Although it soon became clear that the idea missed the mark, the customer was able to collaborate with the team on the spot, to adjust the map until it represented what they actually wanted built. The map itself was the tool that allowed for the idea to be tested (and then adjusted) and moved the project forward.

Testing Application Slices

As Crispin and Gregory demonstrated in their first book Agile Testing, identifying thin slices and small chunks is important for testing agile projects. Story maps help identify those slices but, perhaps more importantly, they help us understand how those thinly sliced stories might fit together to form a thin slice of the whole application. When undertaking an agile project, testers are required to make a vital shift in thinking; only test small pieces at a time. Despite this fundamental change, it is also important to ensure that the first few pieces fit together, enabling end to end testing as early as possible. The story map helps to identify and prioritize that first application slice. It may be based on a user scenario or just a string of stories that represent the smallest stories that allow left to right movement on the map.

Visualizing testing slices in your map

As that the team identifies that first slice, utilizing excellent testing skills is crucial. By looking at the map, you can identify areas that will be difficult to test, areas where the test variations are still relatively unknown, or areas that represent higher risk. This activity can help identify stories that should be included in the first application slice.

When coding and testing begins, personas and user scenarios that were created can be revisited, helping to flesh out the map and application slices. Testing with a persona in mind helps ensure that the targeted customer will be satisfied with the solution. It may not be possible or wise to test if the application works well for everyone but testing should evaluate whether the targeted personas can use the application easily, and that the new functionality fits into, or adds to their current processes without getting in the way.

Story Maps—A Testing Tool After All

At first glance, the story map doesn’t appear to be an obvious asset for testing, but upon closer inspection, it proves its value in any testing toolbox. The map itself is a reliable way to test that the right system is being built before any code is written. The map also provides a visual aid for testing in horizontal application slices, allowing for early confirmation that a project is on the right track.

Subscribe to Winnipeg Agilist by Email

Google Sites lets you create public or internal web sites, and today we’re enabling scripts to support multi-step workflows in sites.

For example, your company can create a site for employees to browse and register for training sessions and career development programs. On the page describing each training session or class, you could add a “Register Now” button, which would automatically add registrants to the class roster, add the details of the session to each participants Google Calendar, and email users to confirm enrollment. All of these automated actions can be driven by a script embedded in the site.



Starting today, you can create, edit, and launch Google Apps Scripts from any Google Site, which allows you to automate business processes that involve multiple applications. As in the example above, an Apps Script function can automate tasks such as sending emails, scheduling calendar events, creating and updating site pages using data from other systems, and more.

You can build a script by clicking “More actions” > “Manage site” > “Apps Scripts.” Once you’ve added a script to your site, you can add links or buttons to trigger the script from any page. For tips to get started with scripts, visit the Google Apps Script site.

Posted by Laurent Tu, Google Apps Team

Want to weigh in on this topic? Discuss on Buzz

We have released version 1.9 of the .NET Library for Google Data APIs and it is available for download.

This version adds the following new features:

• support for 3-legged OAuth
• two new sample applications: BookThemAll (mashup of Calendar and Provisioning APIs) and UnshareProfiles (showcasing a new feature of the Google Apps Profiles API)
• updates to the Content for Shopping API to implement 20+ new item attributes
• support for new yt:rating system and Access Control settings to the YouTube API

This new version also removes the client library for the deprecated Google Base API and fixes 20 bugs.

For more details, please check the Release Notes and remember to file feature requests or bugs in the project issue tracker.

Claudio Cherubino profile | twitter | blog Claudio is a Developer Programs Engineer working on Google Apps APIs and the Google Apps Marketplace. Prior to Google, he worked as software developer, technology evangelist, community manager, consultant, technical translator and has contributed to many open-source projects, including MySQL, PHP, Wordpress, Songbird and Project Voldemort.