Connecting Particle to Google Doc


#1

Hi,
I’m trying to connect the photon particle to Google Doc to transmit data from a sensor. I got the code for the Particle and Google doc Script Editor from Garriaga (http://www.instructables.com/id/Datalogging-with-Spark-Core-Google-Drive/?ALLSTEPS). My issue is that when I publish the data, its good, but when I transmit it to Google doc it writes “undefined” data. When I put the URL from the function into the internet:
“var response = UrlFetchApp.fetch('https://api.spark.io/v1/devices/DEVICE_ID/result?access_token=ACCESS_TOKEN);” . It says:

 {
  "ok": false,
  "error": "Variable not found"
}

So, I’m presuming its something in my code, but not sure what. What variable does “URLFetchApp” look for?
Below is my code:

int sensor1 = A0;
int sensorValue = 0;
char resultstr[64];


void setup() {
    pinMode(sensor1, INPUT);
    Particle.variable("sensorValue", &sensorValue);

}

void loop() {
    sensorValue = analogRead(sensor1);
    sprintf(resultstr, "%d", sensorValue);
    delay(1000);
    Particle.publish("Sensor",resultstr, 60);
}

Thanks!


#2

That does not match:

Therefore it can’t find it :wink:


#3

So, if I replace sensorValue by result, it should work? Do I need to change anything else, as i didn’t declare “result” as a variable before? How do you know this, it is from the Google doc code?


#4

Doesn’t really matter what you call it, as long as they match up.
As to how I know this… Reading the documentation never hurts… :wink:


#5

So resultstr is supposed to be the same as sensorvalue, in terms of type or variable? As isn’t sprintf type cast an int to a string…


#6

May I suggest you try reading some of these:
https://docs.particle.io/guide/getting-started/examples/photon/#read-your-photoresistor-function-and-variable



#7

I’d rather use

  Particle.variable("sensorValue", sensorValue);
  // or the oldfashioned way
  Particle.variable("sensorValue", &sensorValue, INT);

but don’t mix and match.

Nope, sprintf() copies a string, constructed according to rules provided via the format string, into a character array/C string.

Type casting on the other hand is interpreting a variable of one type as if it was of another type, but it’s still the same memory location as the original variable.


#8

I read through them and did some modifications to my code but I still have the same issue. Particle.variable is still “undefined”. Any specific advice to solving the bug?

int sensor1 = A1;
int sensorValue = 0;
char sensorValuestr[64];


void setup() {
    pinMode(sensor1, INPUT);
    Particle.variable("sensorValue", sensorValuestr);

}

void loop() {
    sensorValue = analogRead(sensor1);
    sprintf(sensorValuestr, "%d", sensorValue);
    delay(1000);
    Particle.publish("Sensor", sensorValuestr, 60);
}

#9

I fixed my code as you suggested, but I still have a bug. Particle.variable is “undefined”. Any more suggestions?

int sensor1 = A1;
int sensorValue = 0;
char sensorValuestr[64];


void setup() {
    pinMode(sensor1, INPUT);
    Particle.variable("sensorValue", sensorValuestr);

}

void loop() {
    sensorValue = analogRead(sensor1);
    sprintf(sensorValuestr, "%d", sensorValue);
    delay(1000);
    Particle.publish("Sensor", sensorValuestr, 60);
}

#10

What is the URL you’re trying to call?


#11

My Google doc calls:
var JSONResponse = UrlFetchApp.fetch(“https://api.spark.io/v1/devices/” + device_ID + “/” + variable + “?access_token=” + access_token);

with a result of:
"Request failed for https://api.spark.io/v1/devices/3e0020000247353138383138/undefined?"


#12

Considering you’ve apparently changed quite a few things, we need to see all the code. The URL in that error message has an ‘undefined’ in there, indication that the ‘variable’ wasn’t defined.

Without trying to haggle Google into this just yet, see and try if you can request a variable through the browser. What URL do you require for that?


#13

YESS!! It works thank you! Changed “variable” to the name of my variable and it worked :smiley: However, now I get another Error: 408, “Timed out”. After 30 seconds the Photon restarts itself stopping the data flow. I’ve reduce variable names to 9 characters and less, and still same thing happens. How do I fix this? What should I do?

Also, this is my Google Script:

var device_ID = '3e0020000247353138383138';
var access_token = '3ade0e61444cd9d8572143b5fff92158e7b096ed';
var variables = [''];
var checkLatestData = true;
var refreshTime = 1000;

//  Select the active spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();

//	Function to create a header
function showHeader()	{
	//	Clear the first row
	sheet.deleteRow(1);

	//	At first add the timestamp
	sheet.getRange(1,1).setValue('Timestamp');

	//	Loop through all the variables and add the names of them
	for (var i = 0; i < variables.length; i++) {
		sheet.getRange(1,2 + i).setValue(variables[i]);
	};

	if(checkLatestData) {
		sheet.getRange(1, 3 + variables.length).setValue('Timestamp');

		//	Loop through all the variables and add the names of them
		for (var i = 0; i < variables.length; i++) {
			sheet.getRange(1,4 + variables.length).setValue(variables[i]);
		};
	}

	collectData();
}

//	Function to show the latest data
function showLatestData(timestamp, results)	{
//	At first add the timestamp
sheet.getRange(2,3 + variables.length).setValue(timestamp);

//	Loop through all the variables and add the names of them
	for (var i = 0; i < results.length; i++) {
		sheet.getRange(2,4 + variables.length).setValue(results[i]);
	};
}

//  Function to get and parse the JSON variable from the spark API
function getResponse(variable){
	var JSONResponse = UrlFetchApp.fetch("https://api.spark.io/v1/devices/" + device_ID + "/" + "sensorV" + "?access_token=" + access_token);
	
	var response = JSON.parse(JSONResponse.getContentText());
	return response.result;		
}

function collectData()  {
	//	Declare a result array
	var timestamp = new Date();
	var results = [timestamp];
	var resultsWithKey = [];

	//	Loop through the array with variables and get their content
	for (var i = 0; i < variables.length; i++) {

		//	Push the results in an array
		results.push(getResponse(variables[i]));
		resultsWithKey[variables[i]] = getResponse(variables[i]);
	};

	if(checkLatestData) showLatestData(timestamp, results)
	
	//  Add the responses to the spreadsheet
	sheet.appendRow(results);

	Utilities.sleep(refreshTime)
	collectData();
}

And this is my new code (only difference is shorter names):

int sensor1 = A1;
int sensorV = 0;
char sensorStr[12];


void setup() {
    pinMode(sensor1, INPUT);
    Particle.variable("sensorV", sensorStr);

}

void loop() {
    sensorV = analogRead(sensor1);
    sprintf(sensorStr, "%d", sensorV);
    delay(1000);
    Particle.publish("Sensor", sensorStr, 60);
}

Best.


#14

Are you sure this is the code that runs on your Photon.
In the code above contains a stray d. That would not build.


#15

Now it is! Don’t know how the ‘d’ got there. It builds properly and same issue of Error: 408, “Timed-out”


#16

I can’t see anything that would cause that issue in your Photon code.
Does this also happen when you don’t run your google script?

But just for the sake of fighting superstition you could alter this

char sensorStr[16];  // give it some more space
// and
void loop() {
    static uint32_t ms;

    sensorV = analogRead(sensor1);
    sprintf(sensorStr, "%d", sensorV);
    if (millis() - ms > 1000)
    {
        ms = millis();
        Particle.publish("Sensor", sensorStr, PRIVATE);
    }
}

I’ve just seen your edit.
I was actually addressing this issue first

Has this gone already or is it still there?


#17

The issue seems to be independent of if I run google script or not. I.e. Issue happens when not running google script by checking dashboard.

With using your code and adding 2 lines at the beginning of it, issue continues :
int sensor1 = A1;
int sensorV = 0;

One thing to note, not sure if it’s normal. I’m getting values of 800-1150 when reading an analog port (A1), when un-plugged (reading air), in both cases. Also, I’m powering my Photon by usb.


#18

Yes, that is normal. This is what we call a floating pin. Very, very simplified, what you are reading are minimal charge imbalances in the circtuitry and the surrounding media (e.g. air).

I’ll flash your code to one of my devices and see what it does.


Updata:
@benlandry5, I see what you mean now. Although my Photon never falls into green blinking, it does from time to time a brief handshake with the cloud (quick cyan blinking).
But this seems to go away whan I increase the delay between two publishes from 1000 to 1100.
It would also be advisable to not update the string variable that frequent.
So how does your device behave with this?

void loop() {
    static uint32_t ms;

    if (millis() - ms > 1100)
    {
        ms = millis();
        sensorV = analogRead(sensor1);
        sprintf(sensorStr, "%d", sensorV);
        Particle.publish("Sensor", sensorStr, PRIVATE);
    }
}

This is the issue I opened on GitHub