Sending Daily Reports to a Slack Channel from Google Sheets

If you’ve ever done any kind of commission or service work for many people, and at some point started to outsource or hire out that work, you’ve probably experienced the stress that comes with depending on other people to make sure work gets done in a timely manner. There’s always the chance someone will forget to finish the work for one of your clients, especially if you have several new clients signing up every day.

Where I work, clients and services are organized in a Google Sheets spreadsheet, with every purchase having it’s own row containing information about the purchase type, what services were purchased, whether or not we have the information we need to run that service, and the date it was ready by (among other info). The spreadsheet is now several thousand rows large, with limited sorting capabilities. It take several minutes to scan through the list and find out if some clients have been waiting longer than they should be to get what they purchased done. It’s something that’s easy to forget to do, yet something that’s necessary to make sure nothing is forgotten.

To solve the issue and make sure everyone is kept up to date with the current status of the different services that need to be done, I hooked into Slack’s bot API from inside Google Sheets. Early every morning (before the work day started), it would send a report with all of the services ready to fix (usually 5-20), the age of each one, and some other useful data.


How Did I Do it?

Using Google’s (quite extensive) Scripts service, I scanned the spreadsheet using the Sheet class and the UrlFetchApp library to send an API request to Slack’s API, all in JavaScript. Then, I set up a Scripts Time trigger to run the function that sends the report, every morning at 4-5am.

This feature is something I definitely plan to use for other types of reports, I may even expand it to make queries from Slack to retrieve & transform data from the spreadsheet.

Using Node Applications to Create Tools and Automate Processes

As part of my job scanning and fixing many WordPress websites, I go through a lot of repeated tasks. Sometimes, I will scan as many as a hundred websites, each website requiring steps for marking down different bits of information.

One thing that takes a bit of time that I really want to automate, is collecting user logins for the admin account. It takes me several hours to go through a batch of 200 or more emails. A lot of the data I need is in the form clients send for getting their site scan service done, but it always needs verification. Another thing that needs to be done, is copying all the data into a Password Safe for use later. Doing that takes up the largest amount of time, after testing logins.

I’ve been playing around recently with developing Node apps, and I’ve gotten better at it with practice. An idea I had for automating some of these tasks, was to replace the Password Safe program I currently use (desktop application with the save file in a Dropbox folder) with a custom Node app, storing data in a MySQL database.

It wouldn’t be too difficult to create a simple app that grabs the form data and stores it correctly. It would have an interface which would allow me to easily verify each one (generated admin links and simple one click confirmation buttons). When confirmed, it would also update the spreadsheet to mark it as ready to scan.

Another feature I could add would be in the form of a Chrome plugin, which would fill the login data automatically when going to the login page later on when I need to run a site scan.

The only concern with running this system is security. The most obvious solution to security would be a full authentication method, with a login and proper data sanitation. Then I realized only a few people would actually be using it, so I could use an IP whitelist to only allow certain IPs to use it. The passwords would be stored in a MySQL database, so I don’t have any concerns there.

The final thing I need to worry about would be hosting. I don’t want to self host it, as that would involve always having the computer running (and let’s face it - even desktop computers need to shut down eventually) as I wouldn’t be the only one using the app.

Thankfully, you can get really cheap VPS hosting. I’ve had several through RamNode, which start their VPS’ at as cheap as $4/month. It comes with a Linux installation, full SSH access, etc. Enough to host a simple server, which is more than enough for a low-load Node application.

Using User Stories for Feature and Internal Development

Something that’s commonly used by Scrum teams are User Stories. They’re used as a way to keep development concentrated on solving an issue rather than developing a feature. Often times they result in the same things. For example, a user may want an extra setting in a script for automatically making links in an article open in a new tab (target=”_blank”). The setting might allow them to exclude URLs by domain.

There are two ways this might be done. In many workflows, the person responsible for making sure this feature gets done would tell a team member to “implement a setting feature for excluding URLs by domain”. There’s also a fair chance he’ll tell the team member where exactly the field should go, and any other aesthetic features. The team member will get it done, mark it off the list (send send it off for testing), and continue onto the next feature.

