Fetch postgres data and export entire table to gsheet always clearing all data

Queries and Resources
, ,
1

Goal: I am trying to run a postgres query to extract data and ultimately publish it to a gsheet where I just clear and replace all of the data in the gsheet tab every time it runs. My columns are persistent, my rows (including primary key = id) are not persistent, they can dynamically change.

Here's what I have done:

  1. set up an app
  2. created a postgres query: "IceVaultCommerceProductsSoldToday" with my sql.
  3. set up a table in my app that references my query called: icevault_commerce_table1
  4. set up a 2nd query, this time it's using google sheets (which I've authorized)
  5. I selected "bulk update a spreadsheet by primary key"
  6. I selected my spreadsheet and entered my sheet name
  7. I added my primary key as my first column "id"
  8. For my "array of rows to update", I simply did: {{icevault_commerce_table1.data}}

What's working:

When the number of rows of data is the same and the actual ids in the id column are persistent, then it correctly updates the values in the rest of the columns.

What's not working:

when new rows of data come in (new ids), it doesn't add them to the gsheet.

Question:

Is there a better way to do this? It feels like a very simple and basic thing (which is about all I'm capable of), but maybe I am missing something super obvious. My columns are persistent but my rows are dynamic, including id.

Thanks!

2

Hi @nicole3341, a 'Bulk update a spreadsheet by primary key' query does not work for inserting new records. That's why the extra rows are not added to the GSheet.

As a workaround,

  1. Compare the number of rows between the output of IceVaultCommerceProductsSoldToday with the output of a query that 'reads data from a spreadsheet.'
  2. Slice IceVaultCommerceProductsSoldToday.data in two. One slice with the same amount of rows as we have in the spreadsheet, the other slice with the extra rows.
  3. Make the bulk update with the first slice.
  4. Create a new resource query to 'Append data to a spreadsheet' and pass the second slice to 'Values to append.'

This should update exsiting records and add the new ones.