find the last occurrence on column A and return the value from the same row on C (Apps Script)

Question

I have a data sheet, containing: |ItemCode | Item | Version|

The goal is to get the last version of the item and increment it by 0.1, so if the item's version is 03, then its new version would be 3.1 once this function was called.

The code below finds the first, but not the last occurrence in range(data sheet). I need to find the last version for that item and increment it:

function newVersion() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("ArquivoItens");
  var range = sheet.getRange(2,1,ss.getLastRow(),3).getValues();
  var editarSheet = ss.getSheetByName('EditarItem');
  var itemCode = editarSheet.getRange("W5").getValue();
  var version = editarSheet.getRange("AC4").getValue();
    for(var a = 0; a < range.length; a++){
        if(range[a].lastIndexOf(itemCode)>-1){
        Logger.log("Código: "+ itemCode);
        Logger.log("Versão: "+ range[a][2]);
        Logger.log("Produto: "+ range[a][1]);
        Logger.log(range[a].indexOf(itemCode));
        return range[a][2];
    }
  }

Any light is appreciated.

Answer 1

   function findTheLastVersionAndAdd(itemcode) {
  if(itemcode) {
    const ss=SpreadsheetApp.getActive();
    const sh=ss.getSheetByName('Sheet1');
    const sr=2;//guess start row is 2
    const vs=sh.getRange(sr,1,sh.getLastRow()-sr+1,3).getValues();
    var iObj={itemcode:''};
    vs.forEach(function(r,i){
      if(iObj.hasOwnProperty(r[0])) {
        iObj[itemcode]=i+sr;//updates the object if r[0]==itemcode
      }
    });
    return Number(sh.getRange(iObj[itemcode],3).getValue()) +.1;
  }
}

Answer 2

After your comments and explanations, I made this code that will help you to achieve what you desire:

function newVersion(e){
  var itemCell = e.source.getActiveCell();
  var col = itemCell.getColumn();
  // If the Column is the one where the items are then continue
  if(itemCell.getColumn() === 2){
    var ui = SpreadsheetApp.getUi();
    var result = ui.alert('Please confirm','Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);
    // Process the user's response.
    if (result == ui.Button.YES) {
      // User clicked "Yes".
      // Get the item's version and increment its value
      var versionCell =  itemCell.offset(0, 1);
      var itemVersion = versionCell.getValue();
      var updatedVersion = itemVersion + 0.1;
      versionCell.setValue(updatedVersion);
    } else {
      // User clicked "No" or X in the title bar.
      ui.alert('Permission denied.');
      // Return to value previously edited
      itemCell.setValue(e.oldValue);
    }
  }
}

What the code will do is when you change a value in an item, you will get a pop-up window asking you to confirm it. If you do confirm it then using the offset(rowOffset, columnOffset) method you'll get the item's version cell and in that way, you'll be able to increment it. If you do not confirm it, then the value in your item will come back to be the old one. Notice e represents the Event Object.

Take into consideration I'm using an Installable Trigger, for setting it up, do the following:

1) Go to your Apps Script project

2) Click Edit->Current project's triggers

3) Click "+ Add Trigger"

4) Select :

• Choose which function to run -> Function Name

• Select event source-> From spreadsheet

• Select event type -> On edit

5) Click Save