Writing a request JSON for fetchURL in Google Scripts: Script runs twice?

Question

sorry in advance for the long question. I am trying to create a Google Sheet that tells me how many hours each of my contractors has logged on Clockify each the month. (Full code at the bottom)

In short my problem is creating a JSON file for the UrlFetchApp.fetch() request to the Clockify API using input from the google sheet.

I want the JSON to look like this:

var newJSON = {
        "dateRangeStart": "2022-01-01T00:00:00.000",
        "dateRangeEnd": "2022-01-31T23:59:59.000",
        "summaryFilter": {
          "groups": ["USER"],
          "sortColumn": "GROUP"
        }
      }
var payload = JSON.stringify (newJSON);

And when I use this code, it works perfectly. However, the start and end dates are variables that I compute in the google sheet, as I need these dates to change each month. I wrote a function that gives me the correct outputs ("2022-01-01T00:00:00.000", "2022-01-31T23:59:59.000"), but when I reference the cells in google sheets, I get a 400 error saying that the API was not able to parse the JSON.

Function in Script:

function GetHours(userName, startDate, endDate) {

  var newJSON = {
        "dateRangeStart": startDate,
        "dateRangeEnd": endDate,
        "summaryFilter": {
          "groups": ["USER"],
          "sortColumn": "GROUP"
        }
      }

  var payload = JSON.stringify (newJSON); 
...}

Calling the function in sheets:

=GetHours(C3,$D$45,$D$46)

Output error message:

Exception: Request failed for https://reports.api.clockify.me returned code 400. Truncated server response: {"code":400,"message":"generateSummaryReport.arg1.dateRangeEnd: Field dateRangeEnd is required, generateSummaryReport.arg1.dateRangeStart: Field da... (use muteHttpExceptions option to examine full response)

A weird thing is happening when I use Logger.log(payload), which may be the root of the problem. It appears that the code runs twice, and the first time the payload JSON is correct, but the second it is incorrect.

First time: {"dateRangeStart":"2022-01-01T00:00:00.000","dateRangeEnd":"2022-01-31T23:59:59.000","summaryFilter":{"groups":["USER"],"sortColumn":"GROUP"}}

Second time: {"summaryFilter":{"groups":["USER"],"sortColumn":"GROUP"}}

I have tried a bunch of solutions, but really it boils down to referencing the Google sheet in the JSON. When I copy and paste the output of my date calculation into the JSON, it works. When I create a variable in Scripts with the date calculation output, it works. When I return startDate, it gives me "2022-01-01T00:00:00.000", which is correct. I just don't understand what is going wrong. Thanks for your help!

Full code:

const APIbase = "https://api.clockify.me/api/v1"
const APIreportsbase = "https://reports.api.clockify.me/v1"
const myAPIkey =  "[HIDDEN FOR PRIVACY]"
const myWorkspaceID = "[HIDDEN FOR PRIVACY]"

function GetHours(userName, startDate, endDate) {

  var newJSON = {
        "dateRangeStart": startDate,
        "dateRangeEnd": endDate,
        "summaryFilter": {
          "groups": [
            "USER"
          ],
          "sortColumn": "GROUP"
        }
      }

var payload = JSON.stringify (newJSON);
  var headers = {"X-Api-Key" : myAPIkey, "content-type" : "application/json"};
  var url = APIreportsbase + '/workspaces/' + myWorkspaceID + '/reports/summary'

  var options = {
    "method": "post",
    "contentType": "application/json",
    "headers": headers,
    "payload": payload,
    "muteHttpExceptions" : false
  };


  var response = UrlFetchApp.fetch(url, options)
  var json = response.getContentText();
  var data = JSON.parse(json);
  var people = data.groupOne;


  for (let i = 0; i < people.length; i++) {

    if (people[i].name == userName) {

      if (people[i].duration == 0) {
        return 0;
      } else {
        return people[i].duration/3600;
      }
   } 
  }
}

GetHours();

Answer 1

I got the program working by adding filter so that the second time the program ran, it didn't affect the return value.

if (startDate != null) {
  var response = UrlFetchApp.fetch(url, options)
  var json = response.getContentText();
  .....
}