json google-apps-script google-sheets snapchat

How to Loop Through JSON URLs and get information from them inside Google Sheets using Apps Script

I am getting information from the Snapchat API (Documentation here:https://marketingapi.snapchat.com/docs/?shell#get-campaign-stats) and I am importing it inside Google Sheets using Apps Script.

One of the JSON response requires to have individual IDs in order to get specific information.

My script works fine and the output is correct. However the way I am writing it is inefficient.

Here's my code:

    function readData() {
    
    var accesstoken = "TOKEN"
    
     var sheet = SpreadsheetApp.getActiveSheet()
   
    
    //Ad Set IDs
    var adsetidssurl = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/adsquads"
    var adsetidsurlresponse = UrlFetchApp.fetch(adsetidssurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       }); 
    
    var adsetidsdata = JSON.parse(adsetidsurlresponse.getContentText());
    var idvalues = adsetidsdata.adsquads.map(({ adsquad: { id } }) => [id]);
    sheet.getRange(1,10,idvalues.length).setValues(idvalues);
    
    //Ad Set Stats
    
    var adset1 = sheet.getRange('J1').getValue
    var adset2 = sheet.getRange('J2').getValue
    var adset3 = sheet.getRange('J3').getValue
    var adset4 = sheet.getRange('J4').getValue

//AD SET 1
    
       var adset1statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset1}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset1statsurlresponse = UrlFetchApp.fetch(adset1statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset1statsdata = JSON.parse(adset1statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset1statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(5, 4, impressionsvalues.length).setValues(impressionsvalues);

//AD SET 2

     var adset2statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset2}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset2statsurlresponse = UrlFetchApp.fetch(adset2statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset2statsdata = JSON.parse(adset2statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset2statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(6, 4, impressionsvalues.length).setValues(impressionsvalues);
    

//AD SET 3
        var adset3statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset3}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset3statsurlresponse = UrlFetchApp.fetch(adset3statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset3statsdata = JSON.parse(adset3statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset3statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(7, 4, impressionsvalues.length).setValues(impressionsvalues);
    }

// AD SET 4

        var adset4statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset4}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset4statsurlresponse = UrlFetchApp.fetch(adset4statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset4statsdata = JSON.parse(adset4statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset4statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(8, 4, impressionsvalues.length).setValues(impressionsvalues);
    }

First in my code, I am getting all the available IDs by writing:

  var adsetidssurl = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/adsquads"
        var adsetidsurlresponse = UrlFetchApp.fetch(adsetidssurl, {
               headers: {
                   "Authorization": "Bearer " + accesstoken
               }
           }); 
        
        var adsetidsdata = JSON.parse(adsetidsurlresponse.getContentText());
        var idvalues = adsetidsdata.adsquads.map(({ adsquad: { id } }) => [id]);
        sheet.getRange(1,10,idvalues.length).setValues(idvalues);`

` The output on Google Sheets from "J1" to "J4" is

05280d2f-adaa-4cd5-ba3c-559a9e4eeacf
b322eeb1-b8eb-4e52-9730-6d0e6c7b0029
b81265e7-090c-4a9c-8ec5-29897023dcf4
bd3defd2-f8d5-4f01-af92-1d99eaa17d20

I am then inserting each ID inside the URL:

"https://adsapi.snapchat.com/v1/campaigns/ADSETID/adsquads"

By Replacing ADSETID with each ID from J1 to J4

    //Ad Set Stats
    
    var adset1 = sheet.getRange('J1').getValue
    var adset2 = sheet.getRange('J2').getValue
    var adset3 = sheet.getRange('J3').getValue
    var adset4 = sheet.getRange('J4').getValue

//AD SET 1
    
   var adset1statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset1}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;

 var adset1statsurlresponse = UrlFetchApp.fetch(adset1statsurl, {
       headers: {
           "Authorization": "Bearer " + accesstoken
       }
   });  

var adset1statsdata = JSON.parse(adset1statsurlresponse.getContentText());


var impressionsvalues = adset1statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
sheet.getRange(5, 4, impressionsvalues.length).setValues(impressionsvalues);

//AD SET 2

     var adset2statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset2}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset2statsurlresponse = UrlFetchApp.fetch(adset2statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset2statsdata = JSON.parse(adset2statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset2statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(6, 4, impressionsvalues.length).setValues(impressionsvalues);
    

//AD SET 3
        var adset3statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset3}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset3statsurlresponse = UrlFetchApp.fetch(adset3statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset3statsdata = JSON.parse(adset3statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset3statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(7, 4, impressionsvalues.length).setValues(impressionsvalues);
    }

// AD SET 4

        var adset4statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset4}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    
     var adset4statsurlresponse = UrlFetchApp.fetch(adset4statsurl, {
           headers: {
               "Authorization": "Bearer " + accesstoken
           }
       });  
    
    var adset4statsdata = JSON.parse(adset4statsurlresponse.getContentText());
    
    
    var impressionsvalues = adset4statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
    sheet.getRange(8, 4, impressionsvalues.length).setValues(impressionsvalues);
    }

How can I write this script efficiently

knowing that number of IDs are dynamic, so using the range "J1:J4" is not correct.
to loop through the URLs more efficiently and plot each information taken from the JSON response one under the other.

Thank you

Solution

I believe your goal is as follows.

You want to use each value of idvalues in "AD SET 1" to "AD SET 4". In this case, you want to modify your showing script to a simple script.

In this case, how about the following modification?

Modified script:

function readData() {
  var accesstoken = "TOKEN";
  var sheet = SpreadsheetApp.getActiveSheet();

  var adsetidssurl = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/adsquads";
  var adsetidsurlresponse = UrlFetchApp.fetch(adsetidssurl, { headers: { "Authorization": "Bearer " + accesstoken } });
  var adsetidsdata = JSON.parse(adsetidsurlresponse.getContentText());
  var idvalues = adsetidsdata.adsquads.map(({ adsquad: { id } }) => [id]);
  sheet.getRange(1, 10, idvalues.length).setValues(idvalues);

  // I modified the below script.
  var values = idvalues.flatMap(([adset]) => {
    var adset1statsurl = `https://adsapi.snapchat.com/v1/adsquads/${adset}/stats?&granularity=TOTAL&fields=impressions,swipes,video_views,view_completion,spend,video_views_time_based,frequency,uniques`;
    var adset1statsurlresponse = UrlFetchApp.fetch(adset1statsurl, { headers: { "Authorization": "Bearer " + accesstoken } });
    var adset1statsdata = JSON.parse(adset1statsurlresponse.getContentText());
    return adset1statsdata.total_stats.map(({ total_stat: { stats } }) => [stats.impressions]);
  });
  sheet.getRange(5, 4, values.length).setValues(values);
}

In this modification, the values retrieved in map function are put from "D5".

Reference:

map()