Search code examples
pythonraspberry-pigoogle-sheets-apisensorsgspread

Unable to upload raspberry pi sensor data to a Google Spreadsheet

I am working on a raspberry pi project where I want to send sensor datas to google spreadsheet every 30 seconds. But I am getting output 'failed'. Seems like datas are not appending to spreadsheet. I could not find the exact problem. Also,I expect the program to save the data headers like date, time, temperature , humidity etc.

This is the code that I am using. Can anyone please help? :)

import csv
import os
import time
from time import sleep
import datetime
import json
import sys
import gspread
from oauth2client.service_account import ServiceAccountCredentials

GDOCS_OAUTH_JSON = '***.json'
GDOCS_SPREADSHEET_NAME = '***data'
FREQUENCY_SECONDS = 30

def login_open_sheet(oauth_key_file,spreadsheet):
    try:
        scope = 
['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(oauth_key_file,scope)
        gc=gspread.authorize(credentials)
        worksheet=gc.open(spreadsheet).sheet1
        return worksheet

    except Exception as ex:
        print ('Unable to login and get spreadsheet')
        print('Google sheet login failes with error',ex)
        sys.exit(1)

def main():
    worksheet = None
    ipcon = IPConnection() # Create IP connection
    t = BrickletTemperatureV2(UID_tem, ipcon) # Create device object
    h = BrickletHumidityV2(UID_hum, ipcon)
    co2 = BrickletCO2V2(UID_co2, ipcon)
    aq = BrickletAirQuality(UID_aq, ipcon)
    ptc = BrickletIndustrialPTC(UID_iptc, ipcon)
    ipcon.connect(HOST, PORT)


    while True:
        if worksheet is None:
            worksheet=login_open_sheet(GDOCS_OAUTH_JSON,GDOCS_SPREADSHEET_NAME)


        daten= datetime.datetime.now().strftime("%Y/%m/%d")
        timen= datetime.datetime.now().strftime("%H:%M:%S")
        temp = t.get_temperature() / 100.0
        hum = h.get_humidity() / 100.0
        co2con= co2.get_co2_concentration()
        iaq = aq.get_iaq_index()[0]
        ptctemp = ptc.get_temperature() / 100.0

     
        try:
            worksheet.append_row(daten,timen,temp,strhum,co2con,iaq,ptctemp)
                    

        except:
            print('failed')
            worksheet = None
            time.sleep(FREQUENCY_SECONDS)
            continue
        

        time.sleep(FREQUENCY_SECONDS)

       
 
        ipcon.disconnect()

if __name__ == "__main__":
    main()
Solution

  • From your error message of I removed try except and getting this error now. TypeError: append_row() takes from 2 to 6 positional arguments but 8 were given, it seems that the error occurs at worksheet.append_row(daten,timen,temp,strhum,co2con,iaq,ptctemp).

    From your script, I think that there are 2 modification points.

    1. It seems that the arguments of append_row are append_row(values, value_input_option='RAW', insert_data_option=None, table_range=None).

    2. In your script, strhum is not declaread. By this, even when append_row works, an error occurs for this.

    When these points are reflected to your script, it becomes as follows.

    From:

    worksheet.append_row(daten,timen,temp,strhum,co2con,iaq,ptctemp)

    To:

    strhum = "###" # Please declare this variable.

worksheet.append_row([daten, timen, temp, strhum, co2con, iaq, ptctemp], value_input_option="USER_ENTERED")

    Reference: