How to format the output of Google Sheets' 'Find Many Rows' action

  • 16 July 2020
  • 4 replies
  • 3918 views
How to format the output of Google Sheets' 'Find Many Rows' action
Userlevel 7
Badge +11

 

The Challenge

We had a community member recently who was trying to trigger a Zap with a Google Calendar event, then retrieve multiple rows from a Google Sheet and send the information in Gmail.

The sheet looked something like this:

A little DnD action

And the goal was to send it an email so the recipient received this:

Don’t you all be emailing me!

They had initially tried using the Get Many Spreadsheet Rows (Advanced, With Line Item Support) action in Gogle Sheets, which is a great idea because you can just specify how many rows to return instead of searching by a specific value:

This definitely has its uses!

The Twist

The tricky thing is that this is the output:

Whoa! Look at those nested line items!

Without using something like a Code step, it’s tricky to get the text in the desired format. 

The Solution

Given that the Zap is being triggered by Google Calendar I thought that you could add a Date column in the Google Sheet and use the Find Many Spreadsheet Rows (With Line Item Support action, using that Date column with the Pretty Date field from Google Calendar. So for everyone you want to send in the email, you add the date that corresponds to the event.

The output of that Google Sheet action is more manageable:

 

Formatter to the Rescue

You can now build what you want each line to look like:

It looks more complicated than it is!

What I’ve done there is map the Number field, followed by a period and a space, then the Name field with > and then the Character field. The result:

You get each line AND all of them in a single field.

Now we have something nice and tidy to map to the Gmail step:

You can choose plain text, if you need other formatting.

And sure enough, the recipient will see it in the desired format:

Hope you’ve found this helpful! Untl next Wednesday!


4 replies

Userlevel 2
Badge +1

I’m having the same issue as KBrillefar above. Any insight?

This one got me so close to the finish! Only problem I have is that if I chose HTML (not plain), it just adds all my items in one line (no line shift). Any idea on how to fix that? 

Thanks!

Userlevel 1

I would like to see the same process, but reverse

Userlevel 7
Badge +10

Great stuff @nicksimard - thanks for sharing!

Reply