Pushing Data to Google Sheets

Hi all, I was wondering if anyone could help me figure out why I can’t get data from my photon to publish to a google sheet that I created.

This is my code for the google sheets script editor:

function collectCurrent() {
  "use strict";
  try
  {
    //Replace the device ID below with your Photon's unique device ID
    var deviceID = "xxx";
    
    //Replace the access token below with your Photon's unique access token
    var accessToken = "xxx";
    
    //Replace the room number below with location of the Photon
    var room = "Florasonics";
    
    var sheet = SpreadsheetApp.getActiveSheet();
    
    // Temperature
    var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/" + deviceID + "/temp?access_token=" + accessToken);
    var jsonResponse = JSON.parse(response.getContentText());
    var temp = jsonResponse.result;

    // Humidity - Retrieves value and parses from JSON text.
    response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/" + deviceID + "/humidity?access_token=" + accessToken);
    jsonResponse = JSON.parse(response.getContentText());
    var humidity = jsonResponse.result;
    
    // Pressure
    response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/" + deviceID + "/pressure?access_token=" + accessToken);
    jsonResponse = JSON.parse(response.getContentText());
    var pressure = jsonResponse.result; 
    
    // Baro_Temp
    response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/" + deviceID + "/barometric_temp?access_token=" + accessToken);
    jsonResponse = JSON.parse(response.getContentText());
    var barometric_temp = jsonResponse.result; 
    
    // Soil Moisture
    response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/" + deviceID + "/soil_value?access_token=" + accessToken);
    jsonResponse = JSON.parse(response.getContentText());
    var soil_value = jsonResponse.result;
    

    // Create a timestamp.
    var timeStamp = new Date();

    // Append the timestamp and the temperature to the sheet.
    sheet.appendRow([timeStamp, room, temp, humidity, pressure, barometric_temp, soil_value,]);
  } catch (e)
  {    
    
  }
}

And then this is my code on my photon:

float tempf = 0;
char temp_string[9];
float humidity = 0;
char hum_string[9];
float pascals = 0;
char pasc_string[9];
float baroTemp = 0;
char baro_string[6];
float soilMoisture = 0;

int val = -100; //value for storing moisture value 
char val_string[7];
int soilPin = A1;//Declare a variable for the soil moisture sensor 
int soilPower = 5;//Variable for Soil moisture Power

long lastPrint = 0;

//Create Instance of HTU21D or SI7021 temp and humidity sensor and MPL3115A2 barometric sensor
Weather sensor;

//---------------------------------------------------------------
void setup()
{
    Serial.begin(9600);   // open serial over USB at 9600 baud

    
    pinMode(soilPower, OUTPUT);//Set D7 as an OUTPUT
    
    digitalWrite(soilPower, LOW);//Set to LOW so no power is flowing through the sensor
    
    //Initialize the I2C sensors and ping them
    sensor.begin();

  
    /*You can only receive accurate barometric readings or accurate altitude
    readings at a given time, not both at the same time. The following two lines
    tell the sensor what mode to use. You could easily write a function that
    takes a reading in one made and then switches to the other mode to grab that
    reading, resulting in data that contains both accurate altitude and barometric
    readings. For this example, we will only be using the barometer mode. Be sure
    to only uncomment one line at a time. */
    sensor.setModeBarometer();//Set to Barometer Mode
    //baro.setModeAltimeter();//Set to altimeter Mode

    //These are additional MPL3115A2 functions that MUST be called for the sensor to work.
    sensor.setOversampleRate(7); // Set Oversample rate
    //Call with a rate from 0 to 7. See page 33 for table of ratios.
    //Sets the over sample rate. Datasheet calls for 128 but you can set it
    //from 1 to 128 samples. The higher the oversample rate the greater
    //the time between data samples.


    sensor.enableEventFlags(); //Necessary register calls to enble temp, baro and alt
    
    pinMode(7, OUTPUT);

}
//---------------------------------------------------------------
void loop()
{
    //Get readings from all sensors
    getWeather();
    readSoil();

      
    // This math looks at the current time vs the last time a publish happened
     if(millis() - lastPrint > 15000) //Publishes every 15000 milliseconds, or 15 seconds
      {
        // Use the printInfo() function to print data out to Serial
        printInfo();
        
        sprintf(temp_string, "%f", tempf);
        sprintf(hum_string, "%f", humidity);
        sprintf(pasc_string, "%f", pascals);
        sprintf(baro_string, "%f", baroTemp);
        sprintf(val_string, "%d", val);
        
        // -- Pulish everything -- //
        Particle.publish("temp", temp_string);
        Particle.publish("humidity", hum_string);
        Particle.publish("pressure", pasc_string);
        Particle.publish("barometric_temp", baro_string);
        Particle.publish("soil_value", val_string);
        
        blinky();

        // Record when you published
        lastPrint = millis();

      }
      
    //blinky(); delay(1000);
}


//This is a function used to get the soil moisture content
void readSoil()
{

    digitalWrite(soilPower, HIGH);//turn D7 "On"
    delay(10);//wait 10 milliseconds 
    val = analogRead(soilPin);//Read the SIG value form sensor 
    digitalWrite(soilPower, LOW);//turn D7 "Off"
    //return val;//send current moisture value
}

void blinky() {
    digitalWrite(7,HIGH);
    delay(500);
    digitalWrite(7,LOW);
    delay(50);
}


//---------------------------------------------------------------
void getWeather()
{
  // Measure Relative Humidity from the HTU21D or Si7021
  humidity = sensor.getRH();

  // Measure Temperature from the HTU21D or Si7021
  tempf = sensor.getTempF();
  // Temperature is measured every time RH is requested.
  // It is faster, therefore, to read it from previous RH
  // measurement with getTemp() instead with readTemp()

  //Measure the Barometer temperature in F from the MPL3115A2
  baroTemp = sensor.readBaroTempF();

  //Measure Pressure from the MPL3115A2
  pascals = sensor.readPressure();

  //If in altitude mode, you can get a reading in feet with this line:
  //float altf = sensor.readAltitudeFt();
  
 
}
//---------------------------------------------------------------
void printInfo()
{
//This function prints the weather data out to the default Serial Port

  Serial.print("Temp:");
  Serial.print(tempf);
  Serial.print("F, ");

  Serial.print("Humidity:");
  Serial.print(humidity);
  Serial.print("%, ");

  Serial.print("Baro_Temp:");
  Serial.print(baroTemp);
  Serial.print("F, ");

  Serial.print("Pressure:");
  Serial.print(pascals/100);
  Serial.print("hPa, ");
  Serial.print((pascals/100) * 0.0295300);
  Serial.println("in.Hg");
  

  Serial.print("Soil Moisture = ");    
  //get soil moisture value from the function below and print it
  Serial.println(val);
  
  blinky();
}

If anyone has any ideas why this isn’t working/has any advice/sees any blatant errors I’d be very appreciative.

Thanks.

First, you are violating the rate limit of 1/second with five Particle.publish() calls back to back.

Next, if you want to request the data as you are doing in your Google Sheets script, you need to register a Particle.variable() with that name not use Particle.publish().

Finally, there are some tutorials on this forum and on Hackster that show how you’d push data via a single Particle.publish() and webhooks.

2 Likes