CODEWITHSUNDEEP
google-apps-script
Shilah
Shilah

Reputation: 33

How to set value of a cell based on other cells with Apps Script when new row is added anywhere in the sheet

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);
 }
}
}

Upvotes: 0

Views: 1106

Answers (2)

Shilah
Shilah

Reputation: 33

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
  }

}

Upvotes: 0

jbra95
jbra95

Reputation: 909

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

Upvotes: 1

Related Questions