Facing issue in updating data into google sheets

[uday_8660]

Hello Embedded community, I am assigned a project of developing an attendance system using RFID reader modules (EM-18 and RC522) and updating the data into Google Sheets and using NodeMCU (ESP8266) controller and HTTPSRedirect library to link NodeMCU with Google Sheets.

To brief about my project, first when the RFID card is tapped for the first time, the system becomes active.
Now, RC522 Reader module detects whether the the tag is valid or not, once it is an valid tag then, the corresponding to that tag is stored. Once all tags are read, RFID card is scanned again. Then the read data is updated into google sheets.

My issues:
Only the last read data is being updated into sheets but not the previously updated data. Please help me with my request.
Also once an Valid RFID tag (4 bytes) is read , it should be able to detect the tag and print appropriate message instead of adding that data.

The shared google docs link gives the present IDE and Script code along with the present output - https://docs.google.com/document/d/1_Hf6wmpMqufzm3uRUsNVU78tNb-pEBZYl3kyxF9qGe0/edit?usp=sharing.

The image gives a description of how the attendance data should be updated into sheets.

Here if the student is present on first day, then its value is one. If the same student is present on next day then that cell value will be present cell value + previous cell value (1 + 1 = 2).

If the student remains absent on second day and is present on first day then the corresponding cell value (0 + 1 = 1). Present cell value + previous cell value.

[J-M-L]

@uday_8660

Only the last read data is being updated into sheets but not the previously updated data. Please help me with my request.

post the code here (with code tags)

[uday_8660]

IDE Code

#include <SPI.h>
#include <MFRC522.h>
#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>
#include <HTTPSRedirect.h>

#define RST_PIN   D3
#define SS_PIN    D4
#define GREEN     D0
#define RED       D1
#define BUZZER    D2

MFRC522 mfrc522(SS_PIN, RST_PIN);
MFRC522::MIFARE_Key key;

const char* SSID = "SSID";
const char* PASSWORD = "PASSWORD";
const char* HOST = "script.google.com";
const int httpsPort = 443;
const char* GScriptID = "SheetID";

String payload_base = "{\"command\":\"insert_row\",\"sheet_name\":\"Sheet1\",\"values\":";
String payload = "";

String url = String("/macros/s/") + GScriptID + "/exec";

const int numOfCards = 4;
byte cards[numOfCards][4] = {
  {0x83, 0x40, 0x23, 0x24},
  {0x43, 0x9B, 0x1F, 0x24},
  {0x66, 0xF1, 0x62, 0xAF},
  {0x53, 0x1E, 0x3A, 0x24}
};
String names[numOfCards] = {"Rakshith R", "Sharath M", "Shreyas P S Rao", "Uday C H"};
String usn[numOfCards] = {"1KS20EC077", "1KS20EC093", "1KS20EC098", "1KS20EC108"};

int state = 0; // 0: EM-18 reader inactive, 1: EM-18 reader active

HTTPSRedirect* client = nullptr;

void setup() {
  Serial.begin(9600);
  SPI.begin();
  mfrc522.PCD_Init();
  pinMode(RED, OUTPUT);
  pinMode(GREEN, OUTPUT);
  pinMode(BUZZER, OUTPUT);
  connectWiFi();

  // Use HTTPSRedirect class to create a new TLS connection
  client = new HTTPSRedirect(httpsPort);
  client->setInsecure();
  client->setPrintResponseBody(true);
  client->setContentTypeHeader("application/json");

  Serial.print("Connecting to ");
  Serial.println(HOST);

  bool flag = false;
  for (int i = 0; i < 5; i++) {
    int retval = client->connect(HOST, httpsPort);
    if (retval == 1) {
      flag = true;
      Serial.println("Connected");
      break;
    }
    else
      Serial.println("Connection failed. Retrying...");
  }
  if (!flag) {
    Serial.print("Could not connect to server: ");
    Serial.println(HOST);
    return;
  }
}

