Search all sheet column A and find row of last cell matching specific criteria-CodePudding

I would like to find the row of the last time the ASIN appears in the sheet called 'Historic Sheet'

Here is the sheet called 'Historic Sheet'

The current code I have finds all matching instead of the final record matching the criteria (ASIN)

  for (var i = 0; i < all.length; i  ) {

  let tf = historicSheet.createTextFinder(asin);
  let all = tf.findAll();
  let unbookedStock = historicSheet.getRange(all[i].getRowIndex(), 18).getValue();
  historicSheet.getRange(all[i].getRowIndex(), 18).setValue(newValue);

}

CodePudding user response：

It can be something like this I suppose:

function myFunction() {
  var asin = 'D0752NTHJz';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Historic Sheet');
  var col_a = sheet.getRange('a:a').getValues().flat(); // get values from column A
  var row = col_a.lastIndexOf(asin)   1; // this is the last row that contains the 'asin'

  console.log(row); 
}

CodePudding user response：

Try it this way:

function myFunction(asin = 'D0752NTHJz') {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Historic Sheet");
  const idx = sheet.getRange('A1:A'   sheet.getLastRow()).getValues().flat().lastIndexOf(asin);
  console.log('Row: %s',idx   1); 
}