I’m struggling to see any console logs when using Logger.log in a custom function executed from a cell in Google Sheets. It seems that logging outputs don’t display while the function operates as a formula. What alternative methods can I use to debug or view outputs for custom functions in Google Sheets?
Below is my code which aims to tally occurrences of certain names:
/**
* Counts entries that match specified names
*/
function countMatches(dataset) {
console.log("function started");
var results = [];
var totalCount = 0;
for(var i = 0; i < dataset.length; i++) {
for(var j = 0; j < dataset[0].length; j++) {
if((dataset[i][j]) === "Sarah" || (dataset[i][j]) === "Sarah M") {
totalCount++;
results.push(dataset[i][j]);
console.log(dataset[i][j]); // This doesn't seem to produce any visible output
}
}
}
return totalCount;
}
The logging appears to function correctly when running the script directly from the editor, but no console output is visible when I invoke it as a formula with =countMatches(A1:C10).
I ran into this exact issue last month when debugging a complex pricing function. The workaround I found most effective was creating a separate debugging function that writes to a designated area of your sheet. You can modify your function temporarily to include something like SpreadsheetApp.getActiveSheet().getRange('Z1').setValue('Debug: ' + totalCount) at key points in your code. This way you can see what’s happening in real-time as the formula executes. Just remember to use a cell range that won’t interfere with your actual data. Once you’ve identified and fixed the issue, simply remove those debugging lines. It’s not elegant but it works reliably when you need to see intermediate values during formula execution.
yep, that’s how it works! console logs aren’t visible when the function runs as a formula. instead, you could use a piece of code like throw "debug: " + totalCount to see the output, or just return an array that includes both the count and your debug info.
Custom functions in Google Sheets don’t support Logger.log output when called from cells. I’ve dealt with this limitation many times. What I typically do is create a temporary debugging approach by modifying the return value to include debug information. For instance, you could temporarily change your return statement to something like return totalCount + " matches found, last item: " + results[results.length-1] to see both your result and some debug data directly in the cell. Another approach is to write debug values to specific cells in your sheet using SpreadsheetApp.getActiveSheet().getRange().setValue() method, though this makes your function no longer a pure custom function. Once you’ve verified your logic works correctly, you can revert to returning just the clean result.