The last time I was traveling, I noticed a little stand just past the security checkpoint, asking me to rate my experience. The five buttons ranged from a red frowny face to a green smiley, giving me the ability to respond with what must be the full range of human emotion. Every time I see one of those kiosks, I have to wonder: who uses those things, and who actually looks at the data?

Perhaps inspired by that kiosk, Peter's company does something similar. There's a Slack bot which messages each employee every two weeks to get them to evaluate, on a scale of 1-5, how good a certain aspect of their job is. That data gets collected up onto a Google Sheets dashboard. And yes, the Google Sheet is both the datastore and the dashboard, which isn't surprising at all.

Now, if you're using a regular old SQL database, you know that you shouldn't manually construct SQL strings, and instead use prepared statements, etc. to manipulate the data. But what if a spreadsheet is your database? Well, those rules go out the window, as this Scala block demonstrates:

def statsSheet(year: Int): Vector[Vector[String]] = {
    def monthStats(question: Int): Vector[String] = Vector.tabulate(numberOfPeriods(year))(month =>
      s"=IFERROR(AVERAGE(FILTER('$year'!${weekToColumn(month)}$$2:${weekToColumn(month)}; $$A${2 + question} = '$year'!$$C$$2:$$C)))")

    def questionStats: Vector[Vector[String]] = questions.zipWithIndex.map { case (question, index) =>
      val averagesByPeriod = monthStats(index)
      Vector(question) ++ averagesByPeriod ++
        Vector(s"""=IFERROR(AVERAGE(INDIRECT("RC[-${averagesByPeriod.length}]:RC[-1]", false)))""")
    }.toVector

    val average: Vector[Vector[String]] = Vector(
      Vector("Average") ++ Vector.fill(numberOfPeriods(year) + 1)(s"""=IFERROR(AVERAGE(INDIRECT("R[-${questions.size}]C:R[-1]C"; false)))""")
    )

    Vector(Vector("Question") ++ Vector.tabulate(numberOfPeriods(year))(weekToDates(year, _)) ++ Vector("Year")) ++
      questionStats ++ average
  }

This block of Scala code builds the statistics sheet, and as you can see, it directly constructs formulas to inject into Google Sheets. That alone is pretty ugly and awful, but there's a more subtle problem here.

This Slack bot messages employees every two weeks. Well, that monthStats method isn't aggregating stats across an entire month- it's doing the stats across individual periods, which are two weeks long. So the month variable which gets passed to the weekToColumn method holds an integer representing the individual two week period, not the month.

Now, it's easy to say: "this is just a silly bot so who cares?" But users love dashboards. Especially pointy-haired-bosses. This employee survey has key performance indicators tied to the results, HR and management goals for employee satisfaction depend on those, and a variety of new policies crop up any time certain questions start drifting below an average of 4.

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!