Google-sheets – How to extend script on next column

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

How should I modify the script, in a way that it would work also in another column, eg. by putting data in A:A date will be printed in B:B and also in C:C

function onEdit() {
  var sheetName = "MySheet";
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() !== sheetName) return;
  var r = s.getActiveCell();
  if( r.getColumn() != 2 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT+05:30", "dd-MM-yyyy, hh:mm a");
    SpreadsheetApp.getActiveSheet().getRange('B' + row.toString()).setValue(time);
  };
 };

Best Answer

try this:

function onEdit() {
  var sheetName = "MySheet";
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() !== sheetName) return;
  var r = s.getActiveCell();
  if( r.getColumn() != 2 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT+05:30", "dd-MM-yyyy, hh:mm a");
    SpreadsheetApp.getActiveSheet().getRange('B' + row.toString()).setValue(time);
  };
  if( r.getColumn() != 3 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT+05:30", "dd-MM-yyyy, hh:mm a");
    SpreadsheetApp.getActiveSheet().getRange('C' + row.toString()).setValue(time);
  };
 };

Related Solutions

Google-sheets – Script to only show timestamp when a specific column is changed

This script checks if column B is updated, then updates the last column in that row with a timestamp:

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

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  if (cindex == 2) { // 1 == Column A, 2 == Column B, 3 == Column C, etc.
   var dateCol = sheet.getLastColumn();  
   var lastCell = sheet.getRange(index,dateCol);
   var date = Utilities.formatDate(new Date(), "EST", "MM-dd-yyyy' 'HH:mm:ss' _'");
   lastCell.setValue("'" + date);
  }
}

Note that the term "last column" will mean that, when starting with an empty spreadsheet, the value you enter in e.g. column B will be overwritten with the timestamp, since column B will be the "last column".

I set up an example spreadsheet to demonstrate. Feel free to copy it.

Google-sheets – Check from spreadsheet and add into Calendar, and place a controller not to repeat old added values

Here is how I come up with a solution to my this problem. In case in future anybody gets stuck in something like this, hope this solve his/her problem and gets job done. :)

Google Script:

    function kalender() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 3");
  var lrow = ss.getMaxRows();
  var lcol = ss.getMaxColumns();
  var rng = ss.getRange(1, 1, lrow, lcol);
  var data = rng.getValues();

  for(var i=1; i< 100; i++){
    if(data[i][5] == 'new'){

      var subject = data[i][2] + " with " + data[i][1];
      var startdate = data[i][3];
      var enddate = data[i][4];
   //   var enddate = new Date(addDays(startdate,times));
      var event = CalendarApp.getDefaultCalendar();
      event.createEvent(subject, startdate, enddate);
      Logger.log('Event ID: ' + event.getId());

      data[i][5] = "added to calendar";
      rng.setValues(data);
    }
  }
}

Best Answer

Related Solutions

Google-sheets – Script to only show timestamp when a specific column is changed

Google-sheets – Check from spreadsheet and add into Calendar, and place a controller not to repeat old added values

Related Topic