For Scrum teams, though, there isn’t a specific person in charge of assigning features to specific people. Not only that, but the process of planning out a specific feature, getting it done, and handing it off is contrary to the Agile principle. Enter user stories.

A user story is written in the following format:

“As an X user I want to be able to accomplish Y.”

The reason for using that format over slapping a feature onto the to do list is it both gives you a bigger picture of why you’re creating a certain thing, and ensures that what you’re doing will stay in line with the reason you’re doing it.

Part of the process of using user stories is to ensure that when the feature is complete, that it still meets the goals of the user story. This also allows for a clear definition of when something is done - if the user story can be completed by using the new feature, the new feature is complete. Another great plus is that as the developer creating the feature you can now test it yourself, by acting as the user in the user story.

Using Google Sheet's Scripting to Create Discount Codes in Shopify

My mom needed 250 discount codes created to go onto product labels as a bonus gift, and they all needed to be unique. She uses Shopify for her online store, and uses Shopify’s product discount codes. It would’ve taken forever to manually generate each code, as each one had to be unique and required some settings (one time use). Another requirement is that they had to all be in a CSV file, so it could be printed using the Maestro Label Designer and their “Mail Merge” feature.

Since I found Google Sheet’s scripting tool useful before, I decided it would be a good solution in this case. The first thing to do was find out if Shopify even had a discounts API. Fortunately, because Shopify it a pretty complete platform, they also have an extensive API, including endpoints for Discounts. Next, I needed to connect Google Sheets to Shopify. Google Sheets thought about the need for developers to make API request calls, so they added their UrlFetchApp class.

For making calls to Shopify, I could either authenticate for end users as an app or directly add the API keys. I found it easiest to directly use the API keys instead of going through the full authentication scheme, though if I wanted to make the app publicly available I would have to authenticate differently. It is possible, but somewhat more difficult (External APIs).

Finally, it was a simple matter of generating random text (quick search on StackOverflow to find out how), and sending the requests to Shopify to create the discount code. It was a two part process, the first to create a price rule and the next to create the code itself. I don’t know why the API was designed to require both, since all the data in the price rule is enough to create the code. I’m guessing it was just how it was built internally, with the price rule and discount code being separate.

To actually generate, I ran the script manually through the script editor. Again, if I were building a public app, I could add a whole menu with all the discount code options. The codes all generated one row after the other. From there, it was a simple matter to export as a CSV and importing it into the label designer program.

There are probably many automations you could create using Google Sheets that require repetitive action and data, I hope I can use it more in the future.

Using TamperMonkey to Automate simple tasks

Both as a resource moderator for SpigotMC and working for a business that runs website SEO scans, I’ve found that there are a lot of repetitive tasks. Most of these involve filling out details in a form. For example, about 75% of the reports on resources for Spigot plugins were “invalid”. Either they were making a comment about the plugin itself (intended for the author), or they reported the resource with no proof (i.e “this plugin is stolen”) or source. I would have to write the same stuff each time as a response.

Thankfully, one of the other staff members wrote a script that would create presets as a dropdown list. They did this using a Chrome plugin called Tampermonkey, which allowed you to run JavaScript for certain pages. This lets you add custom HTML to a page, with functionality.

You can also use it if you want things like a better menu system for your favorite websites. You could add a button that switches the background colors (dark/light), or a button to remove all the annoying elements (like a reader mode).

Using Google's spreadsheet scripting tools for automation

I was recently taken on for a startup that specialized in finding SEO related issues in Wordpress blogs, and they had a very interesting setup.

Google Spreadsheets allows for fairly complicated scripting, with a language based around JavaScript. You can create buttons, make HTTP requests, and generate data with it, among other things. The startup I joined used it for tracking client data, as well as a partially automated system for tracking progress and generating an email for the client.

The first step for clients was to have their website scanned manually to give a quote and point out issues. In the client spreadsheet, there was a button to automatically generate a checklist with data for the website to filled in. As you followed the checklist and filled in data from scanning the website, it would automatically generate email text as HTML as well as give quotes and data for a coupon code. At the moment some parts of the process are manual, but there is potential to increase the automation further to automatically send emails, modify users in Mailchimp, and create discount codes as needed.

Resolving Merge Conflicts

