Category Archives: Programming

Simple code and the rest (meetings, requirements, modeling, processes, debugging, tests and support)

Raspberry Pi – Video loop

Here is a nice week-end project, useful for automatic presentations, restaurant menus and waiting room video entertainment/marketing/information (my case).

I used a 32″ TV with a Raspberry Pi B (512 Mb RAM), a Wifi Adapter and a 64 Gb SD card (40 $CND at Costco) to create a simple video looping device.

My goal is simple, I want to use SFTP to upload videos in a folder, and have a simple Debian service loop and play each video file one by one.

Since I am not always here to restart the service, a simple reboot of the Pi (by unplugging) should restart the whole thing. No technical knowledge required !

Here is how I done it:

  1. Create nice corporate information slide deck
    • I suggest creating a visually interesting Powerpoint Presentation, with FULLY automated transitions and animation.
    • Test the presentation in Powerpoint.
    • Once it is perfectly tuned, export the Powerpoint to a MP4 video (sadly, only possible on Windows, not MAC OS)
  2. Setup the system
    • Install Raspbian with NOOBS on my 64Gb SD Card
    • Log-in with the pi user
    • If needed, setup wifi
      • type : startx
      • In LXDE, use the “Wifi” tool to setup the network
      • Exit LXDE
      • Reboot (sudo shutdown -r 0)
    • As a principle, I always upgrade the debian package and install vim (both are optional)
      • sudo aptitude upgrade
      • sudo aptitude install vim ctags
    • As we will use the CLI based Raspberry Pi Video player, omxplayer, there is no need for other software.
  3. Create the looping “application” (2 scripts and a folder)
    • Create a /home/pi/movies folder
    • Use your favorite SFTP client (ex: Mozilla) to push the corporate and other movie files to the Pi
    • Create the loop script (mine is named ~/declicTV/videoplayer.sh):
  4. mkdir /home/pi/declicTV
    cd /home/pi/declicTV
    vi videoplayer.sh

The code is greatly inspired from here.
I have made a few improvements:

#!/bin/sh
#first version from http://www.cenolan.com/2013/03/looping-video-playlist-omxplayer-raspberry-pi/

# set here the path to the directory containing your videos
VIDEOPATH="/home/pi/movies" 

# you can normally leave this alone
SERVICE="omxplayer"
SERVICE_OPTS="-o hdmi -n 3 -b"

# now for our infinite loop!
while true; do
        if ps ax | grep -v grep | grep $SERVICE > /dev/null
        then
        sleep 1;
else
        for file in $VIDEOPATH/*
        do
                #debug
                echo $SERVICE $SERVICE_OPTS "$file"
                
                #Display Files
                $SERVICE $SERVICE_OPTS "$file"
        done
fi
done
  • Then, it is a simple matter of adding a script to init.d, just like explained here.
    • Don’t forget the update-rc.d part.
    • Here is my init.d declicTV script:
#! /bin/sh
# /etc/init.d/declicTV
# taken from http://www.debian-administration.org/article/28/Making_scripts_run_at_boot_time_with_Debian

# Some things that run always
touch /var/lock/declicTV

# Carry out specific functions when asked to by the system
case "$1" in
  start)
    echo "Starting script declicTV "
    nohup /home/pi/declicTV/videoplayer.sh > /home/pi/declicTV/videoplayer.log &
    ;;
  stop)
    echo "Stopping script declicTV"
    killall -9 -r videoplayer.sh
    ;;
  *)
    echo "Usage: /etc/init.d/declicTV {start|stop}"
    exit 1
    ;;
esac

exit 0

I hope this will be helpful to you !

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 !