How to make this google script work in several ranges?

Navigation
3

I have done a script of google sheet that writes me the modification date of a specific range of cells and now I want to make it detect the cell that I am modifying and detect if it is within a few ranges concrete.

This is the script that I have: 

function onEdit(HoraModificacion){
  var DateCol = "K9";
  var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
  var range = sheet.getRange('C11:K109');
  var rowIndex = range.getRowIndex();
  var colIndex = range.getColumnIndex();

  // May need to set up multiple ranges like this and check all ranges if
  // checked fields are not in adjacent cells
  var watchRange = { 
    top : 11,         // start row
    bottom : 109,     // end row
    left : 2,        // start col
    right : 12,        // end col
  };
  // Exit if we're out of range
  if (rowIndex < watchRange.top || rowIndex > watchRange.bottom) return;
  if (colIndex < watchRange.left || colIndex > watchRange.right) return;

  var currentValue = range.getValue();
  currentValue = currentValue.replace(/^\s+|\s+$/g,""); //"trim" kludge

  if (currentValue.length != 0){

    // Set a column to show last edit date
    SpreadsheetApp.getActiveSheet().getRange(DateCol).setValue(curDate);

  };
}

The problem is that I do not know how to detect the cell that I am modifying and from this it detects the range of some previously selected, I had planned to do something like the one below, but it does not finish me. The purpose of this is to have a series of controlled ranges, to know when I have modified them, each range has its own cell to write the date of the modification. 

function onEdit(HoraModificacion){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
    //Get the cell you are modifying to make a conditional for multiple range detect
    var activeCell = sheet.getActiveCell();
    //var activeRange = sheet.getActiveRange();
    var range1 = sheet.getRange('C11:K109');
    if ( activeCell == range1){
        var DateCol = "K9";
        var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
        var rowIndex = range.getRowIndex();
        var colIndex = range.getColumnIndex();

        // May need to set up multiple ranges like this and check all ranges if
        // checked fields are not in adjacent cells
        var watchRange = { 
            top : 11,         // start row
            bottom : 109,     // end row
            left : 2,        // start col
            right : 12,        // end col
        };
        // Exit if we're out of range
        if (rowIndex < watchRange.top || rowIndex > watchRange.bottom) return;
        if (colIndex < watchRange.left || colIndex > watchRange.right) return;

        var currentValue = range.getValue();
        currentValue = currentValue.replace(/^\s+|\s+$/g,""); //"trim" kludge

        if (currentValue.length != 0){

            // Set a column to show last edit date
            SpreadsheetApp.getActiveSheet().getRange(DateCol).setValue(curDate);

        };
    }else if{
    //Check the other range
    return;
    };
}

Based on what Ruben told me, I modified my script and now I have this: 

function onEdit(e){
  var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
  var colInicio = e.range.getColumn();
  var filaInicio = e.range.getRow();
  var ref = e.range.getA1Notation(); // Notación tipo A1 o A1:B2
  var range = e.getRange();
  var colIndex = colInicio.getColIndex();
  var rowIndex = filaInicio.getRowIndex();

  var watchRange1 = { 
    top : 11,         // start row
    bottom : 109,     // end row
    left : 3,        // start col
    right : 11,        // end col
  };

  var watchRange2 = { 
    top : 11,         // start row
    bottom : 109,     // end row
    left : 13,        // start col
    right : 21,        // end col
  };

  if(rowIndex >= watchRange1.top && rowIndex <= watchRange1.bottom && 
  colIndex >= watchRange1.left && colIndex <= watchRange1.rigth && 
  range.getValue() != 0){
    var DateCol1 = "K9";
    SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue(curDate);

 }else if (rowIndex >= watchRange2.top && rowIndex <= watchRange2.bottom && 
 colIndex >= watchRange2.left && colIndex <= watchRange2.rigth && 
 range.getValue() != 0){
    var DateCol2 = "U9";
    SpreadsheetApp.getActiveSheet().getRange(DateCol2).setValue(curDate);
 }else{
    //if test
    var DateCol1 = "K9";
    SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue("Fallo");
 }
}

When trying to execute this script I get this error: Unable to read the "range" property of undefined. (line 4, file "Dia1")

I'm still pretty stuck with the script

I detect that the problem is before if, but I can not solve it.

    
asked by Iván Montero 30.11.2018 в 07:24
source

3 answers

1

From the review 5 of the question

  

When trying to execute this script I get this error: Unable to read the "range" property of undefined. (line 4, file "Dia1")

The error is because you are directly executing the script, that is, without passing an event object, in this case e is undefined . In How do I test a trigger function in Google Apps Script it explains how to try simple triggers.

Another potential error that I could observe at first glance occurs on the line 

var range = e.getRange();

If e were the event object, it would not be a Spreadsheet or Sheet type object, instead of this line you could use 

var range = e.range;

However, I do not see much point in saving two characters, but that's a matter of style.

    
answered by 03.12.2018 / 16:55
source
2

NOTE: Answer corresponding to the revision 3 of the question. For the error entered in the review 5 of the question
  

When trying to execute this script I get this error: Unable to read the "range" property of undefined. (line 4, file "Dia1")

see my second response .

Short answer

To detect the range that is being edited use 

function onEdit(e){
  var colInicio = e.range.columnStart; // Número de fila de la primera columna del rango editado
  var filaInicio = e.range.rowStart; // Número de fila de la primera celda del rango editado
  var ref = e.range.getA1Notation(); // Notación tipo A1 o A1:B2
}

Extended response

The first thing to keep in mind is that you can edit more than one cell at a time.

I consider it very unusual to call HoraModificacion the function parameter onEdit or any other name reserved for simple trigger functions and even functions to be called by installable triggers. The usual is to use event or e .

The parameter of the onEdit function will be assigned an event object that includes the range that was edited among its properties. The name of this property is range . You can check the rest of the properties in link

It is worth mentioning that the event object of the "edit" trigger, in addition to the properties mentioned in the documentation, includes columnStart and rowStart that indicate the column and row respectively of the first cell of the edited range

    
answered by 30.11.2018 в 21:49
0

to detect the cell you can resort to the event itself using onEdit (e) as referred to by official aid

This example checks if the edited cell corresponds to or is within columns 2 to 6 

function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var rango = e.range; 
if (SpreadsheetApp.getActiveSheet().getName() == "Hoja 2") {
  if (rango.getColumn() >= 1 && rango.getColumn() <= 6) {
    ss.toast("Documento Actualizado." + "\n\nRango:" + rango.getA1Notation() + "\n\ncon valor:" 
    + rango.getValue(),"Actualizar Fecha en el Calendario Comercial",15); 
  } 
 }
}

If you add the method "getRow ()" to the second if , you can verify that it is within the range of rows you need to rate.

I hope you find it useful

    
answered by 30.11.2018 в 20:33
Problem using .clone () and select Problem CORS in Angular 2 and PHP