About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Replace null values with 0 in a array

  • 13 September 2023
  • 22 replies
  • 594 views
T
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

Todd Harper
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}];

 

T
Userlevel 1

Yes, of course.

Using it i get this as result

 

 

Todd Harper
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?

Troy Tessalone
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

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
T
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.

 

 

T
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

Troy Tessalone
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.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Todd Harper
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!

 

T
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.

 

Troy Tessalone
Userlevel 7
Badge +14

@Thales Campolim 

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

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Todd Harper
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.

Troy Tessalone
Userlevel 7
Badge +14

@Thales Campolim 

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

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
T
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.

Todd Harper
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);
}

 

T
Userlevel 1

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

 

 

Todd Harper
Userlevel 6
Badge +8

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

T
Userlevel 1

No problem!

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

 

 

Todd Harper
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);
}

 

T
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

Todd Harper
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.

 

 

T
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.

Todd Harper
Userlevel 6
Badge +8

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

Terms & Conditions