void loop() {
  if (WiFi.status() == WL_CONNECTED) {
    readRFID();
  }
}

void connectWiFi() {
  WiFi.begin(SSID, PASSWORD);
  Serial.print("Connecting to WiFi");
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("WiFi connected");
  digitalWrite(GREEN, HIGH);
  digitalWrite(BUZZER, HIGH);
  delay(1000);
  digitalWrite(GREEN, LOW);
  digitalWrite(BUZZER, LOW);
}

void readRFID() {
  if (Serial.available()) {
    char input[12];
    int count = 0;
    while (Serial.available() && count < 12) {
      input[count] = Serial.read();
      count++;
      delay(5);
    }
    if (count == 12) {
      if ((strncmp(input, "4D008CDDDAC6", 12) == 0) && (state == 0)) {
        digitalWrite(GREEN, HIGH);
        digitalWrite(BUZZER, HIGH);
        delay(1000);
        digitalWrite(GREEN, LOW);
        digitalWrite(BUZZER, LOW);
        Serial.println("Valid Card detected");
        state = 1;
        readRFID();
      } else if ((strncmp(input, "4D008CDDDAC6", 12) == 0) && (state == 1)) {
        digitalWrite(GREEN, HIGH);
        digitalWrite(BUZZER, HIGH);
        delay(1000);
        digitalWrite(GREEN, LOW);
        digitalWrite(BUZZER, LOW);
        state = 0;
      } else {
        Serial.println("Invalid card detected");
        digitalWrite(RED, HIGH);
        digitalWrite(BUZZER, HIGH);
        delay(1000);
        digitalWrite(RED, LOW);
        digitalWrite(BUZZER, LOW);
      }
    }
  }

  if (state == 1) {
    // Check if a new card is present
    if (mfrc522.PICC_IsNewCardPresent()) {
      // Read the card
      if (mfrc522.PICC_ReadCardSerial()) {
        byte card_ID[4];
        for (byte i = 0; i < mfrc522.uid.size; i++) {
          card_ID[i] = mfrc522.uid.uidByte[i];
        }

        // Check if the detected card matches any of the stored cards
        for (int i = 0; i < numOfCards; i++) {
          if (compareCardIDs(card_ID, cards[i])) {
            // If valid card detected, update Google Sheets with the student's information
            updateGoogleSheets(names[i], usn[i], 1); // Assuming the present value is always 1 for valid card detection
            return; // Exit the loop after updating attendance for the detected card
          }
        }
      }
    }
  }
}

bool compareCardIDs(byte card1[], byte card2[]) {
  for (int i = 0; i < 4; i++) {
    if (card1[i] != card2[i]) {
      return false;
    }
  }
  return true;
}

bool updateGoogleSheets(String name, String usn, int present) {
  payload = "{\"command\": \"insert_row\", \"sheet_name\": \"Sheet1\", \"values\": \"" + name + "," + usn + "," + String(present) + "\"}";

  // Publish data to Google Sheets
  Serial.println("Publishing data...");
  Serial.println(payload);
  if (client->POST(url, HOST, payload)) {
    // Do something if publish was successful
    return true;
  }
  else {
    // Do something if publish was not successful
    Serial.println("Error while connecting");
    return false;
  }

  // A delay of several seconds is required before publishing again
  delay(5000);
}

[uday_8660]

gs code

// Enter Spreadsheet ID here
var SS = SpreadsheetApp.openById('1xoxTEADe4NQr7kIgvmoJCGiQOLNyFAijf9h7suUiPTI');

