google.script.run.withSuccessHandler does not return value

Question

it is getting me crazy, the code was working yesterday, but not anymore. I tried to check all syntax again, but the issue still persists. this server-side request from Google Sheets, shows value on server side (Logger.log()), but returns null in client side.

function supervisorLine(lineData) {
  if (lineData == 'Name Value is not VALID!') {
    console.log("Supervisor Name Issue!");
  } else {
    document.getElementById('Team').value = lineData[7];
    document.getElementById('Shift').value = lineData[12];
    document.getElementById('action').classList.remove("disabled");
    console.log("team " + lineData[7] + " shift " + lineData[12]);
    ////////////////////////////////// need to be Moved somewhere after password check!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    google.script.run.withSuccessHandler(function(quickStat2) {
      console.log(quickStat2)
    }).loginRecords(lineData[7], lineData[12]);
  }
}

this is my server side code as well:

function loginRecords(team, shift) {
  var sh = ss.getSheetByName("Attn Rec");
  var result = [];
  var matchRec = sh.getRange("a2:l" + sh.getLastRow()).getValues().filter(function(a) {
    return a[1] === shift && a[4].valueOf() == team.valueOf()
  });
  uniqeLogin = removeDups(matchRec.map(function(a) {
    return a[9]
  }));
  // Logger.log(uniqeLogin);
  uniqeLogin.forEach(function(a) {
    var ary = [team, 0, shift, "", 0, 0, 0, 0, 0];
    matchRec.forEach(function(r) {
      if (r[9] === a) {
        ary[1] = new Date(r[0]);
        ary[3] = r[8];
        switch (r[3].toString().toLowerCase()) {
          case "off-site work":
          case "hr action":
            ary[8]++;
            break;
          case "present":
          case "late transfer":
          case "transfer":
            ary[4]++;
            break;
          case "no show":
            ary[5]++;
            break;
          case "Sick":
          case "vacation":
            ary[7]++;
            break;
          case "late":
          case "approved delay start":
            ary[6]++;
            break;
        }
      }
    });
    result.push(ary);
  });
  Logger.log(result);
  return result;
}

for recap, Logger.log(result) returns the array I needed, but console.log(quickStat2) returns null.

Twittery

Answer 1

I bumped into this issue some time ago, and it also nearly drove me mad (oh, the joys of loosely-typed JavaScript!). The problem is that you are trying to return an unacceptable type of data to client-side. Functions called via google.script.run have restrictions on what they can return (for example, you should avoid Date instances).

Restricted types

Currently, you can't return (take a look at the documentation for detailed explanation of restrictions):

1. Date instances;
2. any Function;
3. DOM elements (though <form> is permitted);

Solution

Changing ary[1]= new Date(r[0]); to ary[1] = r[0]; should do the trick, simply move Date parsing to the client.

Balmuth

@k.b my pleasure - always a joy to see this on a older post :) It is a very subtle limitation of client-server comms in Apps Script, and to this day I do not know what is the reasoning behind this. The runner function could at least attempt to stringify the value instead. It would also correlate to how we deal with dates, objects in general, etc., in our day-to-day with normal forms. – Balmuth

You're an absolute legend. Thank you! How would you go about sending the date format instead? What's the typical method? Do you stringify it, turn it into a number or something similar? – Cressi

@Cressi it's mostly just about project setup - usually, I just go with the milliseconds from *nix epoch (obtained from valueOf method), but sometimes the date is retrieved on the client-side as a ISO 8601 string (in UTC) which is convenient to send to the backend too. Both approaches parse back nicely with the Date constructor. – Balmuth

Answer 2

For me it was related to Google's black-box serialization process. If the value originates from a range.getValues() call or other Google API it might not be plain objects/arrays/primitives, there might be complex underlying objects or unexpected Dates that fail the native serialization.

Forcing a regular JS serialization avoids the issue.

return JSON.parse(JSON.stringify(obj));

full example:

function getFirstCellInfo() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB");
  const values = sheet.getRange(1, 1).getValues();
  const row = values[0];

  const cellInfo = {
    value: row[0],
    valueType: typeof row[0]
  };

  const serializedCellInfo = JSON.parse(JSON.stringify(cellInfo));
  // serializedCellInfo is guaranteed to be a plain JS object
  return serializedCellInfo;
}

Bramlett