Help with Google spreadsheet script

Question

Help with Google spreadsheet script

Navigation

#1 by (2 votes)

2

I'm trying to make a script for the Google spreadsheet but I'm stuck and it does not work for me.

My intention is to make a script that detects the cell that I am modifying, to later, see if it is within specific ranges and depending on the range, I write in a specific cell the time of modification of that range.

This is the script that I have done at the moment:

function onEdit(e){
  var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
  var range = e.range;
  //var colInicio = e.range.getColumn();
  //var colInicio = e.getColumn();
  //var filaInicio = e.range.getRow();
  //var filaInicio = e.getRow();
  //var activeRange = e.getActiveRange();
  //var ref = e.getA1Notation(); // Notation tipe A1 or A1:B2
  var ref = range.getA1Notation;
  var colIndex = ref.getColIndex;
  var rowIndex = ref.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 && 
  e.getValue() != 0){

    var DateCol1 = "K9"; //Cell you want to have the date
    SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue(curDate); 
    //Write the date in the cell

  }else if (rowIndex >= watchRange2.top && rowIndex <= watchRange2.bottom && 
  colIndex >= watchRange2.left && colIndex <= watchRange2.rigth && 
  e.getValue() != 0){

    var DateCol2 = "U9"; //Cell you want to have the date
    SpreadsheetApp.getActiveSheet().getRange(DateCol2).setValue(curDate); 
    //Write the date in the cell

  }else{

    var DateCol1 = "K9";
    SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue("Fallo");

  };
}

I think the bug is before the if, but I can not find out.

google-apps-script

asked by Iván Montero 10.12.2018 в 08:52

source

1 answer

Vue data iteration query in mysql with where

score 2 · Accepted Answer

After being investigating and asking I got my script to do what I wanted, I leave it here in case someone has a better solution.

function onEdit(e){
    var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
    var range = e.range;
    var colIndex = range.getColumnIndex(); //Same as getColumn()
    var rowIndex = range.getRowIndex(); //Same as getRow()
    var DateCol1 = "K9"; //Cell you want to have the date
    var DateCol2 = "U9";

    if(e.range.getSheet().getName() === 'Test2'){

        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(colIndex >= watchRange1.left && colIndex <= watchRange1.right && rowIndex 
        >= watchRange1.top && rowIndex <= watchRange1.bottom && e.Value != 0){

            SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue(curDate); 
            //Write the date in the cell

        }else if (colIndex >= watchRange2.left && colIndex <= watchRange2.right && 
        rowIndex >= watchRange2.top && rowIndex <= watchRange2.bottom && 
        e.Value != 0){

            SpreadsheetApp.getActiveSheet().getRange(DateCol2).setValue(curDate);

        };
    };
}

I imagine there will be a solo to not have to do 300 thousand watchRange with their respective if.