How To Upload Data To Google Sheets Using Pi Pico W
Made by mahmood-m-shilleh
About the project
Learn how to use the free service IFTTT couple with the Pico W to upload data to Google Sheets!
Project info
Difficulty: Easy
Platforms: IFTTT
Estimated time: 1 hour
License: GNU Lesser General Public License version 3 or later (LGPL3+)
Items used in this project
Story
Discover how to effortlessly transmit data from your Raspberry Pi Pico W to Google Sheets as CSV files, streamlining the process of logging extensive sensor data. Given the Pico W's constrained storage capacity, offloading data externally becomes crucial for efficient storage management.
In this tutorial, we'll leverage an intermediary service known as IFTTT. This free automation service facilitates the establishment of a straightforward HTTP endpoint, seamlessly connecting your Raspberry Pi Pico W to Google. Notably, the setup incurs no initial costs, requiring only an active internet connection to get started.
Before reading the remainder, be sure to subscribe and support the channel if you have not!
Subscribe:
Support:
https://www.buymeacoffee.com/mmshilleh
1-) Create an IFTTT account and setup serviceGo to ifttt.com and create a free account
Create an app on the dashboard
Select If This and search and select webhook:
Select receive a web request, this will create the endpoint that the Pico W will access over the internet.
Name the event however you like, I name it BME280 in this example because I am using the ShillehTek BME280 Pressure, Temperature, and Humidity sensor to send three columns of data. Really you can use any sensor or any data you want!
You can buy the sensor at shillehtek.com if interested.
Now you have setup the first step, next select Then That and search google sheets. We want to add a row to a spread sheet.
Substitute the information for setup accordingly:
Important, you should setup the formatted row depending on how many columns of data you are pushing at a time.
Now that it is all setup, you can do a quick test by going to ifttt.com/my_services
Select Webhooks
Substitute your values in the JSON body and click Test It, if it is successful you should see a new spreadsheet in your Google Drive!
This page also contains your IFTTT secret key, which you can see in the image above is a long string at the end of the URL. Save this somewhere safe because you will need it in the MicroPython Code in the next step!
Step 2-) MicroPython Raspberry Pi Pico W
import urequests
import time
import machine
import network
from machine import Pin, I2C
import bme280
import config
SSID = config.SSID
WIFI_PASSWORD = config.WIFI_PASSWORD
EVENT_NAME = 'BME280_YT'
IFTTT_KEY = 'czLk7ytVFFpRQZuAeEOeiXi7h18XaGANp3oi1oy_MNm'
IFTTT_URL = f'/trigger/{EVENT_NAME}/with/key/{IFTTT_KEY}'
server = 'maker.ifttt.com'
i2c = I2C(0, sda=Pin(20), scl=Pin(21), freq=400000)
bme = bme280.BME280(i2c=i2c)
def connect_wifi():
try:
sta_if = network.WLAN(network.STA_IF)
sta_if.active(True)
sta_if.connect(SSID, WIFI_PASSWORD)
for i in range(0, 10):
if not sta_if.isconnected():
time.sleep(1)
print("Connected to Wi-Fi")
except Exception as e:
print('There was an issue connecting to WIFI')
print(e)
def make_ifttt_request():
json_data = '{"value1":"' + bme.values[0] + '","value2":"' + bme.values[1] +
'","value3":"' + bme.values[2] + '"}'
headers = {'Content-Type': 'application/json'}
response = urequests.post('https://' + server + IFTTT_URL, data=json_data, headers=headers)
print('Response:', response.content.decode())
response.close()
print('Closing Connection')
connect_wifi()
counter = 0
while True:
counter += 1
time.sleep(1)
print(f'Uploading Value: {str(counter)}')
make_ifttt_request()Here is the code you will need to run on your device to start uploading values. You can see I use the BME280 library because I attached a BME280 to my device, really you can tailor this to fit your needs.
Just make sure you substitute the EVENT_NAME, IFTTT_KEY, and the SSID and WIFI_PASSWORD. I added those to a config file but really you can just hardcode them as a string in the code. If everything is setup you should start seeing continuous values populate your Google Sheet every second or so, you can change the time.sleep() interval appropriately.
ConclusionHope you learned something new, if you did please consider subbing to the channel at https://www.youtube.com/@mmshilleh
Even better consider donating at https://www.buymeacoffee.com/app/dashboard
You can also buy ShillehTek sensors other than the BME280 at our Amazon store!
https://www.amazon.com/stores/ShillehTek/page/F0566360-4583-41FF-8528-6C4A15190CD6?ref_=ast_bln
We offer a wide array of pre-soldered sensors!
Credits
mahmood-m-shilleh
Mechanical and Software Engineering Background. University at Buffalo 2019 Texas A&M 2021 I make data pipelines for my day job. Outside of work, I participate in online communities regarding Full Stack Engineering, Microelectronics, and more. You can find more details about me on my Youtube Channel. https://www.youtube.com/@mmshilleh Feel free to reach out!
Leave your feedback...