Wsrtjtyk

I have been using VBA for years, but I have had some extreme difficulty converting to google script and javascript. I am trying to create an invoice number based on invoice date, taking into consideration previous entries with the same date, using the format YYMMDD## (e.g. 3 invoices on 11/13/2018 would create invoice numbers 18111301, 18111302, and 18111303) in Google Sheets. I cannot use a formula for this particular situation.

I have spent the last 3 days digging through posts and GS tutorials without success. I have GOT to be missing something. I know this has to be some quick fix for something seriously easy to do, but I would greatly appreciate some assistance.

In this current iteration, I tried converting the search value and dataset to a Date format, but I have tried it without the conversion. I apologize if the notes seem copious. I start mapping each step when things go wrong.

function testbed0() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var dte = ss.getActiveCell().getValue() //invoice date entered by user in column 1

  var cdte = Date(dte); //???convert cell value for comparison to date

  var lrow = ss.getLastRow();

  var r = ss.getRange(1, 1, lrow, 1); //set range to first column, rows 1 to last with data

  var data = Date(r.getValues()); //???convert dataset to date format



  var count = 0; //start counter at 0

  //compare variable with dataset & increase counter for matches

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

    if (data[i][0] == cdte) {

      count++;

    }

  }

  return (count);



  //parts of invoice number

  var invDy = ("0" + (dte.getDate())).slice(-2);

  var invMo = ("0" + (dte.getMonth() + 1)).slice(-2);

  var invYr = ("0" + (dte.getYear())).slice(-2);

  var invCt = ("0" + count).slice(-2);



  //enter new invoice number in invoice column of active row

  ss.getActiveCell().offset(0, 2).setValue(invYr+invMo+invDy+invCt);

}

This isn't exactly what you asked for but it accomplishes the same thing and perhaps it will help you to move further. I formatted column one to be plain text. This calculates all of the invoice numbers from top to bottom of the data.

function makeInvoiceNumbers(){

  var ss=SpreadsheetApp.getActive();

  var sh=ss.getSheetByName('Sheet1');

  var rg=sh.getDataRange();

  var vA=rg.getValues();

  var actRow=sh.getActiveCell().getRow();

  for(var i=1;i<vA.length;i++){

    var cnt=0;

    for(var j=1;j<i;j++){//Working from top to bottom creating invoice numbers

      if(vA[i][0]==vA[j][0]){//I assume that the entrees on the top came before the entrees below them.

        cnt++;

      }

    }

    vA[i][1]=getInvoiceString(vA[i][0].toString(),cnt);

  }

  rg.setValues(vA);

}

function getInvoiceString(d,n){

  var dA=d.split('/');

  var yy=dA[2].toString().slice(2);//gets the last two digits of a four digit year

  var mm=padFor2Digits(dA[0]);

  var dd=padFor2Digits(dA[1]);

  var nn=padFor2Digits(String(n));

  return yy+mm+dd+nn;

}

function padFor2Digits(s){

  if(s.length<2){

    s='0' + s;

  }

  return s

}

Data before running makeInvoiceNumbers()

Data after running makeInvoiceNumbers()

If I were doing this for myself I would probably use the onEdit trigger and just do it for one line at a time.