Skip to main content
Best answer

Transform "null" values into actual (placeholder)values

  • 26 August 2020
  • 7 replies
  • 709 views

Hello there,

 

I have a data sample from a webhook with custom values, now i try to convert this into a big data set (in google sheets), but whenever I do so (or try to adjust the data set before), the “null”-values disappear. 

So I would like to put the data (you can see below) exactly like this into google sheets:

Now when I do it, i get this: 

Someone know a fix?

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

7 replies

andywingrave
Userlevel 7
Badge +9

Hey! There are definitely ways around this depending on what you are trying to do, however I’m not quite sure of the format you would like to enter into Google sheets? 

 

(A screenshot from Google sheets would be super helpful if possible)

F
Userlevel 2
Badge

Hey @andywingrave ,

 

Thanks for reaching out! This is how it would ideally look like in the end:

 

With my actual data set there would be multiple row, but if we find a fix, I don't think that should be a problem.

Thanks for the help in advance!

andywingrave
Userlevel 7
Badge +9

Hey! If you use “Create Spreadsheet rows”, this has line item support, and you can paste your data right in. 

However, I see that your data is a combination of the columns, and not split into rows. In that case, you will need to use a code step to format the data in the way you like - Building out your arrays for each column. 

If you’re familiar with code, this should be relatively quick, if not, I suggest hiring a Zapier expert who can help you here. You can find the Experts directory here: https://zapier.com/experts/

J
Userlevel 1

@andywingrave Here from my personal account since I cannot login with my original one. 

 

I know the function with line item support, and how the information is put in is (rows/columns), either is fine, I can work around them, the biggest issue is that if I use “Create Spreadsheet rows” (with line-item support), it cuts out the “null”-values, thats what I'm trying to resolve, now it comes in like this:

 

andywingrave
Userlevel 7
Badge +9

Yeah - This is by design - I’ve spent much time solving problems like this with webhook responses. The short answer is - what you are trying to do is not possible with Zapier’s webhooks currently because of how they handle the response. You will need to make an API call with a code step and handle the response yourself. 

const content = await rawResponse.json().then((data) => {
	function ColumnPair(columnName, columnValue) {
		(this.columnName = columnName), (this.columnValue = columnValue);
	}
	let combined = [];

	var columns = data.Result.Customfields;

	for (var i = 0; i < columns.length; i++) {
		if (columns[i].ColumnValue.length < 1) {
			columns[i].ColumnValue = 'n/a';
		} else {
			columns[i].ColumnValue;
		}
	}

What I am doing here is sending an API call, then handling the null values myself. You can see this in this part here:

 

		if (columns[i].ColumnValue.length < 1) {
			columns[i].ColumnValue = 'n/a';

 

J
Userlevel 1

@andywingrave Im not completely new using code, but I might need some help.

  • If I copy it like you send above, do I still need to original webhook?
    • If YES, what are the values I need to adjust to my output (and what exactly do I put at “Input Data” (see attachement)
    • if NO, what do I put at “Input Data” and how do I get it to request the data

I can provide you with screenshots of whatever you need, but I’ve been struggling with this for a long time now, so if you could help me out you would be my saviour!

andywingrave
Userlevel 7
Badge +9

@Jobv 

Sadly my code was pretty specific to my use case. Given that I don’t know your use case, I was hoping the above might point you in the right direction, specifically with the concept highlighted here:

if (columns[i].ColumnValue.length < 1) {
			columns[i].ColumnValue = 'n/a';

The idea is that you loop through the response, and give some kind of values to the nulls - So in this case, if a value is less than 1 character long, give it a value “n/a”

To subsequently answer your next question - I honestly don’t know - Code steps are advanced actions, and unless I can see what you’re doing, the data/schema you are working with and the desired output, it’s not something that I’d be able to answer on the forum. I’d be happy to schedule a call with you separately if you PM me - Or I would recommend contacting an expert from the expert directory?

https://zapier.com/experts/

 

 

Terms & Conditions