My role in Google Docs is to help manage many projects across Google
Docs/Drive. As a part of my job, I ask for a fair amount of data from all of
those teams and generate reports on project/feature status. To make this much
simpler for everyone involved, I have created a lot of simple tools using
Google Spreadsheets and Apps Script (as well as a lot of complex tools) that
make it easier for collaborators to enter data and for me to collect that data
and create reports. Below is a pair of foundational techniques that I include
in nearly every Spreadsheet/Apps Script tool I build.
Load Settings From a Configuration Sheet
I have a dozens of scripts generating reports. I use a technique where I
set up a dedicated sheet for script configuration and read values from the
sheet during script execution. A simple configuration sheet makes this much
more straightforward.
With a globally accessible array, globals, you can then load the
“settings” from the configuration (sheet SHT_CONFIG, here) at any entrypoint
to the script.
// globally accessible variables
var SHT_CONFIG = 'Config';
var globals = new Array();
function entryPoint() {
globals = (globals.length == 0) ? LoadGlobals(
SpreadsheetApp.getActive(), SHT_CONFIG)
: globals;
// your code goes here
}
The LoadGlobals function, below, parses the data in the first three
columns of the workbook and sheet name passed to it. You can even include a
fourth column (or more!) explaining what the variables do, and they’ll just be
ignored - though hopefully not by your users!
// Generate gloabal variables to be loaded into globals array
function LoadGlobals_(wb, configSheet) {
var configsheet = wb.getSheetByName(configSheet);
var tGlobals = new Array();
// Config data is structured as VARIABLE, ISARRAY, VALUE(S)
// and includes that as the header row
var cfgdata = configsheet.getDataRange().getValues();
for (i = 1; i < cfgdata.length; i++) {
switch (cfgdata[i][1]) {
case 'ARRAY':
// treat as an array - javascript puts a null value in the
// array if you split an empty string...
if (cfgdata[i][2].length == 0) {
tGlobals[cfgdata[i][0]] = new Array();
} else {
tGlobals[cfgdata[i][0]] = cfgdata[i][2].split(',');
}
break;
// Define your own YOURDATATYPE using your customTreatment function (or
// just perform the treatment here)
case 'YOURDATATYPE':
tGlobals[cfgdata[i][0]] = customTreatment(cfgdata[i][2]);
break;
default: // treat as generic data (string)
tGlobals[cfgdata[i][0]] = cfgdata[i][2];
}
}
return tGlobals
}
As long as you’ve loaded the global values during the script execution,
you can refer to any of the values with, for example, globals.toList. For
instance:
function getToList() {
return globals.toList.join(‘,’);
// or
return globals[‘toList’].join(‘,’);
}
Stop Worrying About Column Numbers
Asking colleagues to enter tracking data so they don’t have to report
their own statuses is one thing. Asking them to enter tracking data in a
specific format, within a specific column layout, in a way that doesn’t mesh
with their existing processes is entirely different. So, I use the following
technique, where I rely on column names and not column ordering. The code
below lets me do just that by fetching a key-value object for column headings
and their position in a worksheet.
// Returns key-value object for column headings and their column number.
// Note that these are retrieved based on the array index, which starts at 0
// the columns themselves start at 1...
// pass header row of data (array) and an array of variables/column names:
// eg: BUG_COL_ARRAY['id'] = 'Id';
function ColNumbers(hArray, colArray) {
for (oname in colArray) {
this[oname] = getColIndex(hArray, colArray[oname]);
}
}
// -----------------------------------------------------------------------------
function getColIndex(arr, val) {
for ( var i = 0; i < arr.length; i++) {
if (arr[i].toLowerCase() == val.toLowerCase()) {
return i;
}
}
return -1;
}
With the associative array defined, below, I can ask Apps product
managers to add (or rename) columns to their feature tracking sheets and then
extract features from every apps product team in one fell swoop (a future
post). Each product team can set their columns up in whatever order works best
for them.
// key columns in the app feature sheets
var COLS_KEYAPPCOLS = new Array();
COLS_KEYAPPCOLS[‘feature’] = ‘Feature Title’;
COLS_KEYAPPCOLS[‘desc’] = ‘Description’;
COLS_KEYAPPCOLS[‘visible’] = ‘Visible’;
COLS_KEYAPPCOLS[‘corp’] = ‘Corp Date’;
COLS_KEYAPPCOLS[‘prod’] = ‘Prod Date’;
What does this do for me, really? I reuse this code for every project of
this sort. The steps to reuse are then:
Include the code
Build the associative array
Create a new ColNumbers object, as below
var curFeatures = curSheet.getDataRange().getValues();
var curCols = new ColNumbers(curFeatures[0], COLS_KEYAPPCOLS);
I can, from now on, refer to the Description column using something like
curCols.desc when referencing any of the products’ data. The Spreadsheets team
may list new feature descriptions in the second column, and the Documents team
may list new feature descriptions in the fourth column. I no longer worry
about that.
As a bonus, I can define the columns and ordering to be used in a report
in my config sheet (see above). If I’ve defined reportcols as feature, desc,
prod in my config sheet, I can generate a report very simply:
// Iterate through the rows of data, beginning with 1 (0 is the header)
for ( var fnum = 1; fnum < curFeatures.length; fnum++) {
// Iterate through each of the fields defined in reportcols
for ( var cnum = 0; cnum < globals.reportcols.length; cnum++) {
outputvalue = curFeatures[fnum][curCols[globals.reportcols[cnum]]];
// outputvalue is what you want to put in your report.
}
}
You could do that a lot more simply, but if we want to use the ‘Corp
Date’ I only need to change the value in the config sheet to feature, desc,
corp and I’m done - you’d have to change the code.
Collecting and crunching data in a Google Spreadsheet becomes a lot easier if
you use Apps Script. Trust me, it makes your life a lot easier. Try it now by
copying this
spreadsheet
Keith Howson
Editor’s Note: Keith is a Technical Program Manager with the
Google Docs teams. He is a heavy user of Apps Scripts and
Spreadsheets, and leverages the two to help the Docs teams run at full
steam. He also just completed his first triathlon with Team in
Training, to raise money for the
Leukemia
and Lymphoma Society - Go TEAM!
3 comments :
SEARCH POPULAR TOPICS
Job Tips, Interview Tips, Latest Text papers, Exam Results, Question papers, Answer papers, Job Applications, Online Education Topics.
http://online-education-topics.blogspot.com
Keith,
Great post! Curious about the differences in column handling in what you're describing here around column numbers and the method that converts a range to a Javascript object method shown in the Apps Script tutorials. What are the relative advantages / disadvantages of each?
See the "Library" functions shown here: https://developers.google.com/apps-script/articles/reading_spreadsheet_data
Cheers,
Andrew
Thanks, Andrew.
The two are functionally the same at the end. As I see it, the advantages are:
- this is slightly more compact
- I can define the 'field' names myself, so can make them shorter, or entirely different than the column headings
- By defining the mapping (which isn't much), you have an instant reference for the expected column headings. You can use that in lots of handy ways (eg. check lots of spreadsheets to make sure they have the required headings).
- This creates a simple mapping of field names to column numbers, by only processing the single (heading) row. The example processes every row of data in order to create a new array of key-value pairs. That creates slightly more overhead.
Disadvantages are:
- You have to define the mapping.
- There could be others :D
Post a Comment