CODEWITHSUNDEEP
pythongoogle-sheets-apipygsheets
lameei
lameei

Reputation: 184

How to update google sheet with new outcome every time python script runs?

I have a python script with each time running ,it returns a new value. Then I need to add those values to a google sheet. So every day the outcome of the scripts will be added to a new row. I tried to use “pygsheet” lib but I can not handle the addition to new row for each day. How can I add the data to the last empty row?

Upvotes: 4

Views: 8374

Answers (4)

Vijay Suman
Vijay Suman

Reputation: 1

# Connect with Google Sheet
# pip install pygsheets 
# append data on google sheet with existing data Final working
import pygsheets
import pandas as pd

#Change Path : excel file path
df=pd.read_excel(r"C:\Users\Ankur\Desktop\Google Sheet\CET Not Open.xlsx")
#Change Path : Your Key File Path Json file for Google Sheet API
path=r'C:\Users\Ankur\Desktop\Google Sheet\our-brand-301715-306ddc60d5ce.json'
gc = pygsheets.authorize(service_account_file=path)
# Change Sheet Name : PythonDB to your googlesheet name
sh=gc.open("PythonDB")
wk1=sh[0]
cells = wk1.get_all_values(include_tailing_empty_rows=None, include_tailing_empty=False, returnas='matrix')
last_row = len(cells)
if last_row>1:
    wk1.set_dataframe(df,(last_row+1,1))
    wk1.delete_rows(last_row+1)
else:
    wk1.set_dataframe(df,(1,1))
print("Data inserted successfully....")

Upvotes: 0

Minesh Barot
Minesh Barot

Reputation: 97

The following code snippet will add data to the last non-empty row.

#open the google spreadsheet 
sh = gc.open('name of the google sheet')

#select the first sheet 
wks = sh[0]

#retrieve all rows of the worksheet
cells = wks.get_all_values(include_tailing_empty_rows=False, include_tailing_empty=False, returnas='matrix')

#extract all non empty rows
nonEmptyRows = []
for i in cells:
    if i != []:
        nonEmptyRows.append(i)

#calculating the length 
countOfnonEmptyRows = len(nonEmptyRows)
type(countOfnonEmptyRows)

#defining the data
ls = ['G', 'H', 'I']


#inserting data 
wks.insert_rows(countOfnonEmptyRows, values=ls, inherit=True) # here wks is the worksheet object

Upvotes: 0

Omobolaji
Omobolaji

Reputation: 81

First, get all values in the spreadsheet. You need to specify include_tailing_empty_rows and include_tailing_empty as False, so that empty rows will not be included in the count

new_row = ['firstname', 'lastname', 'Email', 'Number']
worksheet = gc.open('Sign Up').sheet1
cells = worksheet.get_all_values(include_tailing_empty_rows=False, include_tailing_empty=False, returnas='matrix')
last_row = len(cells)
worksheet = worksheet.insert_rows(last_row, number=1, values= new_row)

insert_rows adds the "new_row" list to the position immediately after "last_row".

You can check out other attributes of worksheet here https://pygsheets.readthedocs.io/en/stable/worksheet.html

Upvotes: 3

Nithin
Nithin

Reputation: 5840

There are multiple ways to achieve this using pygsheets. insert_rows can achieve this in a single line.

wks.insert_rows(wks.rows, values=[daily_value], inherit=True) # here wks is the worksheet object

If you dont want to add a new row or want to add value in a non-last row you can use append_table, assuming this is the only data in the sheet. Here you have to make sure your sheet have enough rows to fit new value.

wks.append_table(values=[daily_value])

Upvotes: 3

Related Questions