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:

data = [
  {"name": "John", "city": "NY"},
  {"name": "Anne", "city": "Paris"}
]

rows = [
  ["John", "NY"],
  ["Anne", "Paris"]
]

Update one cell

Sheets = pq.connect("Google Sheets Writeback")

# Update one cell
Sheets.update("cell", 
   spreadsheet_id = "...",
   sheet = "sheet1", 
   row = 15, 
   column = "B", 
   value = "Added from Peliqan" )

Clear cells

Clear an entire sheet:

Sheets = pq.connect("Google Sheets Writeback")

result = Sheets.delete('sheet_values',
   spreadsheet_id = "...",
   sheet = "Sheet1")

Clear a range of cells in a sheet:

Sheets = pq.connect("Google Sheets Writeback")

result = Sheets.delete('sheet_range_values',
   spreadsheet_id = "...",
   sheet = "Sheet1",
   range = "A1:Z20000") #the range can be larger than the existing sheet

☝

Clearing a sheet before writing data is a good practice to make sure no “old” data remains on the sheet, e.g. when the new data has less rows then what was previously written to the same sheet.

Read the values of a sheet

Read the full contents (values) of one sheet:

Sheets = pq.connect('Google Sheets Writeback')
result = Sheets.get('sheet_values', spreadsheet_id = "...", sheet = "Sheet1")
st.json(result)
rows = result['values']

Example result for rows (list of lists):

[
  ["Name", "City" ...],
  ["John", "NY"   ...],
  ["Anne", "DC"   ...]
  ...
]

Example script to convert the rows to a list of dicts (objects), assuming the first row in the Google Sheet is a header row:

headers = rows.pop(0)
rowsObj = []
for row in rows:
    rowObj = {}
    for i, cell in enumerate(row):
        rowObj[headers[i]] = cell
    rowsObj.append(rowObj)

Example result for rowsObj (list of objects):

[
  {"Name": "John", "City": "NY"},
  {"Name": "Anne", "City": "DC"}
]

Write data in batch

When the amount of rows is too high (e.g. 100K rows) to write in one statement, you can append rows in batch:

Sheets = pq.connect("Google Sheets Writeback")

dbconn = pq.dbconnect('dw_123') 
df = dbconn.fetch('db_name', 'schema_name', 'table_name', df=True)

rows = df.values.tolist() 

def batch(rows):
    batch_size = 1000
    for i in range(0, len(rows), batch_size):
        yield rows[i:i + batch_size]

for batch in batch(rows):
    st.json(batch)
    Sheets.add("rows", 
        spreadsheet_id = "...", 
        sheet = "Sheet1",
        values = batch)

Create a new Google Sheet file (spreadsheet)

Sheets = pq.connect("Google Sheets Writeback")

result = Sheets.add("spreadsheet", name = "My new spreadsheet")
new_file_id = result["detail"]["spreadsheetId"]

Get details of a spreadsheet

Use this to e.g. get a list of sheets and find the sheet id of a specific sheet:

Sheets = pq.connect('Google Sheets Writeback')
result = Sheets.get('spreadsheet', spreadsheet_id = "...")
st.json(result)

Tips & Trick

Convert a column number into the Google sheet column name (e.g. column 27 is AA):

def int_to_col_name(n):
    col_str = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        col_str = chr(65 + remainder) + col_str
    return col_str

Build reports in Google Sheets

See the section “Write to Google Sheets” under Reporting:

Write to Google Sheets