I’m using a Google Sheet integration to pull a number of results from Google sheets (Get Many Spreadsheet Rows (Advanced, With Line Item Support)
I’m currently testing with 2 results, which are: 4157069551,4157190930
I’d like to get each result as it’s own text field.
To do this, I’m using Utilities > Line-item-to-text
I’m using the ‘Rows’ result from Google sheets in the input which looks like this:
I’ve put a comma in the separator box.
Here is the output:
output
item_1: 4157069551,4157190930
item_2: 4157069551,4157190930
item_last: 4157069551,4157190930
text: 4157069551,4157190930,4157069551,4157190930
I’m not sure why both results are showing as a one. Any suggestions to get this to split as item 1 and item 2?
thanks
Adam
Best answer by GetUWired
Hi @adamstanley
This would be difficult to split with Zapier’s utility functions. The get many spreadsheet rows function returns 1 list with many lists nested inside which Zapier’s utility doesn’t support very easily, as you are seeing.
Usually, to read something like this I would use a code block. or as you are discovering, multiple line item-text actions but it can be confusing.
The best no code solution I have found is to swap out the Get Many Spreadsheet Rows action with a Google Drive Find File action and then use Zapier’s utility to import the CSV version of the file. From there, you can split your line items in another utility.
The above workaround only works if your Google sheet only has 1 worksheet. Here is a demo:
My Test Spreadsheet just for reference
Use the find a file action to retrieve the spreadsheet
The above will search for the file and return all types of fields but 1 in particular is useful for your scenario.
Next, use Zapier’s utilities to import the csv file that was returned in the search step.
You will get back the line items with headers instead of what you were seeing which was a list of all the rows which as you saw Zapier turns into 1 huge list.
what is returned from the import action.
Since what is returned is a smart list (one with defined headers/(i.e major dimension as columns)) it is more use friendly to continue to work with. From there, you can split your data as it relates to the column it lives under.
With line items, the square brackets that it’s a line item within another line item - a nested item.
I would take a look at how the data is organized in Google Sheets. To get each number as a line item they will need to be on separate rows, if the sheet is set up like that then the Zap should pull both rows as line items. In the Zap editor, it will look like they are just comma-separated but they’re line items. I hope that helps!
This would be difficult to split with Zapier’s utility functions. The get many spreadsheet rows function returns 1 list with many lists nested inside which Zapier’s utility doesn’t support very easily, as you are seeing.
Usually, to read something like this I would use a code block. or as you are discovering, multiple line item-text actions but it can be confusing.
The best no code solution I have found is to swap out the Get Many Spreadsheet Rows action with a Google Drive Find File action and then use Zapier’s utility to import the CSV version of the file. From there, you can split your line items in another utility.
The above workaround only works if your Google sheet only has 1 worksheet. Here is a demo:
My Test Spreadsheet just for reference
Use the find a file action to retrieve the spreadsheet
The above will search for the file and return all types of fields but 1 in particular is useful for your scenario.
Next, use Zapier’s utilities to import the csv file that was returned in the search step.
You will get back the line items with headers instead of what you were seeing which was a list of all the rows which as you saw Zapier turns into 1 huge list.
what is returned from the import action.
Since what is returned is a smart list (one with defined headers/(i.e major dimension as columns)) it is more use friendly to continue to work with. From there, you can split your data as it relates to the column it lives under.
Hi @GetUWired - thanks again for this suggestion - it works perfectly!
I have another query for you if that’s ok. I’m using the above to pull out the Deal IDs and I then want to create a task on each Deal.
This will run daily. Sometimes there are 1 or 2 deals, but other days there might be up to 10.
Do you have any suggestions of how to ‘Create Engagements’ for multiple deals (each task assigned to different users)? I started by using paths, but Zapier limits to only paths so that only works up to the first 3, but on days when i might have 5 or 6 deals I’m a bit stuck!
Any thoughts or suggestions on how to achieve this?