function doPost(e) {
  var parsedData;
  var result = {};
  
  try { 
    parsedData = JSON.parse(e.postData.contents);
  } 
  catch(f){
    return ContentService.createTextOutput("Error in parsing request body: " + f.message);
  }
   
  if (parsedData !== undefined){
    var sheetName = parsedData.sheet_name;
    var dataArr = parsedData.values.split(",");
    var name = dataArr[0];
    var usn = dataArr[1];
    var present = parseInt(dataArr[2]); // Convert to integer

    var sheet = SS.getSheetByName(sheetName);
    
    var date_now = Utilities.formatDate(new Date(), "Asia/Calcutta", "dd/MM/yyyy"); // gets the current date
    var time_now = Utilities.formatDate(new Date(), "Asia/Calcutta", "hh:mm:ss a"); // gets the current time
    
    switch (parsedData.command) {
      case "insert_row":
        var rowIndex = findRowIndex(sheet, name, usn);
        if (rowIndex == -1) {
          rowIndex = sheet.getLastRow() + 1;
          sheet.getRange(rowIndex, 1).setValue(name);
          sheet.getRange(rowIndex, 2).setValue(usn);
        }
        
        updateAttendance(sheet, rowIndex, present);
        break;
         
      // You can add more cases here for other commands if needed
    }
    
    return ContentService.createTextOutput("Success");
  } else {
    return ContentService.createTextOutput("Error! Request body empty or in incorrect format.");
  }
}

// Function to find the row index based on name and USN
function findRowIndex(sheet, name, usn) {
  var dataRange = sheet.getRange("A:B").getValues();
  for (var i = 0; i < dataRange.length; i++) {
    if (dataRange[i][0] == name && dataRange[i][1] == usn) {
      return i + 1; // Adding 1 to convert from zero-based index to sheet index
    }
  }
  return -1; // If not found
}

// Function to update attendance
function updateAttendance(sheet, rowIndex, present) {
  var dateColumn = findNextAvailableColumn(sheet, 3);
  if (dateColumn > 0) {
    sheet.getRange(1, dateColumn).setValue(new Date()); // Update the date in the header row
    sheet.getRange(rowIndex, dateColumn).setValue(present); // Update attendance
  }
  fillEmptyWithZeros(sheet);
}

// Function to find the next available column starting from a given column index
function findNextAvailableColumn(sheet, startColumn) {

  // Check if there are any columns beyond the startColumn
  if (sheet.getLastColumn() < startColumn) {
    return startColumn; // Return the startColumn if no columns exist beyond it
  }

  var headers = sheet.getRange(1, startColumn, 1, sheet.getLastColumn() - startColumn + 1).getValues()[0];
  var emptyColumnIndex = headers.indexOf("");

  if (emptyColumnIndex === -1) {
    return sheet.getLastColumn() + 1; // If no empty column found, return the next column after the last column
  } else {
    return startColumn + emptyColumnIndex; // If empty column found, return its index
  }
}

function fillEmptyWithZeros(sheet) {
  // Get the data range
  var dataRange = sheet.getRange(sheet.getActiveRange().getRow(), 1, sheet.getLastRow(), sheet.getLastColumn());
  
  // Get the values
  var values = dataRange.getValues();

  // Loop through each cell and replace empty ones with 0
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j] === "") {
        values[i][j] = 0;
      }
    }
  }
  // Set the values back to the sheet
  dataRange.setValues(values);
}

[J-M-L]

can you clarify what you get over Serial ?

@uday_8660

    while (Serial.available() && count < 12) {
      input[count] = Serial.read();
      count++;
      delay(5);
    }

(and it's an error prone way of reading the Serial line with the delay, I would suggest to study Serial Input Basics to handle this)

[uday_8660]

I've used this line to read the RFID Card value detected by EM-18 Reader module.

[allanpotts]

It looks like the core functionality of your code is working, but the issue with only the last read data being updated in Google Sheets is likely due to how the data is being posted. It seems like each new tag is overwriting the previous one rather than appending. You might want to adjust the Google Script to ensure it appends the data properly instead of replacing it.

Additionally, you are calling readRFID() recursively, which might cause performance problems or even stack overflow. I would recommend restructuring it to avoid recursion and use loops to manage state transitions. The POST request to Google Sheets also needs a proper delay and timeout to ensure stable connections. Double-check that your Google Script is handling multiple rows correctly and consider adding more logging to identify if there's an issue on the script side. With this, your Arm Blaster project can run much more smoothly.