Unique Id Counter

I've posted a couple questions over the last few days regarding a Google Apps Script Web App I'm working on. So far Serge on here has been super helpful. The post on that script

Solution 1:

Another way to be sure to get a value that is always the last count (the highest value) as counter in a column is like that : (comments in code)

I had this idea when you said that values in column A could possibly be modified manually... in this case it is the only approach that avoids possible duplicates (it does not prevent having unused values though... (but this could be implemented too if necessary)

function OnForm(){
var sh = SpreadsheetApp.getActiveSheet()
var startcell = sh.getRange('A1').getValue();
if(! startcell){sh.getRange('A1').setValue(1);return}; // this is only to handle initial situation when A1 is empty.
var colValues = sh.getRange('A1:A').getValues();// get all the values in column A in an array
var max=0;// define the max variable to a minimal value
  for(var r in colValues){ // iterate the array
    if(Number(colValues[r][0]>max)){max=colValues[r][0]};// get the highest numeric value in th column, no matter what happens in the column... this runs at array level so it is very fast
    }
    max++ ; // increment to be 1 above max value
sh.getRange(sh.getLastRow()+1, 1).setValue(max);// and write it back to sheet's last row.
}

Solution 2:

Ok.. I've updated my code abit and running into an issue I'm hoping someone can help with. The script will see that the spreadsheet is blank and insert a 1 into A1. On next run it will see that 1 is in A1 and add 1 and insert 2 in A2. From then on it just keeps putting 2 in each row over and over.. Here is the code.. not sure why its doing that and not adding 1 each to and increasing the count.

function counter2() {
  var sSheet = SpreadsheetApp.getActiveSheet();
  var ssRange = sSheet.getRange(1,1,1,1);
  var ssValue = ssRange.getValue();
  var setOne = ssRange.setValue(1);  
  var lastRow = sSheet.getLastRow();  
  var setLast = sSheet.getRange(lastRow+1,1,1,1).setValue(ssValue + 1);

  if (!ssValue) {
  setOne;
  }
  else {
  setLast;
  }
  }

EDIT 1

Updated code... The first run on blank spreadsheet fills in A1 = 1 and A2 = 2. It should only put in a 1 in cell A1 and then increment after that. It works fine when running a second time and on. thoughts? What am I missing? function counter2() { //Get the spreadsheet, range, and value of first cell in range var sSheet = SpreadsheetApp.getActiveSheet(); var ssRange = sSheet.getRange(1,1,1,1); var ssValue = ssRange.getValue();

  //will set value of first cell to 1 if null, undefined, false, 0, NAN etc.. (runs in if statement)
  var setOne = ssRange.setValue(1);

  //This goes back and get the range, lastrow of range, and value of first cell in range of last row & adds 1 to that value
  var lastRow = sSheet.getLastRow();
  var startValue = sSheet.getRange(lastRow,1,1,1).getValue();
  var setLast = sSheet.getRange(lastRow+1,1,1,1).setValue(startValue + 1);      

  if (!ssValue) {
  setOne;
  }
  else {
  setLast;
  }
  }

Edit 2 - Fixed

Here is the updated code that now appears to work fine. I moved the variables "lastRow, startValue, setLast" into the else statement for the second statement. Why would that matter? is it because it runs the variables as it gets to it and by wrapping in the if / else {} brackets it only executes if that statement is run?

function counter2() {
  //Get the spreadsheet, range, and value of first cell in range
  var sSheet = SpreadsheetApp.getActiveSheet();
  var ssRange = sSheet.getRange(1,1,1,1);
  var ssValue = ssRange.getValue();

  //will set value of first cell to 1 if null, undefined, false, 0, NAN etc.. (runs in if statement)
  var setOne = ssRange.setValue(1);  

  if (!ssValue) {
  setOne;
  }
  else {

  //This goes back and get the range, lastrow of range, and value of first cell in range of last row & adds 1 to that value
  var lastRow = sSheet.getLastRow();
  var startValue = sSheet.getRange(lastRow,1,1,1).getValue();
  var setLast = sSheet.getRange(lastRow+1,1,1,1).setValue(startValue + 1); 
    setLast;
  }
  }

Solution 3:

You can alternatively use ScriptProperties to manage the unique ID. It's a little bit more reliable and a lot easier to work with.

Reliability note: Suppose your spreadsheet somehow was out of order, and your had IDs of [1,2,3,5,4]. Your script would then insert 5 as the next ID. ScriptProperties does a better job of keeping track of the maximum number.

> Sample Usage

var uid = ScriptProperties.getProperty("last_unique_id")+1;
ScriptProperties.setProperty("last_unique_id",uid); //the current UID is now the "last UID"
//do something with the unique_id

You will, of course, have to set the property initially. I recommend you do this with a little function that you run from the menu.

function setUIDOnce() {
  ScriptProperties.setProperty("last_unique_id",1);
}