Trigger a function if the number of rows increases-CodePudding

I would like my function sendSlackMessage to fire only if the number of rows increases in a sheet.

I am using a code that was kindly provided by a member here, however, each time the code runs (time intervals each min), the rows number property increases by a wrong number; for example (stores old rows as 37.0 when it should be only 34.0)

What could be the issue here? Or how can I improve this code to go around this?

function checkNewRows() {
  var sp = PropertiesService.getScriptProperties();
  var oldRows = sp.getProperty("rows") || 0; 
  var newRows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main").getLastRow();
  if (newRows > oldRows) {
    sp.setProperty("rows", newRows);
    sendSlackMessage();
  } 
}

CodePudding user response：

The code you show looks fine. The rows property does not tell the number of rows in data but the last row number that holds data. That should not make a difference to the results you get, as long as you are using the rows property consistently the same way.

In the event that your sendSlackMessage() function also reads the rows property and interprets it as something else than the row number of the last row, you may want to use a function parameter to convey the row number of the last row instead of reading and writing properties, like this:

    sendSlackMessage(newRows);

If you absolutely need to record the number of data rows rather than the row number of the last row, you can use something like this:

function checknumRows() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Main');
  const props = PropertiesService.getScriptProperties();
  const oldNumRows = props.getProperty('rows') || 0;
  const numRows = sheet.getLastRow() - sheet.getFrozenRows();
  if (numRows > oldNumRows) {
    props.setProperty('rows', numRows);
    sendSlackMessage(numRows);
  }
}

To set the number of header rows, use View > Freeze.

CodePudding user response：

This works for me:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet0') {
    var oldRows = PropertiesService.getScriptProperties().getProperty("rows") || 0;
    var newRows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet0").getLastRow();
    if (newRows > oldRows) {
      PropertiesService.getScriptProperties().setProperty("rows", newRows);
      e.source.toast(`Old: ${oldRows} New: ${newRows}`)
    }
  }
}

If you are using array formula functions that can cause problems with calculation of getLastRow() method