When working with other people on a Git repository, you’ll eventually encounter merge conflicts. This happens when two people attempt to make modifications to the repository, causing one to be pushed while the other must either replace the changes made by the first or somehow merged.

The easiest way to avoid merge conflicts is to work on different files from other people, which is usually easy with OOP languages. Sometimes I will forget to push changes though, and if someone else edits that file under the impression it’s the latest version for everyone then when I go to push my version I’ll encounter an error as the person who modified it already pushed their changes.

The easiest way to resolve conflicts is to simply decide if you want your changes to overwite or to keep their changes. Usually this isn’t ideal, since sometimes you want your changes along with theirs. The IntelliJ IDE (and most IDEs) has an option which allows you to select which changes to keep individually in a file, going change by change.

For example, in one function I added an event that gets fired. Someone else in their version of the file added some debug statements. I want both these changes, but because both files are different Git doesn’t know how to add the differences together without duplicate code. IntelliJ allows you to select which sections to add or remove from both files, giving you the result file. If two changes conflict you can simply choose one and ignore the other.

I had to do this recently when I forgot to push my changes from a project I did weeks ago, and it was recently revamped with package names changing and some code optimization done in most of the classes. I had to go through each conflicting file and use IntelliJ’s conflict resolving feature to allow both changes to merge.

Running Scripts inside Java with ECMAScript

Something that’s in demand for every project being developed is configurability. These days, everything needs to be changeable. Languages, layouts, themes, colors, everything. In some projects, though, simply having a config file to load key values from is not enough. Sometimes people want to add simple scripts to their system, usually for automation.

In the project I’m working on now, I supply end users with a tool that allows the program to automatically purchase and sell cryptocurrency on trading platforms. The previous version of this for another client had some customizability, such as modifying the threshold for some numbers, but in large was tied to his desired criteria which was hardcoded.

The project I’m working on, though, I want to be 100% customizable. End users need to be able to specify their own criteria, given variables. The solution I found is to use Java’s script engine.

The ScriptEngine allows you to evaluate a script (in String or file form), running it and returning a value. The most common scripting language for use with ScriptEngine is JavaScript (a modified version of JavaScript to allow Java capabilities). There are an absolute ton of features you can use with it. For example, one cool feature allows you to use a script as implementation for a Java interface.

You can supply functions to the scripting engine, as well as objects. A common use for this would be a configurable filter. Say you had a list of Students, and you wanted to create a custom filter for them. Each student has the following data columns: Age, Name, Enrollment Date, Avg. Score. Given a Java object as a list of Java Student objects, you can pass this into a script definable by the user. It could look like this, where student is a student in the list (in Java, I would pass each student in the list to this script):

student.getAge() > 20 && student.getAverageScore() > 80

That would filter the list to only include students older than 20 and with an average test score over 80.

Using Enums for Configurable Settings in Java

When writing Spigot plugins for clients, it was common for them to request for everything to be configurable. Usually this was just the messages sent to players, in case they wanted to tweak something to be more consistent with other messages or change the language. 

Usually, this led to quite a lot of code being written for every single message sent. It was really annoying. Instead of writing "player.sendMessage("Player " + player.getName() + " not found!");" I had to write "player.sendMessage(config.getString("player-not-found").replace("{player}", player.getName()));. Then, in the config, I would have to write the entry with the default value so it can be read. Sometimes I would get the name of the config entry wrong, leading to ten minutes of figuring out the issue and recompiling/testing. 

Overtime, I wrote better methods of taking config values that would lead to less repetitive code being written. What I finally ended up with, and what I used for more than just messages, was a method that used Java's enums.

An enum class in Java is a set (constant) instances. They were created at compile time, and are accessed with syntax like "MyEnum.ENUM_OBJECT", the naming convention requiring all upper case with underscores. Enums can take constructors, so you can give them constant data as well. For settings, this is usually the default value and sometimes the readable description. An enum declaration looks like this:

MY_ENUM("Default value", "An example of an enum")

They can also have fields for each enum, which are writable. An example of how I use this method for messages would be to generate a file with all the configurable options by iterating through each enum instance and creating a field for each one using the name and default value. When I want to get the message value, I have a function on the enum which reads the value from the config file.

