Google Spreadsheet Graph with Dynamic Data

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.

//Runs on document open
function onOpen() {
  createGraph();
};

//Runs on any edit
function onEdit() {
  createGraph();
}

/*
  This function reads the data in the Totals columns
 */
function createGraph() {
  var tabName = 'My Sheet';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabName); // This grabs the tab with the name stored in tabName.
    // Read variables
    var startRow = 2;  // What row to start reading at
    var startColumn = 1;  // What column to start reading
    var numOfColumns = 3;  // Number of columns to read

    // Graph variables
    var posX = 5;  // Column to anchor graph to
    var posY = 2;  // Row to anchor graph to
    var width = 600; // Graph Width
    var xTitle = 'Week'; // XAxis Title
    var yTitle = 'Quantity'; // YAxis Title
    var chartType = Charts.ChartType.COLUMN; // Chart Type

    var range = sheet.getRange(startRow, startColumn, sheet.getLastRow(), numOfColumns);

    //If charts already exist, update them
    if (sheet.getCharts().length > 0){
      var chart = sheet.getCharts()[0];
      chart = chart.modify()
      .removeRange(chart.getRanges()[0])
      .addRange(range)
      .setPosition(posY, posX, 0, 0)
      .setOption('width', width)
      .setOption('vAxis.title', yTitle)
      .setOption('hAxis.title', xTitle)
      .setChartType(chartType)
      .build();
      sheet.updateChart(chart);
    }
    //If not, create them
    else {
      var chartBuilder = sheet.newChart();
      chartBuilder.addRange(range)
      .setPosition(posY, posX, 0, 0)
      .setOption('title', 'Total Report Graph')
      .setOption('width', width)
      .setOption('vAxis.title', yTitle)
      .setOption('hAxis.title', xTitle)
      .setChartType(chartType);
      sheet.insertChart(chartBuilder.build());
    }
  }
  return true;
}

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.