Best answer

Replace null values with 0 in a array

  • 13 September 2023
  • 22 replies
  • 643 views

Userlevel 1

I am collecting conversion data from facebook’s API, and grouping it by hour.

As there isnt conversions for every single hour, it’s bringing a whole array of values comma separated, so as I want to put it in a google sheets, I need to input a value 0 for the hours I dont have any conversions.

 

I tried to use the code this article suggest , however, I am no getting the same result as my output just remove the null values instead of replace it with 0.

Input:
 

 

 

Output:
 

 

icon

Best answer by Todd Harper 14 September 2023, 21:38

View original

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

22 replies

Userlevel 6
Badge +8

@Thales Campolim Can you take a screenshot of the output if you use this modified version of Troy’s code?

 

let Set = inputData.Set;//.split(","); // split array into line items by the comma delimiter
let Default = inputData.Default; // default value to set for blank/empty/null items

// FOR loop to iterate over each array item
/*for (let i = 0; i < Set.length ; i++) {
if (Set[i] == "" || Set[i] == null) {
Set[i] = Default; // sets default value
}
}*/

output = [{Set, Default}];

 

Userlevel 1

Yes, of course.

Using it i get this as result

 

 

Userlevel 6
Badge +8

Fascinating! It’s not even acknowledging that those empty values exist. They’re not just null; they’re nonexistent. Can you let us know which endpoint you’re using for FB / link to that section of the API documentation?

Userlevel 7
Badge +14

Hi @Thales Campolim 

To help us have more context, can you post detailed screenshots with how each of your Zap steps are configured?

Wondering if the issue is related to the variable value type as shown in the screenshot below.

Try adding this Zap action: Formatter > Utilities > Line Items to Text

 

Userlevel 1

Hi @Thales Campolim 

To help us have more context, can you post detailed screenshots with how each of your Zap steps are configured?

Wondering if the issue is related to the variable value type as shown in the screenshot below.

Try adding this Zap action: Formatter > Utilities > Line Items to Text

 

Hello, Troy!

I tried it but it removes de null values even before de code part as it doesnt keep the empty spaces between commas to be filled with 0.

My zapier is configured for every once in a while reach facebooks insights API to get the metrics of my account performance.

The endpoint I am using:

https://graph.facebook.com/v17.0/act_1792731180783269/insights

I have set only two parameters:

breakdowns = hourly_stats_aggregated_by_advertiser_time_zone
fields = conversions

I intend to bring this data to store a google sheets.

 

 

Userlevel 1

Yes, of course.

Using it i get this as result

 

 

Fascinating! It’s not even acknowledging that those empty values exist. They’re not just null; they’re nonexistent. Can you let us know which endpoint you’re using for FB / link to that section of the API documentation?

Hi, Todd.

I found it intereseting as well, from the call I get the values and the empty spaces but after zapier just drops it, I tried to make it text as Troy suggested, but it also removes the null values.

I using this fb endpoint:

https://graph.facebook.com/v17.0/act_1792731180783269/insights

The documentation I used to is this one 

https://developers.facebook.com/docs/marketing-api/insights/parameters/v18.0

Userlevel 7
Badge +14

@Thales Campolim 

Try using the Code step to make the API request instead of using the Webhook apps.

Then in the Code step you can handle the response how you’d like.

That should address the issue you are having.

Userlevel 6
Badge +8

@Thales Campolim I agree with Troy here. If you make the call directly from the code step, you’ll be able to access the raw data sent by Facebook without Zapier doing any type conversions.

I’ve encountered a similar, but different issue with ClickUp custom fields before, and that was what did the trick for me!

 

Userlevel 1

@Thales Campolim I agree with Troy here. If you make the call directly from the code step, you’ll be able to access the raw data sent by Facebook without Zapier doing any type conversions.

I’ve encountered a similar, but different issue with ClickUp custom fields before, and that was what did the trick for me!

 

Thank you very much for the awnsers.

Could you share at least partly how did you make this calls?

I am not an js expert exactly so anything would help.

 

Userlevel 7
Badge +14

@Thales Campolim 

Try asking ChatGPT for help configuring the JavaScript code to make the API request and handle the response.

Userlevel 6
Badge +8

Thank you very much for the awnsers.

Could you share at least partly how did you make this calls?

I am not an js expert exactly so anything would help.

 

If you reply with a screenshot of your web hook action, I’ll be happy to convert it to JS for you :) Please be sure to redact or blur any sensitive data, like your authorization access token.

