CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Martin Fedy Fedorko
Martin Fedy Fedorko

Reputation: 333

Google Spreadsheet On change write log to another sheet

please can anyone help with one problem in google spreadsheet?

After changing value in one concrete collumn in sheet "Venues", I would like to write log about name and time, when this value was changed. But I can't really realize , if I am working with spreadsheet "Venues" or some other. I am not very into class structure of google API for Spreadsheet. So can anyone help with it?

I need:

My hard try to write something: (but that is really bad code)

function onEdit(event)
{
  var sheet = event.source.getActiveSheet();

  var cell = sheet.getActiveCell();
  var cellR = cell.getRow();
  var cellC = cell.getColumn();
  var cellValue = cell.getValue();

  var cellCName = cell.getColumn()-1; //column with names
  var name = sheet.getRange(cellR, cellCName).getValue();//get name

  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if(sheet.getName() == "Venues"){
    if(cellC == 5 /* if correct collumn was changed */){

      var output_sheet = active_spreadsheet.getSheetByName("status_history");
      var lastRow = output_sheet.getLastRow();
      var lastRange = output_sheet.getRange(lastRow, 1)
      //HERE: write value: name
      var lastRow = output_sheet.getLastRow();
      var lastRange = output_sheet.getRange(lastRow, 2)
      //HERE: write value: time
    }
  }
}

Upvotes: 0

Views: 2712

Answers (1)

Mogsdad
Mogsdad

Reputation: 45740

You were getting there. Just a couple of tweaks needed.

With onEdit functions, you need to keep things fast, since they get invoked so often.

  • Rely on the event information as much as you can, avoiding calls to Google Apps services.
  • If you must use a service, do it only when you absolutely need to - for example, wait until you are past the if statements that tell whether you are in a cell you want to log before calling SpreadsheetApp.getActiveSpreadsheet().
  • The API is rich, so look for functions that will let you reduce the number of system calls you make - see how appendRow() replaced multiple statements, for example.

Here's your function after a code inspection:

function onEdit(event) {
  var sheet = event.range.getSheet();
  if(sheet.getName() == "Venues"){
    // correct sheet
    var cell = event.range;
    //var cellR = cell.getRow();  // not used at this time
    var cellC = cell.getColumn();
    var cellValue = event.value;

    if (cellC == 5) {
      // correct column

      var name = cell.offset(0,-1).getValue(); // get name, 1 column to left
      var time = new Date();                   // timestamp

      var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var output_sheet = active_spreadsheet.getSheetByName("status_history");
      output_sheet.appendRow([name,time]);
    }
  }
}

You could make it more flexible and portable by using column names to test conditions. Take a look at Adam's answer here.

Upvotes: 1

Related Questions