logToSpreadsheet.js

/**
 * Logs information to a Google Spreadsheet for tracking and debugging purposes.
 *
 * @param {string} functionName - The name of the function being logged.
 * @param {string} functionState - The state or status of the function being logged.
 * @param {string|object} message - The log message to be recorded, which can be a string or an object.
 * @param {string} spreadsheetUrl - The URL of the logging spreadsheet.
 */
function logToSpreadsheet(functionName, functionState, message, spreadsheetUrl) {
  // Get the active sheet of the Google Spreadsheet
  const sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getActiveSheet();

  // Determine the row to write data by getting the last row and adding 1
  const row = sheet.getLastRow() + 1;

  // Define the starting column as 1
  const column = 1;

  // Convert the message to a string if it is an object
  let messageString = message;
  if (typeof message === 'object' && message !== null) {
    messageString = JSON.stringify(message, null, 1);
  }

  // Get the current timestamp in the "America/New_York" timezone
  const timestamp = Utilities.formatDate(new Date(), "America/New_York", "yyyy-MM-dd HH:mm:ss");

  // Define the batch size for splitting the message
  const batchSize = 49000;

  // Initialize the current row and the remaining message
  let currentRow = row;
  let remainingMessage = messageString;

  // Split and write the message in batches until there is no remaining message
  while (remainingMessage.length > 0) {
    // Get the next batch of the remaining message
    const batch = remainingMessage.substring(0, batchSize);

    // Write the batch to the spreadsheet
    sheet.getRange(currentRow, column + 3).setValue(batch);
    sheet.setRowHeightsForced(currentRow, 1, 21);

    // Move to the next row and update the remaining message
    currentRow++;
    remainingMessage = remainingMessage.substring(batchSize);
  }

  // Write the timestamp, function name, and function state to the spreadsheet
  sheet.getRange(row, column).setValue(timestamp);
  sheet.getRange(row, column + 1).setValue(functionName);
  sheet.getRange(row, column + 2).setValue(functionState);
}

function logToSpreadsheet_test() {

  let thisFunctionName = 'logToSpreadsheet_test'
  let spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit'

  function generateHelloWorld() {
    let helloWorld = "Hello World ";
    let finalString = "";
    for (let i = 0; i < 10000; i++) {
      finalString += helloWorld + i + '\n';
    }
    return finalString;
  }

  logToSpreadsheet(thisFunctionName, "Generating Hello World string", generateHelloWorld());
  logToSpreadsheet(thisFunctionName, "Success", "The function ran successfully.");
}