如何在应用程序脚本(javascript)中的json格式API调用中包含变量

Question

我正在为Google Sheets开发一个自定义函数，该函数接收一个单元格值作为输入，并从API调用中吐出响应。我知道在当前阶段，Google Analytics Reporting API无法直接与自定义函数一起使用，因此我遵循此解决方法。

运行对Google Analytics Reporting API的API调用的函数包括一个带有度量、维度、过滤器等的JSON格式请求。问题是维度过滤器是可变的，我希望它是来自Google Sheets的输入。然而，我一直收到这个错误消息：

GoogleJsonResponseException:API调用analyticsreporting.reports.batchGet失败，返回错误：需要字段request.dimensionFilterClauses.filters.expressions。(第2行，文件"代码"(

function plateToCampaign(plate) {
globalThis.r = AnalyticsReporting.Reports.batchGet({
"reportRequests":
[
{
"viewId": {"my_viewId"},
"pageSize": 1,
"dateRanges": [
{"endDate": "today", "startDate": "30daysAgo"}
],
"metrics": [
{"expression": "ga:pageValue"}
],
"dimensions": [{"name": "ga:sourceMedium"}, {"name": "ga:campaign"}, {"name": "ga:dateHourMinute"}],
"orderBys": {"fieldName": "ga:dateHourMinute","sortOrder": "DESCENDING"},
"dimensionFilterClauses": [
{
"filters": [
{
"dimensionName": "ga:pagePath",
"operator": "PARTIAL",
"expressions": [plate]
}
]
}
]
}
]
});
globalThis.c = r.reports[0].data.rowCount > 0? r.reports[0].data.rows[0].dimensions[0] : null
if (c === "google / cpc") {
globalThis.x = r.reports[0].data.rows[0].dimensions[1]
console.log(x)
return(x)
} else {
console.log(c);
return(c)
}
}

function doGet(e) {
const res = plateToCampaign(e.parameter.plate)
return ContentService.createTextOutput(JSON.stringify(res));
}
function test(plate) {
const url = {"my_web_app_url"}
return UrlFetchApp.fetch(url).getContentText();  
}

如果我将变量plate更改为单个值，那么它就起作用了，即函数plateToCampaign(plate={"fixed_value"}(。但是，该值每次都会有所不同。我该如何解决这个问题？

Answer 1

按照以下更正json后

var reportRequests = 
[
{
"viewId": "my_viewId",
"pageSize": 1,
"dateRanges": [
{"endDate": "today", "startDate": "30daysAgo"}
],
"metrics": [
{"expression": "ga:pageValue"}
],
"dimensions": [{"name": "ga:sourceMedium"}, {"name": "ga:campaign"}, {"name": "ga:dateHourMinute"}],
"orderBys": {"fieldName": "ga:dateHourMinute","sortOrder": "DESCENDING"},
"dimensionFilterClauses": [
{
"filters": [
{
"dimensionName": "ga:pagePath",
"operator": "PARTIAL",
"expressions": ["plate"]
}
]
}
]
}
]

例如，你可以通过这种方式更换车牌

function myfunction(item) {
var reportRequests =
[
{
"viewId": "my_viewId",
"pageSize": 1,
"dateRanges": [
{ "endDate": "today", "startDate": "30daysAgo" }
],
"metrics": [
{ "expression": "ga:pageValue" }
],
"dimensions": [{ "name": "ga:sourceMedium" }, { "name": "ga:campaign" }, { "name": "ga:dateHourMinute" }],
"orderBys": { "fieldName": "ga:dateHourMinute", "sortOrder": "DESCENDING" },
"dimensionFilterClauses": [
{
"filters": [
{
"dimensionName": "ga:pagePath",
"operator": "PARTIAL",
"expressions": [""]
}
]
}
]
}
]
reportRequests[0].dimensionFilterClauses[0].filters[0].expressions[0] = item
// include reportRequests in your own script ...
}

应用程序

试试这个

function plateToCampaign(item = 'plate') {
var myRequest =
[
{
"viewId": "my_viewId",
"pageSize": 1,
"dateRanges": [
{ "endDate": "today", "startDate": "30daysAgo" }
],
"metrics": [
{ "expression": "ga:pageValue" }
],
"dimensions": [{ "name": "ga:sourceMedium" }, { "name": "ga:campaign" }, { "name": "ga:dateHourMinute" }],
"orderBys": { "fieldName": "ga:dateHourMinute", "sortOrder": "DESCENDING" },
"dimensionFilterClauses": [
{
"filters": [
{
"dimensionName": "ga:pagePath",
"operator": "PARTIAL",
"expressions": [""]
}
]
}
]
}
]
myRequest[0].dimensionFilterClauses[0].filters[0].expressions[0] = item
globalThis.r = AnalyticsReporting.Reports.batchGet({
"reportRequests": myRequest
});
globalThis.c = r.reports[0].data.rowCount > 0 ? r.reports[0].data.rows[0].dimensions[0] : null
if (c === "google / cpc") {
globalThis.x = r.reports[0].data.rows[0].dimensions[1]
console.log(x)
return (x)
} else {
console.log(c);
return (c)
}
}