Home / Questions

Questions[google-apps-script]

Martin Hope
ZCH
Asked: 2022-08-02 20:44:22 +0800 CST
  • 2

I am making a Webapp and I collect the data from a form, I write it in a Sheets and there I process it with an array formula. The problem is that a row is increased at the end of the sheet, instead of being written to the first empty row. I can delete all the empty rows, but I need another way to do it without having to delete the rows. I add the code of a very simplified version of the Webapp:

//G.S.
function doGet () {
    return HtmlService.createTemplateFromFile('index').evaluate();};
function escribir (array){
  var ss = SpreadsheetApp.openById('').getSheetByName('Hoja 1');
  ss.getRange(ss.getLastRow()+1,1,1,array[0].length).setValues(array);};

indexHTML

<body> 
    <input type="number" id="numero1" value="">
    <input type="number" id="numero2" value="">
    <input type="button" id="btn" value="boton">
    <script type="text/javascript">
        btn.addEventListener('click',escribirPartida)
        function escribirPartida() {
           array = [[numero1.value, numero2.value]]
           console.log(array)
           google.script.run.escribir(array)}
    </script>  
  </body>

This is the result, instead of writing the data in the yellow row, it is written at the bottom of the sheet.

data is written to the bottom of the sheet and not to the first empty row

I tried to put a condition in the array function to make all the cells below it empty. This is the array function:=ArrayFormula(SI(A2:A+B2:B =0;"";A2:A+B2:B))

I need a way to limit the ArrayFormula so that the data is not written at the end. (I know that it can be done from the Script, but I need to do it from the sheets)

google-apps-script
Martin Hope
Iván Montero
Asked: 2020-12-10 23:52:33 +0800 CST
  • 2

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
Martin Hope
Iván Montero
Asked: 2020-11-30 22:24:41 +0800 CST
  • 3

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.

google-apps-script
Martin Hope
A. Cedano
Asked: 2020-10-23 03:31:47 +0800 CST
  • 5

I have created a trigger that sends me an email every time a Google Sheets sheet is updated.

But in the message I want to send the data that was written in each of the cells of that row that was updated.

In the code that I am implementing, I can only get the data of the specific cell that was updated, but not of the entire row.

My code is the following:

function onEdit(e) {
    var gSheet = SpreadsheetApp.getActiveSpreadsheet();
    var cell = gSheet.getDataRange().toString();
    var mEmail='[email protected]';
    var mSubject='Nuevo error 404';
    var mBody='Hoja modificada por: '+Session.getActiveUser()+
              '\nDatos: '+gSheet.getActiveCell().getValue();
    MailApp.sendEmail(mEmail, mSubject, mBody);
}

I was looking among the possibilities for a kind of method getActiveRow(), but I can't find it. How could I get all the values ​​of that row?

Current state of the code

Currently the code is like this:

function onEdit(e) {
    Logger.log("e");
    var gSheet = SpreadsheetApp.getActiveSpreadsheet();
    var cell = gSheet.getDataRange().toString();
    var numFila = e.range.rowStart; 
    var fila = gSheet.getRange(numFila + ":" + numFila);
    var valoresFila = fila.getValues();
    var mEmail='[email protected]';
    var mSubject='Nuevo error 404';
    var mBody='Hoja modificada por: '+Session.getActiveUser()+"\nDatos: "+gSheet.getActiveCell().getValue()+" c1: "+valoresFila[1]+" c2: "+valoresFila[2];
    MailApp.sendEmail(mEmail, mSubject, mBody);
}

When the sheet is updated I receive an email, but without data, something like this:

Hoja modificada por: [email protected]
Datos:
google-apps-script
Martin Hope
Rubén
Asked: 2020-01-28 06:06:25 +0800 CST
  • 6

Soon

I plan to use a onEdit(e)Google Sheets and Google Apps Script (1) event to record the timestamp of the update of cells 1,1 and 180,8 of a 180x8 array of formulas on the understanding that the Google spreadsheet updates from top to bottom and from left to right.

Is this approach adequate?

Considerations

recalculation

Google Spreadsheets has a parameter to determine when to recalculate functions like NOW(), TODAY(), RANDOM, and RANDOM.BETWEEN().

The options are:

  • in changes
  • In changes and every minute
  • In changes and every hour

of (4)

Note: External data functions are recalculated at the following intervals:

  • ImportRange: 30 minutes
  • ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
  • Google Finance: 2 minutes

To Do: Determine if this can be leveraged in any way

Google Apps Script

onEdit(e)it is a simple event. It, its installable version as well as other events in Google spreadsheets respond to user actions, so it can be used to call functions for example when editing a cell.

"Change" is a settable event that can be used for changes of the following types: EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER.

To do: identify what types of changes OTHER encompasses.

It is clear to me from the above that it could be used to identify the start of the recalculation.

To Do: Identify how to determine via Google Apps Script that the recalculation is complete.

Additional Information:

Context

It is required to improve the performance of a Google spreadsheet for which it is being considered to replace the formulas with code. To determine the degree of success, it is desired to have metrics of the execution time of both solutions. In the case of code, for example, you can register the timestamp at the beginning and end and then subtract the first from the last one.

The spreadsheet has a 180 x 8 array of formulas. The formulas take data from the row and header and return a 1x2 array. In other words, formulas are used to nest 1x2 arrays in each element of a 180x8 array.

There is enough space to accommodate the result matrix. In a minimal, complete, and verifiable example of the matrix, headers would be placed every other column. In the actual use case an additional column is included for recording notes.

Spreadsheet performance

Regarding the "understanding" of the order in which the calculations are performed, this is based on the recommendation given in various sources to use the same convention in spreadsheets that is used to read in Western languages ​​(English, Spanish, Portuguese, among others), on the one hand because that is how the search functions work and on the other hand because spreadsheets are recalculated faster this way. Included in references is no. 2 as an example of the various sources. Curiously, in the official documentation, Google Sheets help (see 3), there is no mention of this.

References

  1. Events - Guides- Google Apps Script
  2. 2. Make your spreadsheet read from left to right and top to bottom
  3. File sizes - Google Editors Help
  4. Change the locale, time zone, calculation frequency, and language for a spreadsheet - Google Editors Help

Questions on Stack Exchange about reducing recalculation time

google-apps-script