Sending values to Pushing box/Google script into a File

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:

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)

  1. (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! :slight_smile:
Thank you in advance!

DATABASE_2.ino (4.39 KB)

CCS811_Datasheet-DS000459 Sensor 2 Air quality.pdf (644 KB)

4_20 Used display.pdf (492 KB)

Database.zip (6.56 KB)

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: Arduino IoT Cloud Google Sheets Integration - Arduino Project Hub

Or IFTTT is an easy setup.

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:
Error

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 :slight_smile: