Scripting for Fun and Passion

Category Archives: Web Development

Cross-Workbook Reference Functions in Google Docs

I have been using Google Docs for quite some time now and really love the ease with which it you can do simple tasks like putting a contact form on your website in no time. I personally use Google forms quite a lot not only for my personal purpose but also for all AIESEC(not for profit organizations) purposes. I also suggest many others to use it because it is something which is really user friendly. The best feature is the embedding a form in your website. No problem of it clashing with your website theme. All you need is the plain theme of Google docs and paste the embed code in the web page source.

Well that said and done Google docs is indeed a very simple application made for basic usage. You can do that many automation tasks in Google docs. Also one problem which I find with Google forms is that for every for you need to make a new spreadsheet. This is something I don’t like that much. Let take my case only. I use Google Forms to make application forms for membership and internship for AIESEC. However I want all the details related to all incoming applications in one spreadsheet only. This is something which is not allowed by default in Google. You need to use some function calls present in Google Sheets to make this work.

Fortunately, this feature is easily implementable if you have an idea about using the function called


This is a really neat function which allows you to set up an import of Live data from any other Workbook. Its really simple to use. I will explain it with a simple example of a simple email subscription form. The form takes an Email Id as input and stores in a spreadsheet.


This Workbook has a sheet names Sheet1 and The Columns which will contain data will be A and B. So lets say we need to import the data of this form into another Workbook which has emails from different Websites in a separate Sheets. Now we go to our new Workbook and make a new sheet for our Email Id on from this form. Once the sheet is made we need to ensure that it keeps its data in sync with this form. Now we need to make an assumption here about the maximum number of entries we expect in this form. I assume 200 and for that I add 200 more row in the original form Spreadsheet. Now we go back to our Aggregator sheet (The one where we collect all email ids from different websites) and in the new Sheet for this form select the A1 cell (the selection of cell is personal preference. For forms it makes more sense because I want the exact copy of the spreadsheet as I have in the form).

Now we need to enter the formula in Cell A1. But before applying this formula we need to collect a few values. There are basically 3 Parameters (although it looks 2 only but the 2nd parameter itself contains 2 parameters)

  • SpreadSheetKey: This is the unique identifier of the spreadsheet you want to import from. In this case it is tdtdimATHn-WvPZ4aWGsMMQ
  • SheetName: This is the name of the sheet from which you want to import. This case its Sheet1
  • CellRange: This is the range of cells whose data you want to sync with the new sheet. I am selecting the data for first 200 entries. So it makes CellRange as A1:B201 (Note: I am going one extra with 201 because of the heading row as well.)

Now once you have collected these values just put them in the proper format for the ImportRange Function call. The final function call becomes


PS: Do not forget to put the exclamation mark ( ! )after the Sheetname as it is very important to refer to the cells of the right sheet.

Now once you have made this above statement. Enter it into the first cell of your new Sheet in the Aggregator Workbook. And then press enter. It will take a few seconds (depending on the data you have in the form spreadsheet) and then it will show you your new sheet filled with the data from the form Sheet.

image Also this data import is synced meaning that whenever you enter a new value in the form spreadsheet it will automatically be imported in this aggregator spreadsheet. So now no more hassles of searching multiple spreadsheets from Google docs. Sync it once and you have all your data in once place.

  • Note1: It will only import the date for the cell range specified in the import command. If at any point of time you want to increase the cell range go to the aggregator sheet and click on the first cell and you will see your formula again. Now make the necessary changes to your formula and you are done. It will re-sync the sheet with new data.
  • Note2: At times you might get #REF error in your spreadsheet. This happens if you are trying to import data from the sheet you do not have access to. You need to have atleasr view rights to import data from a workbook.
  • Note3: I have deleted the sheet which I made for the example of this post so this import function will not work with the SpreadSheet key given here. Please use the key of the Sheet you want to import from.

So next time if you think of making a new form and are concerned about the data aggregation try this method and make your life easier.

Please feel free to ask any queries and give any suggestions to improve the process  in the comments section given below.


Facebook revamps Messages: Its all about Conversation Now

Email, something which is so much in use these days that its almost impossible for anyone to live without an email and have a substantial presence in this world. Ever single small thing from registering for the cookie competition to applying for a mega lottery. Sending Job Applications to Registering on a Matrimonial website requires an email address.

Personally for me I have 3 major Email Accounts and I operate all three of them pretty regularly. One good solution I found was of forwarding all my mails to one account and then having the whole conversation there. Top that up with the great threading feature that Gmail has so tracking conversations is easier. But then a few days back I saw this cool app on the Android phone of my friend which organized your SMSs also in threads. This is something anyone is going to love. Having the whole conversation with a person at one place.

And I think Facebook just nailed that. Today Facebook announced its latest Messaging Service on its Official Blog which is going to take Facebook Messages to an all new level. The fact which sets this service apart from anything else is that it allows you to have all your conversations at one single place weather they are via Mail, SMS or by Chat. This is something which any one who loves getting connected with his/her friends through all sorts of communication channels, would love to have. Also the feature that conversation with one person are going to be listed under one thread so its going to be a really pleasant experience for the user of this service as is very beautifully said in the blog post itself

They will have the conversational history with the people in their lives all the way back to the beginning: From “hey nice to meet you” to “do you want to get coffee sometime” to “our kids have soccer practice at 6 pm tonight.” That’s a really cool idea.

Not only this. The message service will only allow your friends to send you messages and everything else will go in an Others folder . Also you can select who you want to receive emails from. Its something I believe very good for personal messaging. I personally would prefer to have a personal space in my mail box where I can only receive emails from my close friends and family members. I believe Facebook is going to give me just that solution.

All your messages together

All your messages together

Full conversation history

Full conversation history

The messages you want

The messages you want

Now top that up with something which any Facebook user would love to have. A Email Id. This is something I personally and very excited about it as being a Facebook use it will be great to show off your email id and feel proud about it =).

Over all I am loving the service as is said in its announcement and am very eager to use it at the earliest. If you are also interested you can ask for an Invite at

Lets see what they make out of it. Personally for me it will be a great service because I am usually present on facebook 24*7 and its a second home to me. Just waiting for the release and getting my id.

Also check the screenshots of the new Message Service available on Mashable at

Drush for Drupal: Command Line for Drupal

I am not new to Content Management Systems and have been developing websites for quite some long time now. I have used CMSs like Joomla, Drupal and WordPress (yeah its not just for blogging). Add to that a few others like Silverstripe etc. Well I enjoy developing websites using CMS as it eases a lot of your work. No more trouble about entering data directly into database or writing HTML code. Easy to install plug-ins, themes and what not. But this case which I am going to mention is really interesting and fascinating. So please read on =)

I tried using Drupal two years back when I was just looking at the various CMSs available. I like the flexibility it had over any other CMS but then the interface was really difficult to navigate (or so my lazy mind felt).  There were not many good free themes  available and also the modules were also less as compared to Joomla and WordPress. Since then I have been building websites majorly on Joomla with WordPress as well for small or quick projects. But then I kept reading Drupal is really cool and you can add a whole new level of functionally to it once you get a good hand at it. Those comments bothered me. At times I wanted to do some stuff in Joomla and then realized that its just too inefficient or rather lengthy process to do those.

A few weeks back I started on a new website. I thought of giving it a shot in Drupal this time. So installed it and started working. One feature which didn’t work for me was the Plugin Manager. Its actually a pretty good plugin which gives you a great control over installing, updating both modules and themes for Drupal. So I was looking for a really rapid development using this. But alas no computer is perfect and no hosting is perfect as well. I am using Godaddy Grid Hosting and after a lot of search I found out that they do not support ftp_connect() php function which spoiled the party for me. (Please if anyone knows how to run Plugin Manager on Godaddy Grid hosting please share it with me). So I was back to looking for something which will help me with rapid Drupal Website Development. After a few searches I stumbled across this Drupal project called Drush. Drush basically stands for “Drupal Shell” and its a command line utility to install modules, themes, updating and do much more. All in all its gives you the control of your drupal installation  through a command line interface. (Which developer won’t love it =D). I downloaded it but really had a hard time trying to figure out how to make it work.

