Here are example low-code Python scripts in Peliqan to work with Google Sheets.

Write data: update rows

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)

Write data: add rows

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)

Insert empty rows

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.

Write data from a table

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)

Write data from a table, with a header row

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)

Write data from a table (no data frame)

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: