Update to yesterdays post

Yesterday, I wrote about making your own reminder service using Google Sheets. Today I made some improvements to that code to look for a date as well, so it’s more viable.

A couple of notes:

  • The timzone function in google scripts seems to be off by an hour, not sure why. So you may have to adjust that.
  • If there’s no date in the spreadsheet, that item is repeated daily.
  • Make sure you’re the date field is set to plain text, and that the date you enter will match the format of the date inside the script: 04-18-2014
  • If you run into trouble, the Logger function is invaluable.  “View > Execution Transcript” will show you everything about the last run.

I think that’s about it. So without much ado, here’s the updated script, basic as it may be:


function sendEmails() {

 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 2; // Start on row 2, row 1 is the legend
 var numRows = 100; // How many rows to check? Set high.
 var dataRange = sheet.getRange(startRow, 1, numRows, 5)
 var data = dataRange.getValues();

 var todaysDate = Utilities.formatDate(new Date(), "GMT-4", "MM-dd-yyyy");
 // This works but the TZ is off by an hour, looking into this
 var currentTime = Utilities.formatDate(new Date(), "GMT-4", "HH:mm"); 

 for (i in data) {
 var row = data[i];
 var emailAddress = row[0];
 var subject = row[1];
 var date = row[2];
 var time = row[3];
 var message = row[4]; 

 if ((date == '') && (time == currentTime)) {
 MailApp.sendEmail(emailAddress, subject, message);
 }

 if ((date == todaysDate) && (time == currentTime)) {
 MailApp.sendEmail(emailAddress, subject, message);
 } 

}
}

Creating your own reminder service

Yesterday, a colleague of mine posted about a service called IFTTT (If this then that) which can create relationships between different services like Facebook, Gmail and Twitter.  Through the use of triggers, you can do some cool stuff.  Be sure to check out Dan’s writeup, it’s probably much more interesting than what you’re about to read.

For me, IFTTT is a little overkill, I usually stick with Google Calendar to remind me about stuff I’m planning on ignoring, but it got me thinking about creating my own very basic version of a crontab in the cloud for some notifications. Like submitting expense reports etc.

Enter Google Sheets and time-driven triggers.  Oh, and before you ask what the point is, the answer is just a little fun.  And if you’re wondering why my idea of fun is working on spreadsheets… ummm… data. mmmmmmm.

So first, we create a new Google Sheets document with some basic details: email address to notify, subject, date, time and message body:

Screen Shot 2014-04-17 at 11.20.31 PM

Once you have a few entries in there (notice the date is blank, I haven’t worked on that just yet – future post) click on Tools > Script Editor

Screen Shot 2014-04-17 at 10.17.57 PM

Here’s the code to add:

function sendEmails() {

 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 2; // Start on row 2, row 1 is the legend
 var numRows = 100; // How many rows to check? Set high.
 var dataRange = sheet.getRange(startRow, 1, numRows, 5)
 var data = dataRange.getValues();

 var todaysDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
 // This works but the TZ is off by an hour, looking into this
 var currentTime = Utilities.formatDate(new Date(), "GMT-4", "HH:mm"); 

 for (i in data) {
 var row = data[i];
 var emailAddress = row[0];
 var subject = row[1];
 var date = row[2];
 var time = row[3];
 var message = row[4]; 

 if (time == currentTime)
 {
 // Function to send email, lets wrap it.
 MailApp.sendEmail(emailAddress, subject, message);
 }
 }
}

What does this do?  Right now nothing unless you run it manually using the play button in the toolbar.  For the automation magic, you need to add a trigger.  Click on ‘Resources’ in the toolbar, then ‘All your triggers’:

Screen Shot 2014-04-17 at 10.55.51 PM

Now click on the ‘Add a new trigger’ link and fill it out:

Screen Shot 2014-04-17 at 10.56.05 PM

You’ve now scheduled your app to run every 60 seconds, checking for upcoming events.  If the time in your spreadsheet matches the current time, emails get sent out.  I’ll probably make some changes and add date support as well.

So, is this better than a commercial service like IFTTT, Google Calendar or a plain old crontab entry set to send an email? Probably not.  Was it more fun to work on? You betcha!

Making Colloquy IRC logs more friendly

If you’ve ever logged anything in “human readable” format from the OSX IRC client Colloquy, you’re probably used to seeing the logfiles in XML format, similar to this:

(click here for a more readable format)

  <event id="XXXXXXXXXXX" name="memberNewNickname" occurred="2013-01-02 12:00:00 -0500">
    <message>user1 is now known as <span class="member">user1|afk</span>.</message>
    <old>user1</old>
    <who hostmask="user1@X">user1</who>
  </event>

