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.
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(‘,’); }
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:
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
