Google sheets UPSERT of array of results from previous step

I have a workflow that is triggered off of a timer. After triggering, the next step is to query a database in Notion. This query can return 0 results or multiple results. The next step I want to perform is to UPSERT each of the results into a Google Sheet.

My question is…can I use the Upsert Row action for Google Sheets and accomplish the UPSERT for each individual result from the Notion query step by writing custom FOR statements in the “Insert - Cells/Column Values” and “Key Value” fields? Or, do I need to use the custom Google Sheets API request action and write the FOR statement into the code for that step?

The first screenshot is showing the structure of the output from the Notion query step. The second screenshot is showing my attempt at writing a FOR statement in the Google Sheets UPSERT action. I’m receiving an error that “record is undefined”.

Any guidance would be greatly appreciated!

Tully


Hi @tully

Great question - supporting looping and iteration in workflows is a high priority for us.

I have written a short tutorial on how to accomplish this with a custom Node.js step (code included in guide) and separating your workflow into a “iterating” workflow and a “processing” workflow:

After that, you’ll be able to leverage pre-built actions like appending to a Google Sheet.

Please feel free to react or leave a comment on that post about your situation, tracking these issues centrally helps us build the feature to your needs!

Hi @pierce,

Thanks for the quick reply.

I feel this may be a silly question, but how do you “populate the Records prop with the array of records you need processed”? Am I supposed to replace “this.records” in the code with something along the lines of “steps.notion.$return_value.results” or do I add the array of records I want to process to the Records prop in some other fashion?

Tully

Hi @pierce ,

I found a video (Passing props to code steps - YouTube) talking about defining custom properties in Node steps, but when I “Refresh Fields” I get an error.

Loom video link of my experience: Loom | Free Screen & Video Recording Software

Just trying to figure out what I might be doing incorrectly to cause the error.

Tully

Thanks for the video @tully , very helpful.

I apologize, I’m the one that made an error here. I forgot that array is not a valid prop type.

I wish we supported object[] which would accept an array of objects - which is what you’re trying to pass in here.

But regardless, the any prop type accepts any data type. That will have to do for now.

Here’s the updated code snippet to try:

import { axios } from '@pipedream/platform';
// To use previous step data, pass the `steps` object to the run() function
export default defineComponent({
  props: {
    records: {
      type: "any",
      label: "Records to loop",
      description: "The array of records to send to processing workflow",
    },
    workflow_url: {
      type: "string",
      label: "Processing workflow URL",
      description: "The HTTP endpoint to connect to that's processing single individual records from this workflow"
    }
  },
  async run({ steps, $ }) {
    for await(const record of this.records) {
      await axios($, {
        url: this.workflow_url,
        method: 'POST',
        data: record
      })
    }
  },
})


I updated the Github comment as well. Let me know if that does the trick for you!

Hey @pierce ,

Just wanted to give you an update that I was able to get things running using your solution. I’ll definitely be putting this to use for other workflows that I have! Sure do appreciate the quick help and effective solution!

Have a great weekend.

Tully

1 Like