google apps scripts spreadsheets

All posts tagged google apps scripts spreadsheets

Screenshot from 2014-03-27 14:23:55

Recently we started dumping some data from the database into a shared spreadsheet on Google Docs.  With all of this data it would be helpful to visualize it in some way.  After searching the net it seems there isn’t really a good way to create a chart that is dynamic with what data is in the spreadsheet.  What I want is to have my graph update every time a new row is added to the sheet without having to edit the graph directly.  The solution was to write a custom Google Apps Script to do just that.

To add a Google Apps Script to a document open the document and click Tools -> Script editor…  This opens up a page for editing scripts.  If a prompt appears for what type of script you want to do just select Blank.  Now you just need to paste in this code below.

To make this work for you just change the tabName variable to whatever your tab is that you want to use the graph in, change the start row and start column of where your data begins and what column to end reading.  You don’t need to specify an endRow because we will just be reading all of the data until there is no more.  After that you can customize where the graph will be located, how wide it is, and the titles of the axis.

You’ll see that this method will get called anytime the document is edited or opened by someone who can edit.  Viewers who cannot edit will not trigger the event even on open.  That’s all there is to it.  dynamic Google spreadsheet graphs are now just a click away and once you have it set up you’ll never have to touch it again.

If you have any other Google Apps Scripts tips leave them below in the comments.