Google Sheets custom function not working when downloading as published CSV

Question

I've created a custom function in a Google Spreadsheet that populates a cell with a pipe-separated list of file paths based on a loop, a prefix, postfix and some cell references. This works great on the sheet itself.

But I've also published this sheet as a CSV using the "Publish to the web..." functionality. This also works, as long as I have the sheet itself open in my browser.

But once I close that browser window, after about 5 minutes (the publish refresh window I believe), those cells are populated with "#NAME?" in the downloaded CSV. It would seem the custom functions cannot be run in the "headless" mode of a published CSV file.

Is there a way around this?

Answer 1

Custom functions do not provide values to the published view as they run under the scope of the person accessing. For the published view, that is an anonymous visitor with no access to the script resources (hence the #NAME error).

The solution is to implement static value assignment from script functions. Depending on your spreadsheet usage (you provide no definitive information), an on edit or a time-based trigger may be used to invoke the customized calculations.

I suspect you will want a function that is triggered when the cells containing the file paths are edited, and writes to a given static range.

function onEdit(e) {
  // Ensure the edited cell was the correct sheet and column.
  var s = e.range.getSheet();
  if(s.getName() == ... && e.range.getColumn() == ... ) {
    // Your code called from here
  }
}