I’m building a custom Zap to integrate Google Local Service Ads API with our CRM. It appears everything is functioning properly however, I noticed several new leads are not coming through, which appears to be due to the API request sending nested data using the values nested under the same field .

Here is an example response:

    {     "leadId": "000000000",     "accountId": "000000000",     "businessName": "Acme, Inc",     "leadCreationTimestamp": "2022-08-09T16:21:14Z",     "leadType": "PHONE_CALL",     "leadCategory": "roofer",     "geo": "Miami,Florida,United States",     "phoneLead": {       "consumerPhoneNumber": "+15555555678"     },     "chargeStatus": "NOT_CHARGED",     "currencyCode": "USD",     "timezone": {       "id": "America/New_York"     },     "id": "0000000000"   },   {     "leadId": "000000000",     "accountId": "000000000",     "businessName": "Acme, Inc",     "leadCreationTimestamp": "2022-08-09T16:39:38Z",     "leadType": "MESSAGE",     "leadCategory": "roofer",     "geo": "Miami,Florida,United States",     "messageLead": {       "customerName": "Jane Doe",       "jobType": "attic_venting",       "postalCode": "33066",       "consumerPhoneNumber": "+15555555789"     },     "chargeStatus": "CHARGED",     "currencyCode": "USD",     "timezone": {       "id": "America/New_York"     },     "id": "1111111111"   },   {     "leadId": "000000000",     "accountId": "000000000",     "businessName": "Acme, Inc",     "leadCreationTimestamp": "2022-08-10T19:12:28Z",     "leadType": "PHONE_CALL",     "leadCategory": "window_repair",     "geo": "Miami,Florida,United States",     "phoneLead": {       "chargedCallTimestamp": "2022-08-10T19:12:28Z",       "chargedConnectedCallDurationSeconds": "280s",       "consumerPhoneNumber": "+15555555890"     },     "chargeStatus": "CHARGED",     "currencyCode": "USD",     "timezone": {       "id": "America/New_York"     },     "id": "2222222222"   }, 

The issue I’m running into is when mapping the data to our CRM, the trigger test data is providing multiple fields for ‘consumerPhoneNumber’ ( based on whether it is a message/phone lead and whether the call connected ). so we are unable to map phoneLead__consumerPhoneNumber and messageLead___consumerPhoneNumber to the same field.

duplicate fields example

How can I parse the API response to properly map the consumerPhoneNumber when the value changes based on messageLead_consumerPhoneNumber versus phoneLead_consumerPhoneNumber?

I understand some basic Javascript but parsing API data is new to me. Any help would be truly appreciated!

Here is the JavaScript code for our API request.

    const options = {   url: 'https://localservices.googleapis.com/v1/detailedLeadReports:search',   method: 'GET',   headers: {     'Authorization': `Bearer ${bundle.authData.access_token}`,     'X-QUERY': bundle.authData.query   },   params: {     'query': 'manager_customer_id:XXXXXXXXX',     'pageSize': '1000'   } } return z.request(options).then((response) => {   response.throwForStatus();   const results = response.json;   const lists = results["detailedLeadReports"].map((item) => {     return Object.assign(item, {       id: item["leadId"],     });   });   return lists; }); 

I am trying to extract the Unnest data contained in JSON Arrays that Google Ads sends via BigQuery Data Transfers. Unfortunately, I am getting stuck in the middle.

Original Data in a BigQuery Table:

Desired Outcome:

This is the query that got me the closest but is still showing JSON.

SELECT   CreativeId,   JSON_QUERY_ARRAY(ResponsiveSearchAdDescriptions) AS Ads FROM   `priXXXXXX.sandbox.Ad_XXXXXXX` WHERE   ResponsiveSearchAdDescriptions IS NOT NULL LIMIT   100 

The Query should be able to include this condition ResponsiveSearchAdDescriptions IS NOT NULL

Some ideas?