Iván Montero's questions

This would be the second part of a post that I made recently: Script traversal of rows and columns

In this part I have added an array, so that I go through certain sheets and later make a loop over them and execute the previous code.

function myFunction() {

  var hojas = [

    "Calvià",
    "Inca",
    "Manacor",
    "LLucmajor",
    "Sóller",
    "Artà",
    "Alcudia",
    "Felaitx"

  ]

  var nombres = 2;
  var parque = 2;
  var posiciong = 2;
  var posiciongr = 2;

  for (i=0; i<hojas.length + 1; i++){

    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(hojas[i]);
    var lastrow = ss.getLastRow();

    for (r=7; r<lastrow+1; r++){

        var G = 0;
        var GR = 0;
        var range = ss.getRange(r,1).getValue();

        if (range!=0){

          var range2 = ss.getRange(r,4).getValue();
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(nombres, 1).setValue(range);
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(parque, 2).setValue(range2);
          nombres = nombres + 1;
          parque = parque +1;

          for (c=6; c<93; c++){

            var rangec = ss.getRange(r,c).getValue().toString();

            if (rangec=="G"){

              G = G + 1;

            }if (rangec=="GR"){

              GR = GR + 1;

            }

          }

          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(posiciong, 3).setValue(G);
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(posiciongr, 4).setValue(GR);
          posiciong = posiciong + 1;
          posiciongr = posiciongr + 1;

        }

    }

  }

}

I think the code is fine, but the problem is that it takes more than 5 minutes to execute completely and it gives me execution time finished. The question is if it would be possible to optimize the script so that it takes less or increase the execution time, I have read that this second option cannot be done.

I am making a script for the google spreadsheet that goes through the rows and columns in search of specific values ​​and counts them for me, to later write it in another sheet.

This is the script I have right now:

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calvià");
  var lastrow = ss.getLastRow();
  var nombres = 2;
  var parque = 2;
  var posiciong = 2;
  var posiciongr = 2;


  for (r=7; r<lastrow+1; r++){

      var G = 0;
      var GR = 0;
      var range = ss.getRange(r,1).getValue();

      if (range!=0){

        var range2 = ss.getRange(r,4).getValue();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(nombres, 1).setValue(range);
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(parque, 2).setValue(range2);
        nombres = nombres + 1;
        parque = parque +1;

        for (c=6; c<93; c++){

          var rangec = ss.getRange(r,c).getValue().toString;

          if (rangec="G"){

            G = G + 1;

          }if (rangec="GR"){

            GR = GR + 1;

          }

        }

        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(posiciong, 3).setValue(G);
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ContadorHoras").getRange(posiciongr, 4).setValue(GR);
        posiciong = posiciong + 1;
        posiciongr = posiciongr + 1;

      }

  }

}

Everything works fine until I add the second for, with the first for, I go through the rows and with the second, the columns of each row looking for some values ​​and counting them, my problem is that I can't get this second for to work well, everything the time tells me that GY GR is 87.

I am trying to select the maximum value of a range of cells from another sheet in google sheets, but I cannot find the formula, I have tried different formulas:

=IMPORTRANGE("Enlace";"Resums per parc!max(D17:F17)")

=query(IMPORTRANGE("Enlace") "Select max(D,E,F) where B=TOTAL")

=query(IMPORTRANGE("Enlace") "Select max(Col4,Col5,Col6) where Col2=TOTAL")

This is another part of my wanderings with google scripts, in the last question How to filter a 2D Array using two exact match criteria? I thought I had finished the script, but then it occurred to me to be able to order the array so that it shows me the data in the order I want, the intention is to put several criteria to order and to execute them sequentially.

The script I have is this:

