Home / Questions / 220905
Accepted
Iván Montero
Asked: 2020-12-10 23:52:33 +0800 CST 2020-12-10 23:52:33 +0800 CST

Google Spreadsheet Script Help

  • 772

I'm trying to make a script for google spreadsheet but I'm stuck and it doesn't 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, write the modification time of said range in a specific cell.

This is the script I have done so far:

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 error is before the if, but I can't fix it.

google-apps-script

1 Answers

  1. Best Answer

    After researching and asking questions, I managed to get my script to do what I wanted, I'll 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 that there will be a solution so as not to have to do 300 thousand watchRange with their respective if.

    • 2