RFID Attendance System with Real-Time Data Storage and Access to Googl – CreateLabz Store

RFID Attendance System with Real-Time Data Storage and Access to Google Spreadsheets

0 comments

Overview

This project is an attendance system using RFID tags/stickers to communicate with an MFRC522 RFID Reader/Writer Module that is connected to an Arduino UNO. The authentication data that is received by the Arduino UNO is then transmitted to Google Spreadsheets through an ENC28J60 Ethernet Module for real-time data storage and access. This makes the attendance system paperless and organized because of the timestamp, card key, and username columns in the spreadsheet API.

Hardware Components

13.56MHz MFRC522 RFID Reader/Writer Module (RobotDyn)

RFID/NFC tags

ENC28J60 Ethernet Module (RobotDyn)

Arduino UNO w/ USB cable

Breadboard

Jumper wires

You can buy all this Hardware at Createlabz.

Software Components

Arduino IDE
Pushingbox API
Google Spreadsheets
Google Script (in Google Spreadsheets)

Application Discussion

How does an RFID Reader/Writer Module work?

An RFID, which means Radio-Frequency Identification, system consists of two main components: a transponder, a.k.a. tag, which is located on the object that we want to be identified, and a transceiver or a reader.

RFID How It Works

The RFID reader consists of a radio frequency module, a control unit and an antenna coil (inside the RFID reader/writer module) which generates a high-frequency electromagnetic field. On the other hand, the tag is usually a passive component, which consists of just an antenna and an electronic microchip. When it gets near the electromagnetic field of the transceiver, due to induction, a voltage is generated in its antenna coil and this voltage serves as power for the microchip.

Technical Details (MFRC522 Reader/Writer Module by Robotdyn)

Compatibility: can read and write Mifare’s tags, also supports MF1xxS20, MF1xxS70 and MF1xxS50 tags

Frequency: communicate using a 13.56MHz electromagnetic field.

Transfer speeds: up to 848 kBd in both directions

Size: 36x36mm

Power: 3.3V or 5V.

Note: If using 5v, you must a logic-level converter to make it 3.3V.

Antenna: built-in

Range of sensitivity: height of 25m

RFID Working Principle

Now as the tag is powered, it can extract the transmitted message from the reader, and for sending a message back to the reader, it uses a technique called load manipulation. Switching on and off a load at the antenna of the tag will affect the power consumption of the reader’s antenna which can be measured as voltage drop. These changes in the voltage will be captured as ones and zeros and that’s the way the data is transferred from the tag to the reader.

Another way of transferring data between the tag and reader is called backscattered coupling. In this case, the tag uses part of the received power for generating another electromagnetic field which will be picked up by the reader’s antenna. [1]

How does an ENC28J60 module work?

The code “ENC28J60” refers to a chip developed by Microchip. This chip has 28 pins and contains a complete stand-alone Ethernet controller for a 10BASE-T network connection with an SPI interface so microcontrollers like the Arduino can communicate to it.

10BASE-T is the same connector you’ll find on your computer (if it has one) to connect with a wire to a network, where “10” indicates a maximum speed of 10 Mbit/sec. That might sound slow, but if you consider that it’s being used by devices like the Arduino, then you can’t really expect massive data loads anyway. [2]

Image result for 10base-t connectors — Photo Credit: Router Switch Blog

Technical Details (ENC28J60 Ethernet Module by RobotDyn)

ENC28J60 is a 10BASE-T stand alone Ethernet module with on board MAC & PHY,

Buffer RAM: 8 kBytes

SPI serial interface for 3.3V and 5V logics.

Target applications: VoIP, Industrial Automation, Building Automation, Home Control, Security, and Instrumentation.

Set-up the Hardware

The whole setup is divided into four main categories which are to be done in a chronological order: Google Spreadsheets and Google Script setup, Pushingbox API setup, hardware setup and assembly, and Arduino IDE setup.

Google Spreadsheets and Google Script setup:

Make a new spreadsheet document. Rename it. Place column labels on your spreadsheet like this.

In the tabs above the document, click Tools –>Script Editor. Copy and paste the Google Script code below. Keep in mind that the language used is JavaScript. If you want to add another column to the spreadsheet, be sure to add another switch case statement in the Google Script code. Copy the syntax of one switch case statement and rename the case name and rowData value inside the brackets [].

Go back to your spreadsheet document to get the Spreadsheet ID needed for the Google Script code. Copy your spreadsheet ID (the highlighted portion in the picture below) and paste it between the apostrophes in

var id = 'place your spreadsheet ID here'; //Spreadsheet ID.

In the Google Script tab in the upper left corner, click File –> Save. Rename your project.

Click Publish tab –> Deploy as web app. Change the access in the “Who has access to the app” portion into “Anyone, even anonymous”, and click the Deploy button.

Then, it will ask for an authorization request. Click the Review Permissions button. Choose the Google Account that you will give permission for access to this app. It will show a safety warning dialog box. But no worries, click Advanced –> click Go to <file name> (unsafe). Then click the Allow button.

Before you click OK, copy the web app URL first. Then press OK. Paste your web app URL under the GET request query portion of the Google Script code for safekeeping purposes.

You can test if your Google Script code is working by putting some sample data in your web app URL. For example, you want to post John Smith in the username column. Just add “?ID=John Smith” after the exec in the web app URL. Paste the whole URL into your browser and click Enter (If John Smith doesn’t work, try adding %20 after John to remove the space).

The webpage should display Ok and your spreadsheet document should look like this. If the time is not showing in the timestamp column, click Format tab in the spreadsheet –> Number –> Date time.

Now we’re done in the software setup for Google Spreadsheets and Google Script. Do not proceed to the next steps if the sample data is not posted in the spreadsheet. If it isn’t posting, try making a new spreadsheet document and re-doing the steps again.

Pushingbox API setup

Go to www.pushingbox.com. Click login with Google in the upper right corner of the webpage. Click My Services tab. Click Add A Service button. Scroll down and click the Select This Service button beside the CustomURL service. Type down the name of your CustomURL configuration and paste your Google Script web app URL code in the Root URL textbox and click the Submit button.

Click the MY SCENARIOS tab. In the “Add a Device or Scenario” portion, enter the name of your new scenario and click the Add button. Click the Add an Action button. Click the Add an action with this service button beside the scenario you’ve just created. If the name of your scenario, for example, is Online Attendance Monitoring, click the Add an action with this service button beside it. In the Data text box, place the data format as shown below. Then click the submit button.

Now you can access and sample test your PushingBox API by sending GET requests in your browser URL in this format: (Your Device ID is seen after you click the Update button. It is shown below the Scenario name.)

api.pushingbox.com/pushingbox?devid=<pasteYourDeviceIDhere>&ID=<sampleNameforID>

A sample PushingBox URL would be:

api.pushingbox.com/pushingbox?devid=v8A9EF9B06CC352B&ID=Pocahontas

Copy and paste that into your browser and it should post the data in your spreadsheet document like this:

Now we’re done with the software setup for PushingBox API. Again, don’t proceed to the next steps if the sample testing didn’t work. If it didn’t, try making a new spreadsheet document and re-doing the steps from the Spreadsheet and Script setup up to the PushingBox API setup.

Hardware Setup and Assembly

Setup your hardware as shown below.

NOTE: Make sure to ground both GND pins of the Ethernet module for it to work properly. Also, connect your ethernet port of the module to the LAN port of your router by using a UTP cable.

Both the RFID and Ethernet modules use SPI to communicate to Arduino so they share pins 11 to 13, which are MOSI (Master Out Slave In), MISO (Master In Slave Out), and SCK (Serial Clock) respectively. Since both of them use SPI, we have to assign separate pins for the modules as Chip Select or Slave Select pin. The Ethernet module automatically assumes pin 10 as chip select, and the RFID module uses pin 9 as slave select (in SDA pin). To activate one of the slave devices, you have to set the CS pin to LOW by the digitalWrite() command. You can’t activate both of the devices so you have to enable one device and disable the other. For this project, we first used the RFID module to detect and store card key strings. After storing, we disable the RFID module and enabled the ethernet module to relay the information to the spreadsheet using the Ethernet connection.

Arduino IDE setup

Copy and paste the Arduino code below to your IDE.

To get the MAC address of your internet connection. Follow these simple steps.

For MAC Users

Connect the ethernet port of your Mac to the LAN port of your router by using a UTP Cable.

Go to System Preferences –> Click Network –>Click Ethernet on the left portion. –> Click Advanced –> Click Hardware Tab. Then, you will see the MAC address. Copy it and paste it into the Arduino code specifically in the

static byte mymac[] = {0xAA,0xBB,0xCC,0xDD,0xEE,0xFF};   // Be sure this address is unique in your network

Replace the letters inside the brackets {} with the MAC pair characters. If your MAC address is 10:a1:b1:ad:66:df, the format should be

static byte mymac[] = {0x10,0xa1,0xb1,0xad,0x66,0xdf};   // Be sure this address is unique in your network

For Windows users

Connect the ethernet port of your PC to the LAN port of your router by using a UTP Cable.

