Google Sheets – Insert Date Automatically When Checkbox is Ticked

data validationgoogle sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I have a simple sheet that has two columns with tick boxes in them (columns 5 and 8) The goal is when the box is ticked the date and time are inserted in the column next to it. I have got it working correctly with one column with the following code:

function onEdit(e) {
  var aCell = e.source.getActiveCell(), col = aCell.getColumn(); 
  if(col == 5) {
    var adjacentCell = aCell.offset(0, 1);  
    var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy kk:mm:ss");
    adjacentCell.setValue(newDate);
  }
}

For the life of me, I can't work out how to modify that so that it works with column 8 as well. Any help will save me tearing my hair out!

Best Answer

you can simply just double it:

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { 
    var r = s.getActiveCell();
    if( r.getColumn() == 5 ) { 
      var nextCell = r.offset(0, 1);
      var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy hh:mm:ss");
      nextCell.setValue(newDate);
    }
    if( r.getColumn() == 8 ) { 
      var nextCell = r.offset(0, 1);
      var newDate1 = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy hh:mm:ss");
      nextCell.setValue(newDate1);
    }
  }
}

Code

function onEdit(e) {
  var aCell = e.source.getActiveCell(), col = aCell.getColumn(); 
  if(col == 2 || col == 4 || col == 6) {
    var adjacentCell = aCell.offset(0, 1);  
    var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy kk:mm:ss");
    adjacentCell.setValue(newDate);
  }
}

Example

I've created an example file for you: Adjacent Cell
Add the code to the script editor (Tools > Script editor...), press the save button and you're on the go !!

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google Sheets – Auto Date Added to Adjacent Cells When Data is Entered

Code

Example

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Related Topic