Userlevel 7
Badge +14

@Thales Campolim 

If you need help, consider hiring a Certified Zapier Expert.

Userlevel 1

Thank you very much for the awnsers.

Could you share at least partly how did you make this calls?

I am not an js expert exactly so anything would help.

 

If you reply with a screenshot of your web hook action, I’ll be happy to convert it to JS for you :) Please be sure to redact or blur any sensitive data, like your authorization access token.


Oh, that would be great, I think I can carry on from there then..
Thank you very much again.
 


Just to make it clear, conversions is the only one field I'm having this problem, all the other fields data is coming just fine as they have results for every single hour.

Userlevel 6
Badge +8

Before I can share the code that will be the end solution, can you tell me what is output when you use the following (being sure to replace the “INSERT_HERE” areas with your access token and full account number, including the “act_” prefix?

output = { conversions: [] };

const accessToken = "INSERT_ACCESS_TOKEN_HERE";
const actNum = "INSERT_ACT_NUM_HERE";
const queryParams = new URLSearchParams({
'access_token': accessToken,
'breakdowns': 'hourly_stats_aggregated_by_advertiser_time_zone',
'fields': 'conversions',
'date_preset': 'today'
});
let appData;

try {
const appResp = await fetch(
`https://graph.facebook.com/v17.0/${actNum}/insights?${queryParams.toString()}`,
{
method: 'GET',
}
);

appData = await appResp.text();
appData = JSON.parse(appData.data);

console.log(appData);

for (let i = 0; i < appData.length; i++) {
output.conversions[i] = appData[i].conversions;
}

} catch (e) {
console.log(e.message);
}

 

Userlevel 1

I am just getting a troubleshot error which I could figure out why

 

 

Userlevel 6
Badge +8

Whoops! I had an extra curly brace in there. Try the code in my revised comment above :)

Userlevel 1

No problem!

Now it worked, however, it got an output which seems to be related do the data not being a Json

 

 

Userlevel 6
Badge +8

Okay, let’s just see how the raw data is returned. Try this:

output = { appData: "" };

const accessToken = "INSERT_ACCESS_TOKEN_HERE";
const actNum = "INSERT_ACT_NUM_HERE";
const queryParams = new URLSearchParams({
'access_token': accessToken,
'breakdowns': 'hourly_stats_aggregated_by_advertiser_time_zone',
'fields': 'conversions',
'date_preset': 'today'
});

try {
const appResp = await fetch(
`https://graph.facebook.com/v17.0/${actNum}/insights?${queryParams.toString()}`,
{
method: 'GET',
}
);

if (appResp.ok) {
output.appData = await appResp.json();

} else {
console.log(`Received status code: ${appResp.status}`);
}

} catch (e) {
console.log(e.message);
}

 

Userlevel 1

Now it worked well!
Returned the converstions when there were.

 


the whole payload it bellow:
 

appData

data

1

conversions

1

action_type

schedule_total

value

1

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

00:00:00 - 00:59:59

2

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

01:00:00 - 01:59:59

3

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

02:00:00 - 02:59:59

4

conversions

1

action_type

schedule_total

value

1

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

03:00:00 - 03:59:59

5

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

04:00:00 - 04:59:59

6

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

05:00:00 - 05:59:59

7

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

06:00:00 - 06:59:59

8

conversions

1

action_type

schedule_total

value

3

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

07:00:00 - 07:59:59

9

conversions

1

action_type

schedule_total

value

5

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

08:00:00 - 08:59:59

10

conversions

1

action_type

schedule_total

value

3

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

09:00:00 - 09:59:59

11

conversions

1

action_type

schedule_total

value

4

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

10:00:00 - 10:59:59

12

conversions

1

action_type

schedule_total

value

4

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

11:00:00 - 11:59:59

13

conversions

1

action_type

schedule_total

value

2

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

12:00:00 - 12:59:59

14

conversions

1

action_type

schedule_total

value

6

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

13:00:00 - 13:59:59

15

conversions

1

action_type

schedule_total

value

2

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

14:00:00 - 14:59:59

16

conversions

1

action_type

schedule_total

value

2

date_start

2023-09-14

date_stop

2023-09-14

hourly_stats_aggregated_by_advertiser_time_zone

15:00:00 - 15:59:59

paging

cursors

before

MAZDZD

after

MTUZD

id

CS6iWcp4C34AVuKRJBKquTzztvqHSMyj

