Skip to content

My first JavaScript app: building a newsletter with Google Apps

2014 January 14

google app scripts

Well over a year ago, I made a promise to start committing more time to learn how to code. I jumped into Codecademy for a number of months, primarily January to March of 2013 (putting in about 1 hr/day for 5 days/week). I focused on JavaScript at first, but then started to poke around with a little Ruby and Python, as my friend Andrew and I developed a mockup for a Facebook contest (something else I need to write a long-overdue blog post about).  Then, I was the co-organizer of a hackathon in April, which went really well. But, I wasn’t using the skills I had learned in Codecademy that much. It’s hard to make the jump from Codecademy into building a whole website or new project. You need a sort of pet project to help you get comfortable, before you take a bigger leap. Codecademy teaches you a lot of useful things about basic programming concepts and basic syntax (grammar for computers) for a number of languages. But I wasn’t applying the skills to a concrete project. By the time fall (quickly) came around, school had started and I had little free time. Not only had I not applied the skills I had learned in Codecademy, but now I was forgetting the syntax I had learned.

Over the winter break though, I discovered Google Apps Scripts, a JavaScript group of “libraries” (lists of useful functions and methods)  that lets different Google Docs talk to each other. I’ll be honest, I kind of hate working in Google Docs. They’re often very slow, and the formatting tools are hardly a fraction of what’s offered in the full Word/Excel/PPT programs. But they are useful for certain things. In my new position as VP of Membership for the Berkeley Energy and Resources Collaborative, I have to format and send out a weekly newsletter about energy events on campus (there’s a lot!). People send me things they want to include in the newsletter. The formatting takes a long time. Cutting, pasting, organizing, writing blurbs, summarizing. Ain’t nobody got time for that!

Well, now everyone can take a couple minutes to fill out a simple Google form, if they want to include something in the newsletter: Name of event, date, time, place, description, and a link. Simple. This goes into a spreadsheet automatically. Using Google Apps Scripts, I can write my own JavaScript program to automatically format those spreadsheets entries into a Google Doc that looks like a newsletter, all with the click of a button. I can look it over and make minor edits, then send it on its way. This will likely save me 1-2 hours per week, which is valuable time in grad school. Now I’ll describe some of the basics of how this program works.

First, I need to gather all the data from the spreadsheet and store it in a Javascript variable so I can access it and manipulate it.

var sheet = SpreadsheetApp.getActiveSheet();
sheet.sort(3);
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows – 1; i++) {

var row = values[i];
Logger.log(row);

}

I got most of this code, just sniffing around some of the examples on Google’s site. That’s one great thing about coding. Someone has likely already tackled the problem you are trying to address. So just have a look around the interwebz for some guidance when you’re stuck. The code above simply uses some existing JavaScript methods like getValues and getNumRows (see here for a huge list of methods). Oh and also, a sorting function to put all of the event submissions in order. Then, there is a “for loop” to go through all the rows in my spreadsheet, and stuff the data into one variable called ‘values’. What’s amazing about Javascript is that you can stuff different types of words and numbers into one variable (not possible in other types of languages which only like one data type in variable/array), and it will just store them in an arrayed structure. Since, I have six columns (event name, date, time, etc.) of information, it will be useful for me to call just a specific column of info when I need it.

Now I can make the newsletter.  When I run the script, it uses a method called DocumentApp.create to immediately create the new Google doc newsletter for the week. I poked around the other library of functions available for Documents. I basically just need to create a heading for the newsletter, make a list of events at the top (like a table of contents), and then run through the details of each event. The functions ‘setHeading’ and ‘appendParagraph’ seem to do the trick. Now I just need to call the right data from the ‘values’ array, with some help from a ‘for loop’ to help me run through all the individual events (in rows). Ok, I need to list the event time which is in column 2…that’s easy, it’s just ‘values[j][2]’ where j is my dummy variable for the rows/events I’m running through in the for loop and 2 is the column I want. As you can see in the top picture, most of my code is calling the values array in some form or another. There are some other tricks in there, but it’s not too difficult. Though I will admit, I spent the better part of today, writing only 90 lines of code or so. There were certainly moments of frustration, but for now, it works and that’s what’s important. Next Monday, I’ll make the first newsletter of the semester with the click of a button. I hope it doesn’t blow up!

  • Facebook