function onEdit(e){

  var range = e.range;
  var colIndex = range.getColumnIndex(); //Same as getColumn()
  var rowIndex = range.getRowIndex(); //Same as getRow()

  //Donde utilizar el filtro
  var rangeLlista = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Llista").getRange("A6:AX500").getValues();

  //Lo que queremos borrar
  var rangeBorrar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Asignacion").getRange("A9:AX500");

  //Asignacion de las celdas que tiene que filtrar.
  var RangeAsignacion = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Asignacion").getRange("A2:F2");

  //Los criterios a filtrar
  var Asignacion1 = RangeAsignacion.getCell(1,2).getValue().toString();
  var Asignacion2 = RangeAsignacion.getCell(1,5).getValue().toString();

  //Columna de los criterios a filtrar, la culumna A = 0
  var columnIndex1 = 5;
  var columnIndex2 = 6;

  //Mira si se modifican las celas que nosotros queremos
  if(range.getSheet().getName() === 'Asignacion'){

    var watchRange1 = {
      top : 2, // start row
      bottom : 2, // end row
      left : 1, // start col
      right : 6, // end col
    };

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

      //Borrado del contenido anterior
      /*var start = 9;
      var end = rangeBorrar.getMaxRows();

      rangeBorrar.deleteRows(start, end - start);*/

      rangeBorrar.clear({contentsOnly: true});

      //Se realiza el filtrado
      var filteredData = rangeLlista.filter(function (dataRow){

        return dataRow[columnIndex1] === Asignacion1 && dataRow[columnIndex2] === Asignacion2;

      });

      var rows = filteredData.length;
      var columns = filteredData[0].length;
      SpreadsheetApp.getActiveSheet().getRange(9,1,rows,columns).setValues(filteredData);

    };

  };

};

This is what I am trying:

var filteredData = rangeLlista.filter(function (dataRow).sort(19,true){ return dataRow[columnIndex1] === Asignacion1 && dataRow[columnIndex2] === Asignacion2; });

I leave a link to a test document:

doc tests

This is what I have right now:

function onEdit(e){

  var range = e.range;
  var colIndex = range.getColumnIndex(); //Same as getColumn()
  var rowIndex = range.getRowIndex(); //Same as getRow()

  //Donde utilizar el filtro
  var rangeLlista = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Llista").getRange("A6:AX500");

  //Lo que queremos borrar
  var rangeBorrar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Asignacion").getRange("A9:AX500");

  //Asignacion de las celdas que tiene que filtrar.
  var RangeAsignacion = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Asignacion").getRange("A2:F2");

  //Los criterios a filtrar
  var Asignacion1 = RangeAsignacion.getCell(1,2).getValue().toString();
  var Asignacion2 = RangeAsignacion.getCell(1,5).getValue().toString();

  //Columna de los criterios a filtrar, la culumna A = 0
  var columnIndex1 = 5;
  var columnIndex2 = 6;

  //Mira si se modifican las celas que nosotros queremos
  if(range.getSheet().getName() === 'Asignacion'){

    var watchRange1 = {
      top : 2, // start row
      bottom : 2, // end row
      left : 1, // start col
      right : 6, // end col
    };

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


      rangeBorrar.clear({contentsOnly: true});

      //La columna 19 es la T
      rangeLlista.sort([{column: 19, ascending: true},{column: 16, ascending: true},{column: 17, ascending: true},{column: 18, ascending: true},{column: 2, ascending: true},{column: 3, ascending: true},{column: 4, ascending: true}]);

      var llista = rangeLlista.getValues();

      //Se realiza el filtrado
      var filteredData = llista.filter(function (dataRow){

        return dataRow[columnIndex1] === Asignacion1 && dataRow[columnIndex2] === Asignacion2;

      });

      var rows = filteredData.length;
      var columns = filteredData[0].length;
      SpreadsheetApp.getActiveSheet().getRange(9,1,rows,columns).setValues(filteredData);

    };

  };

};

The first 2 columns are fine for me, but the rest are not.

This is the third part of a question that started with trying to filter a spreadsheet using a formula:

Formula Filtering

