Write to Google Sheets

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

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:

This is the formula that is used to build up the above “Lookup value” column: =B5&C5

Insert rows directly into the layout of a report

This example assumes you have a Google Sheet template file in which you want to insert new rows of data at a certain row number. You will have to provide enough space (insert empty rows) before writing the cell values.

☝

Alternatively, a best practice is to write your data to separate sheets that are labeled as read-only in Google Sheets. In your reporting sheets, you can use the VLOOKUP formula to look up the values in the read-only sheets. See more details above

Here’s an example Google Sheet template (before data is inserted). We want to add our data at row 5, and insert new rows to avoid overwriting the “Totals” row 8:

Note that this sheet already has some formulas set:

Example once the data has been inserted:

Here’s the low-code Python script in Peliqan to accomplish the above:

def main():

  Sheets = pq.connect("Google Sheets Writeback")
  
  spreadsheet_id = "1dKg7m8AIq0-mtkKcGmbpZv2UC3i9RqZVzZ7fs4pDH2s"
  spreadsheet = Sheets.get("spreadsheet", spreadsheet_id = spreadsheet_id)
  sheet_id = sheet_id(spreadsheet, "My Sheet")

  dbconn = pq.dbconnect('dw_123') 
  df = dbconn.fetch('dw_123', 'accounting', 'invoices', df=True)

  invoices = df.values.tolist()
  Sheets.update("rows", spreadsheet_id = spreadsheet_id, sheet = "My sheet", row = 4, column = "A", values = invoices)

  # Insert empty rows first, to avoid overwriting rows with e.g. "totals"
  Sheets.add("empty_rows", spreadsheet_id = spreadsheet_id, sheet_id = sheet_id, start_row = 4, end_row = len(invoices)+4)
  
	# Write the cell values into the new empty rows:
	Sheets.update("rows", spreadsheet_id = spreadsheet_id, sheet = "My sheet", row = 4, column = "A", values = invoices)



#### Below are helper functions, sheet_id is needed to insert empty rows

def sheet_id(spreadsheet, sheet_name):
  sheet = find_sheet(spreadsheet, sheet_name)
  return sheet["properties"]["sheetId"]

def find_sheet(spreadsheet, sheet_name):
  for sheet in spreadsheet["sheets"]:
    if sheet["properties"]["title"].lower().strip() == sheet_name.lower().strip():
      return sheet

def sheet_rowcount(spreadsheet, sheet_name):
  sheet = find_sheet(spreadsheet, sheet_name)
  return sheet["properties"]["gridProperties"]["rowCount"]

main()

Make a copy of a Google Sheet template file

A great way to build Google Sheet reports is to manually make a template file with the perfect layout, formulas for sums, charts etc.

Next, you make a copy of this template file for each individual report, you rename the copy and then you write the correct data into it.

☝

Keep in mind that you cannot copy a file in Peliqan, if the file was not created from Peliqan. Therefore, create the template file first from Peliqan. Then manually add the layout etc. Note that writing data into existing spreadsheets (not created by Peliqan) is always possible, it’s only copying a file that’s limited.

First create a new Google Sheets file. This will become your template file:

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

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

Now make a copy of the template file and rename it:

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

result = Sheets.copy("spreadsheet", spreadsheet_id = "...")
copy_file_id = result["detail"]["id"]

Sheets.rename("spreadsheet", spreadsheet_id = copy_file_id, name="My report")