4

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?

lameei
  • 184
  • 2
  • 11

4 Answers4

3

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

Omobolaji
  • 71
  • 5
2

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])
Nithin
  • 5,470
  • 37
  • 44
  • 2
    This works but adds the row to the last row not the last non empty row. So as there is 1000 rows by default the data is added to the 1001 row. – lameei Oct 18 '17 at 04:28
0

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
Minesh Barot
  • 87
  • 1
  • 10
0
# 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....")
Vijay Suman
  • 1
  • 2