如何使用应用脚本在 Google 表格中循环浏览 JSON 网址并从中获取信息

Question

我正在从Snapchat API获取信息(文档在这里：https：//marketingapi.snapchat.com/docs/？shell#get-campaign-stats)，我正在使用Apps Script将其导入Google Sheets。

其中一个 JSON 响应需要具有单独的 ID 才能获取特定信息。

我的脚本工作正常，输出正确。然而，我写它的方式效率低下。

这是我的代码：

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

首先在我的代码中，我通过编写以下内容获取所有可用的 ID：

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);`

' 谷歌表格上从"J1"到"J4"的输出是

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

然后，我将每个ID插入URL中：

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

将ADSETID替换为从 J1 到 J4 的每个 ID

//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);
}

如何有效地编写此脚本

1. 知道 ID 的数量是动态的，因此使用范围"J1：J4"是不正确的。
2. 以更有效地循环访问 URL，并将从 JSON 响应中获取的每个信息一个接一个地绘制出来。

谢谢

Answer 1

我相信你的目标如下。

• 您希望使用"AD SET 1"到"AD SET 4"中的每个idvalues值。在这种情况下，您希望将显示脚本修改为简单脚本。

在这种情况下，以下修改怎么样？

修改后的脚本：

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

• 在此修改中，map函数中检索的值从"D5"放置。

参考：

• 地图()