Insider Tips for Using Apps Script and Spreadsheets

Wednesday, May 9, 2012 | 2:56 PM

Labels:

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:

  1. Include the code
  2. Build the associative array
  3. 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:

SUKANYA said...

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

astilly said...

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

Keith Howson said...

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