1

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()
Lizzie
  • 13
  • 3
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your current issue from `But I am getting output 'failed'. Seems like datas are not appending to spreadsheet.`. Can you provide the detailed error message? – Tanaike May 23 '22 at 23:44
  • I am not getting any error messages. When I run the code, I get the output "failed". Kindly check the last para of the code. I am getting that as output. >>> except: print('failed') – Lizzie May 24 '22 at 07:19
  • Thank you for replying. About `I am not getting any error messages. When I run the code, I get the output "failed". Kindly check the last para of the code. I am getting that as output. >>> except: print('failed')`, I think that in your script, try-except is used. By this, such information is returned. In this case, when you remove try-except and run the script, what error will occur? – Tanaike May 24 '22 at 07:33
  • Thank you for replying @Tanaike. I removed try except and getting this error now. TypeError: append_row() takes from 2 to 6 positional arguments but 8 were given – Lizzie May 24 '22 at 07:59
  • Traceback (most recent call last): File "/home/pi/testgooglespreadsheet.py", line 87, in main() File "/home/pi/testgooglespreadsheet.py", line 70, in main worksheet.append_row(daten,timen,temp,hum,co2con,iaq,ptctemp) TypeError: append_row() takes from 2 to 6 positional arguments but 8 were given – Lizzie May 24 '22 at 08:03
  • Thank you for replying. From your reply, I proposed a modification point as an answer. Could you please confirm it? If that was not useful for your situation, I apologize. – Tanaike May 24 '22 at 08:21

1 Answers1

0

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165