PNR_GoogleAppScriptIndianRail

Few days back I have booked railway ticket from IRCTC. Unfortunately the ticket status was Waiting List 81. After that daily I used to check the PNR status, number of times if the waiting list number is decreased. It becomes my daily activity to check the status. After some day an idea comes to mind. Why can’t we automate this daily activity?

The idea was, We have to write a program which will check the PNR status in every 2 hrs. If the PNR status is changed, it will send a mail with related information. Cool yah!!!

For that what We need?

  1. A mechanism to check PNR status.
  2. Store the current status somewhere to check with previous status.
  3. Mailing functionality. If the status is changed, sent mail to recipients.

Lets check how to achieve the points.

Check PNR Status

It need a service/API where user will pass the PNR number and it will return the railway ticket status with related information. There are several website which provides such service. My favourite is railwayapi.com (the constraint is user can request 100 request per day, that is enough for my purpose).
First we need to register in the website. After registration they will provide API key (which is used to track the requested user). Now you have API key and PNR no of the booked ticket. Lets create the request URL. The URL should be like below.

“http://api.railwayapi.com/pnr_status/pnr/<your pnr no>/apikey/<provided api>/”
e.g. http://api.railwayapi.com/pnr_status/pnr/2219233477/apikey/gqeyd6381/

If you open the generated URL in any web browser, it will give you JSON response with current ticket status and several other information.

Store the current status to check with previous status.

So we got the response. Till now the request/response was manual. We were opening the URL manually in the web browser. To automate the process, we will use Google App Script. Its a JavaScript type language which is very easy and can be easily integrated with other Google Products like Google Sheet, GMail etc.

Now click on below link to copy my sample Google Sheet https://docs.google.com/spreadsheets/d/18N5JVM4sAr5rpfToSOiJiqx67KjELnc0YzOTqziZCdY/copy

You can write Google Script inside Google Sheet. Its similar like Microsoft excel with VBA Macro. In Google Sheet Click on Tools -> Script Editor. A Google Script window will open. Default filename is Code.gs with myFunction() default function. Lets write some code inside it.

PNR_ScriptEditor

Create the URL as required to call the API. Request the URL and store the JSON response in a responseString. Parse the JSON data.

var sheet = SpreadsheetApp.getActiveSheet();
var pnr = "2219233454"; //PNR Number
var apikey = "gqeyd6271"; //Your API Key
var url = "http://api.railwayapi.com/pnr_status/pnr/" + pnr + "/apikey/" + apikey + "/";
var response = UrlFetchApp.fetch(url);
var responseString = response.getContentText();
var data = JSON.parse(responseString);

There are lots of information available in JSON response. Only save some required information in Google Sheet. We have chosen some fixed location in the sheet where the data will be stored.

if (data.response_code == "200") {
 //PNR no
 sheet.getRange("A1").setValue("PNR"); sheet.getRange("B1").setValue(data.pnr);
 //From
 sheet.getRange("A2").setValue("From"); sheet.getRange("B2").setValue(data.from_station.name);
 //To
 sheet.getRange("A3").setValue("To"); sheet.getRange("B3").setValue(data.to_station.name);
 //Train Name
 sheet.getRange("A4").setValue("Train Name"); sheet.getRange("B4").setValue(data.train_name);
 //Class
 sheet.getRange("A5").setValue("Class"); sheet.getRange("B5").setValue(data.class);
 //Date Of Journey
 sheet.getRange("A6").setValue("Date Of Journey"); sheet.getRange("B6").setValue(data.doj);
 //Booking Status
 sheet.getRange("A7").setValue("Booking Status"); sheet.getRange("B7").setValue(data.passengers[0].booking_status);
 //Previous Status: Set value of Current Status
 sheet.getRange("A8").setValue("Previous Status"); sheet.getRange("B8").setValue(sheet.getRange("B9").getValue());
 //Current Status
 sheet.getRange("A9").setValue("Current Status"); sheet.getRange("B9").setValue(data.passengers[0].current_status);
 }
 //Last Response Status 
 sheet.getRange("A11").setValue("Last Response Status"); sheet.getRange("B11").setValue(data.response_code);
 //Last Run Date
 sheet.getRange("A12").setValue("Last Run Date"); sheet.getRange("B12").setValue(Date());

We keep track of previous and current status. If the status is changed then call a function sendEmails() where mailing functionality is written.

if(sheet.getRange("B8").getValue() != sheet.getRange("B9").getValue()) {
 //Last Status Changed
 sheet.getRange("A10").setValue("Last Status Changed"); sheet.getRange("B10").setValue(Date());
 sendEmails();
 }

Send Mail

As we are using Google Script, it is easy to use GMail to send the email. Lets implement sendEmails() function.
In E column of Google Sheet we have written the email list where emails need to be sent. There may be multiple receipient and every email id should be written in separate row in E column.

PNR_MailColumn

Lets implement sendEmails function. It will read the emails from E column of google sheet. Start reading emails from 2nd row as Email header is there in 1st row. numRows should be number of email id to be sent. Store the emails in array and iterate the array to send the emails.

function sendEmails() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 2; // First row of data to process
 var numRows = 2; // Number of rows to process. We have 2 email receipient.
 var dataRange = sheet.getRange(startRow, 5, numRows, 1); //5 = E Column
 // Fetch values for each row in the Range.
 var data = dataRange.getValues();
 for (i in data) {
 var row = data[i];
 var emailAddress = row[0];
 var subject = "PNR: "+ sheet.getRange("B1").getValue()+" | Class: "+sheet.getRange("B5").getValue()+" | Current Status: "+sheet.getRange("B9").getValue();
 var message = "Booking Status:" + sheet.getRange("B7").getValue() + " | \
Current Status:" + sheet.getRange("B9").getValue()+ " | \
Train Name:" + sheet.getRange("B4").getValue()+ " | \
From:" + sheet.getRange("B2").getValue()+ " | \
To:" + sheet.getRange("B3").getValue();
 MailApp.sendEmail(emailAddress, subject, message);
 }
}

Your script is ready. Now you need to schedule it in every 2 hours.
In Google Script window, Click on Resources -> Current Project’s Trigger
PNR_CurrentProjectTrigger
Click on “No triggers set up. Click here to add one now”
PNR_NoTrigger

Select the time frequency as you like to execute (e.g. Every 2 hours).

PNR_NewTrigger

You can set execution notification by clicking on notifications link.
When you will save, It will ask for authorization.

PNR_AuthorizationRequired

Allow the required permission.

PNR_RequestForAuthorization

Now your script is ready. In every 2 hour it will request for the PNR status and store the response in Google Sheet. If current response is different than previous response, it will send mail to predefined receipients.

The full Source code is shared in GitHub [https://github.com/sonu041/PNRStatus]. You are free to use it. Any feedback is always welcome.

You can copy the Sample sheet from https://docs.google.com/spreadsheets/d/18N5JVM4sAr5rpfToSOiJiqx67KjELnc0YzOTqziZCdY/copy

Few days back I have written a blog post to increase success rate of irctc tatkal ticket booking. You can check that out.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.