Majelika

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.

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.

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.

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

Dan Lazin   profile | twitter Dan is a technical writer on the Developer Relations team for Google Apps Script. Before joining Google, he worked as video-game designer and newspaper reporter. He has bicycled through 17 countries.

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, Ive 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 thats what were 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 agencys 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.

Querying the API

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

Pivoting the Data

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, Id 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.

Conditional Formatting Using Scripts

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.

Editors Note: If youre interested in further scripting your AdWords accounts, take a look at AdWords Scripts, a version of Apps Script thats embedded right into the AdWords interface.

David Fothergill   profile | twitter | LinkedIn Guest author David Fothergill is a Project Director at the search marketing agency QueryClick, focusing on Paid Search and Conversion Optimisation. He has been working in the field for around 8 years and currently handles a range of clients for the company, in verticals ranging from fashion and retail through to industrial services.

The Google Apps Marketplace brings together hundreds of third-party applications that integrate and enhance Google Drive, part of Google Apps for Work, our suite of collaboration and productivity tools for businesses. To improve discoverability and increase adoption, it’s important to make your Google Drive app integration available on the marketplace.

Today, we want to share with you four easy steps to get listed immediately and enable admins to install your application for all users in their domain. For more details, check out the Google Apps Marketplace documentation.

Step 1: Open your Drive project on Google Cloud console. Turn on “Google Apps Marketplace SDK” for your project.

Step 2: Click the gear icon to configure “Google Apps Marketplace SDK”. Refer to Google Apps Marketplace documentation for details. In the scopes section, be sure to request the same scopes as your Google Drive application, and check the “Enable Drive extension” checkbox.

Step 3: Go to the Chrome Web Store developer console and select your published Drive application.

Step 4: Update the following line in your Drive application’s Chrome Web Store manifest, upload and publish.

"container":"GOOGLE_DRIVE"
with
“container”: [”GOOGLE_DRIVE”, ”DOMAIN_INSTALLABLE”]

You’re done! You application is now available to all Google Apps for Work customers to install on a domain-wide basis through the Google Apps Marketplace. Refer to Publishing your app documentation for details. You can access Google Apps Marketplace inside Google Admin Console and verify your newly listed application.

Please subscribe to the Google Apps Marketplace G+ community for the latest updates.
Posted by Hiranmoy Saha, Software Engine, Google Apps Marketplace

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.

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.

Romain Vialard   profile | YouTube Romain Vialard is a Google Apps Change Management consultant at Revevol. Romain writes scripts to automate everyday tasks, add functionality and facilitate rapid adoption of cutting edge web infrastructures. As a Google Apps Script Top Contributor, he has also built many of the top scripts in the Apps Script Gallery, including the very popular Gmail Meter.

Editors note: This is cross-posted from the Google Enterprise Blog. Guest author John Gale is a developer at Appogee, a Google focused systems integrator in the UK. John helps Appogee customers make the most of the Google platform and is the author of Appogee Bookmarks for Android.

Google Sites offers an incredible way to author and distribute content, and we use it extensively both for ourselves and our clients. Therefore it was not surprising that our customers started requesting a content approval workflow in Google Sites. Now with Google Apps Script, we have been able to develop Appogee Content Approval for Google Sites.

The Solution
Appogee Content Approval for Google Sites (ACA) can be set up for any existing Google Site without having to make any changes to the site. ACA works as follows:

1. The ACA spreadsheet generates a Content Submission Form and any content submitted is routed to a selected approver.
2. The approver receives an email notification, which they can authorize or reject.
3. Once authorized, the ACA spreadsheet writes the new content into the target Site using Apps Script’s Sites services. The new content is then visible to anyone with view permissions in the target site.

Approver receives an email containing the submitted content

The content submission URL can be shared to any group or published directly on the target Google Site, which represents the end goal on the workflow diagram below. Content may only be submitted by users that are logged into your domain and content can only be published with Approver sanction.

ACA Workflow Diagram

We used a number of Apps Script services to develop this application. Apps Script was an easy choice as it builds on pooled knowledge from the Google Web Toolkit (GWT) and uses Javascript syntax. Using Apps Script’s UI Services, we were able build up a working user interface quickly. Google has since introduced a new experimental feature to complement the UI Services, which provides a drag and drop GUI for building screens. We expect this feature to reduce development time required for building future apps, as well as offering a wide range of widgets and controls to improve the user experience. We used a standard Google spreadsheet as a kind of a data store in ACA. It’s worth mentioning that Apps Script’s documentation site has a plethora of tutorials covering everything from simple spreadsheet macros to full help desk applications.

One of Google Apps Scripts’ core features is the ability to seamlessly integrate different services together, in our case this was Google Sites and Spreadsheets, but many other services are accessible, such as Mail and Contacts. It was always the aim to make ACA a powerful tool without unnecessary complexity and thanks to Google Apps Script, we have successfully delivered content approval workflow to Google Sites. ACA represents our third off-the-shelf product to be listed in the Google Apps Marketplace.

Posted by John Gale, Solution Developer, Appogee

Want to weigh in on this topic? Discuss on Buzz

Weve held many Office Hours on Google+ Hangouts over the last two months, bringing together Google Apps developers from around the world along with Googlers to discuss the Apps APIs. Weve heard great feedback about these Office Hours from participants, so weve scheduled a few more in 2011.

General office hours (covering all Google Apps APIs):

• TODAY: December 8 @ 10:00am PST
• December 14 @ 1:30pm PST
• December 20 @ 12:00pm PST

Product-specific office hours:

• Google Apps Marketplace - December 12 @ 12:00pm PST

As we add more Office Hours in the new year, well post them on the events calendar, and announce them on @GoogleAppsDev and our personal Google+ profiles.

Hope you’ll hang out with us soon!

Ryan Boyd   profile | twitter | events Ryan is a Developer Advocate on the Google Apps team, helping businesses build applications integrated with Google Apps. Wearing both engineering and business development hats, youll find Ryan writing code and helping businesses get to market with integrated features.