How to Upload Data to Google Sheets using Pi Pico W

December 20, 2023

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:

Youtube

Support:

https://www.buymeacoffee.com/mmshilleh

1-) Create an IFTTT account and setup service

Go 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 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.