append_table() to add list data or from CSV file to Google sheets using pygsheets

Adding data from list, csv and excel file to Google sheet by append_table() in pygsheets

Data will be added to next row (or column ) of the table. ( Here row id or row number of the locations is not required )

append_table(values, start='A1', end=None, dimension='ROWS',
	overwrite=False)

Options	Details
values	Data List to be added
start	Staring Point of top left cell ( 'A5')
end	Bottom right cell
dimension	The way the value will be added, (Default : ROWS) ,ROWS or COLUMNS( See the example below ).
overwrite	If True the data will be overwritten

Connect to googel drive and open the file (more on connection & authorization)

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc=pygsheets.authorize(service_account_file=path)
sh=gc.open('my_gsheets1')
wk1=sh[0]

Adding one row of data

data1=['Jack',80,90]
wk1.append_table(data1,start='A3')

Using List

l1=[['Jack4',80,90],['Ron4',70,90],['Geek4',50,40]]
wk1.append_table(values=l1)

Using a list with loop

l1=[['Jack4',80,90],['Ron4',70,90],['Geek4',50,40]]
for data in l1:
    wk1.append_table(data,start='A2', end=None, 
            dimension='ROWS', overwrite=False)

By using loop we can add conditions and change the data based on required logic.
append_table() with a list of data

Using CSV file

Download the sample CSV file from here. We kept the file in our local system and updated the path below.

path="D:\\my_data\\student.csv" # sample CSV file, use your path
fob=open(path,)
#headings = next(fob) # removing header row
for rec in fob:
  student=rec.split(',')
  print(student)
  wk1.append_table(start='A2',values=student)

In above code we have not used Pandas DataFrame. We can create the DataFrame by using Pandas read_csv() and then use the set_dataframe() to add data to google sheet.

import pandas as pd
path="D:\\my_data\\student.csv" # sample CSV file, use your path
df=pd.read_csv(path)
wk1.set_dataframe(df,(3,1))# Place DataFrame from row 3 column 1

Using Excel file

Download the same student.xlsx file from here. We used read_excel() to create the DataFrame.

ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl.

pip install openpyxl

import pandas as pd
path="D:\\my_data\\student.xlsx" # sample Excel file, use your path
df=pd.read_excel(path)
wk1.set_dataframe(df,(3,1)) # Place DataFrame from row 3 column 1

Using insert_row()

We should use insert_row() when we row number is available. At a perticular location the data can be added. This is the main difference between insert_row() and append_table()
Pygsheets and google API authorization get_as_df()

Pandas DataFrame Python- Tutorials

Subscribe to our YouTube Channel here

append_table() : Adding list or CSV data to Google sheets

Using List

Using a list with loop

Using CSV file

Using Excel file

Using insert_row()

Subscribe