How can I include a variable in a json-format API call in Apps Script (javascript)?

Question

I'm working on a custom function for Google Sheets, that receives a cell value as input and spits out the response from an API call. Knowing thatin the current stage Google Analytics Reporting API can't be directly used with the custom function, I'm following this and this workaround.

The function that runs the API call to the Google Analytics Reporting API includes a JSON-formatted request with metrics, dimensions, filters, etc. The problem is that the dimension filter is variable and I want that to be the input from Google Sheets. However, I keep getting this error message:

GoogleJsonResponseException: API call to analyticsreporting.reports.batchGet failed with error: Field request.dimensionFilterClauses.filters.expressions is required. (line 2, file "Code")

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

If I change the variable plate to a single value then it works, ie function plateToCampaign(plate = {"fixed_value"}). However this value will be different every time. How do I fix this?

First, I deeply apologize that my answer was not useful. In your script, can you provide the sample value of e.parameter.plate? I think that in your script, "viewId": {"my_viewId"}, and plate = {"fixed_value"} are not correct. How about this? From If I change the variable plate to a single value then it works, ie function plateToCampaign(plate = {"fixed_value"})., your request body is not correct. For example, orderBys is required to be an array. So I'm worried that your provided script is different from the script for correctly replicating your issue. Can you confirm them again? — Tanaike
– Tanaike, Commented Feb 21, 2022 at 1:30
unfortunately I can't share my viewID here. I'm not sure how to provide a sample value of e.parameter.plate. How do I do that? An example for plate = {"fixed_value"} is plate = "XXX1234". If I replace the var plate with that (ie "expressions": ["XXX1234"] then I get the expected result. — Vasilis Tsiklos
– Vasilis Tsiklos, Commented Feb 24, 2022 at 15:21
Thank you for replying. Now, I noticed that an answer has already been posted. In this case, I would like to respect the existing answer. — Tanaike
– Tanaike, Commented Feb 24, 2022 at 22:30

Mike Steelson · Accepted Answer · 2022-02-24 16:22:34Z

0

After correcting your json as follows

  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"]
            }
          ]
        }
      ]
    }
  ]

you will be able to change plate for instance this way

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 ...
}

application

try this

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

edited Feb 24, 2022 at 16:22

answered Feb 21, 2022 at 5:58

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vasilis Tsiklos Over a year ago

I tried this but it gives me either the error "GoogleJsonResponseException: API call to analyticsreporting.reports.batchGet failed with error: Invalid value at 'report_requests[0].dimension_filter_clauses[0].filters[0].expressions' (expressions), Starting an object on a scalar field" or Invalid JSON payload received. Unknown name "": Root element must be a message.". The way I included your code on mine is by starting with this line where your comment is: var r = AnalyticsReporting.Reports.batchGet(reportRequests) and then I kept the rest of the code intact. Am I missing something?

Mike Steelson Over a year ago

I fixed reportRequests to be correct json and allowed a parameter (= that was the subject). But I have no way to work on globalThis.r = AnalyticsReporting.Reports.batchGet({... since I have no access to Google Analytics Reporting API. However, I'll try to work by analogy to give you a probable solution.

Mike Steelson Over a year ago

try what I mentionned after 'application'

Mike Steelson Over a year ago

@VasilisTsiklos : let me know of ot is ok!

Vasilis Tsiklos Over a year ago

Thank you Mike, tat was very helpful as it got rid of all the errors and returned a result for the first time. The problem now is that the script returns the correct result based on the first input, and after that it returns that same result regardless of the input which is wrong, as each input should yield different results. I tried creating an htlm file instead of using ContentService.createTextOutput but no joy. Any ideas on how to go about this?

|

Collectives™ on Stack Overflow

How can I include a variable in a json-format API call in Apps Script (javascript)?

1 Answer 1

application

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

application

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related