Here I realized that what I wanted to do could not be done using the spreadsheet formulas, so I decided to try it using scripts.

Scripted Filtering

To perform this filtering with scripts, I tried to use an external library called 2D Arrays Library , with which I was able to filter the data, but not in the way I wanted.

My intention is to filter the data of a 2D Array in which I can use 2 exact criteria, since with the library I have only managed to filter the cells that contain these criteria.

For this, I leave at your disposal a test spreadsheet that contains a list with the data to be filtered, a sheet with the current filter and another sheet with the expected result, in addition to containing the script and the library that I am using.

test sheet

I have a script in a google spreadsheet that works perfectly but only with my user, when another user does the action that executes the script, it does not work even if the user gives it permission in his account, any solution?

I leave a link to the example of the spreadsheet that I mention:

https://docs.google.com/spreadsheets/d/1y5H1x84tyt43HEhtescDmuL-cXPHhL8KzJ2MkT_3eZo/edit?usp=sharing

I leave an example of the code here:

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() === 'PARCMÒBIL'){
        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'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.

I have made a script that google sheetwrites 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 specific ranges.

This is the script 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 don't know how to make it detect the cell that I am modifying and from this detect in which range it is previously selected, I had thought of doing something like the one below, but it doesn't work out for me. The purpose of this is to have a series of controlled ranges, to know when they have been modified, each range has its own cell to write the modification date.

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 have 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 run this script I get this error: Cannot read property "range" of undefined. (line 4, file "Day1")

I'm still pretty stuck with the script

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

I'm trying to create a cell that automatically saves the modification date of a specific range of cells. I don't know the best way to do it, but I think that with a script it would be something like this:

function onEdit(e){
 var DateCol = "K8";
 var curDate = Utilities.formatDate(new Date(), "UTC+1", "hh:mm")
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
 var range = sheet.getRange('C11:K18');
 //range.activate();
 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 = { // B2:G20
   top : 11,         // start row
   bottom : 18,     // end row
   left : 3,        // start col
   right : 11,        // 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 + rowIndex).setValue("Edited: " + 
 curDate);

 }
}

Right now it does not give me an execution error, but it does not write the date in the cell and it selects the range of all the sheets and not one in particular.

I have managed not to select all the sheets by modifying line 4

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');

I have modified line 26

if (currentValue.length != 0)

Modifying line 29 writes me in the cell that I want

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

The only thing that fails me is that it puts me 1 hour less whatever I do.

I have modified this and it has been fixed

var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");

I need help with a utiljava.

In the program I'm making I need a find and replace.

I need it to work with text and excel documents.

I can't use the tools of programs like notepador the same excelto make the find and replace. Since they ask me to be automatic, it is for a web application uploading to the database and some of the data files arrive with data that needs to be replaced.

At the moment I have this

static void modifyFile(String filePath, String oldString, String newString){

    File fileToBeModified = new File(filePath);
    String oldContent = "";
    BufferedReader reader = null;
    FileWriter writer = null;

    try
    {
        reader = new BufferedReader(new FileReader(fileToBeModified)); 
        //Lectura de todas las líneas del archivo
        String line = reader.readLine();
        while (line != null) 
        {
            oldContent = oldContent + line + System.lineSeparator();
            line = reader.readLine();
        }
        //Reemplazo el contenido viejo
        String newContent = oldContent.replaceAll(oldString, newString);
        //Reescrivo el fichero
        writer = new FileWriter(fileToBeModified);
        writer.write(newContent);
    }
    catch (IOException e)
    {
        e.printStackTrace();
    }
    finally
    {
        try
        {
            reader.close();
            writer.close();
        } 
        catch (IOException e) 
        {
            e.printStackTrace();
        }
    }
}

public static void main(String[] args){
    modifyFile("file.txt", ".", "0");
    System.out.println("done");
}

It replaces the file, but not the points, if not all integer by 0.