7

I am writing a custom function to be used in a spreadsheet and I would like to be able to at least display some data. Few things seem to work, for example Browser.msgBox doesn't find the appropriate permissions.

Breakpoints don't interrupt execution.

I had some hope for this code

function test() {
  var s = "test"
  Logger.log(s)
  return s + s
}

But when I set a cell in the spreadsheet to "=test()" the cell properly shows the value "testtest" but when I return to the script editor and use view>execution transcript or view>logs I don't see anything.

Perhaps this logging goes to a special file somewhere?

Improve this question
1

3 Answers 3

Reset to default
1

When you use a custom function as a formula, it can be evaluated and re-evaluated at many times. Therefore, it is not appropriate to fill up the Logging output or the Execution Transcript with this. If you want to debug, you must run (or debug) the script manually from the script editor.

Take an example, where you have two custom functions - f1() and f2() And say, in cell A1, you enter the formula =f1() and in A2, you enter =f2(A1). In such a case, both the cells will be re-evaluated. So what should the logger output show ?

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

3 Comments

Thanks. I have checkmarked this. Other newbies like me might benefit from some additional advice on how to test a custom function with arguments. One obvious solution is to create a "tester" function that calls the function to be tested providing an argument.
"it is not appropriate to fill up the Logging output or the Execution Transcript"... that's exactly what happens actually. See my answer.
Other people who find this -- what I was having trouble understanding was the shape of my input arguments, which is hard to test directly in the App Script console. This isn't ideal, but the solution I found was to manually throw errors. Your error message will be printed in the mouseover, allowing you to essentially do console.log() debugging, albeit with only one log per run. Not ideal, but it helped me figure out my problem!
0

As mentioned by the accepted answer, App Script functions don't produce any logs when they're evaluated as formulas. The dropdown menu in the Executions tab doesn't allow clicking into the logs view:

enter image description here

You can run the function from the App Script console, but that doesn't work if the problem is based on how it's being used in the sheet. In my case, I didn't realize that even one-column ranges are provided as 2D arrays.

Paying it forward -- the solution I found to test this was throwing Exceptions and treating them like console.log()s:

error message in spreadsheet cell saying "hello, bug"

Your exception message will get printed into the tooltip, which lets you get some info out of the failed execution. You essentially get one log per run. It accepts pretty long failure messages, too, so you could try to do something clever with adding more log messages during the execution until you get to your problem area.

Wish there was a better answer, but this helped me figure out my problem!

Improve this answer

2 Comments

I added a screenshot explaining what I see -- the Cloud logs aren't available, you can't click on the Execution to get any more information.
The "Cloud logs" button might be disabled, but when I left clicked the row, it expanded down and I could see the logs, just like you can see in Dan Dascalescu's answer.
-1
  1. In your Google Sheet, go to Extensions -> Apps Script
  2. In the sidebar choose ≡ -> Executions
  3. This tab contains the history of all scripts runs. Click the most recent run and you'll see the logs output via console.*.

Executions in Apps Script

Improve this answer

2 Comments

This isn't answering the question asked. Your App Script is hooked up to the onSelectionChange simple trigger, which happens whenever the user clicks somewhere. What's being asked about is when a Script's functions get used in a formula (eg =my_function(A:A)). In this case, like the correct answer says, the Execution tab shows the run, but the "Cloud logs" button is disabled. If I could put images in comments, I'd demonstrate.
I didn't setup any trigger, and I still have plenty of "Custom Function" executions. When I left click them I can also see the logs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.