Here are example low-code Python scripts in Peliqan to work with Google Sheets.
Write a range of values, starting on an existing row & column. Keep in mind that this will overwrite existing cell values, it will not insert new rows.
Sheets = pq.connect("Google Sheets Writeback")
data = [
["abc", "def"],
[123, 456]
]
result = google_sheets.update("rows",
spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDA2s",
sheet = "Sheet1",
row = 2, # start row
column = "A", # start column
values = data)
st.json(result)
Append rows at the end of sheet (this will append after the last row that is not empty):
Sheets = pq.connect("Google Sheets Writeback")
data = [
["abc", "def"],
[123, 456]
]
result = google_sheets.add("rows",
spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDA2s",
sheet = "Sheet1",
values = data)
st.json(result)
The above function “Update rows” will overwrite existing cell values. You can insert empty rows first. The following example will insert 6 empty rows, starting at row 4:
Sheets = pq.connect("Google Sheets Writeback")
result = Sheets.add('empty_rows',
spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDA2s",
sheet_id = 0,
start_row = 4,
end_row = 10)
# Note: sheet_id of the first default sheet (Sheet1) is always 0.
# If you want to write into another sheet, get the sheet_id first.
# Example to get the sheet_id of the second sheet:
spreadsheet_details = Sheets.get('spreadsheet', spreadsheet_id = spreadsheet_id)
sheet_id = spreadsheet_details["sheets"][1]["properties"]["sheetId"]
See below for a comprehensive example that inserts empty rows first and then writes cell values.
New rows will be automatically added if needed, existing rows will be overwritten.
The start row (row = 4 in the below example) must exist !
# connect to your Peliqan data warehouse
dw = pq.dbconnect('dw_123')
# fetch a table as data frame (DF)
# replace missing values with an empty string
df = dw.fetch("dw_123", "invoices", df=True, fillna_with='')
# convert DF to a list, needed for Google Sheets
rows = df.values.tolist()
Sheets = pq.connect("Google Sheets Writeback")
Sheets.update("rows",
spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDA2s",
sheet = "Sheet1",
row = 4,
column = "A",
values = rows)
dw = pq.dbconnect('dw_123')
df = dw.fetch("dw_123", "invoices", df=True)
rows = df.values.tolist()
column_names = df.columns.tolist()
rows_with_header = [column_names] + rows
Sheets = pq.connect("Google Sheets Writeback")
Sheets.update("rows",
spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDA2s",
sheet = "Sheet1",
row = 4,
column = "A",
values = rows_with_header)
dw = pq.dbconnect('dw_123')
data = dw.fetch("dw_123", "invoices", df=False)
rows = [list(d.values()) for d in data]
Sheets = pq.connect("Google Sheets Writeback")
Sheets.update("rows",
spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDA2s",
sheet = "Sheet1",
row = 4,
column = "A",
values = rows)
In the above code snippet, data
is a list of objects, and the rows
are a list of lists. Example: