title: How to send data from a serial device to Google Sheets for data logging
body: <p>I am trying to send data from my Esmart MPPT Solar Charge Controller to my raspberry pi which will then be sent to Google Sheets for data logging purposes. I have successfully managed to get the required data from my Solar Charge Controller by using the following library and code that I have found on GitHub.</p>
  <p><a href="https://github.com/skagmo/esmart_mppt" rel="nofollow noreferrer">https://github.com/skagmo/esmart_mppt</a></p>
  <pre><code>#!/usr/bin/python3
  # -*- coding: utf-8 -*-
  
  import esmart
  import time
  
  def handle_data(d):
      
      pv_volt = d['pv_volt']
      bat_volt = d['bat_volt']
      chg_mode = d['chg_mode']
      chg_cur = d['chg_cur']
      load_volt = d['load_volt']
      load_cur = d['load_cur']
      load_power = d['load_power']
      chg_power = d['chg_power']
      bat_temp = d['bat_temp']
      int_temp = d['int_temp']
      soc = d['soc']
      co2_gram = d['co2_gram']
      
      # Correct error in readings
      d['bat_volt'] *= 1.107
      d['load_volt'] *= 1.09
      # Only correct if reading is not zero
      if d['chg_cur']:
          d['chg_cur'] += 1.5
  
      # chg_power uses uncorrected voltage/current, so recalculate
      actual_power = d['bat_volt']*d['chg_cur']
  
      print(&quot;PV Voltage: %.1f V&quot; % (d['pv_volt']))
      print(&quot;Battery: %.1f V&quot; % (d['bat_volt']))
      print(&quot;Charging (status: %s): %.1f A, %.1f W&quot; % (esmart.DEVICE_MODE[d['chg_mode']], d['chg_cur'], 
      actual_power))
      print(&quot;Discharging: %.1f V, %.1f A, %.1f W&quot; % (d['load_volt'], d['load_cur'], d['load_power']))
      print(&quot;Load Voltage: %.1f V&quot; %(d['load_volt']))
      print(&quot;Load Current: %.1f A&quot; %(d['load_cur']))
      print(&quot;Charge Power: %.1f W&quot; %(d['chg_power']))
      print(&quot;Load Power: %.1f W&quot; %(d['load_power']))
      print(&quot;Battery Temperature: %.1f °C&quot; %(d['bat_temp']))
      print(&quot;Internal Temperature: %.1f °C&quot; %(d['int_temp']))
      print(&quot;State of Charge: %.1f %%&quot; %(d['soc']))
      print(&quot;CO2 Reduction: %.1f g&quot; %(d['co2_gram']))
  
  e = esmart.esmart()
  e.open(&quot;/dev/ttyUSB0&quot;)
  e.set_callback(handle_data)
  
  while 1:
      e.tick()
      time.sleep(0.001)
  
  
  </code></pre>
  <p>The output of the code:</p>
  <pre><code>PV Voltage: 0.1 V
  Battery: 13.6 V
  Charging (status: IDLE): 0.0 A, 0.0 W
  Discharging: 0.0 V, 0.0 A, 0.0 W
  Load Voltage: 0.0 V
  Load Current: 0.0 A
  Charge Power: 0.0 W
  Load Power: 0.0 W
  Battery Temperature: 0.0 °C
  Internal Temperature: 32.0 °C
  State of Charge: 68.0 %
  CO2 Reduction: 0.0 g
  
  </code></pre>
  <p>This is the code that I use to send data from my sensors to Google Sheets:</p>
  <pre><code>#import libraries
  from __future__ import print_function  
  from googleapiclient.discovery import build  
  from httplib2 import Http  
  from oauth2client import file, client, tools  
  from oauth2client.service_account import ServiceAccountCredentials  
  import datetime
  import RPi.GPIO as GPIO,time
  import Adafruit_DHT
  
  
  &quot;&quot;&quot;acquire spreadsheet ID at the URL of the spreadsheet
  which are enclosed in *//* e.g https://docs.google.com
  /spreadsheets/d/1KqWlsVCJgGjLFb7YKfrOnqHpl3n3BWGblOXsV22IGOQ/
  edit#gid=0&quot;&quot;&quot;
  
  #declaring spreadsheet ID
  MY_SPREADSHEET_ID = '1KqWlsVCJgGjLFb7YKfrOnqHpl3n3BWGblOXsV22IGOQ'
  
  #defining update_sheet function
  def update_sheet(sheetname, humidity1, temperature1, humidity2, temperature2, humidity3, temperature3):
      
      
      &quot;&quot;&quot;acquire .json file from setting up credentials by following
      this tutorial: https://gspread.readthedocs.io/en/latest/oauth2.html#service-account&quot;&quot;&quot;
      
      #authentication 
      SCOPES = 'https://www.googleapis.com/auth/spreadsheets',
      'https://www.googleapis.com/auth/drive'
      
      &quot;&quot;&quot;for the next line of code, enter the path of the file where the .json file was acquired from the above linked
         tutorial e.g /home/pi/gspread/Hydroponics-0b1b25110de8.json *note: the .json file
         can be placed anywhere as long as the path is declared as shown below*&quot;&quot;&quot;
      
      creds = ServiceAccountCredentials.from_json_keyfile_name('/home/pi/hydroponicsmonitoring/Hydroponics-0b1b25110de8.json',SCOPES)
      
      #authorization
      service = build('sheets','v4', http=creds.authorize(Http()))
      
      #call the sheets API, append the next row of sensor data
      &quot;&quot;&quot;values&quot; is the array of rows that we are updating, it is a single row&quot;&quot;&quot;
      
      values = [[ str(datetime.datetime.now()), 'Humidity at Level 1', humidity1, 'Temperature at Level 1', temperature1,
                  'Humidity at Level 2', humidity2, 'Temperature at Level 2', temperature2,
                  'Humidity at Level 3', humidity3, 'Temperature at Level 3', temperature3]]
      body = {'values':values}
      
      result = service.spreadsheets().values().append(
          spreadsheetId=MY_SPREADSHEET_ID,
          range=sheetname + '!A1:G1',
          valueInputOption='USER_ENTERED',
          insertDataOption='INSERT_ROWS',
          body=body).execute()
     
  def humidtemp1():
      humidity1, temperature1 = Adafruit_DHT.read_retry(Adafruit_DHT.DHT22,4)
      return ((humidity1),(temperature1))
  
  humidity1, temperature1 = humidtemp1()
  print ('Humidity at Level 1: %.2f %%rH' % humidity1)
  print ('Temperature at Level 1: %.2f Celcius' % temperature1)
  
  def humidtemp2():
      humidity2, temperature2 = Adafruit_DHT.read_retry(Adafruit_DHT.DHT22,26)
      return ((humidity2),(temperature2))
  
  humidity2, temperature2 = humidtemp2()
  print ('Humidity at Level 2: %.2f %%rH' % humidity2)
  print ('Temperature at Level 2: %.2f Celcius' % temperature2)
  
  def humidtemp3():
      humidity3, temperature3 = Adafruit_DHT.read_retry(Adafruit_DHT.DHT22,19)
      return ((humidity2),(temperature2))
  
  humidity3, temperature3 = humidtemp3()
  print ('Humidity at Level 3: %.2f %%rH' % humidity3)
  print ('Temperature at Level 3: %.2f Celcius' % temperature3)
  
  update_sheet(&quot;Sheet1&quot;, humidity1, temperature1, humidity2, temperature2, humidity3, temperature3)
  time.sleep(0.5)
  </code></pre>
  <p>However, when i try to integrate the code above with the code I use for Google Sheets data logging I get an error that I don't know how to resolve.</p>
  <pre><code>#import libraries
  #!/usr/bin/python3
  # -*- coding: utf-8 -*-
  from __future__ import print_function  
  from googleapiclient.discovery import build  
  from httplib2 import Http  
  from oauth2client import file, client, tools  
  from oauth2client.service_account import ServiceAccountCredentials  
  import datetime
  import RPi.GPIO as GPIO,time
  import Adafruit_DHT
  import struct, time, serial, socket, requests
  import json
  #library for the MPPT Solar Charge Controller that was found on GitHub
  import esmart
  
  
  &quot;&quot;&quot;acquire spreadsheet ID at the URL of the spreadsheet
  which are enclosed in *//* e.g https://docs.google.com
  /spreadsheets/d/1KqWlsVCJgGjLFb7YKfrOnqHpl3n3BWGblOXsV22IGOQ/
  edit#gid=0&quot;&quot;&quot;
  
  #declaring spreadsheet ID
  MY_SPREADSHEET_ID = '1KqWlsVCJgGjLFb7YKfrOnqHpl3n3BWGblOXsV22IGOQ'
  
  
  
  #defining update_sheet function
  def update_sheet(sheetname, pv_volt):
      
      
      &quot;&quot;&quot;acquire .json file from setting up credentials by following
      this tutorial: https://gspread.readthedocs.io/en/latest/oauth2.html#service-account&quot;&quot;&quot;
      
      #authentication 
      SCOPES = 'https://www.googleapis.com/auth/spreadsheets',
      'https://www.googleapis.com/auth/drive'
      
      &quot;&quot;&quot;for the next line of code, enter the path of the file where the .json file was acquired from the above linked
         tutorial e.g /home/pi/gspread/Hydroponics-0b1b25110de8.json *note: the .json file
         can be placed anywhere as long as the path is declared as shown below*&quot;&quot;&quot;
      
      creds = ServiceAccountCredentials.from_json_keyfile_name('/home/pi/hydroponicsmonitoring/Hydroponics-0b1b25110de8.json',SCOPES)
      
      #authorization
      service = build('sheets','v4', http=creds.authorize(Http()))
      
      #call the sheets API, append the next row of sensor data
      &quot;&quot;&quot;values&quot; is the array of rows that we are updating, it is a single row&quot;&quot;&quot;
      
      values = [[ str(datetime.datetime.now()), 'PV Voltage', pv_volt]]
      
      body = {'values':values}
      
      result = service.spreadsheets().values().append(
          spreadsheetId=MY_SPREADSHEET_ID,
          range=sheetname + '!A1:G1',
          valueInputOption='USER_ENTERED',
          insertDataOption='INSERT_ROWS',
          body=body).execute()
  
    
  STATE_START = 0
  STATE_DATA = 1
  
  REQUEST_MSG0 = b&quot;\xaa\x01\x01\x01\x00\x03\x00\x00\x1e\x32&quot;
  LOAD_OFF = b&quot;\xaa\x01\x01\x02\x04\x04\x01\x00\xfe\x13\x38&quot;
  LOAD_ON = b&quot;\xaa\x01\x01\x02\x04\x04\x01\x00\xfd\x13\x39&quot;
  
  DEVICE_MODE = [&quot;IDLE&quot;, &quot;CC&quot;, &quot;CV&quot;, &quot;FLOAT&quot;, &quot;STARTING&quot;]
  
  
  
  def __init__(self):
      self.state = STATE_START
      self.data = []
      self.callback = False
      self.port = &quot;&quot;
      self.timeout = 0
  
  def __del__(self):
      self.close()
  
  def set_callback(self, function):
      self.callback = function
  
  def open(self, port):
      self.ser = serial.Serial(port,9600,timeout=0.1)
      self.port = port
  
  
  def send(self, pl):
      self.ser.write(self.pack(pl))
  
  def parse(self, data):
      for c in data:
          if (self.state == STATE_START):
              if (c == 0xaa):
                  # Start character detected
                  self.state = STATE_DATA
                  self.data = []
                  self.target_len = 255
                  #else:
                  #print c
                  
          elif (self.state == STATE_DATA):
              self.data.append(c)
              
              # Received enough of the packet to determine length
              if (len(self.data) == 5):
                  self.target_len = 6 + self.data[4]
                  
                  # Received whole packet
                  if (len(self.data) == self.target_len):
                      self.state = STATE_START
                      #print &quot; &quot;.join(&quot;{:02x}&quot;.format(ord(c)) for c in self.data)
                      
                      # Source 3 is MPPT device
                      if (self.data[2] == 3):
                          msg_type = self.data[3]
                          
                          # Type 0 packet contains most data
                          if (self.data[3] == 0):
                                      fields = {}
                                      fields['chg_mode'] = int.from_bytes(self.data[7:9], byteorder='little')
                                      fields['pv_volt'] = int.from_bytes(self.data[9:11], byteorder='little') / 10.0
                                      fields['bat_volt'] = int.from_bytes(self.data[11:13], byteorder='little') / 10.0
                                      fields['chg_cur'] = int.from_bytes(self.data[13:15], byteorder='little') / 10.0
                                      fields['load_volt'] = int.from_bytes(self.data[17:19], byteorder='little') / 10.0
                                      fields['load_cur'] = int.from_bytes(self.data[19:21], byteorder='little') / 10.0
                                      fields['chg_power'] = int.from_bytes(self.data[21:23], byteorder='little')
                                      fields['load_power'] = int.from_bytes(self.data[23:25], byteorder='little')
                                      fields['bat_temp'] = self.data[25]
                                      fields['int_temp'] = self.data[27]
                                      fields['soc'] = self.data[29]
                                      fields['co2_gram'] = int.from_bytes(self.data[33:35], byteorder='little')
                                      
                                      self.callback(fields)
  
  def tick(self):
      try:
          while (self.ser.inWaiting()):
              self.parse(self.ser.read(100))
              
              # Send poll packet to request data every 5 seconds
              
              if (time.time() - self.timeout) &gt; 5:
                  self.ser.write(REQUEST_MSG0)
                  self.timeout = time.time()
                  #time.sleep(0.5)
                  #self.ser.write(LOAD_OFF)
                  
      except IOError:
          print(&quot;Serial port error, fixing&quot;)
          self.ser.close()
          opened = 0
          while not opened:
              try:
                  self.ser = serial.Serial(self.port,38400,timeout=0)
                  time.sleep(0.5)
                  if self.ser.read(100):
                      opened = 1
                  
                  else:
                      self.ser.close()
              
              except serial.serialutil.SerialException:
                  time.sleep(0.5)
                  self.ser.close()
                  print(&quot;Error fixed&quot;)
  
  
  pv_volt = parse
  
  def handle_data(d):
      
      pv_volt = d['pv_volt']
      bat_volt = d['bat_volt']
      chg_mode = d['chg_mode']
      chg_cur = d['chg_cur']
      load_volt = d['load_volt']
      load_cur = d['load_cur']
      load_power = d['load_power']
      chg_power = d['chg_power']
      bat_temp = d['bat_temp']
      int_temp = d['int_temp']
      soc = d['soc']
      co2_gram = d['co2_gram']
      
      # Correct error in readings
      d['bat_volt'] *= 1.107
      d['load_volt'] *= 1.09
      # Only correct if reading is not zero
      if d['chg_cur']:
          d['chg_cur'] += 1.5
  
      # chg_power uses uncorrected voltage/current, so recalculate
      actual_power = d['bat_volt']*d['chg_cur']
      
      print(&quot;PV Voltage: %.1f V&quot; % (d['pv_volt']))
      print(&quot;Battery Voltage: %.1f V&quot; % (d['bat_volt']))
      print(&quot;Charging (status: %s): %.1f A, %.1f W&quot; % (esmart.DEVICE_MODE[d['chg_mode']], d['chg_cur'], actual_power))
      print(&quot;Discharging: %.1f V, %.1f A, %.1f W&quot; % (d['load_volt'], d['load_cur'], d['load_power']))
      print(&quot;Load Voltage: %.1f V&quot; %(d['load_volt']))
      print(&quot;Load Current: %.1f A&quot; %(d['load_cur']))
      print(&quot;Charge Power: %.1f W&quot; %(d['chg_power']))
      print(&quot;Load Power: %.1f W&quot; %(d['load_power']))
      print(&quot;External Temperature: %.1f °C&quot; %(d['bat_temp']))
      print(&quot;Internal Temperature: %.1f °C&quot; %(d['int_temp']))
      print(&quot;State of Charge: %.1f %%&quot; %(d['soc']))
      print(&quot;CO2 Reduction: %.1f g&quot; %(d['co2_gram']))
  
  
  
  
  e = esmart.esmart()
  e.open(&quot;/dev/ttyUSB0&quot;)
  e.set_callback(handle_data)
  
  update_sheet(&quot;Sheet1&quot;, pv_volt)
  time.sleep(0.5)
  
  </code></pre>
  <p>*note: In this code, I am only trying to get the value of the pv_volt to be sent to Google Sheets</p>
  <p>The error that I get from running the code:</p>
  <pre><code>Traceback (most recent call last):
    File &quot;/home/pi/hydroponicsmonitoring/esmarttest2.py&quot;, line 220, in &lt;module&gt;
      update_sheet(&quot;Sheet1&quot;, pv_volt)
    File &quot;/home/pi/hydroponicsmonitoring/esmarttest2.py&quot;, line 60, in update_sheet
      body=body).execute()
    File &quot;/usr/local/lib/python3.7/dist-packages/googleapiclient/discovery.py&quot;, line 1022, in method
      headers, actual_path_params, actual_query_params, body_value
    File &quot;/usr/local/lib/python3.7/dist-packages/googleapiclient/model.py&quot;, line 162, in request
      body_value = self.serialize(body_value)
    File &quot;/usr/local/lib/python3.7/dist-packages/googleapiclient/model.py&quot;, line 275, in serialize
      return json.dumps(body_value)
    File &quot;/usr/lib/python3.7/json/__init__.py&quot;, line 231, in dumps
      return _default_encoder.encode(obj)
    File &quot;/usr/lib/python3.7/json/encoder.py&quot;, line 199, in encode
      chunks = self.iterencode(o, _one_shot=True)
    File &quot;/usr/lib/python3.7/json/encoder.py&quot;, line 257, in iterencode
      return _iterencode(o, 0)
    File &quot;/usr/lib/python3.7/json/encoder.py&quot;, line 179, in default
      raise TypeError(f'Object of type {o.__class__.__name__} '
  TypeError: Object of type function is not JSON serializable
  
  </code></pre>