Transferring Google sheet Data To Notion Database

How To
1

I am trying to transfer data from many sales that we have done with different brands on a Google Sheet to a database in Notion. I would like to run this code every 3-4 hours as the Notion database needs to be up-to-date regularly. The flow must add, update, or delete sales with a start date falling in the range of today to 15 days from now. So every time it is run, the flow must check the google sheet for all the sales within that time range and decide whether to add, update, or delete:

  1. Add: Add if the unique sale code of the selected sale (within the time range) is not already in the Notion database
  2. Update: Update if the unique sale code of the selected sale already exists in the database
  3. Delete: If a sale was added onto Notion prior, but the the sale on the Google Sheet was postponed for some reason to a date outside the range specified, then the flow must be able to identify this sale on the Notion database and delete it.

Issues I’m encountering:

  1. Sometimes the flow never ends, and it goes on a cycle of deleting and adding and updating
  2. Adds the same sales more than once for some reason.

Would appreciate any help on the ideal flow for this. Thanks!

2

Screenshot 2024-10-04 at 11.41.42
Screenshot 2024-10-04 at 11.41.421818×878 74.2 KB

This is my current flow :slight_smile:

3

In your usecase you might be better of to create two scenarios:

  1. Triggers on new row in Google sheet (use the “Watch new rows” module). And if you are smart, add a column in the sheet with the Notion record ID, which you fill in the last step of this scenario. It will help you in the second scenario.

  2. Triggers on changes in a row (use the “Watch changes in a row” module). Because the row will contain the Notion ID, you do not have to search first, but just process the content and edit that specific Notion record ID.

P.S.1 If you don’t want to install the sheet addon for scenario 2, you have several other options to determine if a row has been changed. My preferred method is… Use the script in this video: https://www.youtube.com/watch?v=eQuFwDZLn0s. Then do a regular check on rows with a timestamp set and end the scenario by emptying that cell.

P.S.2 never change the order of columns or add columns in between other columns as this will break your Google sheets automations.

4

Thanks a lot Arnoud, great suggestions. I just have an issue with the Watch changes in a row module. I placed a Notion module ‘Update Database Item’ right after it and it can only access data from columns A-Z from the Watch changes in a row. I have added the add-on to my google sheet and specified a range as well that should be working. What can I do to solve this?

5

I’m not sure if you might have the option to get the full row data with a setting in the module (maybe under advanced). However if the data is indeed limited, there are two options:

  1. add a “Get row” after the trigger and fetch the whole row data.

  2. use “Search rows” as trigger and implement the script to register a row change which will be the filter for the search. This would be the alternative option I mentioned in my first post.

1 Like
6

i also just remembered the Watch changes also might trigger based on cell changes, not row changes. So depending on how you use the sheet, the script option might even be a better/cheaper fit.

1 Like