Troubleshooting console output in Google Sheets formulas with Logger.log

Other Questions Google Sheets
1

I’m having trouble getting console output when using Logger.log in a Google Sheets formula. I’ve tried adding it to my code, but nothing shows up. Here’s a snippet of what I’m working with:

function countNames(sheet) {
  console.log('Starting function');
  let nameCount = 0;
  let foundNames = [];

  sheet.forEach((row, i) => {
    row.forEach((cell, j) => {
      if (cell === 'John' || cell === 'Johnny') {
        nameCount++;
        foundNames.push(cell);
        console.log(cell); // Trying to log here, but nothing appears
      }
    });
  });

  return nameCount;
}

Any ideas on how to fix this? I’m stumped and could really use some help!

2

I’ve run into this issue before, and it can be frustrating! The problem is that Logger.log() and console.log() don’t work directly in custom functions for Google Sheets. Instead, try using the SpreadsheetApp.getActiveSpreadsheet().toast() method to display messages. Here’s how you can modify your code:

function countNames(sheet) {
  SpreadsheetApp.getActiveSpreadsheet().toast('Starting function');
  let nameCount = 0;
  let foundNames = [];

  sheet.forEach((row, i) => {
    row.forEach((cell, j) => {
      if (cell === 'John' || cell === 'Johnny') {
        nameCount++;
        foundNames.push(cell);
        SpreadsheetApp.getActiveSpreadsheet().toast('Found: ' + cell);
      }
    });
  });

  return nameCount;
}

This will display small pop-up messages in the bottom-right corner of your spreadsheet. It’s not ideal for extensive debugging, but it’s a workable solution for quick checks. For more detailed logging, consider writing to a separate sheet or using the Execution transcript in the Script editor.

3

While the toast() method can work, it’s not ideal for debugging complex scripts. A more effective approach is to use the built-in Apps Script debugger. Here’s what you can do:

  1. Open the script editor in Google Sheets.
  2. Set breakpoints in your code by clicking on the line numbers.
  3. Run the function in debug mode using the bug icon or by pressing Ctrl+Enter.

This way, you can step through your code line by line and inspect variables at each stage. It’s much more powerful than simple logging and will help you understand exactly what’s happening in your function.

Additionally, you can use Logger.log() within the script editor environment, and then view the logs in the ‘Execution log’ after running the script manually. This won’t work when the function is called from a cell, but it’s useful during development.

4

hey, have u tried using the execution transcript? it’s in the view menu of the script editor. run ur function there n it’ll show all the Logger.log outputs. way easier than messing with toast() or the debugger imho. just remember to clear the logs before each run or it gets messy fast!