Blog

4 Articles

Push to Slack Channel on Google Form submission.

(Original article was written November 7th 2015)

A recent project that I was apart of was using Google forms for an account request process.  It is not unusual to want to notify a team about new submission and the group working on this project recently adopted using Slack for some communications.  The first thing I looked for is an existing “add-on” to Google spreadsheet, which there was at the time but it has some limitations for the initial free version to about 40 notifications and then you need to buy more (apparently “add-ons” no longer can run in the background so there is no Slack notification “add-on” anymore). The price wasn’t steep but I figured this would be a great opportunity to play with the both theGoogle sheet scripts and the Slack API to push some automated notifications to one of our channels – also when you write and deploy your own solution you are confident that it is more secure and your information is not going somewhere else (with an app/add-on you have no idea what is happening).  So after some research into doing some Google sheets  app scripting.  I came up with the following functions (source can be found on github https://github.com/scleveland/google-form-to-slack).


function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
 
  //Set a trigger when the form updates the spreadsheet to call our Slack notification function
  ScriptApp.newTrigger("CreateMessage")
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit()
    .create();
 
}

function CreateMessage(e){
  try {
    var spreadsheet, columns;
    var my_message;

    //fetch the column names
    spreadsheet = SpreadsheetApp.getActiveSheet();
    columns = spreadsheet.getRange(1, 1, 1, spreadsheet.getLastColumn()).getValues()[0];

    // Only include form values that are not blank
    for (var keys in columns) {
    var key = columns[keys];
      var val = e.namedValues[key] ? e.namedValues[key].toString() : "";
      if (val !== "") {
        my_message +=key + ' :: ' + val + '\n';
      }
    }
    SendSlackMessage(my_message);

  } catch (e) {
    Logger.log(e.toString());
  }
}

function TestSlack(){
  SendSlackMessage("testing my stuff"); 
}

function SendSlackMessage(message){
var url = "https://slack.com/api/chat.postMessage";
  
  var payload =
      {
        "token" : "CHANGE TO YOU KEY",
        "as_user" :"false",
        "text" : "New Request\n" + message,
        "channel" : "#YOUR-CHANNEL",
        "attachments" : [{"pretext": "Notification", "text": message}],
        "type" : "post",
      };
  
  var options =
      {
        "method"  : "POST",
        "payload" : payload,   
        "followRedirects" : false,
        "muteHttpExceptions": true
      };
  
  var result = UrlFetchApp.fetch(url, options);
  
  if (result.getResponseCode() == 200) {
    
    var params = JSON.parse(result.getContentText());
    
    Logger.log(params);
  }
}

To use the above code you must have a Google form setup then go to the response Google sheet associate with it.  Once on the sheet you can go to “Tools->script editor…”.  A new tab should open with the script editor and some default code in the “Code.gs” tab should be visible.  Remove the default code and replace it with the above and save it as whatever project name you wish.  Next you need to run the initialize function by going to “Run” in the menu and choosing “initialize” it will ask you to approve and allow the script to run – do so.

To get this function with Slack you have to have to use your Slack user or create a Bot and set a token that you can use as part of your payload to authenticate to your Slack team space (NOTE the “as_user” field can be set to “true” s that the either you or the named bot show up as the entity posting the message in the channel if you leave this “false” it just posts as “bot”).  You can do this by going to you account settings or your integration settings if you wish to use the Bot. Next you need to decide which channel to use or create a new one and set it in the payload (NOTE If you are using a specific bot to post the message they have to be a member of this new channel otherwise it will not be able to post messages).

Once you have repleaced the token and channel in your script and it has been initialized by running the initialize function in the editor you are good to go. You can test that the settings are correct and will post to your slack channel by selecting the TestSlack function from the dropdown and pushin the play/run button – it should add a message in your slack channel “testing my stuff”. You can ten test by submitting to your Google form and seeing the results in your slack channel.

442 views

Having DataMapper Build You A SQL Query

ORMs are great and they can handle a lot of operations that I don’t particularly want to write from scratch.  But sometimes you need to get in and use the SQL or the power of whatever back-end you want to play.  DataMapper allows you to do this and you can find the documentation here under “talking directly to your datastore“.  But lets say I want to take advantage of the way DataMapper builds my query and sanitizes it because I’m too lazy or their are way more injection attacks then I can keep up with but a community ORM adapter can.  Also,  sometimes I don’t want DataMapper to construct a ruby object for me that consumes more resources and I can get by with just the structs when I’m getting back 100,000s of records.  So in summary I want DataMapper to build my sanitized query but I want to   talk directly to my backend.

Here is the solution that we came up with to extend DataMapper in our application and in this case we are assuming that we are using some sql version of an adapter.

module DataMapper
  def self.generate_sql(dm_query)
    statement, bind_vars = repository.adapter.send(:select_statement, dm_query.query)
    repository.adapter.send(:open_connection).create_command(statement).send(:escape_sql, bind_vars)
  end
end

With this in our application we can now make a call like:

  sql = DataMapper.generate_sql(User.all(:name.like=>’%Bob%’) 

This will return an SQL statement for selecting all the users in the User Table that match Bob in them.  If we want to execute this SQL against our backend then all we need is:

  results = repository(:default).adapter.select(sql)

And now results holds the records as structs.  This is useful when dealing with a large number of records and you just want the results. Other fun things you may want to do include building transactions out of your DataMapper generated statements since I find some things to be lacking with the DataMapper transactions.

11 views

Agile Task Mobile App

So a friend of mine, @jaderobbins, and some of his buddies wrote a cool productivity service called AgileTask and I have been using it for the last two months and find it well suited in helping me get things done each day.  I like it so much that I wanted to get a mobile version for my Droid Incredible.  Unfortunately, there is no app at this time.  However, as I have wanted to write an android application for months now and AgileTask has an easy to use API I have started.

I looked at Titanium’s Appcelerator at first and didn’t get to far and then I ran into rhomobile as I was looking for something that used Ruby since that is what I use day in and day out at work currently.  Having started playing with the framework yesterday afternoon I have already been able to churn out an app that can pull my tasks for “Today” and my “Icebox”.  So needless to say I find this framework Super Awesome  right now.

So I the next few days I’ll put together a git repo (https://github.com/scleveland/AgileTaskMobile) and crank out some more feature and hopefully will have an alpha/beta app in the next week for other AgileTask Pro users to use (Sorry but you will need to have a Pro account to use the app as API keys are only available for Pro Users – but the price is totally worth it for such an awesome software as a service).

Oh and I almost forgot… The rhomobile framework can also output an iPhone app using the same code so I’ll also play with testing that (Blackberry is also supported but I’m not motivated to churn anything out for that – someone else can fork the project if they wish to do a blackberry port)

2 views

New website – M K Daughters Photography

I’ve been working on a website for some clients for the past few months.  mkdaughtersphotography.com is built on Ruby on Rails (ROR) and also has some jQuery employed to power the slideshows and filmstrips.  The basic layout is mostly complete and the work on the client albums section is underway – I hope to use the DataMapper ORM on top of the backend because it is pretty awesome.  Go ahead an take a look at the site and see how great the photographs are 🙂

2 views