As front-end developers, we’ll most likely come across a time when we need to format data that has been given to us by a client or that has been exported from another site, that isn’t quite formatted the way we need it.

Since I am not a software engineer, I rely on plugins to solve a lot of my server side problems. Plugins will make things a lot easier for you but they aren’t always as robust as we’d like them to be. Sometimes getting your data the way you need it can present a challenge as well. So how does a front-end developer fix this problem?

Let’s say your client wants to export some pricing data from one site and import into another, and their old site formats data in separate cells:

Example:

Small Qty.  |  Small Price  |  Med Qty.  |  Med Price |  Large Qty.  | Large Price.

1-11            |  $3.00            | 12-20         |  $2.65          |  21+              | $1.34

But you need your data formatted differently for this new site you’re moving to. They require you to format your data in 1 cell.

Example:

(qty + ‘:’  + price + ‘|’ + qty + ‘:’  + price + ‘|’ + qty + ‘:’  + price + ‘| )

1:3.00|12:2.65|21:1.34

Google Spreadsheets makes it really easy to format your data however you’d like by using custom JavaScript functions.

Step 1: create a spreadsheet / or upload existing

As you can see from the image above, I have my cells formatted from the previous site with what I need my final outcome to be. I repeat the same data a few rows down with an empty Final Outcome field because that’s where we’ll be putting our custom JavaScript function.

Step 2: Create new script file

By selecting Tools > Script Manager in the menu bar at the top you’ll bring up a screen that looks like this. Select New to start a new file.

Step 3: Writing your JavaScript

Now that you have created this new script file, Google starts you off with a dummy function to show you where to put your code.  Here is where we’ll write whatever JavaScript we need to format our data the way we need to. We specify in our function how many different arguments we’ll be taking in and return our final value once formatted. Once you’ve written your JavaScript, remember to save it. Google will automatically use the scripts you save in your open spreadsheet so there is no need to import anywhere.

Step 4: Using the code you’ve written

Now that we have our custom JavaScript function saved we can use it on the data fields we have by using the function the way we would with any of the built-in functions Google has to offer. Once you’re finished, tab out of your current tab to submit the function and you’re cells will be formatted the way you need it to be.

Formatting data this way can save a lot of time for developers or even non-developers who know a bit of JavaScript. Google makes its script editor very easy to use and because it’s JavaScript there is no learning curve for us front-end developers that already know it. And, since all of the tools used are hosted by Google, there is no need to purchase any extra software or plugins to format your data for you.