Writing Google App Script

selvam's picture
Submitted by selvam on Sun, 12/18/2011 - 21:29

Google App script lets us add interesting features that can prove productive. I had a small work for a friend which involved generating custom graphical representation of the Income / Expense maintained with Google Spreadsheet. Though I could have used some implicit ways to achieve the same, but for the sake of extensibility, I decided to write an app script for the graph generation.

The spreadsheet columns look like this,
 
Date,Particulars,From/To,Deposits,Payments,Balance
 
Here are the steps to generate an Area graph,
 
1) Go to your Google spreadsheet, Tools > Script Editor.
 
2) Place this following code,
  1. function buildFromSpreadsheet(range){
  2.   var dataTable = Charts.newDataTable();
  3.   dataTable.addColumn(Charts.ColumnType.STRING, 'Date');
  4.   dataTable.addColumn(Charts.ColumnType.NUMBER, 'Payments');
  5.   dataTable.addColumn(Charts.ColumnType.NUMBER, 'Income');
  6.   var income = new Array();
  7.   var expense= new Array();
  8.   var x='';
  9.   var tmp ='';
  10.   for (var i=3; i < range.length; i++)
  11.   {        
  12.     if(range[i][0].toString()!=''){
  13.       tmp = Utilities.formatDate(range[i][0],"GMT + 5 : 30", "MMM-yyyy");
  14.       if(tmp in income){    
  15.         if(range[i][4].toString()!='')
  16.           income[tmp] = income[tmp] + range[i][4];
  17.       }
  18.       else{
  19.         if(range[i][4].toString()!='')
  20.           income[tmp]  = range[i][4];
  21.       }            
  22.      if(tmp in expense){    
  23.         if(range[i][5].toString()!='')
  24.           expense[tmp] = expense[tmp] + range[i][5];
  25.       }
  26.       else{
  27.         if(range[i][5].toString()!='')
  28.           expense[tmp]  = range[i][5];
  29.       }      
  30.     }
  31.   }
  32.   for(var index in income) {
  33.     if(index in expense)
  34.       dataTable.addRow([index,expense[index],income[index]]);
  35.     else
  36.       dataTable.addRow([index,0,income[index]]);
  37.   }
  38.   dataTable.build();
  39.   return dataTable;
  40. }
  41.  
  42.  
  43. function doGet() {
  44.   var range = SpreadsheetApp.openById('SPREADSHEET_KEY')
  45.                 .getSheetByName('SHEET_NAME').getDataRange().getValues()
  46.   var data = buildFromSpreadsheet(range);
  47.   var chart = Charts.newAreaChart()
  48.       .setDataTable(data)      
  49.       .setTitle("Income/Expense")
  50.       .build();
  51.   var uiApp = UiApp.createApplication().setTitle("My Chart");
  52.   uiApp.add(chart);
  53.   return uiApp;
  54. }
doGet() is the entry point which reads the spreadsheet data and sends it to buildFromSpreadsheet. buildFromSpreadsheet generates data suitable for area graph and returns back.The doGet then creates a new area chart and returns the UI application.
 
3) In the script window, select Share > Publish as Service. You can select suitable options and you will receive a URL which you can share with other people.
 
Tag: 
Google
App
script
Graph

Related blogs

Tags