1

I want to import data inside Google Sheets from the Snapchat API using Apps Script.

So far I have the following code:

function readData() {

var accesstoken = "TOKEN"    
var sheet = SpreadsheetApp.getActiveSheet() 
 var url = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/stats"

 var response = UrlFetchApp.fetch(url, {
       headers: {
           "Authorization": "Bearer " + accesstoken
       }
   });
 var data = JSON.parse(response.getContentText());
 var campaigndata = []
 campaigndata.push(data.total_stats)

 var campaign = []
 campaign.push(campaigndata)

 var targetrange = sheet.getRange('B2:B')

 targetrange.setValue(campaign)

}

As per the Snapchat documentation (https://marketingapi.snapchat.com/docs/#request-response-pattern), I would like to get the number of impressions from that response body :

{
  "request_status": "success",
  "request_id": "57ad1ad600076e58fa35e192",
  "total_stats": [
    {
      "sub_request_status": "success",
      "total_stat": {
        "id": "7057e31f-b908-4bc7-85dd-88169f53e08d",
        "type": "CAMPAIGN",
        "granularity": "TOTAL",
        "stats": {
          "impressions": 0,
          "swipes": 0,
          "spend": 0,
          "quartile_1": 0,
          "quartile_2": 0,
          "quartile_3": 0,
          "view_completion": 0,
          "screen_time_millis": 0
        }
      }
    }
  ]
}

In my array I am pushing:

campaigndata.push(data.total_stats)

and it is working, but as soon as I try looking for the impressions data by writing:

campaigndata.push(data.total_stats.total_stat.stats.impressions)

As per the response body, it is not working. And I am receiving the following error:

TypeError: Cannot read properties of undefined (reading 'stats')

The error occurs on line 38, where the abovementioned line is written.

How can I retrieve specific data from the response body ?

Thank you for your help.

Karim
  • 205
  • 1
  • 9

1 Answers1

2

When you want to retrieve 0 of "impressions": 0, from the following JSON object,

{
  "request_status": "success",
  "request_id": "57ad1ad600076e58fa35e192",
  "total_stats": [
    {
      "sub_request_status": "success",
      "total_stat": {
        "id": "7057e31f-b908-4bc7-85dd-88169f53e08d",
        "type": "CAMPAIGN",
        "granularity": "TOTAL",
        "stats": {
          "impressions": 0,
          "swipes": 0,
          "spend": 0,
          "quartile_1": 0,
          "quartile_2": 0,
          "quartile_3": 0,
          "view_completion": 0,
          "screen_time_millis": 0
        }
      }
    }
  ]
}

and, when this JSON object is data in your showing script, how about the following script?

var res = data.total_stats[0].total_stat.stats.impressions;
console.log(res)

By this script, the value of "impressions": 0, from the above JSON object. In this case, total_stats is an array. Please be careful about this.

If you want to put this value to the cell "B2", how about the following modification?

From:

var data = JSON.parse(response.getContentText());
var campaigndata = []
campaigndata.push(data.total_stats)

var campaign = []
campaign.push(campaigndata)

var targetrange = sheet.getRange('B2:B')

targetrange.setValue(campaign)

To:

var data = JSON.parse(response.getContentText());
var res = data.total_stats[0].total_stat.stats.impressions;
var targetrange = sheet.getRange('B2');
targetrange.setValue(res);
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • it worked like a charm. I can't thank you enough for your ongoing support with my questions. Thank you very much you are an amazing person Tanaike! – Karim Mar 26 '23 at 11:33
  • hello, I have a question related to this. Is it possible to loop through the "impressions" data from the JSON object ? Currently you helped me find "var res = data.total_stats[0].total_stat.stats.impressions;" , but is it possible to have an iterator "i" replace 0 in a ForLoop ? For example like this "var res = data.total_stats[i].total_stat.stats.impressions;" How can I write this inside my script ? Thank you so much for your kind assistance. – Karim Mar 27 '23 at 09:55
  • @Karim About your new question, I would like to support you. I have to apologize for my poor English skill. Unfortunately, I cannot understand your new question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. – Tanaike Mar 27 '23 at 12:12
  • thank you for your help. I have explained my new question in a new thread, I hope it is more clear :https://stackoverflow.com/questions/75862842/how-to-loop-through-json-responses-inside-apps-script – Karim Mar 28 '23 at 06:10