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.

To be sure of what you desire. In your code, you get an item code using ```editarSheet.getRange("W5").getValue();```, then if it's in your ```range```, you increment by 0.1 your item's version, right? Why do you have a return in your function? Is it returning to another function or you're using a [Custom function](https://developers.google.com/apps-script/guides/sheets/functions)? — alberto vielma, May 12 '20 at 08:45
Hi, @albertovielma! The idea was that the function newVersion would be called, if the user clicked confirm. Then, the item that is being edited, would be saved with its version + 0.1. Thanks for looking into it. — onit, May 12 '20 at 12:33
thank you for the info. you're saying *if the user clicked confirm*. That means you are using [Custom Menus](https://developers.google.com/apps-script/guides/menus)? Could you provide a spreadsheet with some dummy data. It will be easier to troublesoot the error — alberto vielma, May 12 '20 at 14:10
Thank you, for looking into this. The user will see a pop up windows, asking him to confirm. Then, the code continues running and calls this function in question. — onit, May 12 '20 at 14:24

Cooper · Answer 1 · 2020-05-11T03:31:49.273

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

edited May 11 '20 at 03:31

answered May 11 '20 at 03:15

Cooper

59,616
6
23
54

Hi! I'm not sure I was clear, but the need is to get the last version of a certain itemCode. – onit May 11 '20 at 03:21
I've been looking for all possible causes, but I still get Exception: The parameters (null,number) don't correspond to method signature for SpreadsheetApp.Sheet.getRange. I''ve tried changing from getActive to getActiveSpreadsheet, but I still get this error when the function is called. Thanks! – onit May 11 '20 at 21:35

score 0 · Answer 2 · answered May 13 '20 at 08:21

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

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

2 Answers2