Here is a demo I made to demonstrate the capacity of Google Drive, Spreadsheet and Google Apps Script to consolidate multiples Spreadsheet from the same Drive Folder, and rapidly create simple and powerful enterprise applications.

This demo:

• Reads (an unknown number of) multiple files  from a single folder, using  predetermined rules (ex: files with name containing “Table “)
• Consolidate number of hours per week, from all files
• Write all the result values in a new spreadsheet

My (next) goal will be to create a simple share timesheet “webapp” for a small business. This demo is only the proof of concept. The goals of the future application will be to:

• Leverage the current spreadsheet timesheet solution (keep the Excel file and/or make a similar replacement)
• Completely remove the need for timesheet email sending, and copy/paste consolidation

The code of this article and all the files are available here.
(Copy into your Drive to edit)

Problem presentation

In a folder, we have an unknown number of spreadsheet containing similar information. There are specific markers in all source files. We  use these markers to determine where to start/stop the information extraction. In the image below, those markers are “WEEK” and “FINISH”. The information is not located at the same spot in all 3 files.

The general idea is to leverage on Drive Cloud qualities. In my target project (next article ?), the sources files will be disposed in a similar folder and correctly SHARED with all relevant personnel.

Just imagine. What a timesheet system could be under such principles :

• An HR person (or group) can own the whole folder, and create Timesheets (annual, ideally) for all new employees.
• Each employee have access to his file Only (and employees can delegate Timesheet filling for any reason)
• There are no emails to send
• There are no Excel incompatibilities (Windows vs Mac, xls vs xlsx, macro support, etc.), everybody use the latest version of the same spreadsheet client
• The timesheet app can now have access easily to all of Google Apps Script APIs : Email notification, Spreadhseet validation, Calendar integration, JDBC, Web service calls, etc.

In light of all those advantages, and with the support of an easy to code (and debug) Javascript IDE, Google Apps Script will be the VBA of the next decade.

The Google Apps Script code

Here are the three script files describing the solution. They are all included in the “Consolidate” Google Script project (link above).

The specialized Object/Array

To store the consolidated information, I use a specialized Object/Array object. The code includes:

• An initialization part (60 weeks reseted to 0 in the constructor)
• An “Add” method which increment the associated week entry with the submitted number of hours
• A “Report” method, which return all non-empty consolidated “lines” from the array

function ConsolidationArray(arrayName) {
  this.arrayName = arrayName;
  this.weekArray = [];
  
  //Array init
  for( i = 0 ; i <= MAX_NUMBER_WEEKS ; i++ ){
    //Any new attributes can be added here (ex : EmployeeType)
    this.weekArray[i] = {nbHours:0};
  }
}

ConsolidationArray.prototype.weekRowAdd = function(weekNumber, amount) {
  this.weekArray[weekNumber].nbHours += amount;
};

ConsolidationArray.prototype.returnConsolidatedNonEmptyWeeks = function() {
  var results = [];
  
  for( i = 0 ; i <= MAX_NUMBER_WEEKS ; i++ ){
    var iNbHours = this.weekArray[i].nbHours;
    if (iNbHours > 0){
      results.push({weekNumber:i,nbHours:iNbHours});
    }
  }
  
  return results;
};

The utility method

This is a simple “find” method for Spreadsheets. There are apparently no native way to search for a value inside a spreadsheet with GAS. Hence the necessity of such (lame) solution. The value of the SEARCH_RANGE_PARAMETER is currently 26 columns and 200 lines, and can be adjusted by constants in the main code file (next section). The bigger the range, the longer it will take to search.

// Based on http://stackoverflow.com/questions/10807936/how-do-i-search-google-spreadsheets

function find(ss, value) {
  
  var range = ss.getRange(SEARCH_RANGE_PERIMETER);
  
  var data = range.getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == value) {
        return range.getCell(i + 1, j + 1);
      }
    }
  }
  return null;
};

The main code

• Determine the list of source files (all that contains “Table ” in name)
• Open all files
  • Find the beginning and end markers
  • Extract the information in the specialized Array
• Results are written from the array to another file (with unique filename based on timestamp)

//CONSTANTS
var SEARCH_RANGE_PERIMETER = "A1:Z200"
var MAX_NUMBER_WEEKS = 60;
var PARENT_FOLDER_ID = "0B46WfgcTStaBTkhxQ3M1WGozWWs";
var FILENAME_COMMON_EXP = "Table ";
var TOP_MARKER = "WEEK";
var CONSOLIDATION_HEADER_1 = "WEEK";
var CONSOLIDATION_HEADER_2 = "HOURS";
var BOTTOM_MARKER = "FINISH";

