Go Down

Topic: Sending values to Pushing box/Google script into a File (Read 168 times) previous topic - next topic

Majortirk

Hey guys,

I am a hobby electrician and I wrote a program which measure the air quality in the first place.
The values are printed by a display.
Furthermore, the values are sent to the pushing box which activate a written google script. The script writes the values in this online excel file:

https://docs.google.com/spreadsheets/d/1ygH84L-8VqKUPFNwdUbinl7Y312P6bTVc2HW3CzrJjs/edit?usp=sharing

Used hardware:
MKR Wifi 1010
Adafruit CCS811 Air quality sensor
EA W204-NLED Display

Now i have got 2 (probably minior) problems.

1. (Important problem)
It writes "0" to the file (instead of the actual values)


2. (Less important problem)
How can i display each value in one row? (It just write everything consecutively and \n does not work)


As you might see in the file i already tried to convert the values into Int, Float and String. Nothing seems to work. (It is possible i made an error in setting them)
Maybe i can read the values as string and convert them into an int somehow?


Please note:
The google script, the spreatsheet and the pushing box are working (as you can see in the file, when i sent a link by hand)
The values are shown on the display as well.
Wifi connection seems to work since it does sent the link for the pushing box.



Attached is the program code as well the data sheets


If i did not upload anything correctly, then i am really sorry.

I would be very grateful for any help! :)
Thank you in advance!

jld13

I think you would need to post your Google script and PushingBox setup as well to get the answer.  Were you using a tutorial like https://www.hackster.io/detox/transmit-esp8266-data-to-google-sheets-8fc617 ??

There is also a good write up using IoT cloud and bypassing PushingBox here: https://create.arduino.cc/projecthub/Arduino_Genuino/arduino-iot-cloud-google-sheets-integration-71b6bc?ref=user&ref_id=65561&offset=0

Or IFTTT is an easy setup.
 

Majortirk

I was using this tutorial: https://www.hackster.io/detox/send-mkr1000-data-to-google-sheets-1175ca

First i set up the google script (only changing some values) and i used a scenario from the pushing box.

Google script:

/*   Using spreadsheet API
GET request query:
https://script.google.com/macros/s/AKfycbxu4XRFGLSk8ac4m8icyE1RIdf9tjNGnhpnPdb5U1BNefaKPAsd/exec
----------------------------------------------------------------------
GScript, PushingBox and Arduino/ESP8266 Variables in order:
temp
eCO2
TVOC
----------------------------------------------------
*/
function doGet(e) {
 Logger.log( JSON.stringify(e) );  // view parameters

 var result = 'Ok'; // assume success

 if(e.parameter==undefined){
   result = 'No Parameters';
 }
 else {
   var id = '1ygH84L-8VqKUPFNwdUbinl7Y312P6bTVc2HW3CzrJjs';//docs.google.com/spreadsheetURL/d
   var sheet = SpreadsheetApp.openById(id).getActiveSheet();
   var newRow = sheet.getLastRow() + 1;
   var rowData = [];
   //var waktu = new Date();
   rowData[0] = new Date(); // Timestamp in column A
   
   for (var param in e.parameter) {
     Logger.log('In for loop, param='+param);
     var value = stripQuotes(e.parameter[param]);
     //Logger.log(param + ':' + e.parameter[param]);
     switch (param) {
       case 'temp': //Parameter
         rowData[1] = value; //Value in column B
         break;
       case 'eCO2':
         rowData[2] = value;
         break;
       case 'TVOC':
         rowData[3] = value;
         break;
       default:
         result = "unsupported parameter";
     }
   }
   Logger.log(JSON.stringify(rowData));

   // Write new row below
   var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
   newRange.setValues([rowData]);
 }
 // Return result of operation
 return ContentService.createTextOutput(result);
}
/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
 return value.replace(/^["']|['"]$/g, "");
}


Tomorrow i try to cast the values as int. That might fix it, we will see :)

Go Up