Google Sheets are a great destination for your data reports, because it allows you to add charts, work with formulas, edit the layout etc. Here are example low-code Python scripts in Peliqan to work with Google Sheets.

Basic examples to write data to Google Sheets

Here is a basic example to append rows at the end of a sheet:

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)

For more examples on how to read from a Google Sheet and write to a Google Sheet, see:

Google Sheets - Getting started in Peliqan

Best practises

Write data to read-only “data sheets”

A good practice is to write your data to separate sheets (the “data sheets”) in Google Sheets that you label as read-only. This allows you to clear the entire sheet before writing a new update of the data, making sure that no data from a previous run is still present in the Google Sheets. In the actual reporting sheets, you can use the VLOOKUP formula to look up the required values.

VLOOKUP with lookup on multiple columns

A common issue with VLOOKUP is that it can only look up values using one lookup column and not a combination of columns. Therefore you might have to add a “lookup” column in the data sheet which concatenates multiple cells.

Here’s a basic example, the report uses a VLOOKUP which concatenates the different lookup values (product & city):

This is the VLOOKUP formula that is used in the above report:

=vlookup(C$2&$A4,'data sheet'!$A$2:$D$5,4,false)

And here is the data sheet, where a lookup column (in grey) has been added, which also concatenates the different dimension columns: