I’m having troubles extracting data from an API get request into a Google sheets format.
My goal is to build an automated report from data in our OMS system. I’m able to pull the data using an HTTPS get_request, but I can’t figure out how to convert it to an array and export it to Google Sheets.
You can see I’m able to access very specific bits of data - in the screenshot my return value is correct, but I need to return this piece of data for every result in the API data set, then populate that into a row. I also will need to reference several other components of data in the same way to build the full report.
Are any of the pre-built Google Sheets actions applicable for your problem? I think the Google Sheets - Add single row action sounds like it might fit your use case.
That action will guide you on the props needed to export the data to a specific Google Sheet:
Do you know if there is any way to loop through all results using some sort of wildcard? The current workflow I used only returned one result - {{steps.get_request.$return_value[0].public_reference}} - which is exactly what I told it to do.
In between each trigger at 11:00 am MT there could be any number of results, so my tactic was just going to be to return all results from this year (the report is for 2022) and overwrite everything with the old and new data each time the report is run.
However, I realized that Pipedream didn’t overwrite the data each time the trigger is run - it just added the data again as a new row.
You can see what I currently have setup in the screenshots below - let me know if you have any ideas on how to best handle this in Pipedream.
Looping over records in the same workflow allowing you to use using pre-built actions on each individual record is a highly requested feature and one that we’re working on a solution for:
If you’d like real time updates to when this happens, please comment or subscribe to that issue so you’ll get a notification.
But in the meantime, I recommend creating a second workflow that’s responsible for accepting 1 row at a time via an HTTP request.
Then you can loop over the records in your first workflow, and send them one at a time to this second workflow.
This allows you to use pre-built actions with an array of records.
@pierce I think I’m following - would it be possible to send an example or documentation that goes over how I can create an HTTP request that only returns one row at a time, and how I can link workflows in Pipedream?
