Google Sheets undoubtedly is one the most popular spreadsheet software in the industry today. Almost everyone who use Google have used Google Sheets at some point. In Microsoft Excel, there are VBA and Office for JavaScript API, but honestly, VBA is lacked the functionality of working with outside software, and Office for JavaScript… well it is JavaScript that’s all I can say.
Google Sheets API however, in my opinion, is one of the most versatile and powerful (stabled to since it is already on build v4) APIs when it comes to Google Suites Applications (Google Doc, Slides, GMAIL, etc). In this tutorial, I will show you a very powerful tip – how to import a Pandas DataFrame into a Google Spreadsheet using Google Sheets API.
Google.py
DF_To_GSheet.py
Hello Jie Jenn,
may you help me? The code works fine for me as long as I’m not in the corporate network. As soon as I’m in the corporate network, I have to get through the proxy. Unfortunately I don’t know how. Do you have any idea how I could do that? I tried the rebuild_proxies function, but I have no clue how to get it work.
Would be great if you could help me out on this topic. pleease =)
best regards
benni
Sorry about the late reply. Regarding of your issue, unfortunately, it is probably something to do with the firewall which you will have to reach out to your IT support for help.
Can you help me this file name Google.py have error this line :
print(client_secret_file, api_name, api_version, scopes, sep=’-‘)
I find many website to fix them.
Did you know this ploblem ?
What’s the exact error message you are getting?
i am new to python world. How to replace/update google sheet every day with latest data in data frame? I need whole data in google sheet to be replaced with new data in data frame? Your help is much appreciated.
Maybe clear the sheet before you paste a new values. You can clear the contents using sheets.values.clear method. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear
Wow worked like a charm.. Thanks
One more question
I want to put this file as .py or .bat file in Windows task scheduler. So that it gets refresh on a schedule time daily.
So far I was able to successfully put few files in the scheduler and they are working fine. However when I try to execute this file from scheduler (either manually running it or scheduling it at sometime) the task scheduler keeps on running without executing the code. My best guess is since this file is calling credentials.json it is having this issue.
Any help on this?
No sweat I realized there was some problem with my .bat file I was able to resolve it.
Thanks
Hi Nice one!!
Almost exactly what I needed. One small question:
My reuireent is that I want to mirror my google sheet with daily generated csv file. The problem is sometimes there are 600 rows some time 500 rows but always 4 rows. Is there a way to first delete the existing record and then migrate the data from csv to g sheet.
Hi jiejenn!
First off, big fan! You have taught me a ton about python and its abilities throughout your entire library. I do have a question however. Do you have any advice on how to run this from cmd? I duplicated the DF_To_GSheet script three times in one project seeing that I have three sheets that I need to update and it works flawlessly from pycharm but if I try it from cmd it looks like this:
C:\Users\matthewjames1>python C:\Users\matthewjames1\PycharmProjects\taskbuilderv.3\naopu.py && python C:\Users\matthewjames1\PycharmProjects\taskbuilderv.3\naoau.py && python C:\Users\matthewjames1\PycharmProjects\taskbuilderv.3\naocu.py
Traceback (most recent call last):
File “C:\Users\matthewjames1\PycharmProjects\taskbuilderv.3\naopu.py”, line 1, in
from Google import Create_Service
File “C:\Users\matthewjames1\PycharmProjects\taskbuilderv.3\Google.py”, line 10
print(client_secret_file, api_name, api_version, scopes, sep=’-‘)
^
SyntaxError: invalid syntax
Any help would be appreciated. Thanks!
Hi, nice video I am gonna subscribe!
Unfortunately getting error here in Google.py file:
the following code
flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE,SCOPES)
produce this error:
FileNotFoundError: [Errno 2] No such file or directory: ‘client_secret.json’
Can you please help? Thanks
client_secret.json file is not supplied. Did you download your client file and rename it to client_secret.json?
Hi jiejenn, thank you so much for this tutorial. It works perfectly for me. Juste one question: how can I export the dataframe without the headers. I tried many things like changing the response_data part of the code searching for headers arguments. I tried to replace the headers by the first row etc.
Do you have any idea how to modify your code to export the dataframe without the headers ? (my goal is to update my google sheet adding new data)
Can you send me your script to YouTube@LearnDataAnalysis.org for me to take a look at it.
Your email is invalid. Anyway, df.values.T.tolist()
Hii can you help me with the error?
while running this script
from Google import Create_Service
import pandas as pd
import numpy as np
CLIENT_SECRET_FILE = ‘client_secret.json’
API_SERVICE_NAME = ‘sheets’
API_VERSION = ‘v4’
SCOPES = [‘https://www.googleapis.com/auth/spreadsheets’]
gsheetId = ‘1aRMp_3-M5OP-m3GRHH7vYOWnBHe9MW-A3Gb6HWB5sbU’
service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
I am getting this error
cannot import name ‘Create_Service’ from ‘google’ (unknown location)
Hello Jie Jenn,
I am not able to see the google.py file. Is it just me? could you paste the code in the comment section?
Great videos btw. Amazing tutorial.