JavaScript variable (or structured data) to google sheets when scripting online? Anything beyond Functions?

Question

We can write custom functions in javascript, which we call from Google Sheets standard GUI: e.g.

=myFunction(a, b, 'c')

But is there a way of accessing a data structure directly, not via a function? For example I want to have some structured javascript data/objects, and retrieve data in something like below, without using a function approach?

=friends['mike'].birthday

Answer 1

No, there is no support for that. You can only access non-private functions declared with function from Sheets. Review the "custom functions guide" for the exact limitations.

Further, there is no persistent modification of globals as each invocation is from a "clean slate" as defined by your literal project text. So in your example, your "friends" object would need to be completely static to be used, or would need to be a non-global that is instantiated by the accessing function, e.g. deserialized from either CacheService or PropertiesService, or loaded from some remote endpoint.

var friends = {
  "mike": {
    birthday: new Date("..."),
     ...
  },
  ...
};
function FRIENDINFO(friend, property) {
  if (friends[friend]) {
    return friends[friend][property];
  }
  return "No friend named " + friend;
}

Note that using PropertiesService, CacheService, or UrlFetchApp in a custom function can quickly result in poor performance and quota breach, unless you design your function for range input and range output.

Lastly, even if you load the data dynamically, it is expected that the result of a custom function is deterministic, i.e. dependent only upon the input function arguments. So even if your back-end data updates, there is no requirement for the displayed data output by the custom function to update.