Why is my edit issue affect other columns?

Question

Link to my sheet: https://docs.google.com/spreadsheets/d/1gkWCU0fHotORi-urOfuHjGGiqrnYcdtZQ9bZhm7XLIk/edit?usp=sharing

Question: How can I fix an edit issue when deleting dates or unchecking a checkbox without it affecting other columns. (please see images)

How my 'Function' button works:
Menu panel: Function > Insert New Column Insert as many columns as I want by manipulating the script.

How my AutoTimeStamp script works: Once checkboxes are checked (Column C), Dates automatically apply to Column D.

How my 'Copy' script works: Once Insert New Column is activated, it makes a copy of Columns C and D from left to right. This also applies the TimeStamp feature to every newly created column.

Function Button:

Newly Created Columns

Clicking checkboxes and autodating is successful

Error When deleting dates

Error when on last column

Here is the Insert New Column Function:

var ss = SpreadsheetApp.getActive();

function onOpen() {      
  var menu = [{name:"Insert New Columns", functionName:"addColumn"}];
  ss.addMenu("Functions", menu); 
}

function addColumn() {    
  var sh = ss.getActiveSheet(), lCol = sh.getLastColumn(); 
  var lRow = sh.getLastRow(), range = sh.getRange(1,lCol-1,lRow,2);
  sh.insertColumnsAfter(lCol,10);
  var newRange = sh.getRange(1,lCol+1,lRow,10);
  var columnWidths = SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS
  range.copyTo(newRange);
  range.copyTo(newRange,columnWidths,false);
  newRange.setFormulas(newRange.getFormulas());
}

Here is the AutoTimeStamp script:

var COLUMNTOCHECK = 3;
var DATETIMELOCATION = [0, 1];
var SHEETNAME = 'Training Dash'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    if( selectedCell.getColumn() >= COLUMNTOCHECK) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
      }
  }
}

This is the Copy script along side some macros.

function Copy() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 2);
  spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 2).activate();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn(), sheet.getMaxRows(), 1).activate();
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn() - 2, sheet.getMaxRows(), 2).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

function UntitledMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 2);
  spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 2).activate();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn() - 2, sheet.getMaxRows(), 2).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn(), sheet.getMaxRows(), 2).activate();
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn() - 2, sheet.getMaxRows(), 2).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
};
function UntitledMacro1() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn(), sheet.getMaxRows(), 2).activate();
  spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 2);
  spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 2).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet15'), true);
  spreadsheet.getCurrentCell().offset(-2, 0, 17, 2).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Training Dash'), true);
  spreadsheet.getCurrentCell().activate();
  spreadsheet.getRange('Sheet15!A1:B17').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn(), sheet.getMaxRows(), 2).activate();
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, spreadsheet.getCurrentCell().getColumn() - 2, sheet.getMaxRows(), 2).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
};
function UntitledMacro2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E:N').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 10);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 10).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet15'), true);
  spreadsheet.getRange('A1:B17').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Training Dash'), true);
  spreadsheet.getRange('E1:N1').activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getRange('Sheet15!A1:B17').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('E:N').activate();
  spreadsheet.getRange('O1:P16').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet15'), true);
  spreadsheet.getActiveSheet().hideSheet();
};

Can I ask you about your situation? 1. What is ``This is the Copy script along side some macros``? It seems that these are not used at ``onOpen`` and ``onEdit`` event functions. 2. About ``Error When deleting dates`` and ``Error when on last column``, if a cell is edited, the error occurs, it is required to check the function of ``onEdit``. At ``onEdit``, the column is checked. But the row is not checked. Is this related to the issue? 3. Can you check the shared Spreadsheet again? In my environment, I couldn't get it. If these questions are not useful for your situation, I'm sorry. — Tanaike, Dec 15 '18 at 01:34
The `function Copy()` along side some macros, work in tangent with one another, my co-worker and I came up with this broken looking method to copy formulas within Column C and Column D. Ted has answered my question and has resolved the issue. The problem lies within my Auto Time Stamp script. As far as your questions, I found them very helpful as I tend to think the same as a way to aggressively figure things out. Also, this post was revised to be as minimal as possible, upon your return here, my situation will be asked a tad bit differently. — FeatherDuster, Dec 17 '18 at 14:59
@Tanaike Did you want access to the worksheet? Please let me know if this would be useful to you. — FeatherDuster, Dec 17 '18 at 15:03
Thank you for replying. I noticed that the answer has already been posted. I think that it will resolve your issue. — Tanaike, Dec 17 '18 at 22:33

score 1 · Answer 1 · answered Dec 15 '18 at 04:20

The problem lies in this line of code in OnEdit(e)
`if( selectedCell.getColumn() >= COLUMNTOCHECK) { `

This seeks to determine only if the column number of the selected cell is greater than or equal to 3. Whether the column contains a checkbox or a date is not considered. So when a date is deleted, OnEdit kicks in, evaluates that the column is greater than 3, and so puts a datestamp in the cell adjacent to the selected cell.

The solution is to check whether the column of the selected cell is an odd or even number. Column numbers containing checkboxes are odd, column numbers containing datestamps are even. So, if the column number is odd, then OnEdit should proceed as usual, but if the column number is even, then OnEdit should do nothing.

The test for odd/even is done using modulus. Refer this extensive discussion.

Replace the problem code with this line:
` if (selectedCell.getColumn() >= COLUMNTOCHECK && selectedCell.getColumn() % 2 == 1) {`

The differences are:
&& - meaning "AND" - the code is now evaluating two outcomes
selectedCell.getColumn() % 2 == 1 - an outcome of 1 (one) identifies an odd number.

Oh my god! IT FREAKING WORKS! and DUH!! I had known about the odd and even number affect, however I did not even place that on my mind for I thought the problem lied within my `function Copy()`. Ok, I can use this information to build out better `if` functions and I will take what you have empowered me with and continue to learn through this. I can't thank you enough for all you've done Ted, it's been an honor communicating with you on some of my projects. The information is priceless! Have a great day man! — FeatherDuster, Dec 17 '18 at 14:45

Why is my edit issue affect other columns?

1 Answers1