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
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.
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.
Here's a video demonstrating a real use case for Solve360 Reporting:
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); }
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:
Solve360Service
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):
onInstall
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:
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:
Xml.parse()
XmlElement
/* * 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.
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 is a core software engineer for Solve360, a CRM application on the Google Apps Marketplace.
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:
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.
function doGet() { // Populate the DataTable. We'll have the data labels in // the first column, "Quarter", and then add two data columns, // for "Income" and "Expenses" var dataTable = Charts.newDataTable() .addColumn(Charts.ColumnType.STRING, "Quarter") .addColumn(Charts.ColumnType.NUMBER, "Income") .addColumn(Charts.ColumnType.NUMBER, "Expenses") .addRow(["Q1", 50, 60]) .addRow(["Q2", 60, 55]) .addRow(["Q3", 70, 60]) .addRow(["Q4", 100, 50]) .build();
SpreadsheetApp
UiApp
UrlFetch
// Build the chart. We'll make income green and expenses red // for good presentation. var chart = Charts.newColumnChart() .setDataTable(dataTable) .setColors(["green", "red"]) .setDimensions(600, 400) .setXAxisTitle("Quarters") .setYAxisTitle("$") .setTitle("Income and Expenses per Quarter") .build();
setDataTable()
build()
// Add our chart to the UI and return it so that we can publish // this UI as a service and access it via a URL. var ui = UiApp.createApplication(); ui.add(chart); return ui;}
// Save the chart to our Document List var file = DocsList.createFile(chart); file.rename("Income Chart"); file.addToFolder(DocsList.getFolder("Charts")); // Attach the chart to the active sites page. var page = SitesApp.getActivePage(); page.addHostedAttachment(chart, "Income Chart"); // Attach the chart to an email. MailApp.sendEmail( "recipient@example.com", "Income Chart", // Subject "Here's the latest income chart", // Content {attachments: chart });
In March, we announced that we would start requiring clients to use SSL when making requests to the Google Documents List API, the Google Spreadsheets API, and the Google Sites API. This is part of our ongoing effort to increase the security of user data.
The time has come, and we are starting to roll out this requirement. On average, about 86% of requests to these APIs are already using SSL, so we expect there to be minimal migration required. The implementation will continue throughout September. If an application receives an HTTP 400 Bad Request response to a request, then it may be because the request was not made using HTTPS.
Clients that have not already started using SSL for all requests should do so immediately. This is as simple as upgrading to the latest version of the relevant API client library. Developers with questions should post in the API forums.
gContact:status
<gcontact:status indexed="true"/>
indexed
false
AccountManager
GoogleAccountManager
GoogleAccountManager googleAccountManager = new GoogleAccountManager( activity); Account[] accounts = accountManager.getAccounts();
AccountManager.getAuthToken()
AccountManagerCallback
googleAccountManager.manager.getAuthToken(account, AUTH_TOKEN_TYPE, null, activity, new AccountManagerCallback<Bundle>() { public void run(AccountManagerFuture<Bundle> future) { try { // If the user has authorized your application to use the tasks API // a token is available. String token = future.getResult().getString( AccountManager.KEY_AUTHTOKEN); // Now you can use the Tasks API... useTasksAPI(token); } catch (OperationCanceledException e) { // TODO: The user has denied you access to the API, you // should handle that } catch (Exception e) { handleException(e); } } }, null);
AUTH_TOKEN_TYPE
String AUTH_TOKEN_TYPE = ”Manage your tasks”;
useTasksAPI(String accessToken) { // Setting up the Tasks API Service HttpTransport transport = AndroidHttp.newCompatibleTransport(); AccessProtectedResource accessProtectedResource = new GoogleAccessProtectedResource(accessToken); Tasks service = new Tasks(transport, accessProtectedResource, new JacksonFactory()); service.setKey(INSERT_YOUR_API_KEY); service.setApplicationName("Google-TasksSample/1.0"); // TODO: now use the service to query the Tasks API }
service
import gdata.apps.emailsettings.client import gdata.contacts.client # replace these values with yours CONSUMER_KEY = 'mydomain.com' CONSUMER_SECRET = 'my_consumer_secret' company_name = 'ACME Inc.' admin_username = 'admin'
xoauth_requestor_id
# request a 2-legged OAuth token requestor_id = admin_username + '@' + CONSUMER_KEY two_legged_oauth_token = gdata.gauth.TwoLeggedOAuthHmacToken( CONSUMER_KEY, CONSUMER_SECRET, requestor_id) # Email Settings API client email_settings_client = gdata.apps.emailsettings.client.EmailSettingsClient( domain=CONSUMER_KEY) email_settings_client.auth_token = two_legged_oauth_token # User Profiles API client profiles_client = gdata.contacts.client.ContactsClient( domain=CONSUMER_KEY) profiles_client.auth_token = two_legged_oauth_token
HtmlSignature()
# helper class used to build signatures class SignatureBuilder(object): def HtmlSignature(self): signature = '%s' % self.name if self.occupation: signature += '%s' % self.occupation if self.company: signature += '%s' % self.company signature += 'Email: <a href=\'mailto:%s\'>%s</a> - Phone: %s' % ( self.email, self.email, self.phone_number) return signature def __init__( self, name, company='', occupation='', email='', phone_number=''): self.name = name self.company = company self.occupation = occupation self.email = email self.phone_number = phone_number
GetProfilesFeed()
next
# get all user profiles for the domain profiles = [] feed_uri = profiles_client.GetFeedUri('profiles') while feed_uri: feed = profiles_client.GetProfilesFeed(uri=feed_uri) profiles.extend(feed.entry) feed_uri = feed.FindNextLink()
SignatureBuilder
name
company
occupation
email
phone_number
# extract relevant pieces of data for each profile for entry in profiles: builder = SignatureBuilder(entry.name.full_name.text) builder.company = company_name if entry.occupation: builder.occupation = entry.occupation.text for email in entry.email: if email.primary and email.primary == 'true': builder.email = email.address for number in entry.phone_number: if number.primary and number.primary == 'true': builder.phone_number = number.text # build the signature signature = builder.HtmlSignature()
UpdateSignature
# entry.id has the following structure: # http://www.google.com/m8/feeds/profiles/domain/DOMAIN_NAME/full/USERNAME # the username is the string that follows the last / username = entry.id.text[entry.id.text.rfind('/')+1:]
# set the user's signature using the Email Settings API email_settings_client.UpdateSignature(username=username, signature=signature)