runtime_meta

memory_used_mb

65

duration_ms

492

logs

async

true

Userlevel 6
Badge +8

With all that in mind, the following code should work for you. Let me know if you run into any errors with it, but I think it covers everything.

output = { inlineLinkClicks: [], costPerInlineLinkClick: [], cpm: [], impressions: [], conversions: [] };

const accessToken = "INSERT_ACCESS_TOKEN_HERE";
const actNum = "INSERT_ACT_NUM_HERE";
const queryParams = new URLSearchParams({
'access_token': accessToken,
'breakdowns': 'hourly_stats_aggregated_by_advertiser_time_zone',
'fields': 'inline_link_clicks,cost_per_inline_link_click,cpm,impressions,conversions',
'date_preset': 'today'
});
let appData;

try {
const appResp = await fetch(
`https://graph.facebook.com/v17.0/${actNum}/insights?${queryParams.toString()}`, {
method: 'GET',
}
);

if (appResp.ok) {
appData = await appResp.json();

const insights = appData.data || [];

for (let i = 0; i < insights.length; i++) {
output.inlineLinkClicks[i] = insights[i].inline_link_clicks;
output.costPerInlineLinkClick[i] = insights[i].cost_per_inline_link_click;
output.cpm[i] = insights[i].cpm;
output.impressions[i] = insights[i].impressions;

if (!insights[i].conversions) {
output.conversions.push("0");
} else {
output.conversions.push(insights[i].conversions[0].value);
}
}

console.log(output);
} else {
console.log(`Received status code: ${appResp.status}`);
}

} catch (e) {
console.log(e.message);
}

Caveat: conversions are returned as lists of objects that each include an action type and a value. This code returns only the value of the first item in the list. So for example, using your previous screenshot, it currently includes 1 conversion object. If two existed, as in

conversions

  1

    action_type schedule_total

    value 1

  2

    action_type schedule_total

    value 8

only “1” (the value of the first conversion object - bolded above) will be returned. Instances of “schedule_total” and the value of 8 are ignored. If you need to handle additional data like this, it would be beyond the scope of my free assistance, and I’d recommend hiring a Zapier Expert to refine the code.

 

 

Userlevel 1

With all that in mind, the following code should work for you. Let me know if you run into any errors with it, but I think it covers everything.

output = { inlineLinkClicks: [], costPerInlineLinkClick: [], cpm: [], impressions: [], conversions: [] };

const accessToken = "INSERT_ACCESS_TOKEN_HERE";
const actNum = "INSERT_ACT_NUM_HERE";
const queryParams = new URLSearchParams({
'access_token': accessToken,
'breakdowns': 'hourly_stats_aggregated_by_advertiser_time_zone',
'fields': 'inline_link_clicks,cost_per_inline_link_click,cpm,impressions,conversions',
'date_preset': 'today'
});
let appData;

try {
const appResp = await fetch(
`https://graph.facebook.com/v17.0/${actNum}/insights?${queryParams.toString()}`, {
method: 'GET',
}
);

if (appResp.ok) {
appData = await appResp.json();

const insights = appData.data || [];

for (let i = 0; i < insights.length; i++) {
output.inlineLinkClicks[i] = insights[i].inline_link_clicks;
output.costPerInlineLinkClick[i] = insights[i].cost_per_inline_link_click;
output.cpm[i] = insights[i].cpm;
output.impressions[i] = insights[i].impressions;

if (!insights[i].conversions) {
output.conversions.push("0");
} else {
output.conversions.push(insights[i].conversions[0].value);
}
}

console.log(output);
} else {
console.log(`Received status code: ${appResp.status}`);
}

} catch (e) {
console.log(e.message);
}

Caveat: conversions are returned as lists of objects that each include an action type and a value. This code returns only the value of the first item in the list. So for example, using your previous screenshot, it currently includes 1 conversion object. If two existed, as in

conversions

  1

    action_type schedule_total

    value 1

  2

    action_type schedule_total

    value 8

only “1” (the value of the first conversion object - bolded above) will be returned. Instances of “schedule_total” and the value of 8 are ignored. If you need to handle additional data like this, it would be beyond the scope of my free assistance, and I’d recommend hiring a Zapier Expert to refine the code.

 

 

Hey, Todd.

It was already a lot of help from you, and I could be more thankful, I think I can take over from here.

Again, thank you very much, you are such a nice person.

Userlevel 6
Badge +8

Let me know if you have any additional questions about it!