Well as they say “Google when in trouble” so I did just that. I was getting the error

Content-type: text/html

drush.php is designed to run via the command line.

They had a few tutorials on fixing it. The best amongst them which worked 90 percent for me was this one by Joshua Riddle again as I figured out it was basically a Godaddy problem which was not allowing Drush to run properly. Basically Drush requires itself to be run by php-cli which by default is not accessible in command line by the hosting. So in order to make it work I was required to add an alias to the drush command I intended to use with the exact path of the php executable. The line which was required was:

alias  drush=’/usr/local/php5/bin/php ~/html/drush/drush.php’

So this used to solve the problem whenever I entered the above in the shell. But there is a need for automation so that things get taken care of for always. So I added this line to .bashrc file of my directory. But it didn’t work out somehow (Again it would be great if someone can explain that as well). So I “Googled” and lo I got the answer again. The solution was presented on this Wiki How article “Install Drupal with Drush 2”. The point which solved the matter for me was

echo “alias drush=’/usr/local/php5/bin/php ~/drush/drush.php'” >> ~/.bash_profile
source .bash_profile

This done now I can log into my hosting and control installing any module, theme directly from the command line. And first of all its fast and secondly its really easy. Say for example you want to install the Dynamic Display Block Module module in your drupal site so all you are required to do is change your current working directory to your drupal install folder. After that fire this simple command in your command line

drush dl ddblock
drush en ddblock

Done and your module is downloaded and enabled. As simple as that. Not only this you can download and install modules in bulk all you need to write a command similar to this:

drush dl cck zen es

Which will Download latest versions of CCK, Zen and Spanish translations for your Drupal version. For a detailed lists of commands you can always type

drush help or simply drush

Details about the Drush module can be found on Overall its a must use for anyone who wants to develop Drupal websites very quickly. Its insanely powerful and increases your working pace 10 folds.

There are some small quirks related to installing it but once done its like a cake walk for any Drupal Developer. I am new to it but have already started to see the positive implications of this project. Thanks to the Drush team for giving us such a great tool.

Feel free to post your issues related to the module or anything related to the hosting problems. Also if anyone can figure out how the .bashrc and the Plugin Manager issue can be solved I would be very thankful.

Shifting to Microsoft Live Writer for Blogging

Windows Live Editor

Windows Live Editor

Hello folks , its nice to talk about our biggest passion – Blogging , and I really love the way technologies have come up to facilitate a bloggers life. Things like RSS and Atom Feeds are a really cool way of keeping people updated about your latest blog happenings. Also things like Mashups and Twitter add spice to you daily blogging life. Because now you can not only blog but you can also deliver your content to your public through several interfaces . It is this evolution that has led to the growing blogging community .

Now a new advancement in this field has been the availability of desktop blogging solutions. And the software that is stealing the show in this race is Microsoft’s Live Writer. Although this software came in the market quite a long time ago but it has really become popular nowadays.Especially because people want most of their web applications out of the browser which actually saves you the trouble of loading of a web page and those age old browser hiccups. Read more of this post

Technex ’06 , An Official Report

The news item from Hindustan Times dated Feb. 17 states about the prizes won by BHS High School, Allahabad, at Technex, the annual technical festival of IT-BHU.

(There was another story about MNNIT, Allahabad students winning ‘Best team Prize’ at Technex in Hindustan Times, but the link is lost.)

News Article
BHS students excel at BHU’s ‘Technex’
HT Live CorrespondentAllahabad,
February 17 Read more of this post

%d bloggers like this: