Apps-Script. Log into Google Sheets

Few snippets to write logs into Google Sheets.

Minimal usage code:

function test_write2SpreadsheetLog() {
  var data = {"hello": "world"};
  write2SpreadsheetLog_({data: data});
  write2SpreadsheetLog_({data: data, sheetName: 'logs'});
}

The code:

var C_REMEMBER = {
  ssId: '1234567990-ABCDEFGHIjKLMNOPQR_StuVWXYZAbcDef' // !!! CHANGE
}


// function test_write2SpreadsheetLog() {
//   var data = {"hello": "world"};
//   write2SpreadsheetLog_({data: data});
//   write2SpreadsheetLog_({data: data, sheetName: 'logs'});
// }
/** write2SpreadsheetLog_
 * 
 * @param {String} ssId
 * @param {String} sheetName
 * @param {Array} datarow, 1D array
 * @param {any} data
 */
function write2SpreadsheetLog_(options) {
  var ssId = options.ssId || C_REMEMBER.ssId;
  var ss = SpreadsheetApp.openById(ssId);
  var s;
  if (options.sheetName) {
    s = ss.getSheetByName(options.sheetName);
  } else {
    s = ss.getSheets()[0];
  }

  if (!options.datarow) {
    options.datarow = getStandardDataRow_(
      options.data);
  }

  s.appendRow(options.datarow);
  return 0;
}

/** getStandardDataRow_
 * 
 * @param {any} data
 */
function getStandardDataRow_(data) {
  var datarow = [
    new Date(),
    Session.getEffectiveUser().getEmail(),
    data2String_(data)
  ];
  return datarow;
}


// function test_data2String() {
//   var d = 'Hello';
//   console.log(data2String_(d))
//   d = new Date();
//   console.log(data2String_(d))
//   d = ['a', 'b'];
//   console.log(data2String_(d))
//   d = {"a": 100};
//   console.log(data2String_(d))
// }
/** data2String_
 * 
 *  @param {any} data
 */
function data2String_(data) {

  if (typeof data === 'string') {
    return data;
  }

  return JSON.stringify(data, null, 2)
}

This will help if you are working with a WebApp and wish to log the progress somewhere. The code execution is ~1 sec.

Result

As a result, you may pass a JSON, or any other data, the script will log it, the user email, and the date.

Photo by Timon Studler on Unsplash

Blabla-Words

Logging data into Google Sheets provides an efficient way to organize, store and analyze data. It allows for easy collaboration amongst team members, as well as the ability to access data from anywhere. Additionally, the spreadsheet platform is highly customizable and can be used to create visualizations and interactive charts. Google Sheets also has powerful functions and formulas allowing faster data analysis.

About the author