Search and replace in a new sheet in a specific column with script

Question

I ask for your help to solve the following matter.
In a Google spreadsheet I have a sheet called "Database" that has the following data:

 Item    Department Budget  Prev.Year   Forecast
A3929232    Germany  1000    5.504      5050
B3232323    Germany  2000    4.500      5050
C6506506    Spain    3000    6.080      5080
D5046506    Spain    4000    9.090      7700
E5650600    Spain    5000    6.050      9900

I have another sheet called "Plan" that has the following:

Item      Department  New Amount
B3232323    Germany     5000
D5046506    Spain       2000

In the sheet "Plan" I would enter an amount in C2 (New Amount) for example 5000 and I would like a script that searches on the same row (A2) for Item B3232323 in the sheet "Database" and replace in "Database" column C with the new amount (replace old 2000 with new 5000).

I'd like that script to work on all rows of the sheet "Plan" that have values and if the script can't find in "Database" the Item and value should always be entered in the last row at the end of "Database".

The link of the file is the following: https://docs.google.com/spreadsheets/d/1ZPfB1DJD2LJIuSngU4NscziXJ3TCgTDtH-TS2_r7uBU/edit?usp=sharing

Thank you very much for your advice

Answer 1

I wrote this script that does what you want. I wrote some comments in it so that you can see clearly was is happening on each line:

function onEdit(e) {
  // Getting info about the edited cell (sheet, row and column):
  var sheet = e.source.getActiveSheet();
  var sheet_name = sheet.getSheetName();
  var columnIndex = e.range.getColumn();
  var rowIndex = e.range.getRow();
  var foundItem = false; // Variable to keep track of whether the item was already present in Database
  if(sheet_name == "Plan" && columnIndex == 3 && rowIndex > 1) { // Checking whether the edited cell is a new amount from Plan
    // Getting info on the edited row:
    var row = sheet.getRange(rowIndex, 1, 1, 4).getValues();
    var itemNumber = row[0][0];
    var department = row[0][1];
    var newAmount = row[0][2];
    var targetSheet = e.source.getSheetByName("Database"); // Getting info from Database sheet
    var values = targetSheet.getDataRange().getValues();
    for(var i = 1; i < values.length; i++) { // Looping through all the rows in Database:
      if(itemNumber == values[i][0]) { // Checking whether item number from Plan matches the one in Database
        targetSheet.getRange(i + 1, 3).setValue(newAmount) // Setting new budget in database
        foundItem = true; // Item was found
      }
    }
  }
  // Appends new data if item was not found
  if(foundItem == false) {
    targetSheet.appendRow([itemNumber, department, newAmount]);
  }
}

I hope this can help you!