CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Renee Rattray
Renee Rattray

Reputation: 5

Run trigger script when a one or more cells change in a range

I'm new to google sheets & script app. I'm using google sheets to maintain real-time project budgets. I've created a history tab to capture historical high level information (project code, category, Total Approved Budget, Projected Cost, Date stamp). Each time the scrip runs, it captures 4 rows of data.

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('ForMaster');
  var sheet2 = ss.getSheetByName('History');
  var data = sheet1.getRange('S84:W87').getValues();
  var last_row = sheet2.getLastRow();

  sheet2.getRange(last_row + 1,1,4,5 ).setValues(data);
}

I'd like to set up a trigger that runs where ever there is a change to any of the cells in data range (S84:W87).

I've tried the following, but it doesn't seem to work.

function setupTrigger() {
  ScriptApp.newTrigger('recordHistory')
  .forSpreadsheet('1TSX27lzmOpv6P8Z8zJJhYem_yLQwP-gYK4WYHJ3LEuA')
  .onEdit()
  .create();
}

function recordHistory(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('ForMaster');
  var sheet2 = ss.getSheetByName('History');
  var data = sheet1.getRange('S84:W87').getValues();
  var last_row = sheet2.getLastRow();
  
  sheet2.getRange(last_row + 1,1,4,5 ).setValues(data);
}

Upvotes: 0

Views: 66

Answers (1)

Marios
Marios

Reputation: 27390

Explanation:

You don't need an installable trigger since you don't use any service that requires permission.

  • You can use a simple onEdit(e) trigger instead.

In order to define the range S84:W87 that you would like to accept edits, you can do that:

row>=84 && row <= 87 && col >=19 && col <=23

84 is the starting row, 87 is the end row, column 19 is S and column 23 is W.

Also you want to only accept edits on the sheet ForMaster.

Therefore the if statement would look like that:

if(as.getName()=="ForMaster" && row>=84 && row <= 87 && col >=19 && col <=23)

Solution:

function onEdit(e) {
  const ss = e.source;
  const as = ss.getActiveSheet();
  const row = e.range.getRow();
  const col = e.range.getColumn(); 
  if(as.getName()=="ForMaster" && row>=84 && row <= 87 && col >=19 && col <=23){
     const sheet2 = ss.getSheetByName('History');
     const data = as.getRange('S84:W87').getValues();
     const last_row = sheet2.getLastRow();
     sheet2.getRange(last_row + 1,1,data.length,data[0].length).setValues(data);
  }
}

Note that!

  • The onEdit triggers are triggered upon user edits. This function won't be triggered by formula nor another script.

  • Again, this is a trigger function. You are not supposed to execute it manually and you will actually get errors. All you have to do is to save this code snippet to the script editor and then it will be triggered automatically upon edits.

Upvotes: 1

Related Questions