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.
