Welcome to my blog!
This blog will be primarily aimed at innovative educational techniques but will however have the occasional post about education in general.

Sunday, April 13, 2014

Adding a Trendline to a Google Sheets Chart

(Note: if you want to skip my story and get straight to the Add On I wrote here is a link Trendline Add On Make a copy and run "on install")

As a science teacher I have always had my students graph data from a lab activity or experiment.  As many of you know, by graphing you can create (or determine) the mathematical model that describes the data.  This is a crucial skill for ALL students to learn!  It is also one way that we science teachers can show the students how what they are doing in math class works in their science class!

Before Google existed (yes I am old....), I would have students use Microsoft Excel to enter their data and create an xy-scatter graph.  They could then "right click" on a data point and add a "trendline" and even have the equation displayed.  This was wonderful!!!

Flash forward to today, using Google Spreadsheets I can have the student enter their data, create an xy-scatter graph BUT they cannot "right click" and add a trendline!!!  So frustrating and I have heard many science teachers NOT use Google Spreadsheets because of this one little (read HUGE) shortcoming of Sheets.....

So, I would go through a quick lesson on how to do a linear regression in Google Sheets (Here is a GDoc that I created for this - Using Google Sheets - Linear Regression)  I would then show them how to create another column of data using the generated linear equation and how to plot it on a graph.... but I am sure some of you have stopped reading by now so you could only imagine what some of my Physical Science students felt about this.....

I knew I needed something better.  There had to be a better way....  As I was looking at some Google Apps developer stuff, I happened across this page: Google Charts - Trendlines.  WHAT?!?!?  You can put a trendline on a graph on a web page!?!?  Why can't this be done in Sheets?!?!  All the pieces are there: the equations, the data, Google apps script.... What?!?! Google apps script??!  How could I use it........

So I am happy to announce the creation of Trendline Add On Version 0.01!!!  Click the link, save a copy, go to "Tools - Script Editor" and run either "On install" or "On open".  This will add a menu command on your "Add ons" menu.  Click "Create Graph" and follow the instruction in the sidebar!  The basic steps are to select your data, make sure it has labels in the first row (these are used for the axes titles), pick your colors, give it a title and click create!

Once the graph is created you have complete editing capabilities, you can even put the graph on it's on tab.

It is EXTREMELY rudimentary BUT it does make generating trendlines EASY.

I tried to comment the code as best as possible so it should be very easy to follow and there is a "Readme.gs" file with links to where I found other information.

Have fun with it!  Use it! Improve it! Let me know what you think!!