Not bad when there’s only a few entries, but with multiple logfiles containing thousands of entries each, it can chew up time and eat away at system resources. Wouldn’t it be nice if it looked more like this?

[12:00pm] user1 is now known as user1|afk.

Well, tonight I needed to go back and look for something in an old log, and parsing through the format of the log made it difficult to do with a simple grep command (note: I said simple). After some poking around I ran across a pretty simple, yet elegant solution: xsltproc.

From the project description: “It takes as first argument the path or URL to an XSLT stylesheet, the next arguments are filenames or URIs of the inputs to be processed. The output of the processing is redirected on the standard output.“. Yep, that’s what we need.

I found a pretty good PlainText.xsl file in a Google cache copy of a defunct add-ons page for Colloquy (it could use some improvements though, it only displays the time – no date. I’ll work on that), you can grab it here.

xsltproc /path/to/PlainText.xsl /path/to/channel.colloquyTranscript

The result? A nicely displayed logfile.

Happy Parsing.

Dealing with “Like Spam”, and a few spitballed solutions..

wordpress-logo-notext-rgb

This isn’t a new problem, merely a new approach to getting spam on your blog and in your posts.  The angle?  “Like” a page.  Seems innocent enough, but there’s a little more to it.  Likes are shown on each one of your posts, with links to the person’s Gravatar profile.

So what’s the problem?  Well, on those Gravatar pages, there are usually links to SEO and money-making websites, so there’s a possibility that your blog posts are helping their ranking in popular search engines, or that *your* followers may check them out thinking that we’re all like minded.

It’s frustrating, but we’ve been told that the WordPress folks are working on a solution. Until then, this is just me talking aloud.

What do you do now?

So what options do you have right now if you’ve experienced this on your WordPress blog?

  • Disable “likes” on posts that seem to attract these sorts of people (although in my own personal experience, there’s not a specific post type that is more appealing than others).
  • Disable “likes” altogether on your blog until there’s a better solution.
  • Visit the users Gravatar page and report their profile as spam.  I haven’t had a lot of success with this, but I’m sure there’s a backlog, WordPress accounts outnumber the staff millions to one.

What’s the solution?

That’s a matter of opinion.  From reading the support forums and many many blog posts, it’s evident (as expected) that just about everyone has their own idea about how “likes” should be handled with a few very vocal about not having them at all.

These are just some ideas I had to deal with the issue:

Solution #1 – Moderate “Likes”

This lines up with how WordPress users deal with comments, allowing the blog owner to choose how they would like to deal with “Likes”.  Maybe a simple radio button in their site config with some basic options:

  1. Allow all “Likes”
  2. Hold all “Likes” for moderation
    • This option would allow you the opportunity to do a little investigating and determine whether or not you want their like displayed visibly. Perhaps an additional link to report “like spam” to the WordPress staff.
      • Downside:  Lots of extra cycles would be required for staff to investigate all the spam claims.
      • Downside: For mainstream blogs that get a *lot* of likes, it could be quite time consuming to approve each one.
  3. Hold new “Likes” for moderation, allow previously approved users to like without moderation
    • Downside: User can create an account with no spam content, like a bunch of sites and then go back and add spam content to their site without the blog owner being aware.

Solution #2 – Edit “Likes”

This seems like a less likely solution.

  • Allow blog owners to delete “likes” at will. Once a like has been deleted, the profile cannot “like” the page again.
    • Downside: This doesn’t really address the issue, you’d spend all day (if you have a busy blog – not like this one) removing likes.

Solution #3 – Gravatar crack-down on self-hosted sites

This could be resource intensive.  I’m not suggesting a review of every Gravatar account, just an update to the terms of service and strict policy enforcement going forward.  The spammers will out themselves, the WordPress community will make sure of that.

  • Hold linked sites in Gravatar “verified” profiles to the same standard as WordPress.com users.
    • Violations should result in permanent blacklisting:
      • Gravatar account revocation.
      • URL’s associated with the previous account would be disallowed in *any* future Gravatar account.

Solution #4 – Combine solution #1 and #3.

I think this may be the best solution.  The combination of self moderation (for smaller sites) and policy changes to allowable websites in profiles may help reduce the numbers of spam likes and comments tremendously.

Conclusion

I don’t really have a conclusion, just a myriad of thoughts on the subject.  I’m sure the WordPress folks are all over this on the backend and have probably already contemplated every possible scenario.  I’m anxious to see which solution they run with, and to see these spam likes stopped.