Adding multiple rows to google sheets

[ankerstrom]

Being a complete novice here, I have managed to set up a webhook where I receive data that I need to add to google sheets.

I am able to manage single row, but the messages I receive contain multiple rows of data.
Please see example of the raw format I receive where multiple rows are included in the body.

I have tried to map some of the first fields from the first row, but I assume there is a way to get the data in regardless of the number of rows without having to map each and every field of every row?

Like this:
[0]: steps.trigger.event.body[0].agent
[1]: steps.trigger.event.body[0].agent_id
etc.

[dylburger (Dylan Sather)]

Hi @ankerstrom ,

Thanks for reaching out. I’ll give you a few resources I think may be helpful for you:

First, check out this general overview of how to add multiple rows to Google sheets.

Re: your data format, check out this example workflow. I start with data that looks like yours:

[
  { name: "Luke", title: "Jedi" },
  { name: "Leia", title: "General" }
]

and return an array of arrays containing just the values from the individual objects:

[
  ["Luke","Jedi"],
  ["Leia","General"]
]

This is the format that Google sheets require you pass when you add multiple rows to a sheet. Each “inner” array contains a row of data. You’ll need to modify this code a bit to reference the body of the event (it looks like steps.trigger.event.data.body in your case) instead of the data variable I reference locally.

When you select the Google Sheets action to Add Multiple Rows, you’ll want to enter {{steps.nodejs.$return_value}} in the Row Values param, which references the array of arrays you return from that step:

Let me know if that helps.

[ankerstrom]

Thanks for taking the time to help out. I am still a neophyte with coding, so sorry if this might seem trivial.

Just to provide more details on the format it looks like this:

So the array is comprised of a body of containing 3 arrays contained in
[0], [1] and [2]

So step 1 is to try to format this data via a Node.js step to make it comply with the google format. Do I need to add a further step like the examples you link to for ‘sample’ data, or can I use the data directly from the webhook?

I tried with this (and a bunch of other tries):
return steps.trigger.event.body$return_value.steps.trigger.event.body(character => [character.agent, character.agent_id, character.agent_number]);

And the error I get here is this:

TypeErrorCannot read property 'steps' of undefined
No [return values or exports] for this step

I have also tried to create another Node.js called steps.data and in the next .js I wrote this:
return steps.data.$return_value.steps.trigger.event.body(character => [character.agent, character.agent_id, character.agent_number]);
Which retured this error:
TypeErrorCannot read property ‘trigger’ of undefined
No [return values or exports] for this step

Any pointers as to what I am doing wrong?

[dylburger (Dylan Sather)]

Hi @ankerstrom , can you try this in a new Node.js code step?

return steps.trigger.event.body.map(character => [character.agent, character.agent_id, character.agent_number]);

[ankerstrom]

@dylburger

return steps.trigger.event.body.map(character => [character.agent, character.agent_id, character.agent_number]);

Thanks. Worked like a charm - now I get it

[muiznieks]

As Im dealing with same problem, I will add here, instead of creating a new topic. Hope, that’s ok.

My problem I’m dealing past 24+hours is this:

• I have Http GET request, which pulls in data for hiking events.
• I had to create a step, which checks for event id duplicates. It returns Id’s correctly.

And here starts my suffering:

Previous step image (sorry Im allowed to upload only one image): previous-step-1 — ImgBB
Here is the return values after existing id’s filtering. (no problems here)

So, what I want is - filtered ID populates data array according it’s ID, so instead of null I get data, which will be mapped in next step… >>

Previous step image: next-step-1 — ImgBB
and after this everything is passed to Google sheets (which works as it should).

What am I doing wrong?

p.s. I’ve managed to get only to one result. But not the whole.

[pierce]

Hi @muiznieks

I’m just going to take a quick stab at it. It looks like you’re trying to consolidate 2 step outputs: steps.get_events_from_calendar and steps.nodejs.

There is most likely a mismatching of data between those steps.

