Winning with LiveCode: Using LiveCode to run a city council election campaign

LiveCode has many uses. Something it excels at is processing data from all kinds of sources to provide sensible insights and actionable outcomes. This is an example of such a use.

The Project

Recently I was involved in a city council election campaign. For the campaign we divided up our district precincts into smaller turfs of about 100 houses each. That way we could give our canvassers a reasonable-sized area to cover in a reasonable amount of time. We wanted to get a sense of how effective our efforts were.

The Raw Material

The political database system we used to run our campaign has some canned reporting formats, one of which generates our status Turf Report to view how our canvassing effort is going. We couldn’t modify the format, but we could add more fields to the report. The report comes out in CSV format, but luckily there were no data errors that got in the way and the first row contained the column headers. What we needed for our reports was a spreadsheet format which we then pasted into Google Docs.

Doing it the hard way

There were several tasks we started doing manually:

• Import the csv file as a spreadsheet
• Eliminate some irrelevant (to our needs) data columns
• Rename some columns
• Move some columns around for grouping in order to better visualize our results
• Export the spreadsheet file to upload to Google Docs

But for a weekly or better report this was going to be a lot of repetitive work, and that was effort we could put to better use elsewhere.

So LiveCode to the rescue.

A reusable approach

To make this more generic and reusable (looking ahead to the next four years) I saved a preferences table. After stripping comments the format is:

• line 1: column names to be renamed
• line 2: the new names for those columns
• line 3: the named columns to be removed

Note that lines 1 and 2 needed to be kept in order for ease of iterating through a repeat loop.

Before reading the csv data itself I read these lines from the preferences file and store them as custom properties of the “Open…” button on the Workbook viewer stack. Here is the code I created, which may be of use to others doing similar operations:

Sample code

constant kPrefsFile = "/CanvassingPrefs.txt"

command loadPrefs
   local tPath
   local tPrefs
   local tOldColumnNames, tNewColumnNames, tColumnsToDelete
   
   put specialFolderPath("home") into tPath
   if there is a file (tPath & kPrefsFile) then
      put url ("file:" & tPath & kPrefsFile) into tPrefs
   else
      # file doesn't exist, so create first, saving the default preferences
      put savePrefs() into tPrefs
   end if
   
   filter tPrefs without "#*" # ignore comment lines
   filter tPrefs without empty # ignore blank lines
   put line 1 of tPrefs into tOldColumnNames
   replace comma with cr in tOldColumnNames
   set the OldColumnNames of me to tOldColumnNames
   
   put line 2 of tPrefs into tNewColumnNames
   replace comma with cr in tNewColumnNames
   set the ColumnNames of me to tNewColumnNames
   
   put line 3 of tPrefs into tColumnsToDelete
   replace comma with cr in tColumnsToDelete
   set the ColumnsToDelete of me to tColumnsToDelete
end loadPrefs

Excel Library and friends

Originally I looked at zyrip the slug’s Excel library, an addon for LiveCode which I got through the Summer Megabundle. But alas, it relies on Excel (duh) itself to do the heavy lifting and I use LibreOffice Calc for spreadsheets. So no go. What I hadn’t noticed was there’s a companion library XLSXLib, and I immediately started to put it to use. The XLSXLib archive has a sample Workbook viewer stack, and it seemed easy enough to modify to accomplish our goals, so I started with that. The Workbook viewer stack uses a datagrid (of course) and allowed me to view the data for a sanity check before exporting in xlsx format. All my code changes are in the “Open…” button of the stack with the exception of reading the preferences file in the openStack handler and a mouseDoubleUp handler in the path display field to allow for double-clicking to launch LibreOffice Calc.

The outcome

In short, LiveCode in combination with the xlsx library turned a repetitive, awkward, time-consuming task with an inflexible data format into essentially a single-click operation.

XLSX Lib Functions

There are several functions in the library that we took advantage of. They’re all well-documented in the accompanying XLSX Doc stack.

• XLSX_Workbook_NewFile
• XLSX_Sheet_Name_Set
• XLSX_Range_Values_Set
• XLSX_Range_Formula_Set
• XLSX_Range_Styles_Set
• XLSX_Sheet_FrozenPane_Set
• XLSX_Column_Width_Set
• XLSX_Workbook_SaveFile
• XLSX_Workbook_CloseFile
• In particular XLSX_Range_Styles_Set is very flexible and powerful.

Library examples

Here are some examples of how we used the library routines:

constant kWBName = "myWorkbook"
constant kSheetName = "sheet2"

# first off, start a new workbook and new sheet
      XLSX_Workbook_NewFile kWBName
      XLSX_Sheet_Name_Set kWBName, 1, kSheetName

First test was to see that I could read the input csv file with no problems, then load the csv file into memory and process it:

on convertCSVFile pFilePath
   local tData, tColumns, tDataArray
   local tCSVItemDelimiter

# allow tsv files as well
      if pFilePath ends with ".csv" then
         put comma into tCSVItemDelimiter
      else if pFilePath ends with ".tsv" then
         put tab into tCSVItemDelimiter
      end if

   # import the csv file into a variable
   put url("file:"&pFilePath) into tData
   
   # rename some columns
   # row 1 of the csv file has the column headings
   put renamedColumnHeadingsFrom(line 1 of tData) into tColumns
   
   # we're done with the header now, so
   delete line 1 of tData
   
   # convert the csv file to a LiveCode array
   put csvToArray (tData, tColumns, tCSVItemDelimiter) into tDataArray
   ...

   # move some columns around
   put adjustedColumnHeadingsFrom(tColumns) into tColumns

   # set up the header
   set the dgProp["columns"] of group "sheet" to tColumns