Click the icon, in the toolbar below the desktop, that looks like a PC monitor. Click Network & Internet Settings. Click View Your Network Properties in the Status Tab (right side). Find the network property that is named Ethernet, and copy the Physical Address (MAC) and paste it into the Arduino code with the same format as above.

To continue the Arduino IDE setup

Paste your PushingBox Device ID in the <insertPushingBoxdevIDhere> portion of the Arduino code (Make sure to remove the <> symbols).

const char x[] PROGMEM = "<insertPushingBoxdevIDhere>&ID=Eddyson%20Canoy";
const char y[] PROGMEM = "<insertPushingBoxdevIDhere>&ID=Mikko%20Alinsub";

Press RUN and tap your NFC/RFID tag. A sample serial monitor output will look like this (the whole output can be seen in the featured image):

Code

Libraries used

SPI.h by Arduino

This library is used if you’ll use pins 10-13, which are used for SPI communication.

EtherCard.h by Jean-Claude Wippler

This library is used if you will use an ENC28J60 Ethernet Module. Using an Ethernet Shield, you will have to use a different library. This library only supports the ENC28J60 chip. Depending on the size of the buffer for packets, this library uses about 1k of Arduino RAM. Large strings and other global variables can easily push the limits of smaller microcontrollers. This library uses the SPI interface of the microcontroller and will require at least one dedicated pin for CS, plus the SO, SI, and SCK pins of the SPI interface.

MFRC522.h by Miguel Balboa

This is the Arduino library for MFRC522 and other RFID RC522 based modules. It reads and writes different types of Radio-Frequency IDentification (RFID) cards on your Arduino using an RC522 based reader connected via the Serial Peripheral Interface (SPI) interface. For more details about the technical and compatibility aspects, click the link above.

Google Script code

//-----------------------------------------------
//Originally published by Mogsdad@Stackoverflow
//Modified for jarkomdityaz.appspot.com
//-----------------------------------------------
/*

GET request query:
<paste your web app URL here>

Pushingbox link:
<paste your Pushingbox link here>

/* Using spreadsheet API */

function doGet(e) { 
  Logger.log( JSON.stringify(e) );  // view parameters

  var result = 'Ok'; // assume success

  if (e.parameter == undefined) {
    result = 'No Parameters';
  }
  else {
    var id = 'paste your spreadsheet ID here'; // Spreadsheet ID
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;
    var rowData = [];
    //var waktu = new Date();
    rowData[0] = new Date(); // Timestamp in column A
    for (var param in e.parameter) {
      Logger.log('In for loop, param='+param);
      var value = stripQuotes(e.parameter[param]);
      Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'ID': //ID
          rowData[1] = value; //Value in column B
          break;
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));

    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }

  // Return result of operation
  return ContentService.createTextOutput(result);
}

/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

Arduino IDE code

////
//
// General code from http://www.pushingbox.com for Arduino + Ethernet Shield/Module (ENC28J60)
// Library EtherCard from https://github.com/jcw/ethercard is under mit license
//
////
#include <EtherCard.h>
#include <SPI.h>
#include <MFRC522.h>

  /////////////////
 // MODIFY HERE //
/////////////////
static byte mymac[] = {paste mac address here};   // Be sure this address is unique in your network

// Debug mode
boolean DEBUG = true;
  ///////
 //End//
/////// 


const char website[] PROGMEM = "api.pushingbox.com";
byte Ethernet::buffer[400]; //change the value within the range of 300 to 850 for faster connection

#define rfidCSpin 9 //CS pin for MFRC522 RFID module
#define etherCSpin 10 //CS Pin for ENC28J60 Module
#define RST_PIN 8 //Reset pin

MFRC522 rfid (rfidCSpin, RST_PIN);
MFRC522 :: MIFARE_Key key;

String data ; //GET query with data
String ID;

void setup () {
  Serial.begin(9600);
  //pinMode(pinDevid1, INPUT);
  SPI.begin();
  rfid.PCD_Init();

  //disable RFID
  pinMode(9, OUTPUT);
  digitalWrite(9, HIGH);

  //enable ethernet
  pinMode(10, OUTPUT);
  digitalWrite(10, LOW);

 
}

