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

Question

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

You say `My script works fine and the output is correct. However the way I am writing it is inefficient.`. But, I'm worried that you might request additional functions of `1. knowing that number of IDs are dynamic, so using the range "J1:J4" is not correct. 2. to loop through the URLs more efficiently and plot each information taken from the JSON response one under the other.`. What is your question? First, I would like to correctly understand your question. — Tanaike, Apr 02 '23 at 00:25
@Tanaike Hello and thank you for your ongoing support Tanaike. In my code, I am writing a script for //AD SET 1, a script for //AD SET 2, etc... so my question is, how can I write my code in a way to retrieve a dynamic number of AD SETS. — Karim, Apr 02 '23 at 05:36
@Tanaike You understood my question perfectly and your answer is brilliant. Really, thank you so much for your time and ongoing support my friend! — Karim, Apr 02 '23 at 05:57

score 1 · Accepted Answer · answered Apr 02 '23 at 05:50

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()

It is EXACTLY what I was asking for and it worked like a charm. Tanaike, you are very talented in Apps Script! And I am very grateful to have met you. Thank you very much for your support! — Karim, Apr 02 '23 at 05:56
hello, could you please have a look at my new question? https://stackoverflow.com/questions/75948275/apps-script-if-google-sheets-named-ranges-contain-specific-text-then-apply-an — Karim, Apr 06 '23 at 10:04

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

1 Answers1

Modified script:

Reference:

Linked