Using a queue system to facilitate requests to a rate limited server

Another of the features I wanted to improve for my Bittrex project was to create a better system for sending requests to the HTTP API servers. Because of their rate limit, I couldn't just send requests for data whenever I needed it. Ideally, I want to get market ticks every minute as that's when they update. There are around 90 Bitcoin markets, though, which would mean 90 requests every minute. This would lead to Bittrex temporarily blocking my requests. 

On top of market tick requests, I also make requests for order data, as well as requests to place orders. If I sent them immediately, I could easily accidentally send five at once and get blocked. Instead, I used a queue with a callback feature.

Callbacks are usually anonymous classes which let you run code at a future data. You run the function, say getOrders(), and supply it with a function that will be run when the request goes through. The HTTP manager class collects requests in a list, and executes them in order with a one second delay, and returning the data or throwing an exception if required.

HTTP API wrappers

Most APIs for online applications (Facebook, Twitter, Shipstation, etc) are in the form of an HTTP API. An HTTP endpoint is an online URL you can make requests to, similarly to how a browser would make requests to a normal website. The HTTP server would take requests with parameters and return relevant data. For example, the project I'm working on uses the crytocurrency trading platform's API. The URL to get the current Bitcoin price in USD can be taken by sending a request to this URL:, The data is in a JSON format, so it can easily be read by programs.

If an HTTP API is commonly used, especially if it requires authentication in some way, it's common for there to exist a wrapper for it in most languages. A wrapper still ends requests, but instead of writing code to send the request you can simply call a function such as "getBitcoinPrice". They normally come complete with error handling, to make things easier. For authentication, they usually automate the process by only requiring you to supply an API key once for it to be used in later requests.

How I Collect Data In my Bittrex Core Library

The largest aspect of my previous Bittrex trading program was how the program collected market data. Market data was collected in "ticks", or "candlestick". Each tick was period of time that it covered, in varying lengths. For example, a tick length might be an hour. The tick for that specific hour in time would contain data like the highest price (during that hour), the lowest price, what it started at at the beginning of that tick and what it ended at. 

The program uses that data to decide when to buy or sell data. It uses it in varying tick lengths, depending on the buying criteria "style". For example, each tick could be a full 24 hour period, and it would check the last five ticks and if it's been gradually climbing from the open of the first tick to the close of the last tick it would buy. A short/quick buy criteria might be measured in one minute ticks, where if the market climbs in price by x amount in 30 minutes it would buy, or sell for the opposite result.

The Bittrex API allows you to ask for the data ticks in whatever length you require (from one minute to one day), but because I'm checking nearly a hundred markets as often as I can (ideally every minute as that's when the ticks update) I'm not able to query the server that many times (because of the rate limits). Instead, I can get the most specific data for the markets, and scale it up to whatever length I want.

Cryptocurrency Trading Library/Platform

Awhile ago, I was hired to create a crytocurrency automatic trader, using Bittrex's API. It was the first time I've heavily used an HTTP API from Java. It mostly works, but unfortunately still has some issues. I've decided to re-write to make it more stable, especially due to the fact that Bittrex has a vague rate limit. I also wanted a better way to calculate things like a market's volatility, using a different method to operate on existing data.

Since I was recreating it from scratch, I also decided to create it in two different layers. This was mainly because I wanted to use the base trading core for both personal and portfolio uses, as well as making it open source. Otherwise, I wouldn't be able to release any of the source (as it's a client project).

The core wouldn't do anything on it's own, except track data. It would provide calculation functions for getting any kind of data you want in any form. The second layer, which would be for the client specifically, would check the data against it's own criteria and make the purchases/sells through the core. 

Attaching Objects to ItemStacks with NBT Data

Sometimes you need to attach data to an ItemStack, in order to keep track of something. A project I'm working on is for an RPG themed server where they want items that act as teleportation scrolls. When interacted with, they open a GUI where players can teleport to pre-defined areas. Each individual item had a limited amount of uses, as well as a cooldown. 

