Turning Trello into a Fully Functional Ticket System

If you haven’t heard of Trello by now…have you been living under a rock?

Seriously, though – Trello is a powerful and user-friendly board-style app. People use it for all sorts of things, but for me it has always been a handy organization system for my work. Trello boards consist of lists and cards; cards can be moved from list to list and can contain descriptions, checklists, attachments, and comments. You can even assign members of your board to a card.

I set up my boards with Backlog, Do, Doing, and Done lists. Cards start on the left and move, column by column, until they’re done.

For a long time I was the only member of my boards, but now that I’ve been able to bring on some freelance help I’ve also been able to add them into my boards, assign cards (tickets) to them, and communicate with them on a card-by-card basis. It’s really nice!

One thing that has always held me back, however, is that Trello has not been my one-stop-shop for tracking work. Trello doesn’t natively come with time tracking in cards, and it doesn’t natively come with a way to email status updates to clients who aren’t a member of the board. But I recently found workarounds for both limitations, and they have turned my Trello boards into powerful tools to track my work *and* communicate with clients.

Tracking Time in Trello

Time tracking apps are a dime-a-dozen, but I’d like to focus on a service called Toggl. Toggl integrates with 100+ services, including Trello, and with their Chrome-powered Toggl Button Extension you put a time tracker in every Trello card. Heck, you can put a time tracker on every checklist item. Want to know how much time you spent on every aspect of your gig? Toggl can do it.

Click here to read more about the Toggl Button Extension. I would note that while the extension is available in several browsers, I’ve found that it only embeds a tracker in my Trello cards and checklists on Chrome. Maybe that will change down the road.

Sending Notifications for Trello-powered Tickets

Zapier is an integration app that uses webhooks to connect various cloud systems, which is awesome. An action in App A magically triggers an action in App B. And by combining the power of Zapier with the content in your Trello boards and cards, you can easily automate notifications to your clients when cards get moved into your Done list, for example.

In Zapier, you’ll need to connect to Trello and your email provider of choice – I use Gmail.

You’ll then need to set up your trigger. Zapier will walk you through the set-up process. By the end, you’ll be selecting your Trello Board, List, and determining which card(s) this trigger will apply to. I set this to all cards.

Next up, you’ll set up your Gmail action. When a card is added to my Done list, I want to send an email to my client to let them know that the work has been completed. I’d also like to remind them what the work was to begin with, so there are a couple of fields we need to import from the Trello card.

For starters, I set the Gmail “To” field to match the Trello “Card First Comment Data Text” field. To make this work, right before I move the card to the Done list, I enter the client’s email address as a comment in the card.

Then I manually fill in the From, From Name, and Reply To fields with my own information.

I set my email Subject to match the Trello “Card Title” field.

Then in my email Body I add a nice message to the client letting them know that the work is done. You can see in the image below that these fields can be sent in-line with plain text, which is an awesome way to create automated but conversational messages for your clients. I even include the Trello card’s “Description” field in the email to remind my clients what I was working on.

You can even add attachments to the email based on your Trello card’s comments – that could be the description, or data from checklists and attachments, etc. Whatever Trello offers, you can plug it into your email message.

And voila! Finish the set-up process by testing your Zap (that’s what Zapier calls these integrations) and your customers will get a pleasant notification even time you complete work on a ticket.

Advertisements

Sending Values from an HTML Form to a JavaScript File

I was doing some UX/UI tweaking on a Google Sheet today and did some experimentation with custom menus vs. custom sidebars.

Menus are nice when you just need to execute script files. But when you need to pass values into a script, your options are:

A) making the user enter data into cells, or

B) setting up a custom HTML sidebar

As a developer, it would probably be easiest to create your “form” in the sheet. This, however, would leave your sheet vulnerable to accidental changes. User deletes a row here or a column there and all of a sudden your formulas and scripts break. So from a UX/UI standpoint, I think it’s easiest for the end user to enter values into an HTML form they can’t change.

This raises a new challenge – how do you get data from the custom front-end HTML sidebar into your server-side JavaScript file?

In Your HTML File