Do things with the array after that. When you’re done:

# save an xlsx file for import into spreadsheet
   if there is a file pFilePath then
      delete file pFilePath  # XLSX_Workbook_SaveFile doesn’t overwrite
   end if
   XLSX_Workbook_SaveFile kWBName, pFilePath
   XLSX_Workbook_CloseFile kWBName

Some of the routines that use the XLSX routines:

The datagrid needs an array of data and that’s what the xlsx library exports to the output xlsx file so we need to convert the tab- or comma-separated data to an array.

function csvToArray pCSVData, pColumnHeaders, pCSVItemDelimiter
   local tLineCount, tDataCount
   local tLineArray
   local tDataArray
   
   put 1 into tLineCount
   # pCSVItemDelimiter allows for csv or tsv input files
   set the itemDelimiter to pCSVItemDelimiter

   # each line contains the data for one turf
   repeat for each line tTurf in pCSVData
      put 1 into tDataCount
      repeat for each item tItem in tTurf
         put tItem into tLineArray[line tDataCount of pColumnHeaders]
         add 1 to tDataCount
      end repeat
      put tLineArray into tDataArray[tLineCount]
      add 1 to tLineCount
   end repeat
   return tDataArray
end csvToArray

uOldColumnNames and uColumnNames are initialized by reading from the preferences file when the stack loads. Since they are kept in order in the preferences file we can iterate in a loop through both lists, renaming the old column name to the corresponding new column name:

function renamedColumnHeadingsFrom pColumns
   local tOldColumns, tNewColumns, tCount
   
   replace comma with cr in pColumns
   put the uOldColumnNames of me into tOldColumns
   put the uColumnNames of me into tNewColumns
   put 1 into tCount
   repeat for each line tColumn in tOldColumns
      replace line tCount of tOldColumns with line tCount of tNewColumns in pColumns
      add 1 to tCount
   end repeat
   return pColumns
end renamedColumnHeadingsFrom

We want the header row (row 1) and the totals row (the last row) to have bold text:

command SetBoldRows pColumns, pTotalsRow
   local tFontStyleArray
   local tLastColumn, tColumnChar
   
   put "bold" into tFontStyleArray["style"]
   set the itemDelimiter to tab
   # use the last column
   put the number of items of line 1 of pColumns into tLastColumn
   put char tLastColumn of kRowLetters into tColumnChar
   
   XLSX_Range_Styles_Set kWBName, kSheetName, "A1:"&tColumnChar&1, tFontStyleArray, "","", "", ""
   
   XLSX_Range_Styles_Set kWBName, kSheetName, "A"&pTotalsRow&":"&tColumnChar&pTotalsRow, tFontStyleArray, "", "", "", ""
end SetBoldRows

Here’s use of XLSX_Range_Styles_Set to set color and number format. Set some column colors to match our google docs spreadsheet:

NOTE: the columns and ranges are hard-coded here…May need to adjust them later on

command SetColumnColors pColumns, pDataArray
   local tBGArray, tCount, tRowCount
   local tColumn, tColumn2
   
   put the number of elements of pDataArray into tRowCount # count of rows (not including totals)
   put "solid" into tBGArray["type"]

set the “Undecided” total color. Color names are not available here, you have to use R,G,B format:

 put columnIndexFrom("U", pColumns) into tColumn
   put tColumn&the number of lines of pColumns into tCount # the totals row
   put "255,242,204" into tBGArray["interior color"]
   XLSX_Range_Styles_Set kWBName, kSheetName, tCount,"","",tBGArray,"",""

Set the “strong support” and “leans support” columns color:

put columnIndexFrom("SS", pColumns) into tColumn
   put columnIndexFrom("LS", pColumns) into tColumn2
   put tColumn&"2:"&tColumn2&tRowCount into tCount
   
   put "226,239,218" into tBGArray["interior color"]
   XLSX_Range_Styles_Set kWBName, kSheetName, tCount,"","",tBGArray,"",""

Also set the format for the last two columns to “number”:

 local tNumberFormatArray

   put 9 into tNumberFormatArray["format number"]
   put "O2:P39" into tCount
   XLSX_Range_Styles_Set kWBName, kSheetName, tCount,"","","","",tNumberFormatArray
end SetColumnColors

These two helper functions allow for translating a column header text to its corresponding spreadsheet column:

constant kRowLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
function columnIndexFrom pColumnName, pColumns
   local tLine
   
   put columnNumberFrom(pColumnName, pColumns) into tLine
   return char tLine of kRowLetters
end columnIndexFrom

function columnNumberFrom pColumnName, pColumns
   set the wholeMatches to true
   set the itemDelimiter to tab
   return itemoffset (pColumnName, line 1 of pColumns)
end columnNumberFrom

That’s it. Thanks to LiveCode and Zyrip the Slug’s xlsx library (a bonus included with the Excel library) our data management and reporting tasks during the election campaign were greatly eased. While we didn’t win our city council seat, we lost by only 377 votes out of over 7000 ballots cast. That’s the closest anyone has come to unseating an incumbent in our district in recorded history, and I’m proud of the fact that we did this in a three month campaign while the incumbent spent a full year rounding up endorsements and funds. Next time…

The spreadsheet library is available for purchase through the LiveCode website here.