Some time ago, for work, I need to add a row (or more) at the END of a google sheet in an automatic way.
Searching on-line I did’t find any post or StackOverflow about adding row at the end of the sheet so here we are.
Before you start
This tutorial expect you to have follow the Google prerequisit’s post so please follow it.
In our case we need:
- a API Key with writing power
- the email for the project (create with the app)
- a gsheet with data
You need to add the email of the project as editor for the gsheet
The code
Fist we need to import all the library and have a setup of the constant of the script. In this case
import datetime
import logging
from typing import List
import pygsheets
SCOPES = [
"https://www.googleapis.com/auth/spreadsheets",
]
SERVICE_ACCOUNT_FILE = "account.json"So we need a function insert my data in the gsheet so I declered a function and define the gsheet as sheet
def add_to_sheet(spreadsheet_id: int, worksheet_title:str, data=list[dict]):
client = pygsheets.authorize(service_file=SERVICE_ACCOUNT_FILE)
sheet = client.open_by_key(spreadsheet_id)Now I need to set a pointer (in this case i) at the first row empty (k.a.k. the one after the last wrote row)
wks = sheet.worksheet_by_title(worksheet_title)
all_values = wks.get_all_values()
i = 0
for e in all_values:
if "".join(e) != "":
i += 1After all this preparation you can cycle through your data for the writing phase and we have
for element in data:
wks.insert_rows(
i,
values=[
element['col1'],
element['col2'],
element['col3'],
element['col4'],
],
inherit=True,
)
done.append(element.id)
i += 1If you did all right now you have a function for write at the end of your gsheet data.You can use as is or you can combine it with other in to bigger project.
Conclusions
For easy access this is the full code, write me if you have problems
import datetime
import logging
from typing import List
import pygsheets
SCOPES = [
"https://www.googleapis.com/auth/spreadsheets",
]
SERVICE_ACCOUNT_FILE = "account.json"
def add_to_sheet(spreadsheet_id: int, worksheet_title:str, data=list[dict]):
client = pygsheets.authorize(service_file=SERVICE_ACCOUNT_FILE)
sheet = client.open_by_key(spreadsheet_id)
wks = sheet.worksheet_by_title(worksheet_title)
all_values = wks.get_all_values()
i = 0
for e in all_values:
if "".join(e) != "":
i += 1
for element in data:
wks.insert_rows(
i,
values=[
element['col1'],
element['col2'],
element['col3'],
element['col4'],
],
inherit=True,
)
done.append(element.id)
i += 1