</pre>
<div id="stepThree" style="background-color:white;padding:5px;margin:5px;"><strong>Step 3:</strong></div>
<pre>

The code above is just a div block with some text and a button. Note that the onClick action makes a call to the callApiButton() script. This callApiButton() script lives in the HTML file as well, right above the closing tag.

function callApiButton() {
google.script.run.callAPI(document.getElementById('companyForm'));
var stepThree = document.getElementById('stepThree');
stepThree.style.backgroundColor = 'lightgreen';
}

This script does several things; first, it tells us that callApiButton() executes a script called callAPI and passes through the contents of the HTML element with an ID of companyForm. It also contains code that will turn the div containing this button green when it has been pressed – just a little visual confirmation for the user that they’re on the right track.

In Your JavaScript File

function getCompany(form) {
var companyName = form.companyNames;

This code is the bridge between your HTML and JS. In the HTML file we grabbed the value that the user gave us in the companyForm, and we passed it into the callAPI() script. The callAPI() script passes that same value into the getCompany() script and assigns that value to a variable – note that var companyName = form.companyNames, not form.companyForm. Back in our HTML file, we give our <select> a name of “companyNames” and in our JS file we’ve got to look at the <select> element to get our value.

When all of this is put together, the end user sees a form in the sidebar where they can select a company name from a drop-down list. Their selection is fed into a form that is submitted using a button, and then passed into our server-side API call.

Think you could do this better? I’m all ears – seriously! I’m always looking for ways to improve my code.

Switching Between Companies with the QuickBooks API

I’ve got a client whose accounting team manages several companies in QuickBooks. We’ve automated some processes for them using the ultra-useful QuickBooks Accounting API and the powerful flexibility of Google Sheets, but up until now I’ve been setting up API connections on a per-company basis, which is kind of a pain to manage and would never be user-friendly for the accounting staff. So I wrote some code to make company switching a much easier process, and I’ve got some ideas for how to improve it further.

Step 1: use Data Validation to turn a cell into a drop-down selection tool

Data validation is super easy to use. Pick your cell(s), select the “List of items” option, and enter your company names. You wind up a with a cell in your report where the end user can select the company they’ll be working with. This is going to drive the API call.

Step 2: Create an array of your companies and their QuickBooks IDs

Using the Google Sheets Script Editor, set up a .gs file – even the default code.gs will do.

Create an array of companies, like this:

var YOUR_COMPANIES = [
["Company 1", "1234567890"],
["Company 2", "9876543210"],
["Company 3", "1357924680"]
];

You’re ready to move on.

Step 3: Set up the rest of your scripts

I’m a big fan of using templates whenever possible – no point in reinventing the wheel when you don’t have to. So I highly recommend this OAuth2 for Apps Scripts Github repo which contains all of the building blocks you’ll need to connect Sheets to QuickBooks.

Step 4: Some slight modifications are in order.

function getCompany() {
var companyName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Input").getRange("I1").getValue();
for (i = 0; i < YOUR_COMPANIES.length; i++) {
if (YOUR_COMPANIES[i][0] == companyName) {
var companyId = YOUR_COMPANIES[i][1];
Logger.log(companyId);
return companyId;
} else {
Logger.log("No match found");
}
}
}

All this does is figure out which company in the array matches the company the end user selected, and then it returns the company’s QuickBooks ID so that it can be passed into another script.

We take that company ID and we pass it into the URL of our GET request.

//get the company ID
var companyId = getCompany();

// call the API, return the object, and parse
var url = 'https://quickbooks.api.intuit.com/v3/company/' + companyId + '/query?query=Select%20%2a%20from%20Department&minorversion=4'

This is great – we have given our end user the ability to select which company’s data they’d like to GET via the API. But the biggest challenge remains – every time they need to change companies, they can’t just pass in a new company ID through the URL. QuickBooks uses OAuth2 which requires the end user to authenticate to a specific company, and then QuickBooks returns a token for that company. So when we want to switch companies, we need to break the connection and then re-authenticate.

Step 5: Let them reset the connection and re-establish it with a new company

I built this handy little custom menu to let the end user do all this work without really realizing everything that is entailed. That’s the beauty of good code – the end user just knows that it works 🙂

When they click on Reset Connection, it runs the reset() function from the OAuth2 template.

When they click on QuickBooks Verification, it runs the makeRequest() function from the OAuth2 template. Our code runs, and since we haven’t authorized yet, we execute this ELSE statement:

} else {
var authorizationUrl = service.getAuthorizationUrl();
Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
var htmlOutput = HtmlService
.createHtmlOutput('Click the following link to continue: Click Here')
.setWidth(250)
.setHeight(300);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Verification');
}

So in the OAuth2 template, the script just sends the authorization URL to the log. I needed to send the URL to the end user so they could click on it, so I created a modal that pops up and gives them a link to click. The end user clicks the link, they authorize to the company they originally selected, and then they redirect to a success page. Then they can go back to the Sheet and run the Refresh Customers menu item, which unbeknownst to them just runs the makeRequest() function again. This time, since they’re authorized, their data refreshes. Poof. Magic.

How could I make this better? For starters, I’d like to move all of this into a custom sidebar. Rather than using Data Validation to turn a cell into a drop-down menu, just pop open a sidebar where the drop-down menu can live. And then move the three custom menu items into the sidebar as well so all of their selections and clicks can happen in one place.

What else would you do to make this easier for the end user?

Sheets to Docs Mail Merge

TL;DR: I needed a way to perform a mail merge, passing data from a Google Sheet over to a Doc. Nearly everything works, with the exception of two complicating factors – the template file I’m copying includes a table, and one of the table cells must contain an image. I’m stumped!

---

If you’re anything like me, when you think “mail merge” you don’t think “G Suite”. For years I’ve been mail merging with Excel and Word because…it’s easy. You open the wizard, you click some buttons, and poof – labels, envelopes, whatever. But a recent project requiring a QR code API and product labels has me digging into the depths of G Suite for a solution that will allow me to get the QR codes via an API and then feed the code along with some product data from a Google Sheet.

Pulling in the QR codes was a piece of cake.

// Writes images to a spreadsheet
function generateCodes() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var firstRow = 2;
var lastRow = sheet.getLastRow();
var barCol = 2;
var codeCol = 8;

for (var i = firstRow; i <= lastRow; i++) {
var currentValue = sheet.getRange(i,barCol).getValue();
var url = "https://api.qrserver.com/v1/create-qr-code/?size=50x50&color=000000&margin=2&bgcolor=255-255-255&data=" + currentValue;
var urlRequest = UrlFetchApp.fetch(url).getBlob();
var qrValue = sheet.insertImage(urlRequest,codeCol,i);
}
}

// Deletes all images in a spreadsheet
function clearCodes() {
var images = SpreadsheetApp.getActiveSpreadsheet().getImages();
for (var i = 0; i < images.length; i++) {
images[i].remove();
}
}

After writing that code, I realized it would be even easier to just insert a Sheets formula – I don’t really need to use a FOR loop to write images to the spreadsheet, just point to their location. Just use something like:

=arrayformula(image(hyperlink("https://api.qrserver.com/v1/create-qr-code/?size=100x100&color=000000&margin=2&bgcolor=255-255-255&data=" & B2:B9)))

That formula grabs the base URL for the QR code API, tacks on the barcode or product ID from column B, and spits out the QR code itself into an array of cells.

Now that the QR codes are created, it’s time to mail merge. This is where things get tricky.

There are a couple of decent add-ins already out there that will do this – autoCrat was the best that I found. It had a slick interface, a lot of customization, and best of all – it worked. But how does it work? I needed to find out. So I did some digging to see if I could write some code that would do basically the same thing, just without most of the polish.

I found this StackExchange question and answer where a user basically laid out exactly what needed to happen to create a basic mail merge. They even went as far as to dump the code in Github. So I ran the code, and it almost all worked – everything except the QR code passed through.

