CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dan
Dan

Reputation: 105

Script triggered by new row appearing in sheet

I am using a snapData script sourced externally and shown below:

    function snapData() {

    // get current sheet and tabs 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); var current = ss.getSheetByName('Fees (management)'); var database = ss.getSheetByName('Fees (data)');

    // count rows to snap var current_rows = current.getLastRow(); 
    var database_rows = database.getLastRow() + 1; 
    var database_rows_new = current_rows + database_rows - 2; 
    var rows_new = current.getRange("A2:F" + current_rows).getValues();

    // snap rows, can run this on a trigger to be timed 
    database.getRange("A" + database_rows + ":F" + database_rows_new).setValues(rows_new);

    }

At the moment, it is triggered by pressing the 'Run' button, however I would like to have the script triggered by a new row appearing on another tab.

Help with this would be appreciated. Thanks!

Upvotes: 0

Views: 181

Answers (1)

Tedinoz
Tedinoz

Reputation: 7949

"I would like to have the script triggered by a new row appearing on another tab."

By using the installable trigger onChange, it is possible to trigger the script as planned. source is an Event Object returned by onChange, though it is not included in the documnentation.

  • var sheetname = src.getActiveSheet().getSheetName(): get the sheet name
  • var ctype = e.changeType;: get the change type
  • if (sheetname === "target" && ctype === "INSERT_ROW"){: test if the type was inserting a row on "target"
function so5899606501(e) {

  // return all the event objects
  Logger.log(JSON.stringify(e));

  // get the source
  var src = e.source;
  var spreadsheetname = src.getName();
  var sheetname = src.getActiveSheet().getSheetName()
  Logger.log("spreadsheet = "+spreadsheetname+", sheet name = "+sheetname);
  var currentcell = src.getCurrentCell();
  Logger.log("the current cell = "+currentcell.getA1Notation());

  // get the change type
  var ctype = e.changeType;
  Logger.log("the change type is "+ctype);

  // test for the type of change
  if (ctype == "INSERT_ROW"){  
  Logger.log("A row was just inserted");
  }
  else
  {
  Logger.log("the change type wasnt a new row, it was "+ctype)
  }

  //test for the sheet where the change took place
  if (sheetname == "target"){  
  Logger.log("the change took place on the sheet named 'target'");
  }
  else
  {
  Logger.log("the change took place on the sheet named" +sheetname+".")
  }

  // test for the change type AND the sheet name
  if (sheetname == "target" && ctype == "INSERT_ROW"){
  Logger.log("Eureka!. A new row was inserted on the sheet named 'target. Lets do stuff");
  }
  else{
  Logger.log("Sigh. It either wasn't a new row AND/OR it wasn't on the sheet named 'target'. Either way, we can ignore it.");
  }

}

Upvotes: 1

Related Questions