Google Sheets and Microsoft Excel are no doubt the two most and widely used spreadsheet software today. In this tutorial I will be showing you how to use Google Sheets API and win32com module to integrate these two applications in Python.
We will be using win32com to control Excel spreadsheet and Google Sheets API to read data from Excel.
Install Google Client Python Library
https://youtu.be/euIHL4q4L0Q
Create a new project
https://youtu.be/wJ6WC0G8w4o
Create a client secret file
https://youtu.be/HzICUriU3k0
Create a Google Sheets Service instance
https://youtu.be/xE0kuiAPxtA
Export Excel To Google Sheets.py
Google.py
hi thanks for the video, i got this error, can you help on how to fix this please.
sheets service created successfully
Traceback (most recent call last):
File “C:\Python38\Excel_To_GSheet.py”, line 17, in
response = service.spreadsheets().values().append(
File “C:\Python38\lib\site-packages\googleapiclient\discovery.py”, line 1078, in method
headers, params, query, body = model.request(
File “C:\Python38\lib\site-packages\googleapiclient\model.py”, line 162, in request
body_value = self.serialize(body_value)
File “C:\Python38\lib\site-packages\googleapiclient\model.py”, line 275, in serialize
return json.dumps(body_value)
File “C:\Python38\lib\json\__init__.py”, line 231, in dumps
return _default_encoder.encode(obj)
File “C:\Python38\lib\json\encoder.py”, line 199, in encode
chunks = self.iterencode(o, _one_shot=True)
File “C:\Python38\lib\json\encoder.py”, line 257, in iterencode
return _iterencode(o, 0)
File “C:\Python38\lib\json\encoder.py”, line 179, in default
raise TypeError(f’Object of type {o.__class__.__name__} ‘
TypeError: Object of type datetime is not JSON serializable
I charge a $5.00 USD for debugging, let me know if you still need my help.
yes thats ok. There is also another error below, can you also help me with that please.
[‘https://www.googleapis.com/auth/spreadsheets’]
sheets service created successfully
Traceback (most recent call last):
File “C:\Python38\Excel_To_GSheet.py”, line 19, in
response = service.spreadsheets().values().append(
File “C:\Python38\lib\site-packages\googleapiclient\_helpers.py”, line 134, in positional_wrapper
return wrapped(*args, **kwargs)
File “C:\Python38\lib\site-packages\googleapiclient\http.py”, line 935, in execute
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: https://sheets.googleapis.com/v4/spreadsheets/1dZk3v8vtGS6hoSBKQ2qjyOxTy0iltPzsB7FaVMdS0HQ/values/WorksheetName%21A1:append?valueInputOption=RAW&alt=json returned “Invalid value at ‘data.values’ (type.googleapis.com/google.protobuf.ListValue), “[
Send me your script to YouTube@LearnDataAnalysis.org and a $5.00 USD fee via PayPal to https://www.paypal.com/paypalme/jiejenn/5