I logged some test results, and I figured out that the script sees my template as a paragraph, a table, and another paragraph element. It’s writing text to the paragraphs elements just fine, but when it comes to writing the QR code to the table, it’s not producing the intended result. If I change the field that I’m trying to write to the table – say, to regular text – then it works. So there has to be something about the QR code image that this script doesn’t like.

} else if (child.getType() == DocumentApp.ElementType.TABLE) {
mergedDoc.appendTable(child);

Maybe I’m writing the table to the Doc, but the image isn’t being written to the cell? Or maybe the problem is in the FOR loop that overwrites the existing template contents?

for (var f = 0; f < fieldNames.length; f++) {
body.replaceText("\\[" + fieldNames[f] + "\\]", row[f]);//replace [fieldName] with the respective data value
}

I’m at a loss. What do you think?

You Get What You Pay For

I learned an important lesson this week.

In my role as a professional web designer, I do my best to respect my clients’ budgets. I know that not everyone has $40,000 laying around for a website. Sometimes – especially when using an open-source system like WordPress – you’ll find there are multiple ways to get the job done. For example, if you want a plug-in that can modify your functions.php file, a quick search will turn up half a dozen viable options. And usually there’s at least one free option that will work out just fine. No need to pass along the cost of a premium plug-in to the customer. Everybody wins.

But on my latest project we ran into a problem. My client needs to offer financing options by means of a subscription system – the end user should be able to pick between 36, 60, and 120-month financing options. Each comes with a different monthly cost, of course. So, as I usually do, I browsed the plug-in marketplace and found that there were a number of options that promised to handle subscriptions. And since my client wanted to use Stripe as the payment processor, and Stripe also offers the ability to set up subscriptions, I wanted to use a plug-in that would allow my client to manage the subscriptions from Stripe’s back-end while offering his customers the ease-of-use that comes from a WooCommerce front-end.

I went through iteration after iteration, changed countless settings, chased that rabbit as far down the rabbit hole as I could go. Multiple plug-ins. Multiple configurations.

Nothing. Nada. Couldn’t get it to work.

I had to admit defeat.

And, in the end, my client had to shell out $199 for the “official” WooCommerce Subscriptions plug-in. And guess what? It works! Out of the box!

It was truly a case of “you get what you pay for”. I spent hours trying to get the free plug-ins to do something that they simply couldn’t do and honestly I’m going to take the hit for some of those hours – no sense in billing my client for the time it took to experiment with the free options. Chalk it up as a learning experience.

That’s not to say that all free WordPress plug-ins are bad, or that paid options are always better. But if you’re a WordPress designer/developer, don’t forget that sometimes it might cost you and your client more in the time and labor it takes to force workarounds for free plug-ins when you could just pay for the one that works right away.

 

Tech Sandwich, Episode 1

Five Things You Need to Know About Digital and Web Literacy This Week, vol. 1

I could call it FTYNTKADAWLTW, buuuuuut nobody would read that.

So the Today Doug Learned newsletter is my weekly dump of links relevant to digital and web literacy – good, informative reads that will highlight key areas in which digital literacy efforts can change the web – and the world – for the better.

---

Fake News Or Real? How To Self-Check The News And Get The Facts

via NPR

False Averages and Consensus

I know, I know – if you wanted to read Seth Godin all the time, you’d just visit his blog.

I can’t think of many writers who I turn to every single day for inspiration, new perspectives, or to refresh my commitment than Seth.

His post today was on point, as always:

Yes, there are true averages (like how high to mount a doorknob). But more often than not, trying to please everyone a little is a great way to please most people not at all.

And it’s true! I worked with someone once who always talked about consensus within the organization. Like averages, a consensus isn’t always a bad thing. But a consensus can be downright fatal as well! In the same way that using a middle-ground product or service to please two groups of people with opposite preferences leaves none satisfied, forcing a consensus between two groups of people with opposing viewpoints can produce an action plan that nobody cares to undertake. You’ll leave the meeting feeling good because perhaps you avoided conflict or appeased the centrist minority, but guess what – your project is dead on arrival.

Sometimes you need to accept that your one group might need to pursue more than one course of action, or perhaps the group simply needs to split into two.

Trello for Desktop is here!

It was supposed to be available on 9/14.

I checked all day…refresh, refresh, refresh…and nothing.

Then, I thought maybe it would become available on my birthday.

Still…nothing.

So this morning I saw that Trello for Desktop (for Windows) was finally available!

Rejoice!

I’ll spend some time playing around with it some more, but I really like the idea of having the application open and being able to work in my boards locally with changes being synced to the web.

I also like one of their newest features, which is embedding cards. I’m going to give it a test run right now.

Video production card template

Do you use Trello? What are your favorite features? What power-ups do you use on a daily basis?

100 Days

I’ve been running WaltersWorks in various forms since 2012, but I’ve only been full-time for the last 100 days.

What have I learned in that time?

A bunch, that’s what. I’ll try to break it down in this blog post.

Be patient.

When you’re working a 9-5 job and your business is just a side hustle, you’ve got plenty on your plate to keep you busy. But when you step out on your own…

…it’s often a waiting game.

I’m a pretty level-headed guy, but when your income is project-based and you’re waiting for feedback, approval, payment, etc. I do find myself starting to panic when things are moving slowly.

It’s the nature of the game, though. Several of my projects have pushed well past six months because I’m never going to tell my clients to hurry up. Even when I need that money to come in.

I never let projects fall off track, though. Even if it’s just a reminder email, a quick phone call, or a text to say “hi”, I make sure to give my clients subtle prods. And they seem to appreciate it!

Doug. Your constant reminders keep me on track.

That’s what I’m here for.

Be flexible…

I started out just designing web sites. But over time, I’ve generalized my services – a lot. Once I went full-time I found myself doing work that I never thought I would do. I was transcribing audio files, taking photographs and shooting video, narrating videos, managing social media accounts…even basic computer troubleshooting. I wasn’t going to turn down any work that landed in front of me.

Generalizing like that has helped me to extend my network. I’ve connected with individuals and business owners who have passed along my name to their friends and professional contacts. Doing good work – any kind of work – will get you further ahead than simply waiting for the kind of work you’d rather be doing to show up.

…but know when to specialize.

A month or two ago, I sat down with a mentor from SCORE. One of the things he told me that really stuck with me was not to diversify too much. And he’s right – eventually, every generalist has to specialize. All of the odd jobs I was doing came with a wide variety of hourly rates. I was making anywhere from $5 to $60 an hour depending on the source of the work. I reached a point where I had to cut something out…so I ditched the low-paying transcription work in favor of more specialized web projects.

I still think it was valuable for me to take a wide range of gigs at the onset. Bills to pay, you know? But you have to be ready to transition as opportunities to specialize become available. At some point, you’re going to have to say no to the gigs that aren’t getting you ahead.

Maximization vs. optimization

This idea came to me courtesy of Seth Godin’s blog.

Capitalism sometimes seeks competitive maximization instead. Who cares if you burn out, I’ll just replace the part…

That’s not a good way to treat people we care about, or systems that we rely on.

Reflecting on my various careers, it’s true that we often find ourselves pressured – internally and externally – to maximize. Put the pedal to the metal and churn out as much work as you can, work those nights, work those weekends, churn, churn, churn!

What I found is that burnout is real, and when you reach a point where you can optimize, do it.

Identify your strengths and weaknesses. Are you an early riser or a night owl? Figure it out, and then set up your schedule so that you’re working at the optimum time for you, not based on somebody else’s schedule. Find balance. Make time for you – time to prepare, time to relax, and time to reflect. They say when it comes to finances, you should always pay yourself first. The wise man budgets his time in the same way.

Keep learning.

You never know when the industry you work in will be disrupted. Working in a tech field, I’m sure that the work I do will be automated more and more to the point where web design is literally as easy as making toast. Younger generations are going to enter the working world and they won’t need me to publish across their social channels. Taking photos and videos won’t require a trained eye (heck – they already don’t).

During these first 100 days, I’ve made it a priority to keep learning. I’ve also made a point to keep teaching. Nothing ignites my passion more than learning something new and then sharing it with others. Who knows what the next 5, 10, or 50 years will look like – but as long as I have an open mind and my eyes fixed on the road ahead rather than the road behind, I’ll be okay.