Maybe using just 1 as the “source of truth” would be helpful?

I’m assuming your steps.get_events_from_calendar looks like this:

// steps.get_events_from_calendar.$return_value
[
  { name: 'Meeting with Dylan' },
  { name: 'Meeting with Pierce' },
]

So perhaps you can replace your code above with just:

async (event, steps) => {
  return { data: steps.get_events_from_calendar.$return_value };
}

Instead of looping over each ID, create a new object with the key of data and assign the value.

But if this doesn’t work can you please share the output of each step?

[muiznieks]

The problem is that in this case I will loose the point of filtering before and this step will pass all the data and will create a duplicates in Google Sheets.

Thats what Im trying to avoid, @pierce

0. Here is the single DATA output:

Basically - that is what I want - so this ID is replaced with ID from the Array, so it returns expected data, which is later mapped and sent to Google sheets.

Ok, here is the full process:

1. Send GET request, receives JSON output with all the hiking events: https:// ibb. co/L01sYM1 (sorry about broken link - it looks that I have limitations )
Here is response output: step-1-2 — ImgBB

Each hiking event starts with it’s ID (1), which also is passed as an property.

2. Then I am making request to Google sheets, to get all synced event ids: https:// ibb. co/wWHNyGW

3. Then I brake down 1st step .events array, so I can extract specific event IDs: https:// ibb. co/GHvLyq4
I do this, because I dont know how can I extract it otherwise.

4. Here is the ID extraction: https:// ibb. co/WDf6Vr6
Maybe 3 and 4 somehow can be unified, but thats ok for me, my background is just yet unfinished codecadamy course and a lot of googling. Sorry if my problem is very basic.

5. Then I make the filter - comparing two arrays which returns ID’s that are not found in step 2: step-5-1 — ImgBB
The output with ID’s https:// ibb. co/NpQpgB0

6. In step six are my pain point, which is described before, so i will not attach the same picture.
I want that what I described in this comment point 0, so array IDs (in previous comment red arrow 1) are replaced with 5th step output ID’s.

7. this is where I just preparing all the info for Google sheets: https:// ibb. co/yRgw2BT

Does this helps better understand what I want to achieve?

[muiznieks]

Hey, @pierce

As the saying goes - morning is smarter than evening.

I got through and solved my problem so I receive data as expected!

Here is the solution that worked for me. As I said - sorry for my basic problems.

[muiznieks]

Okay. This is the case when I dont’t know what Im doing, but somehow I got to the expected end result.
I will leave this to “future myself” or in case someone goes through similar problem.

After I got data populated according the filtered Id’s I faced a problem where it didnt mapped correctly.

So I stared on this for an hour or two and decided to go in same manner as as pprevious step before:

var arr1 = steps.nodejs_5.$return_value

var arr2 = []

for (var i=0; i < arr1.length; i++) {
arr2.push(
arr1[i].data.map(events => [
  events.location_lon,
  events.location_lat,
  events.location_name,
  events.id,
  events.name,
  events.start,
  events.end,
  events.featured,
  events.image_url,
  events.details,
  events.permalink,
  events.learnmore_link])

)

}
return arr2

Short was my moment of joy, because next I received an 400 error from Google sheets - no data went in. Fortunately I fast enough figured that the response goes in one array level more than necessary, like [[[response array]]].

so in the end, with a help of Google gods, i got to some other souls topic where was a suggestion add .flat()

so, I added this on my last step ending line to get from [[[response array]]] to [[response array]]:

return arr2.flat()

And that’s it. After multiple testings with IDs I managed to:

1. Check whether Id’s already synced
2. Populate data from source JSON with unsynced ID’s
3. Map data so it complies with Google sheet requirements
4. Send it successfully to Google sheets.

Thanks author that went through similar problem a year before me. :))

With this text of line I end my 24+ hour hopelessness and empty staring on screen by doing things I dont understand how to do properly.

Thank’s developers for such great service! I will advance on paid membership, as I see more and more useage for my projects.