How to automatically add the ordinal number of an entry based on timstamp to a row when non-contiguous new data is added to it in Google Sheets?

Question

How to make Google sheets automatically add the ordinal number of the current row based on the following criteria 'new entry on this particular row?

How to make it work even when the data is not entered on a row contiguous basis (for example 1st entry on row 1 (say cell B1), 2nd entry on row 2 (in B2), 3rd entry on row 3 (B3), but then 4th entry on row 9 (B9), 5th on row 11 (B11), 6th on row (B7))?

And not adding ordinals to intermediary empty rows (for example when B5/row5, B6/row6, B8/row8, B10/row10 etc. are blanks, don't add ordinals to A5/row5, A6/row6, A8/row8, A10/row10, etc.)?

Here's the expected result:

The Sheet: Ordinals to non-contiguous rows based on data entry timestamps

So far I've come up with this pull down formula (to be pre-set in A2 and dragged down to A12):

=IF(B2<>"",MAX($A$2:$A)+1,"")  [in A2]
=IF(B3<>"",MAX($A$2:$A)+1,"")  [in A3]
=IF(B4<>"",MAX($A$2:$A)+1,"")  [in A4]
=IF(B5<>"",MAX($A$2:$A)+1,"")  [in A5]
=IF(B6<>"",MAX($A$2:$A)+1,"")  [in A6]
=IF(B7<>"",MAX($A$2:$A)+1,"")  [in A7]
=IF(B8<>"",MAX($A$2:$A)+1,"")  [in A8]
=IF(B9<>"",MAX($A$2:$A)+1,"")  [in A9]
=IF(B10<>"",MAX($A$2:$A)+1,"")  [in A10]
=IF(B11<>"",MAX($A$2:$A)+1,"")  [in A11]
=IF(B12<>"",MAX($A$2:$A)+1,"")  [in A12]

But the result I get when I enter then data succesively one by one in B2, B3, B4, B7, B9, and B12, is that it updates all previous cells with the new max value so that the succesion isn't achieved.

Here's a gif that shows the live result:

https://i.imgur.com/1I1Dk8f.gifv

How to lock the previous results so that the ordinal count can proceed to the next entry when it occurs consecutively?

I've consulted those post that are close to what I'm looking for:

How do you replace a formula with its result?

using this script:

function freezeOutput(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet");
  var range = sheet.getRange("A1:A10");     
  range.copyTo(range, {contentsOnly:true});
}

Stop Excel from updating formula after a criteria is matched

using this formula:

=IF(NOW() < C1,2*A1,A2)

Alternatively this VBA code:

Function EvaluateIf(expression As String, condition As Boolean) As Variant
    Application.Volatile
    Dim myText As String
    Dim myVal As Variant
    If condition Then
        myVal = Application.Evaluate(expression)
    Else
        myText = Application.Caller.Text
        If IsNumeric(myText) Then
            myVal = Val(myText)
        Else
            myVal = myText
        End If
    End If
    EvaluateIf = myVal
End Function


Function FreezeAfter(expression As String, deadline As Date) As Variant
    Application.Volatile
    Dim myText As String
    Dim myVal As Variant
    If Now > deadline Then
        myText = Application.Caller.Text
        If IsNumeric(myText) Then
            myVal = Val(myText)
        Else
            myVal = myText
        End If
    Else
        myVal = Application.Evaluate(expression)
    End If
    FreezeAfter = myVal
End Function

How do I copy cell values programatically?

using this script:

function getRangeValues() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:B4");
  var values = range.getValues();
  return values;
};

With Sheet here: Copy cell values programmatically (72539)

Lastly:

Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets

using this script:

/**
* Creates a Date Stamp if a column is edited.
* https://yagisanatode.com/2018/02/21/add-the-current-date-to-a-sheet-when-data-is-added-so-that-the-date-does-not-changestatic-google-sheets/
*/

//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 6;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-5];
// Sheet you are working on
var SHEETNAME = 'Sheet3'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if( selectedCell.getColumn() == COLUMNTOCHECK) { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[8]);
      dateTimeCell.setValue(new Date());
      }
  }
}

But I don't see how to make it work.

The expected result shoud be simply add ordinal number to corresponding cell in column A upon entering data on corresponding cell in column B.

How do we do that?

Answer 1

Flow:

• Use edit trigger to capture events in Col B
• Get all of Col A on edit of Col B
• Calculate Max of Col A and add 1
• SetValue the max to Col A

Snippet:

function onEdit(e) {
  var rg = e.range,
    row = rg.rowStart,
    col = rg.columnStart,
    sht = rg.getSheet();

  //exit code
  if (col !== 2 || sht.getName() !== 'Sheet1' || row === 1) return;
  
  //Calculate max value and add 1
  rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
    rg
      .offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
      .getValues()
      .reduce(function(acc, curr) {//get max of colA
        return Math.max(acc, Number(curr[0]));
      }, 0) + 1
  );
}

To Read and Practice:

• Array#Reduce
• Range#Offset
• Event Objects