SQL output to update Google Sheets

Help
1

Hello there,
I am very new to this platform and still exploring its features so forgive me if this has been already asked. I am trying to update a Google spreadsheet from a Redshift SQL output and have used the “find_row_custom_query” component. It works fine and the output is as expected. However, I am getting the error “Rows data is not an array of arrays. Please enter an array of arrays in the Rows parameter above” when linking this with “Update Google Sheets” component. I inputted {{steps.find_row_custom_query.$return_value}} in the “Row Values” field. How do I resolve this issue? Thanks!

2

Hi @Madhav44

First off, welcome to the Pipedream community. Happy to have you!

Glad to hear you’re at least half way there, you’re very close.

The error means that this prop in particular is expecting a multi-dimensional array.

The find_row_custom_query might be returning a single dimensional array, I’m not sure what you’re output looks like exactly, but let’s pretend it’s a simple array like this:

steps.find_row_custom_query.$return_value = [1, 2, 3];

You’ll need to transform it into a multi-dimensional array, a.k.a. an array of arrays.

The description beneath the Google Sheets Update Row prop describes it like this:

Each nested array should represent a row, with each element of the nested array representing a cell/column value (e.g., passing [["Foo",1,2],["Bar",3,4]] will insert two rows of data with three columns each).

So you’ll need to use Javascript or Python to map the values to an array of arrays:

return steps.find_row_custom_query.$return_value.map((value, index) => ({
   return [
     value
   ]
});

Hope this helps!

3

Thank you Pierce for your reply. I think I am almost there. The SQL output looks something like this

{"initial_service_start_date":"2020-06-25 00:00:00","session_id":"8960824","anonymous_id":"bf2128bd-36e9-4d3a-97a7-63d6e425e53f","email":"johnny@silverconcrete.net","revenue":4020,"paid_influence":"1","stagename":"Closed Won","createddate":"2020-06-23 14:30:38","type":"SMB","utm_source":"adwords","utm_medium":"paid","utm_campaign":"zlien-brand","utm_content":null,"utm_traffic_grouping":"Product","utm_traffic_subgrouping":null,"paid_influence_date":"2020-06-23 08:56:18.231"}

I included the code you send in a new node JS step and it was returning some errors. I am not familiar with Javascript but I tried and modified the code to

return steps.find_row_custom_query.$return_value.map((value,index) => [value]);

This worked only partially as the node JS step succeeded while there is the following error in the Google Sheets component.

Invalid values[0][0]: struct_value { fields { key: “anonymous_id” value { string_value: “bf2128bd-36e9-4d3a-97a7-63d6e425e53f” } }

4

Hi @Madhav44

Great. The issue is that some of your SQL output fields are multi-dimensional and include objects instead of a primitive strings, integers or floats.

The error message shows what looks like an ID field that contains an object.

There are multiple ways to solve this.

  1. Manually construct the input to Google Sheets using a Python or Node.js step and manually create the array. This option is the most typing and leg work, but it will be the most resilient to new columns being added to the table.
  2. Use a filter in Javascript or Python to only include values that are “flat” and don’t include an object or an array.
  3. Use a utility like flatMap with Lodash for Javascript. This will attempt to flatten a collection of arrays or objects into one single dimensional array: Lodash Documentation

If you need professional help with your workflow, you can connect with a Pipedream Partner here: Connect with a Pipedream Partner

5

Yes, the SQL output is multi-dimensional. I was referring to another thread in the community and deployed the following code and it worked! Thank you for your help, Pierce!!

Blockquote
return steps.find_row_custom_query.$return_value.map(character => [character.account_id, character.account_name, character.owner,character.stage,character.month]);

1 Like