I need some help. I have write my coding and all has function well on the hardware. But i have some problem for the google sheet communication. why my hardware cannot sent the data to the google sheet automatic. it need to copy the https link that produce by Arduino and paste to the google. My project is about price indicator RFID Trolley. It means If the UID card been scan and it will read at the LCD and these has 6 button which is for accept, unaccepted, ++,--, remove item and for done buying. The problem is when I push the button these only give a calculated to the Hardware only. It not sent to the google sheet. I will show my coding.
#At Arduino#
/*
RFID PN532 Uno
SCK D13
MSO D12
MOSI D11
SS (SDA) D10
VCC 3.3V
GND GND
IRQ Non
RSTO D9
RFID PN532 Mega
SCK D52
MSO D50
MOSI D51
SS (SDA) D53
VCC 3.3V
GND GND
IRQ Non
RSTO D49
______________
Push Button
Pin 2 Select Item
Pin 3 TotDisplay Item
Pin 4 Quantity ++
Pin 5 Quantity -
Pin 6 Remove Item
Pin 7 Done Buying
Signal Component
Pin 22 Led Red
Pin 24 Led Yellow
Pin 26 Led Green
Pin 28 Buzzer
LCD
SDA 20
SCL 21
5V
GND
*/
#include <SPI.h>
#include <Adafruit_PN532.h>
#include <LiquidCrystal_I2C.h>
/*########################### Setup Wifi Module ############################*/
#include "WiFiEsp.h"
// Emulate Serial1 on pins 7/6 if not present
#ifndef HAVE_HWSERIAL1
#include "SoftwareSerial.h"
SoftwareSerial Serial1(6, 7); // RX, TX
#endif
char ssid[] = "??"; // your network SSID (name)
char pass[] = "password11223344"; // your network password
int status = WL_IDLE_STATUS; // the Wifi radio's status
// Initialize the Ethernet client object
WiFiEspClient client;
/*########################### Google Script Variable ############################*/
String urlGscript = "script.google.com/macros/s/AKfycbzXVE8HiqMC6VVrWMcjVUV0hIcmC5969DOUfWZINiIVQjiRTIrblsdeOswT3VxcLz_pyg/exec?"; // base url for google apps script
String urlFinal = ""; // will change based on cart condition
// Time
#define TIMEOUT_MS 5000
// RFID
#define PN532_SCK 13
#define PN532_MISO 12
#define PN532_MOSI 11
#define PN532_SS 10
Adafruit_PN532 nfc(PN532_SCK, PN532_MISO, PN532_MOSI, PN532_SS);
// LCD
LiquidCrystal_I2C lcd(0x27, 16, 2); // I2C address 0x27, 16 column and 2 rows
bool userChoice = false; // User has not made a choice yet
float Input = 0; // input for Item price
float TotValue = 0; // Total Price iteam
float Value = 0; // valur for each item
String itemName = "";
String itemUID = "";
int Item = 0; // count Product
int minValue;
int i;
int p1 = 0, p2 = 0, p3 = 0, p4 = 0, p5 = 0;
/*########################### Void Setup Starting ############################*/
void setup()
{
// INPUT PIN
pinMode(2, INPUT_PULLUP); // wants
pinMode(3, INPUT_PULLUP); // didn't wants
pinMode(4, INPUT_PULLUP); // ++ Item
pinMode(5, INPUT_PULLUP); // - Item
pinMode(6, INPUT_PULLUP); // Remove Item// Done Buying
pinMode(7, INPUT_PULLUP); // Done Buying
// OUTPUT PIN
pinMode(22, OUTPUT); // RED LED
pinMode(24, OUTPUT); // YELLOW LED
pinMode(26, OUTPUT); // GREEN LED
pinMode(28, OUTPUT); // BUZZER
Serial.begin(115200); // Initiate a serial communication
// initiate communication with esp
Serial1.begin(115200);
WiFi.init(&Serial1);
// check for the presence of the shield
if (WiFi.status() == WL_NO_SHIELD)
{
Serial.println("WiFi shield not present");
// don't continue
while (true)
;
}
// attempt to connect to WiFi network
while (status != WL_CONNECTED)
{
Serial.print("Attempting to connect to WPA SSID: ");
Serial.println(ssid);
// Connect to WPA/WPA2 network
status = WiFi.begin(ssid, pass);
}
Serial.println("You're connected to the network");
printWifiStatus();
// Start Cording
lcd.begin(); // initialize the lcd
lcd.backlight();
nfc.begin(); // Initiate PN532
Starting();
// lcd.print();
// Serial.println();
{
for (int position = 0; position < 23; position++)
{
lcd.setCursor(0, 0);
lcd.scrollDisplayLeft();
lcd.print(" Welcome to The Market... ");
delay(300);
}
}
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("Please Scan Items");
delay(200);
}
/*############################ Void Loop starting ##############################*/
void loop()
{
uint8_t success;
uint8_t uid[] = {0, 0, 0, 0, 0, 0, 0}; // Buffer to store the returned UID
uint8_t uidLength; // Length of the UID (4 or 7 bytes depending on ISO14443A card type)
if (digitalRead(7) == LOW)
{
lcd.clear();
lcd.setCursor(0, 0);
lcd.scrollDisplayLeft();
lcd.print(" Done Buying ?");
delay(500);
lcd.setCursor(0, 1);
lcd.print(" Hold / || Hold X");
delay(500);
}
if (digitalRead(2) == LOW)
{
lcd.clear();
lcd.setCursor(3, 0);
lcd.print("Thank you");
delay(2000);
for (i = 0; i < 25; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(0, 1);
lcd.print(" Please show the Barcode to the counter");
delay(500);
}
TotDisplay();
}
else if (digitalRead(3) == LOW)
{
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("Scan Items");
delay(2000);
lcd.clear();
TotDisplay();
return loop();
}
else
{
success = nfc.readPassiveTargetID(PN532_MIFARE_ISO14443A, uid, &uidLength, TIMEOUT_MS);
if (!success)
{
digitalWrite(22, 1); // RED
digitalWrite(26, 1); // YELLOW
delay(500);
digitalWrite(22, 0); // RED
digitalWrite(26, 0); // Buzzer
return;
}
// Show UID on serial monitor
Serial.print("UID tag :"); // If want to show UID tag
String content = "";
for (byte i = 0; i < uidLength; i++)
{
Serial.print(uid[i] < 0x10 ? " 0" : " ");
Serial.print(uid[i], HEX);
content.concat(String(uid[i] < 0x10 ? " 0" : " "));
content.concat(String(uid[i], HEX));
}
lcd.clear();
lcd.setCursor(0, 0);
Serial.println();
lcd.print("Item : ");
Process();
content.toUpperCase();
Input = Serial.parseFloat();
/*############################ RFID TAG PARTS ####################################*/
if ((content.substring(1) == "53 FD 6A AB 71 00 01") || (content.substring(1) == "53 C8 C4 AC 71 00 01")) // change here the UID of the card/cards that you want to give access
{
lcd.setCursor(6, 0);
lcd.print(" Maggie");
lcd.setCursor(0, 1);
lcd.print("Price: RM 3.50");
Input = 3.50;
Item = 1;
itemName = "Maggie";
itemUID = "53 FD 6A AB 71 00 01";
p1;
delay(500);
}
else if ((content.substring(1) == "53 F4 6E AB 71 00 01") || (content.substring(1) == "53 B9 56 AB 71 00 01")) // sec item
{
lcd.setCursor(6, 0);
lcd.print(" Bekas");
lcd.setCursor(0, 1);
lcd.print("Price: RM 5.00");
Input = 5;
Item = 1;
itemName = "Bekas";
itemUID = "53 F4 6E AB 71 00 01";
p2;
delay(500);
}
else if (content.substring(1) == "53 FC 72 AB 71 00 01") // third item
{
lcd.setCursor(6, 0);
lcd.print(" Lifebuoy ");
lcd.setCursor(0, 1);
lcd.print("Price: RM 7.00");
Input = 7;
Item = 1;
itemName = "Lifebuoy";
itemUID = "53 FC 72 AB 71 00 01";
p3;
delay(500);
}
else if ((content.substring(1) == "53 2A 64 AB 71 00 01") || (content.substring(1) == "53 1E 5A AB 71 00 01")) // fourth item
{
for (i = 0; i < 20; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(6, 0);
lcd.print(" Mushroom Carbonara");
delay(300);
}
lcd.clear();
lcd.setCursor(0, 0);
lcd.print(" Mushroom Carbonara");
lcd.setCursor(0, 1);
lcd.print("Price: RM 7.50");
Input = 7.50;
Item = 1;
itemName = "Mushroom Carbonara";
itemUID = "53 2A 64 AB 71 00 01";
p4;
delay(500);
}
else if (content.substring(1) == "53 0D 67 AB 71 00 01") // Fifth item
{
lcd.setCursor(6, 0);
lcd.print(" Holick");
lcd.setCursor(0, 1);
lcd.print("Price: RM 10.50");
Input = 10.50;
Item = 1;
itemName = "Holick";
itemUID = "53 0D 67 AB 71 00 01";
p5;
delay(500);
/*for(i = 0; i < 20; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(6,0);
lcd.print(" Mushroom Carbonara");
delay(300);
}
lcd.clear();
lcd.setCursor(6,0);
lcd.print(" Holick");
lcd.setCursor(0,1);
lcd.print("Price: RM 10.50");
Input = 10.50;
Item = 1;
p5;
delay(500);*/
}
else
{
Error();
lcd.clear();
lcd.setCursor(0, 0);
lcd.print(" Error!");
lcd.setCursor(1, 1);
lcd.print(" Rescan Item ");
delay(2000);
TotDisplay();
return loop();
}
/*##################################### Quantity Parts #####################################*/
while (i = 1)
{
if (digitalRead(4) == LOW)
{
IncreaseItem();
}
if ((digitalRead(5) == LOW) && (i >= 0))
{
DecreaseItem();
}
/*##################################### Customer Picking Parts #####################################*/
// Customer Wanna buy or not
if (digitalRead(2) == LOW) // Want the item
{
lcd.clear();
userChoice = true;
Granted();
for (i = 0; i < 8; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(0, 0);
lcd.print(" InCart "); // wants the item
delay(500);
}
if ((content.substring(1) == "53 FD 6A AB 71 00 01") || (content.substring(1) == "53 C8 C4 AC 71 00 01"))
{ // first item
p1 = p1 + Item;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p1) + "&action_type=add";
}
else if ((content.substring(1) == "53 F4 6E AB 71 00 01") || (content.substring(1) == "53 B9 56 AB 71 00 01"))
{ // sec item
p2 = p2 + Item;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p2) + "&action_type=add";
}
else if (content.substring(1) == "53 FC 72 AB 71 00 01")
{ // third item
p3 = p3 + Item;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p3) + "&action_type=add";
}
else if ((content.substring(1) == "53 2A 64 AB 71 00 01") || (content.substring(1) == "53 1E 5A AB 71 00 01"))
{ // four item
p4 = p4 + Item;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p4) + "&action_type=add";
}
else if (content.substring(1) == "53 0D 67 AB 71 00 01")
{ // fith item
p5 = p5 + Item;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p5) + "&action_type=add";
}
TotAdds();
sendToSheet(urlFinal);
return loop();
}
else if (digitalRead(3) == LOW) // Didn't want the item
{
lcd.clear();
lcd.setCursor(0, 0);
userChoice = true;
Granted();
for (i = 0; i < 9; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(0, 0);
lcd.print(" Unaccept "); // wants the item
delay(400);
}
TotDisplay();
return loop();
}
/*########################## Remove Item ###################################*/
if (digitalRead(6) == 0)
{
digitalWrite(28, 1); // Buzzer
delay(200);
digitalWrite(24, 0); // Yellow
delay(50);
digitalWrite(28, 0); // Buzzer
delay(100);
digitalWrite(26, 1); // RED
delay(1000);
digitalWrite(26, 0); // RED
delay(50);
if ((content.substring(1) == "53 FD 6A AB 71 00 01" ) || (content.substring(1) =="53 C8 C4 AC 71 00 01" )) // First Item
{
if (p1 > 0)
{
for (i = 0; i < 20; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(6, 0);
lcd.print(" Remove Maggie");
lcd.setCursor(0, 1);
lcd.print("Price:-RM 3.50");
delay(200);
}
TotValue = TotValue - 3.50;
p1--;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p1) + "&action_type=add";
ZeroChecker();
sendToSheet(urlFinal);
return loop();
}
else
{
Error();
lcd.clear();
NoIncart();
TotDisplay();
return loop();
}
}
else if ((content.substring(1) == "53 F4 6E AB 71 00 01" ) || (content.substring(1) == "53 B9 56 AB 71 00 01")) // sec item
{
if (p2 > 0)
{
for (i = 0; i < 20; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(6, 0);
lcd.print(" Remove Bekas");
lcd.setCursor(0, 1);
lcd.print("Price:-RM 5.00");
delay(200);
}
TotValue = TotValue - 5;
p2--;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p2) + "&action_type=add";
ZeroChecker();
sendToSheet(urlFinal);
return loop();
}
else
{
Error();
lcd.clear();
NoIncart();
TotDisplay();
return loop();
}
}
else if (content.substring(1) == "53 FC 72 AB 71 00 01") // third item
{
if (p3 > 0)
{
for (i = 0; i < 20; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(6, 0);
lcd.print(" Remove Lifebuoy");
lcd.setCursor(0, 1);
lcd.print("Price:-RM 7.00");
delay(200);
}
TotValue = TotValue - 7;
p3--;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p3) + "&action_type=add";
ZeroChecker();
sendToSheet(urlFinal);
return loop();
}
else
{
Error();
lcd.clear();
NoIncart();
TotDisplay();
return loop();
}
}
else if ((content.substring(1) == "53 2A 64 AB 71 00 01") || (content.substring(1) == "53 1E 5A AB 71 00 01")) // fourth item
{
if (p4 > 0)
{
for (i = 0; i < 26; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(0, 0);
lcd.print(" Remove Mushroom Carbonara");
lcd.setCursor(0, 1);
lcd.print("Price:-RM 7.50");
delay(200);
}
TotValue = TotValue - 7.50;
p4--;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p4) + "&action_type=add";
ZeroChecker();
sendToSheet(urlFinal);
return loop();
}
else
{
Error();
lcd.clear();
NoIncart();
TotDisplay();
return loop();
}
}
else if (content.substring(1) == "53 0D 67 AB 71 00 01") // Fifth item
{
if (p5 > 0)
{
for (i = 0; i < 14; i++)
{
lcd.scrollDisplayLeft();
lcd.setCursor(6, 0);
lcd.print(" Remove Holick");
lcd.setCursor(0, 1);
lcd.print("Price: -RM 10.50");
delay(200);
}
TotValue = TotValue - 10.50;
p5--;
urlFinal = urlGscript + "uid=" + itemUID + "&item_name=" + itemName + "&item_price=" + String(Input) + "&item_quantity=" + String(p5) + "&action_type=add";
ZeroChecker();
sendToSheet(urlFinal);
return loop();
}
else
{
Error();
NoIncart();
TotDisplay();
return loop();
}
}
sendToSheet(urlFinal);
}
}
}
// if the server's disconnected, stop the client
if (!client.connected())
{
Serial.println();
Serial.println("Disconnecting from server...");
client.stop();
while (true);
}
}
/* ###########Void Code##################*/
void printWifiStatus(){
// print the SSID of the network you're attached to
Serial.print("SSID: ");
Serial.println(WiFi.SSID());
// print your WiFi shield's IP address
IPAddress ip = WiFi.localIP();
Serial.print("IP Address: ");
Serial.println(ip);
// print the received signal strength
long rssi = WiFi.RSSI();
Serial.print("Signal strength (RSSI):");
Serial.print(rssi);
Serial.println(" dBm");
}
void sendToSheet(String url){
// send GET Request
Serial.println("Sending data to google sheet");
Serial.println(url);
client.print(String("GET ") + url + " HTTPS/1.1\r\n" +
"Host: " + "script.google.com" + "\r\n" +
"Connection: close\r\n\r\n");
/*unsigned long timeout = millis();
while (client.available() == 0)
{
if (millis() - timeout > 10000)
{
Serial.println(">>> Client Timeout !");
client.stop();
return;
}
}*/
// Read all the lines of the reply from server and print them to Serial
while (client.available())
{
String line = client.readStringUntil('\r');
Serial.print(line);
}
Serial.println();
// Close connection
Serial.println("closing connection");
Serial.println();
client.stop();
}
void Starting(){
digitalWrite(22, 1); // RED
digitalWrite(24, 1); // YELLOW
digitalWrite(26, 1); // GREEN
delay(1000);
digitalWrite(22, LOW); // RED
delay(500);
digitalWrite(24, LOW); // YELLOW
delay(500);
digitalWrite(26, LOW); // GREEN
delay(500);
}
void NoIncart(){
lcd.clear();
lcd.print("No Incart!");
lcd.setCursor(0, 1);
delay(500);
}
void Process(){ // for starting process UID
digitalWrite(28, 1); // buzzer
delay(100);
digitalWrite(28, 0);
digitalWrite(22, 1); // RED
delay(1000);
digitalWrite(24, 1); // Yellow
delay(50);
digitalWrite(22, 0); // RED
}
void Granted(){ // LED access
digitalWrite(28, 1); // Buzzer
delay(100);
digitalWrite(24, 0); // Yellow
delay(50);
digitalWrite(28, 0); // Buzzer
delay(100);
digitalWrite(26, 1); // Green
delay(1000);
digitalWrite(26, 0); // Green
delay(50);
}
void Error(){ // LED Error
digitalWrite(28, 1); // Buzzer
delay(200);
digitalWrite(24, 0); // Yellow
delay(50);
digitalWrite(28, 0); // Buzzer
delay(100);
digitalWrite(28, 1); // Buzzer
delay(200);
digitalWrite(28, 0); // Buzzer
delay(100);
digitalWrite(22, 1); // Yellow
delay(1000);
digitalWrite(22, 0); // Yellow
delay(50);
}
void ZeroChecker(){ // Value Not zero
if (TotValue < minValue)
{
TotValue = 0; // set value to minimum allowed value
}
lcd.clear();
lcd.setCursor(0, 0);
lcd.print(" Total Price: ");
lcd.setCursor(1, 1);
lcd.print(" Rm ");
lcd.print(TotValue);
delay(500);
}
void TotAdds(){ // display with add calculation
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("Total Price: ");
TotValue = TotValue + (Input * Item);
if (TotValue < minValue)
{
TotValue = 0; // set value to minimum allowed value
}
lcd.setCursor(0, 1);
lcd.print("RM ");
lcd.setCursor(3, 1);
lcd.print(TotValue, 2);
delay(500);
}
void TotDisplay(){ // Value display without calculation
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("Total Price: ");
TotValue = TotValue;
lcd.setCursor(0, 1);
lcd.print("RM ");
lcd.setCursor(3, 1);
lcd.print(TotValue, 2);
delay(500);
}
void IncreaseItem(){ // Item Check more than one
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("Quantity: ");
Item = Item + 1;
i++;
lcd.print(Item);
lcd.setCursor(0, 1);
lcd.print("Price:RM ");
lcd.setCursor(9, 1);
Value = Input * Item;
lcd.print(Value);
delay(500);
}
void DecreaseItem(){ // Item Check less Item IncreaseItem
lcd.clear();
lcd.setCursor(0, 0);
lcd.print("Quantity: ");
Item = Item - 1;
i--;
if (Item < minValue)
{
Item = 0; // set value to minimum allowed value
}
lcd.print(Item);
lcd.setCursor(0, 1);
lcd.print("Price:RM ");
lcd.setCursor(9, 1);
Value = Input * Item;
if (Value < minValue)
{
Value = 0; // set value to minimum allowed value
}
lcd.print(Value);
delay(500);
}
And this is at the Appscript for google sheet
function doGet(e) {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1jdP620MyhF-75FRpavHCx8FnN2aLqV7d8_hmsufm_kM/edit#gid=0");
var sheet = ss.getSheetByName("Cart");
addData(e, sheet);
}
function addData(e, sheet) {
var uid = e.parameter.uid;
var item_name = e.parameter.item_name;
var item_price = e.parameter.item_price;
var item_quantity = e.parameter.item_quantity;
var quantity = item_quantity * item_price;
var timestamp = new Date();
var actionType = e.parameter.action_type ?? "add";
var uidColumn = sheet.getRange("A1:A100" + sheet.getLastRow());
var tf = uidColumn.createTextFinder(uid).findAll();
try {
if (actionType == "add" && item_quantity > 0) {
if (tf.length > 0) {
var row = tf[0].getRow();
console.log("Updating sheet");
sheet.getRange(row, 4).setValue(item_quantity);
sheet.getRange(row, 5).setValue(quantity);
sheet.getRange(row, 6).setValue(timestamp);
return;
} else {
console.log("Writing to sheet");
console.log(uid, item_name, item_price, item_quantity, timestamp);
sheet.appendRow([
uid,
item_name,
item_price,
item_quantity,
quantity,
timestamp,
]);
}
} else if(actionType == "add" && item_quantity == 0) {
if (tf.length > 0) {
var row = tf[0].getRow();
console.log("Deleting row %s", row);
sheet.deleteRow(row);
// shift rows up
sheet.getRange(row, 1, sheet.getLastRow() - row + 1, 6).moveTo(
sheet.getRange(row, 1)
);
} else {
console.log("No row to delete");
}
} else {
if (tf.length > 0) {
var row = tf[0].getRow();
console.log("Deleting row %s", row);
sheet.deleteRow(row);
// shift rows up
sheet.getRange(row, 1, sheet.getLastRow() - row + 1, 6).moveTo(
sheet.getRange(row, 1)
);
} else {
console.log("No row to delete");
}
}
} catch (e) {
console.log("Failed with error %s", e.message);
}
}