Unique ID counter

Question

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 is here.

I'm looking to add a column in that script to generate a uniqueID counter, so that when a new form is submitted, the counter will see previous uniqueID and increment by 1. Idea would be blank spreadsheet with a header row. The script runs on form submit, see A2 is blank and inserts 1. Next submitted form will see 2 in A2 add 1 and put 3 in A3 and so on.

Right now I'm working on just the counter add 1 to the number in the cell. I know as I develop this piece I'll need to grab last row, then find cell value and add to, but beacuse I'm new at GAS, i'm teaching myself as I go, so I"m trying to build slowly to, well you get the idea.

Here is a basic start and I wanted to get some feedback if this is a right direction to start..

function counter() {
   //Grabs the range and pinpoint cell
   var ssRange = SpreadsheetApp.getActiveSheet().getRange(1,1,1,1);
   //Grab the cell value of A1
   var value = ssRange.getValue();
   //if statement to check value in A1 and if it is null, undefined, false, 0, NAN
if (!value) {
  //if the cell is null, undefined, false, 0, NAN sets value to 1
   var setCell = ssRange.setValue(1);
}
  //if not updates the cell by getting the value and adding 1 to it
 else {
   var updateCell = ssRange.setValue(value + 1);  
}
}

EDIT (updated code to use Phil Bozak tip of .setProperty & .getProperty

I'm still testing on how this code handles if the MYID that inserted into the spreadsheet gets manually changed as you mentioned. I need to ensure the MYID stay unique. Have to think on that.. Here is the code.. Any feedback is appreciated!

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();
  var setUniqueID = ScriptProperties.setProperty("MYID",1);
  var getUniqueID = ScriptProperties.getProperty("MYID");

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

  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 setLast = sSheet.getRange(lastRow+1,1,1,1).setValue(getUniqueID + 1); 
    setLast;
  }
  }

Answer 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.
}

Answer 2

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

Answer 3

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