How to create a log report inside your gSheet.

 

Let’s say your shared google spreadsheet uses multiple scripts to automate processes and you need a way to notify its users of the status of each one.

 

Why not create a dedicated cell that will store all latest notifications of the script’s executions?

 

We will need to use Logger to create custom messages inside our scripts.

So the idea is simple, use Logger to create custom messages during the execution of the script, and display them all into a cell. It would also be nice to keep a short history of the previous executions’ loggers.

 

Here is an example using Google Apps Scripts:

 

// Display the log of the last run in spreadsheet "Settings".
function log_report() {
  const cell = "B3";
    
  // Get your sheet by name.
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
 
  // Fill 'Execution Report' in spreadsheet "Sheet1".
  var old_value = ss.getRange(cell).getValue();
  // Each cell has a limit of 50000 characters.
  // So we set the max number of lines to be displayed to 100, meaning max of 500 characters per line.
  // Where each line is actually a message from Logger.log().
  var max_lines = 100;
  
  // Split the text by line
  var old_value_split = old_value.split("\n");
  var num_of_lines = old_value_split.length;

  // Keep the last 'max_lines'.
  if (num_of_lines > max_lines) {
    var last_lines = old_value_split.splice(-max_lines, max_lines);
    old_value = last_lines.join("\n");
  }

  // Update the local execution report.
  ss.getRange(cell).setValue(old_value + "\n" + Logger.getLog());
}

 

And we call this function at the end of our main function:

 

function main() {
  // Code...

  Logger.log("Message 1")
   
  // More code ...
  
  Logger.log("Message 2")
  
  // Even more code...
  
  log_report()
}

 

 

We could even use another spreadsheet to store much more logs.

 

 

In this case, each execution’s report will be stored into a separate cell.

 

function log_report() {
  // The id of the log spreadsheet.
  const log_file_id = "";

  var ss_log = SpreadsheetApp.openById(log_file_id).getSheets()[0];
  // Update the log file.
  ss_log.appendRow([Logger.getLog()])
}

 

 

Together with a local script into the log spreadsheet to keep an upper limit of the total rows.

 

function clean_up() {
  var ss_log = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  // Set the max number of total rows.
  var max_rows = 20000;
  var last_row = ss_log.getLastRow();
  if (last_row - max_rows > 0 ) {
   ss_log.deleteRows(1, last_row - max_rows);
  }  
}

 

And why not combine both of them?

 

 

That’s a simple way of creating and using reports of you executions other than using the official Stackdriver Logging.