Best way to sync JSON feed to Google Sheet?

[kpetrowski]

Hi- I have a JSON feed with approx 500 rows of data that I need to keep in sync with a Google Sheet.
I see I can use the 'Schedule by Pabbly' and 'API by Pabbly: GET' to pull the data into Pabbly Connect.

What's the best way to ensure the Sheet is in sync?
Option 1: Should I try to have Pabbly search for updates to existing rows, new rows, remove rows no longer in the JSON feed? That seems task intensive.

Or Option 2: am I better off just replacing all rows in the Sheet every time the workflow runs? That way it's always "in sync"? If I go this route, what Google Sheet Action will accomplish this?

Thanks,

Kenny

 

[ArshilAhmad]

Hey @kpetrowski,

For your use case, I would suggest setting up your workflow as follows:

1. Schedule using Pabbly to execute the workflow.
2. Use API by Pabbly to capture data.
3. Use Google Sheets: Delete Row(s) function to remove old data.
4. Use Google Sheets: Add Multiple Row(s) function to add new data.

 

[kpetrowski]

Thanks, @ArshilAhmad. If I had 500 items in that JSON feed would running this workflow cost 1000 tasks (500 for the delete rows action, 500 for the add multiple rows action)?

 

[ArshilAhmad]

If you retrieve all 500 items from the API at once, only 3 of your tasks will be used per workflow execution.

Task 1. Getting all 500 items using API by Pabbly
Task 2. Deleting rows of your spreadsheet using Google Sheets: Delete Row(s)
Task 3. Adding all 500 items to your spreadsheet using Google Sheets: Add Multiple Rows

Skip to 7:03

 

[kpetrowski]

Hi @ArshilAhmad - I'm trying to implement as suggested above but when I get to the Google Sheets: Add Multiple Rows action I'm struggling to understand how to map all fields of the API results to Google Sheets. That action wants me to manually select each record attribute from the API action, which obviously is not helpful because I simply want to replace all rows in the Sheet with data from the API call.

I think I'm missing something easy and obvious. Perhaps I need to use a Data Transformer or Iterator action to separate the results from the API action?

Appreciate your help here.

 

[kpetrowski]

Update: I figured this out.

I removed the 'Simple Response' setting on the API action and added a Data Transformer > Line Itemizer action and then mapped that into the Google Sheets: Multiple Rows action.

Seems to be working great!