Google script and Arudino coding

Hello, i have an inquiry. Do the google script coding need to reflect with Arduino coding if i modified the Arduino coding to my current needed but still using the same google script that i get from the github, is it the reason why the google spreadsheet cannot capture the data that i want from the Arduino?

Thanks in advance.

What is “the google script”? Most helpers have no psycic capacity.
Post the code here, regarding the rules given in the first helpung topic.

hi @Railroader sorry for my mistake. below is the google script that i used.

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

  var result = 'Ok';

  if (e.parameter == undefined) {
    result = 'No Parameters';
  }
  else {
    var id = ''; // Spreadsheet ID
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;
    var rfidData = [];
    rfidData[0] = new Date();
    for (var param in e.parameter) {
      Logger.log('In for loop, param='+param);
      var value = stripQuotes(e.parameter[param]);
      switch (param) {
        case 'machine_location': //Parameter
          rfidData[1] = value; //Value in column B
          break;
        case 'Machine_ID':
          rfidData[2] = value;
          break;
        default:
          result = "Wrong parameter";
      }
    }
    Logger.log(JSON.stringify(rfidData));

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

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

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

and here is my arduino coding @Railroader

basically the arduino coding is working. just the data cannot transfer to the google sheet. i’m using pushing box too.

/* Arduino Code which sends data to google spreadsheet */

#include<SPI.h>
#include <Ethernet.h>
#include <SoftwareSerial.h>
SoftwareSerial mySerial (5,6);
#define Sending_To_Spreadsheet;
unsigned char incomingByte;

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char server[] = "api.pushingbox.com/pushingbox?devid=9";   //YOUR SERVER
IPAddress ip(10, 30, 20,95);
EthernetClient client; 
int j=incomingByte;
void sendIdentifyCmd ()
{
  mySerial.write (0x7c);    
  mySerial.write (0xff);
  mySerial.write (0xff);  
  mySerial.write (0x01);                  
  mySerial.write (0x08);                  
  mySerial.write (0x7d);              
#ifdef DEBUG
  Serial.print (0x7c);
  Serial.print (0xff);
  Serial.print (0xff);
  Serial.print (0x01);
  Serial.print (0x08);
  Serial.print (0x7d);
  Serial.println ();
#endif
}    
void setup(){
 Serial.begin(9600);
 mySerial.begin(9600);
    SPI.begin();
    Serial.println ("begin initial Serial!\n");
    
if   (Ethernet.begin(mac) == 0) {
      Serial.println("Failed to configure Ethernet using DHCP");
      Ethernet.begin(mac, ip);
    }
    delay(1000);
    Serial.println("connecting...");
 }
void loop(){

    {
  sendIdentifyCmd ();
  delay (2);
  while(mySerial.available () > 0)
  {
      incomingByte=mySerial.read ();
      Serial.print (incomingByte,HEX);
      Serial.print (' ');
  }
  Serial.println ();
  delay (1000);
}

             
 void Sending_To_spreadsheet() ;  //CONNECTING WITH MYSQL
 {
   if (client.connect(server, 80)) {
    Serial.println("connected");
    // Make a HTTP request:
    client.print("GET /pushingbox?devid==");  
   }//YOUR URL
    if(j==incomingByte)
    {
      client.print('1');
//      Serial.print('1');
    }
    else
    {
      client.print('0');
    }
    
    client.print("&Member_ID=");
    if(incomingByte)
        {          
         client.print(incomingByte);                          
    }
   
   client.print(" ");      //SPACE BEFORE HTTP/1.1
    client.print("HTTP/1.1");
    client.println();
    client.println("Host: api.pushingbox.com");
    client.println("Connection: close");
    client.println();
  
  }
 }

Thanks. Study how to post code. You are posting text, not code.
Use the Arduino Autoformat function and then use code tags before pasting it.

Don’t share your spreadsheet ID…

Does it even compile ?

Where are you calling Sending_To_spreadsheet()()? (And what is the semi colon doing after the function declaration?)

@J-M-L ouh… noted. i removed all the spreadsheet ID already.

Yes, it able to compile. just unable to transfer the data to the google sheet.

Where are you calling `Sending_To_spreadsheet()
do you mean i’m not declaring the function? or how is it suppose to work?
as per my understanding, we need to use pushing box as a medium to transfer the data from arduino to google spreadsheet as google script isnt compatible with it.

correct me if i’m wrong in answering your question or if my understand concept is wrong too.

the code you posted does indeed compile but you have a prototype function definition actually injected in the loop() and as it’s useless then you have it’s body that is executed as part of the loop

what is connected to mySerial?

what do you expect from int j=incomingByte;as a global variable? it will be 0
what do you expect from if(j==incomingByte) {...?
what’s the URL you are trying to build ?

@J-M-L

what is connected to mySerial ?
mySerial is to read our ID for RFID tag. it connected with UHF Rfid Scanner and RS232.

As for the Sending_To_spreadsheet, those coding I’m copy from the github I found during example of the almost similar project of it. the video that i seen, able to capture the data, but they are using MFRC522. but i able to change the coding that run with RS232.

the code as it stands is really weird. the incoming bytes on mySerial are just dumped on the Serial monitor and you exit the the while loop with the variable holding the last byte received…

1 Like

@J-M-L thanks… i just noticed it too after you highlighted it. I will review it back and let you know if its works.

@J-M-L i re-do the coding back. but i think its something wrong to push the data to the pushing box. could you help to check for me?

/* Arduino Code which sends data to google spreadsheet */

#include<SPI.h>
#include <Ethernet.h>
#include <SoftwareSerial.h>
SoftwareSerial mySerial (5, 6);
#define Sending_To_Spreadsheet;
String data;
unsigned char incomingByte;

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
char server[] = "api.pushingbox.com/pushingbox?devid=";   //YOUR SERVER
IPAddress ip(10, 30, 20, 95);
EthernetClient client;

int j = incomingByte;
void sendIdentifyCmd ()
{
  mySerial.write (0x7c);
  mySerial.write (0xff);
  mySerial.write (0xff);
  mySerial.write (0x01);
  mySerial.write (0x08);
  mySerial.write (0x7d);
#ifdef DEBUG
  Serial.print (0x7c);
  Serial.print (0xff);
  Serial.print (0xff);
  Serial.print (0x01);
  Serial.print (0x08);
  Serial.print (0x7d);
  Serial.println ();
#endif
}
void setup() {
  Serial.begin(9600);
  mySerial.begin(9600);
  SPI.begin();
  Serial.println ("begin initial Serial!\n");

  if   (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    Ethernet.begin(mac, ip);
  }
  delay(1000);
  Serial.println("connecting...");
}
void loop() {

  {
    sendIdentifyCmd ();
    delay (2);
    while (mySerial.available () > 0)
    {
      incomingByte = mySerial.read ();
      Serial.print (incomingByte, HEX);
      Serial.print (' ');
    }
    Serial.println ();
    delay (1000);
  }
  void get_Data(); {
    data += "";
    data += "GET /pushingbox?devid=";
    data == "HTTP/1.1";
  }
  void send_Data();
  Serial.println("connected");
  client.println(data);
  client.println("Host:api.pushingbox.com");
  void Sending_To_spreadsheet() ;  //CONNECTING WITH MYSQL
  {
    if (client.connect(server, 80)) {
      Serial.println("connected");
      // Make a HTTP request:
      client.print("GET /pushingbox?devid=");
    }//YOUR URL
    if (j == incomingByte)
    {
      client.print('1');
      //      Serial.print('1');
    }
    else
    {
      client.print('0');
    }

    client.print("&Member_ID=");
    if (incomingByte)
    {
      client.print(incomingByte);
    }
  }

}

As I said the code is total :poop:, not sure where this is coming from…

what’s the point of flushing mySerial? what’s connected there? (second time I ask, please provide information)

  while (mySerial.available () > 0)   {
    incomingByte = mySerial.read ();
    Serial.print (incomingByte, HEX);
    Serial.print (' ');
  }

what’s incomingByte for? what is j? please answer

what are the function prototype declaration in the middle of the loop? please answer.

void get_Data();
void send_Data();
 void Sending_To_spreadsheet() ;  //CONNECTING WITH MYSQL

it looks like it was meant to be a separate function since you have {} just after them

there is an attempt to send stuff the the client before the client.connect…

throw that code away and start from a blank sheet, read about making HTTP requests and the Ethernet library…

point of flushing mySerial

  • the intention of this is to print my data in 1 line instead of print it by row.

what’s incomingByte for?

  • the function of declaring this is to integrate data that capture from RS232 into arduino.

what is j?

  • j function is representative to read the Rfid ID. the general idea of this is, i modified the coding from MFRC522 to suit with RS232.

hope my explanation is understandable to your side.

and i will dig more regarding HTTP requests and the Ethernet library.

can you describe the full hardware setup? what do you use, what’s the purpose?

I’m using Arduino Uno Rev3 + Ethernet Shield, connect with RS232 that connected with the UHF Rfid Reader.

The purpose of my project is to track the asset movement. At the machine there, i will stick the Rfid sticker (passive Rfid; with its own ID printed) and once the machine move and the UHF Rfid Reader read the Rfid sticker, it will capture the asset ID (refer to Rfid sticker).

Hence, the data capture will be transfer to the googlesheet using Pushing box.

This is my general idea on this.

OK - the code is not very well architected to this end.

try to split the work in various functions. The loop ultimately could look like this:

void loop() {
  if (newAssetAcquired(anAssetID)) {
    // I received a new asset tag from Rfid sticker 
    if (initiateConnexionOK()) {
      writeToGoogleSheet(anAssetID);
    } else {
      // deal with connexion error
    } 
  } 
}

and you then write the various functions

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.