Category Archives: Small Business

Spreadsheet consolidation with Google Apps Script

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.

Example of source table file
Example of source table file

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:
GAS project with Log window displayed
GAS project with Log window displayed

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:

Capture d’écran 2014-08-13 à 08.19.49

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

Small business network – Part1 – Requirements

Hi, welcome to this first post of the “Small business network” series.

Introduction

My girlfriend’ small business is moving to new headquarters. Yay.
She needs a whole new network. Great.

This entreprise is specialized in education services for early childhood. As one might imagine, such type of entreprise, especially since it existed for only five years, does NOT have the budget for a “professional” network (read : >10 thousand $ of hardware network products, expensive wiring and and automatic workstation management). This series of articles are about the next best thing, a “home made” network installation, built out of some FOSS, commercial software, hardware and (a little bit of) duck tape.

This first article is aimed at specifying the business needs. We then document the requirements and draw a plan of the network installation.

Needs – a short list

  • Server(s) : File sharing (internal), File sharing (secured extranet), Incremental backup, Authentication and Authorization, Accounting software service deployment
  • Workstations: 4-5 Desktops and 2-3 Laptops (all wired to the network). Theses are already belonging to the company. Typical usage include : email, productivity applications, web browsing.
  • Network printer/fax
  • 7 multi-line telephones (4 Lines). The technology implied is “normal” telephone lines. (see “VoIP shopping” bellow)

VoIP Shopping

In prevention of the “You should go with VoIP” argument, I should tell you that it was considered. The main problem, in the case of this company, is that it as an ongoing agreement with their current telephone provider (a local cable company). Cancelling would imply cost that VoIP savings just don’t cover.

Aside from this, the company had already invested in pretty good “4 lines” phones from RCA.  Those phones include some “telephone system” features like transfers and conference. Those feature are currently sufficient for the company. Software phones (for working from home), dynamic redirections and other features could be useful, but are not necessary.

More than half the number of phone needed were already bought in the years before, and this added to the cost of changing as well (the difference between buying 7 new phones or only 3).

That said, and considering all I just wrote, the VoIP offer from one of the provider we consulted represented almost enough savings to convince us.

VoIP, see you in 2 to 3 years 😉

In the meantime, it is important to state as a requirement that wiring *must* be forward-compatible to a time where the whole telephone network is going to be passing trough Ethernet.

Requirements

Since needs can be pretty basic when you don’t have anything in place, I have a preference for documenting the requirements as the checklist that I am going to use over the course of the whole project. Here it is.

New Headquarters network infrastructure list

  • Deploy a wired network for Workstations and telephone (see part 2)
    • Make it forward compatible for future VoIP. The 4 current lines will be carried by the same CAT5 cable that could be used for VoIP in the future.
    • Make the Workstation wiring CAT6 for new Gigabit Ethernet workstations
    • We need 13 identical faceplates with 1 Ethernet and 2 RJ-45 jacks (each holding two lines on the 4 central pins). Those 2 last should have been RJ-11 jacks, but my RJ-45 jacks were available and will do fine (even if 2 pairs of pins are going to be unused).
    • And one special faceplate with one Ethernet connector and the fax line
    • The closet (as in: replacement of a network rack) will include the following:
      • A 24 ports patch panel for each outlet (for only 13 outlets)
      • A distribution box of at least 49 output pairs (lines). I found a model with 50.
      • A router (generic Linksys 4 ports)
      • A 16 ports Gigabit switch (not intelligent. no budget for VLANs)
      • 2 cable company modems, for the 5 telephone lines (including fax)
      • A KVM for two servers
      • A monitor, keyboard and a mouse
      • Ventilation
  • Deploy servers (see part 3)
    • Install file sharing solutions including authentication and authorization of both local and external personnel
    • Install simply accounting (server mode)
    • Configure a incremental backup solution
  • Configure workstations (see part 4)
    • Install software (Productivity)
    • Establish a good way to to remote control of specific workstations from the outside

Documentation – Wiring plan

Wiring plan preview
Don’t you just love a useful document ?

The first “deliverable” in this series is a plan of the network drawn before the beginning of the wiring effort. This document is useful for many reasons:

  • It will serve as a guide for wiring (from the basement of the building)
  • It visually document the layouts of the different connectors. This will be useful in the future for managing the network closet (the plan should be as display in the closet)
  • The corresponding numbers can be used for workstation ID, phone ID or any other relevant purpose

I hope it will inspire you in your projects and encourage you to read on.

The plan is OpenOffice/LibreOffice compatible. I also provide the PDF version.
SBN-Part1-WiringPlan.odg
SBN-Part1-WiringPlan.pdf