Using NBT data, you can attach whatever kind of data you want to items. NBT acts very similar to JSON (in fact, the string representation could almost be parsed that way), so you could add strings, integers, lists, whatever. In this case, I had an object called ScrollData with three fields - the Scroll it belonged to (the Scroll object kept track of things like how many uses a scroll starts with and where it can teleport to), the remaining uses, and the next time the scroll can be used as a millisecond timestamp (cooldown).

One way to store data would be to create NBT tags for all three fields. Instead, though, I generated a random ID for the item and tied an object to it. Then I stored the item in a YAML data file. On startup, I load all of the data up. The only NBT data that needs to be stored is the ScrollData UUID I generated for the item.

Using Flexboxes in CSS

A joke among CSS/HTML designers is how difficult it is to simply center an item either vertically or horizontally. As simple as this sounds, there isn't a simple "position: center" type field in CSS. 

In simple cases you could simply add a 25% margin to the left side of the object, which will take 25% of the width of the outer element (or the page if none) and push the element that much from the left. Of course, that only works in simple cases. If you wanted to use a different positioning method or wanted to have another element on the left or right of the centered element it would break.

There are many different ways to center an element, each with their own pros and cons. There are so many, in fact, that someone even created a website to help you find the best method for your use case:

I recently learned about a new multi-purpose display trick in CSS for positioning and sizing elements, called flexboxes. To use them, you would declare an outer div element as a flexbox (display: flex), making all of it's children follow the flex settings. One of my favorite features of flexboxes is the fact that you can apply all of these settings in the wrapping element instead of doing it on each individual element. 

For a use case, we'll assume a navigation bar. FIrst, let's say the outer wrapper's width goes across the entire page, and the height is relative to the elements inside. Each inside element is a simple box with padding, text, and a background. You want them to display as elements side by side, in the center, with space between each one. All of those effects can be applied in the outer flexbox, without having to touch the buttons. 

All you have to do to center the buttons is apply the justify-content: center field. If you wanted one button to be larger than the rest, but only by a percentage of the rest, you can use flex-grow on the specific element. By default, it's "1". This basically tells the element to take up as much space as one element in that flexbox. If you set it to 2, it will be twice as large as the other elements. This is extremely helpful when you have to deal with a changing navigation bar, as you don't have to worry about things breaking when buttons are added or removed.

Using SVG Graphics

Rollup logo drawn as an SVG. Source:

Rollup logo drawn as an SVG. Source:

I went to a local meetup last night, with presentations on different topics around web development. One of them was on SVG graphics, and how to manipulate them with CSS.

SVG graphics aren't images, but HTML in an <svg> tag. It's supported by pretty much every browser. In the HTML that makes up the graphic, there are elements which give drawing data. For example, a rectangle that starts at x/y and goes to x/y. The primary benefit is that SVGs can scale perfectly, unlike images which can waste bandwidth if scaled down or lose quality if scaled up. For logos or anything that isn't a photo with a lot of color, SVGs are also a lot smaller in size. 

Another cool feature with SVGs is that each of the elements in the SVG can be targeted with CSS (like any other element). You can change the rotation of an element, a color, or the size of a rectangle. You can also combine it with CSS animations to animate your SVG. This makes it preferable over gifs, especially when you factor in file size and loading time. 

To design SVGs, you can use Adobe Illustrator. Otherwise, there are free online tools, like SVG-Edit.

Using Minecraft Maps as GUIs

While browsing the SpigotMC forums, I found a forum post in which the author used a combination of the input packet and custom Minecraft map rendering to create a nice looking GUI. 



To use these GUIs, you hold the map in your main hand. It mounts you onto a fake entity, so the client will send movement input packets. To navigate the UI, you use the WASD keys, as well as space/shift. 



He also uses it to connect placed maps in the world together to seamlessly create displays, even interactable displays. 

All of the tools for this are available in his commons library, which I plan to use myself at some point for personal projects.

42, a programming and entrepreneurial bootcamp


There's a programming bootcamp in Silicon Valley called 42. It's a 3-5 year program, during which you learn different programming languages and get work experience by going through internships with their partners.

To begin, you send in an application, answering basic questions. If accepted, you would then go to the next step, the piscine. You would go to the 42 campus sight, and for four weeks nearly 24 hours a day learning how to program with different projects. It's the most intensive part of the entire program. Only a fraction of the people who go through it will be accepted past that for the full course.

