0

I'm trying to update a cell with Apps Script whenever a new row is inserted anywhere in a spreadsheet, and the value is determined by what's entered in other columns within its row. I have tried getActiveRange but it keeps only returning the 1st row. Here's what I have so far. Any help is much appreciated!

function SetOwner() {
  var gifLastRow = GIF_SHEET.getLastRow();
  var gifNumRows = gifLastRow - START_ROW + 1;   // Number of rows to process (accounting for Zero index)
  // Fetch the range of cells starting at A2
  var gifRange = GIF_SHEET.getRange(START_ROW, 1, gifNumRows, 19); 
  // Fetch values for each row in the Range.
  var gifData = gifRange.getValues();
  var rownum = gifRange.getRow();

 for (var i = 0; i < gifData.length; i++) {
    var row = gifData[i];
   var mowner = "=vlookup(B:B,Config!A$1:B$10,2,false)"//This is the VLOOKUP for Team A!
   var cowner = "=vlookup(B:B,Config!E$1:F$10,2,false)"//This is the VLOOKUP for Team B!
    if (row[13] == "Team A") {  //check if created by team a
      GIF_SHEET.getRange(rownum, 17).setValue(mowner);
    } else if (row[13] == "Team B") { //check if created by team b
       GIF_SHEET.getRange(rownum, 17).setValue(cowner);
 }
}
}
Shilah
  • 33
  • 3
  • Use the trigger onEdit()?? https://developers.google.com/apps-script/guides/triggers – Kevkeev13 Nov 20 '19 at 11:01
  • @Kevkeev13 Seems that `onEdit` is too inefficient. `onChange` is the way - [How can I run a script only when someone adds new rows](https://stackoverflow.com/q/20258098/1330560) and it also has Event Objects that include [INSERT_ROW](https://developers.google.com/apps-script/guides/triggers/events#change) – Tedinoz Nov 20 '19 at 11:08
  • 2
    Does this answer your question? [How can I run a script only when someone adds new rows](https://stackoverflow.com/questions/20258098/how-can-i-run-a-script-only-when-someone-adds-new-rows) – Tedinoz Nov 20 '19 at 11:09

2 Answers2

1

This function will return the range in which you make the changes. Basically, if you edit cell A10, the script will return the range A10. 

function onEdit(e){
  var cell = e.range;
  Logger.log(cell.getA1Notation());

  return cell;
}

Documentation: https://developers.google.com/apps-script/guides/triggers#onedite

enter image description here

enter image description here

jbra95
  • 881
  • 2
  • 11
  • 30
  • Thank you so much for your help. I copied and pasted the code exactly as you have it and de-activated all of my other code to test it, and I'm getting nothing when I check the logs. Am I missing something? – Shilah Nov 22 '19 at 04:26
  • It works properly, write something in the spreadsheet and then check the logs. – jbra95 Nov 25 '19 at 10:16
  • Thanks, I see it now, not sure why it wasn't working before since I was making edits. – Shilah Dec 02 '19 at 05:29
0

Thanks to Juan, I was able to build this out:

  function onEdit(e){
  var cell = e.range; //Get cell that was edited
  Logger.log(cell.getA1Notation());//Confirm cell
  Logger.log(cell.getRowIndex()); //Confirm row
  var row = cell.getRowIndex(); //Get row number

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Name of Sheet');
  var range = sheet.getRange(row,14); //Get the Created By column for the edited row
  var value = range.getValues(); // Get the value in Created By

  var aowner = "=vlookup(B:B,Config!A$1:B$10,2,false)"; //Look up owner for team A
  var bowner = "=vlookup(B:B,Config!E$1:F$11,2,false)"; //Look up owner for team B


  if (value [0] == "Team A") { //check if created by team A
    sheet.getRange(row,17).setValue(bowner); //set owner to Team B contact
  } else if (value [0] == "Team B") {  //check if created by team B
    sheet.getRange(row,17).setValue(aowner); //set owner to Team A contact
  }

}
Shilah
  • 33
  • 3