/*
Read all pertinent files, and use specialez "ConsolidationArray" object to create a report.
*/
function consolidate(){
  var arr = new ConsolidationArray("consolidation");
  
  //Variables
  var files2Read = [];
  
  Logger.log("Consolidate - START");
  
  // Get parent folder
  var currentFolder = DriveApp.getFolderById(PARENT_FOLDER_ID);
  Logger.log("Try to find \""+FILENAME_COMMON_EXP+"\" in folder : "+currentFolder.getName())
  var files = currentFolder.getFiles();

  // Create source file list
  while(files.hasNext()) {
    file = files.next();
    name = file.getName();
    id = file.getId();
    
    // Add only files matching the expression (non-regex)
    if(name.indexOf(FILENAME_COMMON_EXP)>=0){
      files2Read.push({id:id,name:name});
    }

  }
  Logger.log(files2Read.length + " files found !");
  
  // For all source files
  for (var i = 0; i < files2Read.length; i++) {
    // Open spreadsheet (id du membre i)
    var ss = SpreadsheetApp.openById(files2Read[i].id);
    Logger.log("---------File: "+files2Read[i].name);
    
    //Find top marker (and startLine + startColumn)
    var topCell = find(ss,TOP_MARKER);
    Logger.log("SEMAINE found at "+topCell.getColumn()+":"+topCell.getRow());
    var startLine = topCell.getRow()+1;
    var startCol = topCell.getColumn();
    //Find bottom marker (and endLine)
    var bottomCell = find(ss,BOTTOM_MARKER);
    Logger.log("FIN found at "+bottomCell.getColumn()+":"+bottomCell.getRow());
    var endLine = bottomCell.getRow()-1;
    Logger.log("Extraction will occur between Start:"+startLine+" Finish:"+endLine)
    
    //For each source line of current file
    for (var j = 0; j <= endLine - startLine; j++) {
      // Get values and use specialized array object to consolidate
      var weekNb = ss.getActiveSheet().getRange(startLine+j, startCol).getValue();
      var nbHours = ss.getActiveSheet().getRange(startLine+j, startCol+1).getValue();
      Logger.log("ADDING " + nbHours + " hours for week " + weekNb);
      arr.weekRowAdd(weekNb,nbHours);
    }
    
  }
  
  // Get all non empty array records
  var results = arr.returnConsolidatedNonEmptyWeeks();
  Logger.log("+++++RESULTS ");
  for ( i = 0 ; i < results.length ; i++ ){
     Logger.log("++"+CONSOLIDATION_HEADER_1+" "+results[i].weekNumber+" "+CONSOLIDATION_HEADER_2+" "+results[i].nbHours);
  }
  
  //Create report (new spreadsheet with unique name)
    
  // Create file
  var fileName = arr.arrayName+""+new Date().getTime();
  var ss = SpreadsheetApp.create(fileName);
  
  // Move the file from root to current folder
  var fileSS = DocsList.getFileById(ss.getId());
  var originalFolders = fileSS.getParents();
  var probableParent = originalFolders[0];
  var folder = DocsList.getFolderById(PARENT_FOLDER_ID);
  fileSS.addToFolder(folder);
  fileSS.removeFromFolder(probableParent);
  
  // Write content - Header
  ss.getRange("A1").setValue(CONSOLIDATION_HEADER_1);
  ss.getRange("B1").setValue(CONSOLIDATION_HEADER_2);
  
  // Write content - all results lines
  for ( i = 0 ; i < results.length ; i++ ){
    ss.getActiveSheet().getRange(i+2, 1).setValue(results[i].weekNumber);
    ss.getActiveSheet().getRange(i+2, 2).setValue(results[i].nbHours);
  }
  
  Logger.log("Consolidate - END");
};

Execution, Logging and results

To test the this project yourself:

• Copy all 4 files (GAS Project + 3 spreadsheets) from the link to your own Drive account
• Change the PARENT_FOLDER_ID to the ID of the destination folder where you copied the files
• Run the “consolidate” method from “Code.gs”
• To validate that the code ran correctly, display the log (ctrl-enter on Windows)
• The log should look like this:

As a final validation, verify that a new spreadsheet has appeared in the same folder (it can take 3-4 seconds to do so in the Drive view). This new spreadsheet contains all the consolidated data from the three source files:

That’s it ! I hope this example is useful to you somehow !