After a year, you're required to take an internship with one of their partners, usually lasting for a year. During the internship you're still a part of the program. Near the end, you would take either a full time job or internship. In between, you can take yet another internship, optionally. You can quit at any time, there isn't a specific program end date.

My Upcoming Bukkit Structure API

I've always felt that structures have never really been taken full advantage of, especially as the format and official support makes it a better alternative to WorldEdit/MCEdit schematics. It isn't in the Bukkit API yet, either, despite the fact that it would make perfect sense to access from a Spigot plugin. 

At the moment, you can use structures using DefinedStructure/DefinedStructureManager available in the Minecraft NMS source. Obviously this isn't ideal, especially as it's constantly changing. The fact that the implementation is there, though, makes it an easy candidate for adding to Spigot. 

It's easy to just add a wrapper to DefinedStructure and to replace the DefinedStructureManager with a Bukkit version, but I wanted to give more control than that. Mainly, I wanted the ability to manipulate structures while they're still in memory. For example, filling chests in a structure with loot before being pasted into the world. 

At the moment, the syntax looks like this:


That example loads a structure from file called house.structure in the plugin folder. The Structure object holds all the blocks and other info in the structure. The next line gets a Structure.Block instance, which gets a block at a location in the structure. This block is at coordinates 0, 0, 0. Notice I also check if the block is null - this is because it's possible for a block at a location to be empty. That's not to be confused with Minecraft's air. The difference is that when the structure is placed in the world, air blocks will replace whatever is in the way, where empty blocks will not modify the world at that block position. In vanilla, there's a block called structure void which can be placed around to mark areas that should not be saved to file (empty blocks). They aren't saved in NBT anywhere.

After making sure the block isn't empty, I check if the block type is a chest. If it is, it's safe to assume that the BlockState is a Chest as well. In Bukkit, BlockStates are not directly tied to the World, though they do have a location. That means all data in the BlockState does not update with the world, nor do modifications made to the BlockState change the block until you call the update function. It makes the perfect class to use for structures as well. Additionally, all tile entities (blocks that hold data, like chests) extend that class.

Next I declare a variable with the Chest BlockState, casting from Structure.Block#getState(). I clear the inventory, and set it to a double chest expanding left. Just like BlockStates in a Bukkit world, it needs to be updated to take effect in the structure.

Finally, I call the paste function, giving the spawn location of "world" as the paste location. I'll add more arguments later, like rotating it or whether or not to include entities.

X-Bows Keyboard

Back in October, I helped fund the Kickstarter for the X-bows keyboard. The main feature of the keyboard is that it's ergonomic. The keyboard is shaped in a way that forces you to rotate your hands, instead of positioning your hands straight from your wrists.


The project was funded both on Kickstarter and then later on IndieGoGo, both times surpassing their goal by about eight times. Unfortunately, due to delays with their manufacturer in China, they weren't able to ship until seven months after the expected date. 

The keys are all mechanical, and you could choose between different key types to get the right feeling of clickiness and sound. Personally I went for a more clicky sound and as much of a mechanical press effect as I could get. It feels amazing, definitely the best feeling keys I've ever typed with.

 The layout itself is difficult to work with at first, though I'm slowly getting used to it. One of the best design features was putting the shift, enter, and backspace keys in the center of the keyboard (as well as in their normal positions) so they can be used with either the index finger or thumb. It also takes advantage of the spacebar being too long in normal keyboards to split it to be used by either thumb and replaced the center with Ctrl and Shift buttons. 

The keys are all also removeable, and because they use cherry switches you can replace many of the keys with keycaps that support the switches.

I'm also taking advantage of learning a new keyboard by also trying to learn the Dvorak layout. It's pretty old, invented in 1936 after keyboards and touch typing became common practice. It places the most commonly typed keys close together, so your fingers don't have to move as much allowing for faster typing. The reason the QWERTY keyboard didn't use a similar idea was because typewriters used to jam if two keys were pressed together at the same time, which happened a lot if two keys were pressed close together at nearly the same time, The solution was to just move the common keys as far away as possible from each other. Of course, with modern keyboards this wasn't an issue.