void loop () {
    
    ether.packetLoop(ether.packetReceive());

    if (! rfid.PICC_IsNewCardPresent () ||! rfid.PICC_ReadCardSerial ())
    return ;
    
    digitalWrite(10, HIGH); //turn off ethernet
    digitalWrite(9, LOW); //turn on RFID
     
    if (digitalRead(rfidCSpin) == LOW && digitalRead(etherCSpin) == HIGH){
      //Serial.println("Scan NFC tag.");
      rfidDetect(); //detects the validity of the NFC/RFID tags
      storeKey();//if validated, stores card key into a string
      delay(10);
    }
    delay(50);
    
      digitalWrite(9, HIGH); //turn off RFID
      digitalWrite(10, LOW); //turn on ethernet
      connectEthernet(); //connects ethernet module to your router
      
      //if-else statements that will determine what URL to open
      if (ID == "Eddyson Canoy"){
        const char x[] PROGMEM = "<insertPushingBoxdevIDhere>&ID=Eddyson%20Canoy";
        ether.browseUrl(PSTR("/pushingbox?devid="), x, website, my_callback);
        ID = "";
      }
      else if (ID == "Mikko Alinsub"){
        const char y[] PROGMEM = "<insertPushingBoxdevIDhere>&ID=Mikko%20Alinsub";
        ether.browseUrl(PSTR("/pushingbox?devid="), y, website, my_callback); 
        ID = ""; 
      }
      else {
        Serial.println("Can't store data to online server");
      }
      
      
}

// called when the client request is complete
static void my_callback (byte status, word off, word len) {
  Serial.println(">>>");
  Ethernet::buffer[off+300] = 0;
  Serial.print((const char*) Ethernet::buffer + off);
  Serial.println("...");
  Serial.println("-------------");
  Serial.println("Tap card key.");
}
void storeKey(){
  String strID = "";
  for (byte i = 0; i <4; i ++) {
    strID +=
    (rfid.uid.uidByte [i] <0x10? "0" : "" ) +
    String (rfid.uid.uidByte [i], HEX) + (i!=3 ? ":" : "" );
    //Serial.println(strID);
    delay(10);
  }
  strID.toUpperCase ();

  // At this point, the strID variable will store the UID of the tag. 
  Serial.print ( "Card key: " );
  Serial.println (strID);
  
   
  if(strID == "F1:95:B2:01"){ //replace the strID depending on the NFC/RFID tag's card key
     ID = "insert Employee 1 name here";
     Serial.println(ID);
     }
  else if (strID == "F1:8E:B2:01"){ //replace the strID depending on the NFC/RFID tag's card key
     ID = "insert Employee 2 name here";
     Serial.println(ID);
    }
  else{
     Serial.println("ID not recognized.");
    }
  rfid.PICC_HaltA ();
  rfid.PCD_StopCrypto1 ();
}
void rfidDetect(){
  // Serial.print (F ("PICC type:"));
  MFRC522 :: PICC_Type piccType = rfid.PICC_GetType (rfid.uid.sak);
  // Serial.println (rfid.PICC_GetTypeName (piccType));

  // Check is the PICC of Classic MIFARE type 
  if (piccType != MFRC522 :: PICC_TYPE_MIFARE_MINI && piccType != MFRC522 :: PICC_TYPE_MIFARE_1K && piccType != MFRC522 :: PICC_TYPE_MIFARE_4K) {
    Serial.println( "Your tag is not of type MIFARE Classic." );
    return ;
  }
}
void connectEthernet(){
  if(DEBUG){Serial.println("\n[getDHCPandDNS]");}
  
  //
  //***Depending of your Shield, you may have to try this line instead of the second***//
  //if(ether.begin(sizeof Ethernet::buffer, mymac) == 0) 
  if(ether.begin(sizeof Ethernet::buffer, mymac, 10) == 0)//pin 10 is the CS pin for ethernet
    if(DEBUG){Serial.println( "Failed to access Ethernet controller");}
  
  // Wait until we have an IP from the DHCP
  while(!ether.dhcpSetup()){
    if(DEBUG){Serial.println("Error: DHCP failed. Can't get an IP address, let's retry...");}
  }
  
  if(DEBUG){
    ether.printIp("My IP: ", ether.myip);
    ether.printIp("Netmask: ", ether.netmask);
    ether.printIp("GW IP: ", ether.gwip);
    ether.printIp("DNS IP: ", ether.dnsip);
    //connected = true;
    Serial.println("Connected.");
  }

  if (!ether.dnsLookup(website))
    if(DEBUG){Serial.println("DNS failed");}
  if(DEBUG){ether.printIp("Server: ", ether.hisip);}
}

Conclusion

This project has its own limitations. An example would be that it would take a lot of time to connect to the internet when you scan your RFID tag for the second time. It was hard making this happen but at least you’ve come this far! You’ve now accomplished another IoT mini-project. You can now tinker and edit the hardware and software to make it advanced.