"No Data" is creating Empty cells populating google sheets

  • 14 November 2022
  • 3 replies

I am trying to pull in data through Zapier Formater “Text to Line” thus creating a column of data. The issue is that when a submission is made that contains “No Data” it creates empty cells instead of ignoring the entry. What I need is for Zapier to only create cells in the list if there is Data. Here is an example of what the Zap looks like and how it appears in the sheet. What I don't want are all the empty cells at the top. 




This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

3 replies

Userlevel 5
Badge +7

Hi there @Tim Moxey 👋🏻


Wondering how you are getting along with your Google Sheets code block? It’s all a bit out of my league, but luckily it looks like you have two thorough responses to help you along the way. Let us know if you ran into any other snags or if you’re all set!




Userlevel 6
Badge +8

Hey @Tim Moxey,

Let me see if I can help here…

I’m not sure I’m fully understanding your desired workflow. Now, it looks like the first line item that came through had a quantity of 4 and no other values. So a row was entered with just that set. And then the next line item had a quantity of 270, and so on.

Finally, the sixth line item had a quantity, lot number, and batch number come through, but no other information. Are you saying that you want the lot number and batch number to be in the top row, even though that data isn’t associated with the “4” that’s already in the top row? Or are you saying you only want data entered when all the values are already there? Or are you saying something else?

Userlevel 7
Badge +12

Hi @Tim Moxey 

From my understanding of what you are looking to do. the most efficient solution would be a code block to ignore empty values. 

The code below would allow you to split up the input by comma and only return values that exist. This code step also returns a line item so you can map it directly to Google Sheets in the same manner

*TIP*: You can use keyboard shortcuts like cntrl+a (or command+a on a mac) on the input of your current set up to highlight everything you’ve already mapped. then use cntrl+x (or command+x) to cut the input and cntrl+v (or command+v) to paste it into the input of the code block.

let values = inputData.input.split(",");
let filtered = values.filter(value => value != "")

return {output: filtered}