CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
FeatherDuster
FeatherDuster

Reputation: 36

Why is my edit issue affect other columns?

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();
};

Upvotes: 0

Views: 88

Answers (1)

Tedinoz
Tedinoz

Reputation: 7959

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.

Upvotes: 1

Related Questions