DataLogging to Google Sheets

[bluhminga]

Hallo Zusammen,

ich habe einen Raspberry Pi Pico W mit Micropython.
Hier möchte ich gerne Daten erfasst, wie z.B. Zeit, Datum, Temperatur, Luftdruck,etc.
Diese möchte ich gerne an eine Google Tabelle (Google Sheets) in einfügen. Um später diese Grafisch dar zu stellen.

Via IFTTT habe ich das bereits hin bekommen. Ich möchte aber gerne direkt mit der Tabelle Arbeiten, ohne Zwischendienst. Wenn das denn geht?
Ich kann in IFTTT lediglich 3 Daten per IFTTT senden, was zu wenig ist.
Oder gibt es andere Dienste als IFTTT die es ermöglichen mehrere Daten an eine Tabelle zu senden?

Ich habe jetzt soooo viele Videos und Anleitungen gesehen und versucht nach zu bauen. Aber meistens wird in Python oder C programmiert. Aber nie in Micropython. Oder es ist so umständlich, umfangreich, dass ich da einfach nicht mehr durch steige.

Könnten Sie mir da behilflich sein?

Danke schon ein mal im Voraus.

Hello everyone,

I have a Raspberry Pi Pico W with Micropython.
I would like to record data here, such as time, date, temperature, air pressure, etc.
I would like to paste this into a Google spreadsheet (Google Sheets). In order to later represent this graphically.

I was able to do that via IFTTT. But I would like to work directly with the table, without an intermediate service. What if that works?
In IFTTT I can only send 3 data via IFTTT, which is not enough.
Or are there services other than IFTTT that allow multiple data to be sent to a table?

I have now seen soooo many videos and instructions and tried to build. But most of the time programming is done in Python or C. But never in Micropython. Or it's so cumbersome and extensive that I just can't get through it anymore.

Could you help me with that?

Thanks in advance.

[ThinkTransit]

You could save the data to a csv file format on the Pico and then periodically upload the file to google drive using urequests + token.

https://gist.github.com/prasathmani/5d78a6126c3e1920ffb6fd591a759edb

[bidrohini]

You can interface an SD card with your Raspberry PI Pico W. Your data will be recorded in a .txt file inside the SD card. You can export your data to excel from the text file.

[2dof]

direct solution :
[esp32-data-logging-to-google-sheets-with-google-scripts] (https://iotdesignpro.com/articles/esp32-data-logging-to-google-sheets-with-google-scripts)
not direct solution:
[2] raspberry PI + pygsheets could be a solution ( Pico W as measurement unit , raspberry PI/laptop as data-logging unit or
[3] [using N8N]https://gitlab.iotiot.in/repo-public/examples/tree/master/demo-examples/send-data-to-google-sheets)

solution [3] is licenced (20 $/month) , [4] raspberry pi (From $35) .

[scruss]

The link to ESP32 Data Logging to Google Sheets with Google Scripts is interesting, even if it doesn't have a working MicroPython component. It is, in theory, a way of logging data to a Google Sheet by accessing it via a URL. So existing MicroPython libraries should be able to handle it.

Note, though, that as written the tutorial doesn't work: Google Scripts may have added stricter type checking since it was created (or maybe it never worked at all). There are some comments which partially address the issue.

[bluhminga]

Danke,
dass ist wirklich interessant.
Da werde ich mal was versuchen.

Danke.

[2dof]

@scruss yes script need to be coreced and code is for arduino, but I googled and find out that there is already interesting project :
https://github.com/artem-smotrakov/esp32-weather-google-sheets

@bluhminga it seems to solving your problem.

Edit:
So I analysed linked project ESP32 Data Logging to Google Sheets with Google Scripts and other and done fast check in python and I:

1. prepared sheet as in tutorial,
2. corrected app script (date as string):

var sheet_id = "<GOOGLE_SCRIPT_ID>"
var sheet_name = "esp_data";

function doGet(e){
var ss = SpreadsheetApp.openById(sheet_id);
var sheet = ss.getSheetByName(sheet_name);
var sensor = Number(e.parameter.sensor);
var date = String(e.parameter.date);
sheet.appendRow([date,sensor]);
}

3. since i used Python i imported import requests (in micro-python it will be urequests lib .
   So in tutorial they manual test script by copy do browser (+ enter) , so idea in pythpn/micropythpn is
   that we can use requests.get() to perform that same action, request will return error 200 but it will update data to the sheet.

sample Pyhon Code:

# simple  Data Logging to Google Sheets with Google Scripts using python 

import requests     
from datetime import datetime

# Google script ID and required credentials
GOOGLE_SCRIPT_ID ="AKfycbyOE47bKpXjpuEUWIPhjaP-5kIKrEgS7VzoW1s1i-sfKFRqahUBn_zIX6_XXXXXXX"

# some data to update
sensor_value = 15 
currentDateAndTime =  str(datetime.now())

urlFinal = "https://script.google.com/macros/s/"+GOOGLE_SCRIPT_ID+"/exec?" +"sensor="+ str(sensor_value)+"&date="+currentDateAndTime;

# update data ->  responce will be eroor  <[200]> but it will update sheet 
response = requests.get(urlFinal)